答复: MDAM on index
Thanks all, I want to confirm if all conditions meet, index access can also use MDAM. it is supported and that is great! I believe in practice if all PK and Index still cannot cover the query pattern, it is time to check the design ☺ Thanks, Ming 发件人: Qifan Chen [mailto:qifan.c...@esgyn.com] 发送时间: 2016年3月28日 23:27 收件人: user@trafodion.incubator.apache.org 抄送: Dave Birdsall 主题: Re: MDAM on index 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 mailto: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 mailto:dave.birds...@esgyn.com>> Date: 03/28/2016 10:12 AM (GMT-06:00) To: user@trafodion.incubator.apache.org<mailto: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<mailto:ming@esgyn.cn>] Sent: Monday, March 28, 2016 5:12 AM To: user@trafodion.incubator.apache.org<mailto: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
Re: MDAM on index
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 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 > 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
RE: MDAM on index
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 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
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
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
