select t1.s1 from t1 left join t2 on t1.s1=t2.s1 order by t1.s1

Regards
=========
|| ISMAEL ||
=========

----- Original Message -----
From: Andreas Rulle
To: firebird-support@yahoogroups.com
Sent: Tuesday, July 20, 2010 8:39 AM
Subject: [firebird-support] Re: Firebird 2.1.3 optimizer in does not use index - Email found in subject





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.

e) A left join with a plan

An attempt to suggest the usage of the index of the first table as well

select t1.s1 from t1 left join t2 on t1.s1=t2.s1 PLAN JOIN (T1 INDEX (IDX_T11),T2 INDEX (IDX_T12))

fails with

>Message: isc_dsql_prepare failed

>SQL Message : -284
>index @1 cannot be used in the specified plan

>Engine Code : 335544642
>Engine Message :
>index @1 cannot be used in the specified plan

Does Firebird not use an index for "the left table" of a left outer join?

Greetings

Andreas





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