The scan optimizer picks the MDAM scan or subset scan based on the *cost*. For MDAm to win, the low UEC on the leading key columns is a pre-condition.
Thanks --Qifan On Mon, Mar 28, 2016 at 10:23 AM, Rohit <rohit.j...@esgyn.com> wrote: > 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 <dave.birds...@esgyn.com> > Date: 03/28/2016 10:12 AM (GMT-06:00) > To: user@trafodion.incubator.apache.org > 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:ming....@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 > > > -- Regards, --Qifan
