You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "David Wayne Birdsall (JIRA)" <ji...@apache.org> on 2015/12/01 22:55:11 UTC
[jira] [Comment Edited] (TRAFODION-1641) Apparent inappropriate
choice of MDAM in query plan
[ https://issues.apache.org/jira/browse/TRAFODION-1641?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15034657#comment-15034657 ]
David Wayne Birdsall edited comment on TRAFODION-1641 at 12/1/15 9:55 PM:
--------------------------------------------------------------------------
I've had the opportunity to do some follow-on testing.
Test result summary: I compared two instances, one with this fix and one without. I created two test tables, differing only in their primary key order (to vary the UECs of leading columns). I ran the same set of queries against both tables, and on both instances. The Optimizer chose MDAM for the same queries in both instances. (That is, I did not encounter an example where the Optimizer used to pick MDAM but now does not with the fix. Though I do believe such examples can be constructed.) For most queries, the Optimizer chose the same disjuncts in the MDAM plans, however in a small subset of queries on one of the tables, it chose disjuncts on just the first key column with the fix as opposed to all three columns without the fix. Average execution times for those queries were on the order of 20 to 100 times better, showing that the fix picked better plans in these cases. For queries where the same disjuncts were chosen, execution times varied by about a factor of 2, which controls for the difference in the two execution instances.
Conclusion: No further work is needed at this time, though I continue to be concerned about RangeSpec logic unilaterally picking all columns for an MDAM plan.
Details: The script used to create and populate the tables is shown below. For table TD, the primary key columns are "_SALT_", A, B and C, with UECs of 4, 10, 100 and 2000 respectively. For table TE, the primary key columns are "_SALT_", A, B and C, with UECs of 4, 2000, 100 and 10 respectively. Expectation is that MDAM tends to use the same or fewer key columns in table TE than TD, due to the higher UEC of the leading columns. The set of queries tested is also shown below. The set for which MDAM chose disjuncts on just "_SALT_" and A with the fix rather than all columns without the fix is shown after that.
Script to create and populate tables:
?section create
drop table if exists td;
drop table if exists te;
CREATE TABLE TD
(
A INT NO DEFAULT NOT NULL NOT DROPPABLE
SERIALIZED
, B INT NO DEFAULT NOT NULL NOT DROPPABLE
SERIALIZED
, C INT NO DEFAULT NOT NULL NOT DROPPABLE
SERIALIZED
, D INT NO DEFAULT NOT NULL NOT DROPPABLE
SERIALIZED
, PRIMARY KEY (A ASC, B ASC, C ASC)
)
SALT USING 4 PARTITIONS
;
create table te like td;
?section populate
-- UEC of td.a is 10, td.b is 100, td.c is 2000
upsert using load into td
select x1, x2+10*x3, x4+10*x5+100*x6+1000*x7, x1+x2+10*x3+x4+10*x5+100*x6+1000*x7
-- the from clause below creates 2,000,000 rows, the cross product of
-- 6 copies of { 0, ... 9 } and one copy of { 0, 1 }
from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) T(x1)
transpose 0,1,2,3,4,5,6,7,8,9 as x2
transpose 0,1,2,3,4,5,6,7,8,9 as x3
transpose 0,1,2,3,4,5,6,7,8,9 as x4
transpose 0,1,2,3,4,5,6,7,8,9 as x5
transpose 0,1,2,3,4,5,6,7,8,9 as x6
transpose 0,1 as x7;
update statistics for table td on every column;
upsert using load into te
select c,b,a,d from td; -- reverse UEC order
update statistics for table te on every column;
-- you'll need to exit sqlci for the new stats to take effect
The set of queries tested:
select * from td where b > 97 and (c = 5 or c = 25)
select * from te where b > 97 and (c = 5 or c = 25)
select * from td where b > 97 and (c = 5 or c = 25)
select * from te where b > 97 and (c = 5 or c = 25)
select * from td where a = 4 and b > 97 and (c = 5 or c = 25)
select * from te where a = 4 and b > 97 and (c = 5 or c = 25)
select * from td where a = 4 and b > 97 and (c = 5 or c = 25)
select * from te where a = 4 and b > 97 and (c = 5 or c = 25)
select * from td where b = 20
select * from te where b = 20
select * from td where b = 20
select * from te where b = 20
select * from td where a < 10 and b = 20
select * from te where a < 10 and b = 20
select * from td where a < 10 and b = 20
select * from te where a < 10 and b = 20
select * from td where a > 1 and a < 10 and b = 20
select * from te where a > 1 and a < 10 and b = 20
select * from td where a > 1 and a < 10 and b = 20
select * from te where a > 1 and a < 10 and b = 20
select * from td where a > 2 and a < 10 and b = 20
select * from te where a > 2 and a < 10 and b = 20
select * from td where a > 2 and a < 10 and b = 20
select * from te where a > 2 and a < 10 and b = 20
select * from td where a > 3 and a < 10 and b = 20
select * from te where a > 3 and a < 10 and b = 20
select * from td where a > 3 and a < 10 and b = 20
select * from te where a > 3 and a < 10 and b = 20
select * from td where a > 4 and a < 10 and b = 20
select * from te where a > 4 and a < 10 and b = 20
select * from td where a > 4 and a < 10 and b = 20
select * from te where a > 4 and a < 10 and b = 20
select * from td where a > 5 and a < 10 and b = 20
select * from te where a > 5 and a < 10 and b = 20
select * from td where a > 5 and a < 10 and b = 20
select * from te where a > 5 and a < 10 and b = 20
select * from td where a > 6 and a < 10 and b = 20
select * from te where a > 6 and a < 10 and b = 20
select * from td where a > 6 and a < 10 and b = 20
select * from te where a > 6 and a < 10 and b = 20
select * from td where a > 7 and a < 10 and b = 20
select * from te where a > 7 and a < 10 and b = 20
select * from td where a > 7 and a < 10 and b = 20
select * from te where a > 7 and a < 10 and b = 20
select * from td where a > 8 and a < 10 and b = 20
select * from te where a > 8 and a < 10 and b = 20
select * from td where a > 8 and a < 10 and b = 20
select * from te where a > 8 and a < 10 and b = 20
select * from td where a > 9 and a < 10 and b = 20
select * from te where a > 9 and a < 10 and b = 20
select * from td where a > 9 and a < 10 and b = 20
select * from te where a > 9 and a < 10 and b = 20
select * from td where a > 10 and a < 10 and b = 20
select * from te where a > 10 and a < 10 and b = 20
select * from td where a > 10 and a < 10 and b = 20
select * from te where a > 10 and a < 10 and b = 20
select * from td where c = 5
select * from te where c = 5
select * from td where c = 5
select * from te where c = 5
select * from td where a < 10 and c = 5
select * from te where a < 10 and c = 5
select * from td where a < 10 and c = 5
select * from te where a < 10 and c = 5
select * from td where a > 1 and a < 10 and c = 5
select * from te where a > 1 and a < 10 and c = 5
select * from td where a > 1 and a < 10 and c = 5
select * from te where a > 1 and a < 10 and c = 5
select * from td where a > 2 and a < 10 and c = 5
select * from te where a > 2 and a < 10 and c = 5
select * from td where a > 2 and a < 10 and c = 5
select * from te where a > 2 and a < 10 and c = 5
select * from td where a > 3 and a < 10 and c = 5
select * from te where a > 3 and a < 10 and c = 5
select * from td where a > 3 and a < 10 and c = 5
select * from te where a > 3 and a < 10 and c = 5
select * from td where a > 4 and a < 10 and c = 5
select * from te where a > 4 and a < 10 and c = 5
select * from td where a > 4 and a < 10 and c = 5
select * from te where a > 4 and a < 10 and c = 5
select * from td where a > 5 and a < 10 and c = 5
select * from te where a > 5 and a < 10 and c = 5
select * from td where a > 5 and a < 10 and c = 5
select * from te where a > 5 and a < 10 and c = 5
select * from td where a > 6 and a < 10 and c = 5
select * from te where a > 6 and a < 10 and c = 5
select * from td where a > 6 and a < 10 and c = 5
select * from te where a > 6 and a < 10 and c = 5
select * from td where a > 7 and a < 10 and c = 5
select * from te where a > 7 and a < 10 and c = 5
select * from td where a > 7 and a < 10 and c = 5
select * from te where a > 7 and a < 10 and c = 5
select * from td where a > 8 and a < 10 and c = 5
select * from te where a > 8 and a < 10 and c = 5
select * from td where a > 8 and a < 10 and c = 5
select * from te where a > 8 and a < 10 and c = 5
select * from td where a > 9 and a < 10 and c = 5
select * from te where a > 9 and a < 10 and c = 5
select * from td where a > 9 and a < 10 and c = 5
select * from te where a > 9 and a < 10 and c = 5
select * from td where a > 10 and a < 10 and c = 5
select * from te where a > 10 and a < 10 and c = 5
select * from td where a > 10 and a < 10 and c = 5
select * from te where a > 10 and a < 10 and c = 5
select * from td where c = 5
select * from te where c = 5
select * from td where c = 5
select * from te where c = 5
select * from td where b < 10 and c = 5
select * from te where b < 10 and c = 5
select * from td where b < 10 and c = 5
select * from te where b < 10 and c = 5
select * from td where b > 1 and b < 10 and c = 5
select * from te where b > 1 and b < 10 and c = 5
select * from td where b > 1 and b < 10 and c = 5
select * from te where b > 1 and b < 10 and c = 5
select * from td where b > 2 and b < 10 and c = 5
select * from te where b > 2 and b < 10 and c = 5
select * from td where b > 2 and b < 10 and c = 5
select * from te where b > 2 and b < 10 and c = 5
select * from td where b > 3 and b < 10 and c = 5
select * from te where b > 3 and b < 10 and c = 5
select * from td where b > 3 and b < 10 and c = 5
select * from te where b > 3 and b < 10 and c = 5
select * from td where b > 4 and b < 10 and c = 5
select * from te where b > 4 and b < 10 and c = 5
select * from td where b > 4 and b < 10 and c = 5
select * from te where b > 4 and b < 10 and c = 5
select * from td where b > 5 and b < 10 and c = 5
select * from te where b > 5 and b < 10 and c = 5
select * from td where b > 5 and b < 10 and c = 5
select * from te where b > 5 and b < 10 and c = 5
select * from td where b > 6 and b < 10 and c = 5
select * from te where b > 6 and b < 10 and c = 5
select * from td where b > 6 and b < 10 and c = 5
select * from te where b > 6 and b < 10 and c = 5
select * from td where b > 7 and b < 10 and c = 5
select * from te where b > 7 and b < 10 and c = 5
select * from td where b > 7 and b < 10 and c = 5
select * from te where b > 7 and b < 10 and c = 5
select * from td where b > 8 and b < 10 and c = 5
select * from te where b > 8 and b < 10 and c = 5
select * from td where b > 8 and b < 10 and c = 5
select * from te where b > 8 and b < 10 and c = 5
select * from td where b > 9 and b < 10 and c = 5
select * from te where b > 9 and b < 10 and c = 5
select * from td where b > 9 and b < 10 and c = 5
select * from te where b > 9 and b < 10 and c = 5
select * from td where b > 10 and b < 10 and c = 5
select * from te where b > 10 and b < 10 and c = 5
select * from td where b > 10 and b < 10 and c = 5
select * from te where b > 10 and b < 10 and c = 5
select * from td where c = 5
select * from te where c = 5
select * from td where c = 5
select * from te where c = 5
select * from td where b = 10 and c = 5
select * from te where b = 10 and c = 5
select * from td where b = 10 and c = 5
select * from te where b = 10 and c = 5
select * from td where b in (1,10) and c = 5
select * from te where b in (1,10) and c = 5
select * from td where b in (1,10) and c = 5
select * from te where b in (1,10) and c = 5
select * from td where b in (1,3,10) and c = 5
select * from te where b in (1,3,10) and c = 5
select * from td where b in (1,3,10) and c = 5
select * from te where b in (1,3,10) and c = 5
select * from td where b in (1,3,5,10) and c = 5
select * from te where b in (1,3,5,10) and c = 5
select * from td where b in (1,3,5,10) and c = 5
select * from te where b in (1,3,5,10) and c = 5
select * from td where b in (1,3,5,7,10) and c = 5
select * from te where b in (1,3,5,7,10) and c = 5
select * from td where b in (1,3,5,7,10) and c = 5
select * from te where b in (1,3,5,7,10) and c = 5
select * from td where b in (1,3,5,7,10) and c in (5,7)
select * from te where b in (1,3,5,7,10) and c in (5,7)
select * from td where b in (1,3,5,7,10) and c in (5,7)
select * from te where b in (1,3,5,7,10) and c in (5,7)
select * from td where b in (1,3,5,7,10) and c in (1,5,7)
select * from te where b in (1,3,5,7,10) and c in (1,5,7)
select * from td where b in (1,3,5,7,10) and c in (1,5,7)
select * from te where b in (1,3,5,7,10) and c in (1,5,7)
select * from td where b in (1,3,5,7,10) and c in (1,5,7,9)
select * from te where b in (1,3,5,7,10) and c in (1,5,7,9)
select * from td where b in (1,3,5,7,10) and c in (1,5,7,9)
select * from te where b in (1,3,5,7,10) and c in (1,5,7,9)
select * from td where (b = 2 and c = 2) or (b = 3 and c = 3)
select * from te where (b = 2 and c = 2) or (b = 3 and c = 3)
select * from td where (b = 2 and c = 2) or (b = 3 and c = 3)
select * from te where (b = 2 and c = 2) or (b = 3 and c = 3)
select * from td where (b = 2 and c in (2,3)) or (b = 3 and c = 3)
select * from te where (b = 2 and c in (2,3)) or (b = 3 and c = 3)
select * from td where (b = 2 and c in (2,3)) or (b = 3 and c = 3)
select * from te where (b = 2 and c in (2,3)) or (b = 3 and c = 3)
select * from td where (b in (2,4) and c in (2,3)) or (b = 3 and c = 3)
select * from te where (b in (2,4) and c in (2,3)) or (b = 3 and c = 3)
select * from td where (b in (2,4) and c in (2,3)) or (b = 3 and c = 3)
select * from te where (b in (2,4) and c in (2,3)) or (b = 3 and c = 3)
The set of queries where, with the fix, MDAM was chosen on just "_SALT_" and A, whereas before it was chosen on all key columns giving a much slower execution time:
select * from te where a < 10 and c = 5
select * from te where a > 1 and a < 10 and c = 5
select * from te where a > 2 and a < 10 and c = 5
select * from te where a > 3 and a < 10 and c = 5
select * from te where a > 4 and a < 10 and c = 5
select * from te where a > 5 and a < 10 and c = 5
select * from te where a > 6 and a < 10 and c = 5
select * from te where a > 7 and a < 10 and c = 5
select * from te where a > 8 and a < 10 and c = 5
was (Author: davebirdsall):
I've had the opportunity to do some follow-on testing.
Test result summary: I compared two instances, one with this fix and one without. I created two test tables, differing only in their primary key order (to vary the UECs of leading columns). I ran the same set of queries against both tables, and on both instances. The Optimizer chose MDAM for the same queries in both instances. (That is, I did not encounter an example where the Optimizer used to pick MDAM but now does not with the fix. Though I do believe such examples can be constructed.) For most queries, the Optimizer chose the same disjuncts in the MDAM plans, however in a small subset of queries on one of the tables, it chose disjuncts on just the first key column with the fix as opposed to all three columns without the fix. Average execution times for those queries were on the order of 20 to 100 times better, showing that the fix picked better plans in these cases. For queries where the same disjuncts were chosen, execution times varied by about a factor of 2, which controls for the difference in the two execution instances.
Conclusion: No further work is needed at this time, though I continue to be concerned about RangeSpec logic unilaterally picking all columns for an MDAM plan.
Details: The script used to create and populate the tables is shown below. For table TD, the primary key columns are "_SALT_", A, B and C, with UECs of 4, 10, 100 and 1000 respectively. For table TE, the primary key columns are "_SALT_", A, B and C, with UECs of 4, 1000, 100 and 10 respectively. Expectation is that MDAM tends to use the same or fewer key columns in table TE than TD, due to the higher UEC of the leading columns. The set of queries tested is also shown below. The set for which MDAM chose disjuncts on just "_SALT_" and A with the fix rather than all columns without the fix is shown after that.
Script to create and populate tables:
?section create
drop table if exists td;
drop table if exists te;
CREATE TABLE TD
(
A INT NO DEFAULT NOT NULL NOT DROPPABLE
SERIALIZED
, B INT NO DEFAULT NOT NULL NOT DROPPABLE
SERIALIZED
, C INT NO DEFAULT NOT NULL NOT DROPPABLE
SERIALIZED
, D INT NO DEFAULT NOT NULL NOT DROPPABLE
SERIALIZED
, PRIMARY KEY (A ASC, B ASC, C ASC)
)
SALT USING 4 PARTITIONS
;
create table te like td;
?section populate
-- UEC of td.a is 10, td.b is 100, td.c is 2000
upsert using load into td
select x1, x2+10*x3, x4+10*x5+100*x6+1000*x7, x1+x2+10*x3+x4+10*x5+100*x6+1000*x7
-- the from clause below creates 2,000,000 rows, the cross product of
-- 6 copies of { 0, ... 9 } and one copy of { 0, 1 }
from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) T(x1)
transpose 0,1,2,3,4,5,6,7,8,9 as x2
transpose 0,1,2,3,4,5,6,7,8,9 as x3
transpose 0,1,2,3,4,5,6,7,8,9 as x4
transpose 0,1,2,3,4,5,6,7,8,9 as x5
transpose 0,1,2,3,4,5,6,7,8,9 as x6
transpose 0,1 as x7;
update statistics for table td on every column;
upsert using load into te
select c,b,a,d from td; -- reverse UEC order
update statistics for table te on every column;
-- you'll need to exit sqlci for the new stats to take effect
The set of queries tested:
select * from td where b > 97 and (c = 5 or c = 25)
select * from te where b > 97 and (c = 5 or c = 25)
select * from td where b > 97 and (c = 5 or c = 25)
select * from te where b > 97 and (c = 5 or c = 25)
select * from td where a = 4 and b > 97 and (c = 5 or c = 25)
select * from te where a = 4 and b > 97 and (c = 5 or c = 25)
select * from td where a = 4 and b > 97 and (c = 5 or c = 25)
select * from te where a = 4 and b > 97 and (c = 5 or c = 25)
select * from td where b = 20
select * from te where b = 20
select * from td where b = 20
select * from te where b = 20
select * from td where a < 10 and b = 20
select * from te where a < 10 and b = 20
select * from td where a < 10 and b = 20
select * from te where a < 10 and b = 20
select * from td where a > 1 and a < 10 and b = 20
select * from te where a > 1 and a < 10 and b = 20
select * from td where a > 1 and a < 10 and b = 20
select * from te where a > 1 and a < 10 and b = 20
select * from td where a > 2 and a < 10 and b = 20
select * from te where a > 2 and a < 10 and b = 20
select * from td where a > 2 and a < 10 and b = 20
select * from te where a > 2 and a < 10 and b = 20
select * from td where a > 3 and a < 10 and b = 20
select * from te where a > 3 and a < 10 and b = 20
select * from td where a > 3 and a < 10 and b = 20
select * from te where a > 3 and a < 10 and b = 20
select * from td where a > 4 and a < 10 and b = 20
select * from te where a > 4 and a < 10 and b = 20
select * from td where a > 4 and a < 10 and b = 20
select * from te where a > 4 and a < 10 and b = 20
select * from td where a > 5 and a < 10 and b = 20
select * from te where a > 5 and a < 10 and b = 20
select * from td where a > 5 and a < 10 and b = 20
select * from te where a > 5 and a < 10 and b = 20
select * from td where a > 6 and a < 10 and b = 20
select * from te where a > 6 and a < 10 and b = 20
select * from td where a > 6 and a < 10 and b = 20
select * from te where a > 6 and a < 10 and b = 20
select * from td where a > 7 and a < 10 and b = 20
select * from te where a > 7 and a < 10 and b = 20
select * from td where a > 7 and a < 10 and b = 20
select * from te where a > 7 and a < 10 and b = 20
select * from td where a > 8 and a < 10 and b = 20
select * from te where a > 8 and a < 10 and b = 20
select * from td where a > 8 and a < 10 and b = 20
select * from te where a > 8 and a < 10 and b = 20
select * from td where a > 9 and a < 10 and b = 20
select * from te where a > 9 and a < 10 and b = 20
select * from td where a > 9 and a < 10 and b = 20
select * from te where a > 9 and a < 10 and b = 20
select * from td where a > 10 and a < 10 and b = 20
select * from te where a > 10 and a < 10 and b = 20
select * from td where a > 10 and a < 10 and b = 20
select * from te where a > 10 and a < 10 and b = 20
select * from td where c = 5
select * from te where c = 5
select * from td where c = 5
select * from te where c = 5
select * from td where a < 10 and c = 5
select * from te where a < 10 and c = 5
select * from td where a < 10 and c = 5
select * from te where a < 10 and c = 5
select * from td where a > 1 and a < 10 and c = 5
select * from te where a > 1 and a < 10 and c = 5
select * from td where a > 1 and a < 10 and c = 5
select * from te where a > 1 and a < 10 and c = 5
select * from td where a > 2 and a < 10 and c = 5
select * from te where a > 2 and a < 10 and c = 5
select * from td where a > 2 and a < 10 and c = 5
select * from te where a > 2 and a < 10 and c = 5
select * from td where a > 3 and a < 10 and c = 5
select * from te where a > 3 and a < 10 and c = 5
select * from td where a > 3 and a < 10 and c = 5
select * from te where a > 3 and a < 10 and c = 5
select * from td where a > 4 and a < 10 and c = 5
select * from te where a > 4 and a < 10 and c = 5
select * from td where a > 4 and a < 10 and c = 5
select * from te where a > 4 and a < 10 and c = 5
select * from td where a > 5 and a < 10 and c = 5
select * from te where a > 5 and a < 10 and c = 5
select * from td where a > 5 and a < 10 and c = 5
select * from te where a > 5 and a < 10 and c = 5
select * from td where a > 6 and a < 10 and c = 5
select * from te where a > 6 and a < 10 and c = 5
select * from td where a > 6 and a < 10 and c = 5
select * from te where a > 6 and a < 10 and c = 5
select * from td where a > 7 and a < 10 and c = 5
select * from te where a > 7 and a < 10 and c = 5
select * from td where a > 7 and a < 10 and c = 5
select * from te where a > 7 and a < 10 and c = 5
select * from td where a > 8 and a < 10 and c = 5
select * from te where a > 8 and a < 10 and c = 5
select * from td where a > 8 and a < 10 and c = 5
select * from te where a > 8 and a < 10 and c = 5
select * from td where a > 9 and a < 10 and c = 5
select * from te where a > 9 and a < 10 and c = 5
select * from td where a > 9 and a < 10 and c = 5
select * from te where a > 9 and a < 10 and c = 5
select * from td where a > 10 and a < 10 and c = 5
select * from te where a > 10 and a < 10 and c = 5
select * from td where a > 10 and a < 10 and c = 5
select * from te where a > 10 and a < 10 and c = 5
select * from td where c = 5
select * from te where c = 5
select * from td where c = 5
select * from te where c = 5
select * from td where b < 10 and c = 5
select * from te where b < 10 and c = 5
select * from td where b < 10 and c = 5
select * from te where b < 10 and c = 5
select * from td where b > 1 and b < 10 and c = 5
select * from te where b > 1 and b < 10 and c = 5
select * from td where b > 1 and b < 10 and c = 5
select * from te where b > 1 and b < 10 and c = 5
select * from td where b > 2 and b < 10 and c = 5
select * from te where b > 2 and b < 10 and c = 5
select * from td where b > 2 and b < 10 and c = 5
select * from te where b > 2 and b < 10 and c = 5
select * from td where b > 3 and b < 10 and c = 5
select * from te where b > 3 and b < 10 and c = 5
select * from td where b > 3 and b < 10 and c = 5
select * from te where b > 3 and b < 10 and c = 5
select * from td where b > 4 and b < 10 and c = 5
select * from te where b > 4 and b < 10 and c = 5
select * from td where b > 4 and b < 10 and c = 5
select * from te where b > 4 and b < 10 and c = 5
select * from td where b > 5 and b < 10 and c = 5
select * from te where b > 5 and b < 10 and c = 5
select * from td where b > 5 and b < 10 and c = 5
select * from te where b > 5 and b < 10 and c = 5
select * from td where b > 6 and b < 10 and c = 5
select * from te where b > 6 and b < 10 and c = 5
select * from td where b > 6 and b < 10 and c = 5
select * from te where b > 6 and b < 10 and c = 5
select * from td where b > 7 and b < 10 and c = 5
select * from te where b > 7 and b < 10 and c = 5
select * from td where b > 7 and b < 10 and c = 5
select * from te where b > 7 and b < 10 and c = 5
select * from td where b > 8 and b < 10 and c = 5
select * from te where b > 8 and b < 10 and c = 5
select * from td where b > 8 and b < 10 and c = 5
select * from te where b > 8 and b < 10 and c = 5
select * from td where b > 9 and b < 10 and c = 5
select * from te where b > 9 and b < 10 and c = 5
select * from td where b > 9 and b < 10 and c = 5
select * from te where b > 9 and b < 10 and c = 5
select * from td where b > 10 and b < 10 and c = 5
select * from te where b > 10 and b < 10 and c = 5
select * from td where b > 10 and b < 10 and c = 5
select * from te where b > 10 and b < 10 and c = 5
select * from td where c = 5
select * from te where c = 5
select * from td where c = 5
select * from te where c = 5
select * from td where b = 10 and c = 5
select * from te where b = 10 and c = 5
select * from td where b = 10 and c = 5
select * from te where b = 10 and c = 5
select * from td where b in (1,10) and c = 5
select * from te where b in (1,10) and c = 5
select * from td where b in (1,10) and c = 5
select * from te where b in (1,10) and c = 5
select * from td where b in (1,3,10) and c = 5
select * from te where b in (1,3,10) and c = 5
select * from td where b in (1,3,10) and c = 5
select * from te where b in (1,3,10) and c = 5
select * from td where b in (1,3,5,10) and c = 5
select * from te where b in (1,3,5,10) and c = 5
select * from td where b in (1,3,5,10) and c = 5
select * from te where b in (1,3,5,10) and c = 5
select * from td where b in (1,3,5,7,10) and c = 5
select * from te where b in (1,3,5,7,10) and c = 5
select * from td where b in (1,3,5,7,10) and c = 5
select * from te where b in (1,3,5,7,10) and c = 5
select * from td where b in (1,3,5,7,10) and c in (5,7)
select * from te where b in (1,3,5,7,10) and c in (5,7)
select * from td where b in (1,3,5,7,10) and c in (5,7)
select * from te where b in (1,3,5,7,10) and c in (5,7)
select * from td where b in (1,3,5,7,10) and c in (1,5,7)
select * from te where b in (1,3,5,7,10) and c in (1,5,7)
select * from td where b in (1,3,5,7,10) and c in (1,5,7)
select * from te where b in (1,3,5,7,10) and c in (1,5,7)
select * from td where b in (1,3,5,7,10) and c in (1,5,7,9)
select * from te where b in (1,3,5,7,10) and c in (1,5,7,9)
select * from td where b in (1,3,5,7,10) and c in (1,5,7,9)
select * from te where b in (1,3,5,7,10) and c in (1,5,7,9)
select * from td where (b = 2 and c = 2) or (b = 3 and c = 3)
select * from te where (b = 2 and c = 2) or (b = 3 and c = 3)
select * from td where (b = 2 and c = 2) or (b = 3 and c = 3)
select * from te where (b = 2 and c = 2) or (b = 3 and c = 3)
select * from td where (b = 2 and c in (2,3)) or (b = 3 and c = 3)
select * from te where (b = 2 and c in (2,3)) or (b = 3 and c = 3)
select * from td where (b = 2 and c in (2,3)) or (b = 3 and c = 3)
select * from te where (b = 2 and c in (2,3)) or (b = 3 and c = 3)
select * from td where (b in (2,4) and c in (2,3)) or (b = 3 and c = 3)
select * from te where (b in (2,4) and c in (2,3)) or (b = 3 and c = 3)
select * from td where (b in (2,4) and c in (2,3)) or (b = 3 and c = 3)
select * from te where (b in (2,4) and c in (2,3)) or (b = 3 and c = 3)
The set of queries where, with the fix, MDAM was chosen on just "_SALT_" and A, whereas before it was chosen on all key columns giving a much slower execution time:
select * from te where a < 10 and c = 5
select * from te where a > 1 and a < 10 and c = 5
select * from te where a > 2 and a < 10 and c = 5
select * from te where a > 3 and a < 10 and c = 5
select * from te where a > 4 and a < 10 and c = 5
select * from te where a > 5 and a < 10 and c = 5
select * from te where a > 6 and a < 10 and c = 5
select * from te where a > 7 and a < 10 and c = 5
select * from te where a > 8 and a < 10 and c = 5
> Apparent inappropriate choice of MDAM in query plan
> ---------------------------------------------------
>
> Key: TRAFODION-1641
> URL: https://issues.apache.org/jira/browse/TRAFODION-1641
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 1.3-incubating
> Reporter: David Wayne Birdsall
> Assignee: David Wayne Birdsall
> Fix For: 2.0-incubating
>
>
> Details to be supplied.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)