Dne 20.07.2010 14:39, Andreas Rulle napsal(a):

>
>
>
> Hello Sean,
>
> thank you for your hints!
>
> They have motivated us to create a fresh database with a Firebird
> 2.1.3 server.
>
> And then the following statements have then been executed:
>
> a) The two tables:
>
> create table T1 (s1 integer, s2 varchar(50));
>
> create table T2 (s1 integer, s2 varchar(50));
>
> b) A really small data set
>
> insert into T1 values(1,'Wert 1');
> insert into T1 values(2,'Wert 2');
> insert into T1 values(3,'Wert 3');
>
> insert into T2 values(1,'Wert 1');
> insert into T2 values(2,'Wert 2');
> insert into T2 values(3,'Wert 3');
>
> c) The indices
>
> create unique index IDX_T11 on T1 (s1);
> create unique index IDX_T12 on T2 (s1);
>
> d) A left join
>
> select t1.s1 from t1 left join t2 on t1.s1=t2.s1
>
> FlameFobin reports that the following plan has been used:
>
> PLAN JOIN (T1 NATURAL, T2 INDEX (IDX_T12))
>
> Yes, the optimizer takes the second index, but not the first one.
>

Why FB should use index on T1 when it must go through all records in T1?
IMHO natural scan is more effective here. Try this:

select t1.s1 from t1 left join t2 on t1.s1=t2.s1
WHERE T1.S1 = 1

Now you get what you're expecting.

Milan


[Non-text portions of this message have been removed]