At 10:56 PM 16-09-01 +0200, you wrote:

>Hi,
>i have two tables:
>create table t_auftrag (id_auftrag integer NOT NULL primary key);
>create table t_apos (id_apos integer NOT NULL primary key, f_id_auftrag integer NOT NULL);
>ALTER TABLE T_APOS
> ADD CONSTRAINT fk_apos_id_auftrag FOREIGN KEY
> (F_ID_AUFTRAG)
> REFERENCES T_AUFTRAG
> (ID_AUFTRAG)
> ON DELETE NO ACTION
> ON UPDATE NO ACTION;
>Two Queries:
>1) SELECT id_auftrag FROM t_auftrag WHERE id_auftrag in (select distinct f_id_auftrag from t_apos where f_id_auftrag = 2);
>with PLAN:
>PLAN SORT ((T_APOS INDEX (RDB$FOREIGN13)))PLAN (T_AUFTRAG NATURAL)
>2) SELECT id_auftrag FROM t_auftrag WHERE id_auftrag in (2);
>with PLAN:
>PLAN (T_AUFTRAG2 INDEX (RDB$PRIMARY4))
>
>Why is in the first query the index on the primary field of t_auftrag not used ?
>Can i get force Firebird to use it ?

The index is not used when the IN (....) predicate is used for your selection criteria.
The second query is a little bit ropy as well - IN (2) isn't logical. ..WHERE id_auftrag=2 is logically correct.

If you want a query that selects only the t_auftrag set that has matching members in t_apos, try an inner join:

SELECT a.id_auftrag FROM a.t_auftrag
join t_apos p
on a.id_auftrag = p.f_id_auftrag
where p.f_id_auftrag = 2

This should show a plan that uses both indexes. If there are no matching rows in t_apos, you won't get any output.

Use SELECT DISTINCT if you want one row regardless of how many matches are found in t_apos; and drop the WHERE clause if you want to find out which t_auftrag rows have matches in t_apos.

Regards,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________