> one year ago i ask advise to optimize this SQL : > > CREATE TABLE HASH( > ID INTEGER NOT NULL, > x1_y1 SMALLINT NOT NULL, > x1_y2 SMALLINT NOT NULL, > x1_y3 SMALLINT NOT NULL, > x1_y4 SMALLINT NOT NULL, > x1_y5 SMALLINT NOT NULL > PRIMARY KEY (ID) > ); > CREATE INDEX HASH_X1_Y1_IDX ON HASH (X1_Y1); > CREATE INDEX HASH_X1_Y2_IDX ON HASH (X1_Y2); > CREATE INDEX HASH_X1_Y3_IDX ON HASH (X1_Y3); > CREATE INDEX HASH_X1_Y4_IDX ON HASH (X1_Y4); > CREATE INDEX HASH_X1_Y5_IDX ON HASH (X1_Y5); > > fill it with 20 millions rows > > ... > > and philippe makowski sugest me this : > > CREATE ASC INDEX HASH_IDX ON HASH (X1_Y1, X1_Y2, X1_Y3, X1_Y4, X1_Y5); > > Select > ID > from > HASH > where > X1_Y1 IN > (10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35 > ,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50) > and > x1_y2 >= <#randomnumber2> - 20 and > x1_y2 <= <#randomnumber2> + 20 and > x1_y3 >= <#randomnumber3> - 20 and > x1_y3 <= <#randomnumber3> + 20 and > x1_y4 >= <#randomnumber4> - 20 and > x1_y4 <= <#randomnumber4> + 20 and > x1_y5 >= <#randomnumber5> - 20 and > x1_y5 <= <#randomnumber5> + 20; > > that was very (very) much efficient (10x more faster) !! > this is based on > http://explainextended.com/2010/05/19/things-sql-needs-determining- > range-cardinality/ > > now with this strategy i don't remenbered, but did i need to keep the index > CREATE INDEX HASH_X1_Y1_IDX ON HASH (X1_Y1); CREATE INDEX > HASH_X1_Y2_IDX ON HASH (X1_Y2); CREATE INDEX HASH_X1_Y3_IDX ON > HASH (X1_Y3); CREATE INDEX HASH_X1_Y4_IDX ON HASH (X1_Y4); CREATE > INDEX HASH_X1_Y5_IDX ON HASH (X1_Y5); > > or i can remove them ?
1 - What PLAN does the engine generate for the query? 2 - What other types of queries to do run? Do you ever search for rows without X1_Y1, like X1_Y2 and X1_Y3 only? Stephane, it seems you have been asking this list to think for you and have not tried things for yourself... Sean
