On 08-01-2011 15:59, tempo93fr wrote:

> --- In Firebird-Architect@yahoogroups.com, Dimitry Sibiryakov <sd@...> > Could you provide examples of such queries, please?..
> yes :
> CREATE TABLE TABLEA(
> 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)
> );
> Select
> ID
> from
> TABLEA
> 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;
>
>

What's the problem with this approach, as Firebird can use multiple
indexes in a lookup?

SQL> CREATE INDEX TABLEA_1 ON TABLEA (X1_Y1);
SQL> CREATE INDEX TABLEA_2 ON TABLEA (X1_Y2);
SQL> CREATE INDEX TABLEA_3 ON TABLEA (X1_Y3);
SQL> CREATE INDEX TABLEA_4 ON TABLEA (X1_Y4);
SQL> CREATE INDEX TABLEA_5 ON TABLEA (X1_Y5);

Select
ID
from
TABLEA
where
x1_y1 >= ? - 20 and
x1_y1 <= ? + 20 and
x1_y2 >= ? - 20 and
x1_y2 <= ? + 20 and
x1_y3 >= ? - 20 and
x1_y3 <= ? + 20 and
x1_y4 >= ? - 20 and
x1_y4 <= ? + 20 and
x1_y5 >= ? - 20 and
x1_y5 <= ? + 20;

PLAN (TABLEA INDEX (TABLEA_5, TABLEA_4, TABLEA_3, TABLEA_2, TABLEA_1))


Adriano