And remember, the key available for MDAM in a secondary index includes both the
secondary index columns followed by the primary key columns, or c3, c4, c1, c2
in this case. Same MDAM rules should apply to the secondary index as the
clustering index since its a clustering index too.
Rohit
-------- Original message --------
From: Dave Birdsall <[email protected]>
Date: 03/28/2016 10:12 AM (GMT-06:00)
To: [email protected]
Subject: RE: MDAM on index
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:[email protected]]
Sent: Monday, March 28, 2016 5:12 AM
To: [email protected]
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
