I have no idea,

but using a JOIN instead of the IN (SELECT .. obtains the same
result and usually works faster because it eliminates the NATURAL
element from the PLAN

Best Regards
Hans

========================================================

Carsten Schaefer 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 ?
> (i 'm using Firebird Beta2 on Win2kSP2)
>
> gruse
> Carsten
>
>
> [Non-text portions of this message have been removed]
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/