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 <dave.birds...@esgyn.com> 主题: 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 <rohit.j...@esgyn.com<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 <dave.birds...@esgyn.com<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
