MDAM on index

["Liu, Ming (Ming)" <mi...@esgyn.cn>]

Hi, all,

If we are creating a table t(c1,c2,c3,c4,,c5, primary key(c1,c2)) and then create an index indx on t(c3,c4).
Query 1: select * from t where c2 =10;
Query 2: select * from t where c4 = 10;
I think Query 1 will use MDAM, can Query 2 use MDAM to access indx as well?

Thanks,
Ming

RE: MDAM on index

[Dave Birdsall <da...@esgyn.com>]

Hi,



In principle at least, MDAM should be possible with Query 2. Whether it is
a good plan or not depends on many things: If the UEC of column c3 is high,
then MDAM on the index on C4 may not be a good choice. If the query
accesses other columns in the base table besides c3 and c4, then there is
an extra join using index access which raises the cost. It still might be a
good plan though. For example, if there is a highly selective predicate on
c3 and c4, resulting in just a few accesses to the base table then it still
may be good. Your mileage will vary.



Dave



*From:* Liu, Ming (Ming) [mailto:ming.liu@esgyn.cn]
*Sent:* Monday, March 28, 2016 5:12 AM
*To:* user@trafodion.incubator.apache.org
*Subject:* MDAM on index



Hi, all,



If we are creating a table t(c1,c2,c3,c4,,c5, primary key(c1,c2)) and then
create an index indx on t(c3,c4).

Query 1: select * from t where c2 =10;

Query 2: select * from t where c4 = 10;

I think Query 1 will use MDAM, can Query 2 use MDAM to access indx as well?



Thanks,

Ming