Hello,

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

Select
ID
from
HASH
where
x1_y1 >= <#randomnumber1> - 20 and
x1_y1 <= <#randomnumber1> + 20 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;

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 ?

thanks by advance
stéphane