> 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