Dmitry,

> Still, this doesn't explain why I do see it sometimes using an index to
> > support a starts-with join, and he doesn't.
>
> I can hardly guess without a test case.
>
>

Way back in the message chain was an example. If I understood it
correctly, it was something like this:

create table core (coreName varchar(30), coreCode char (2));

insert into core (coreName, coreCode) values ('Bicycle', 'BI');
insert into core (coreName, coreCode) values ('Chair', 'CH');
insert into core (coreName, coreCode) values ('Horse', 'HO');

create table details (detailName varchar(30), detailCode char(6) primary
key);

insert into details (detailName, detailCode) values ('Wheel', 'BI-001');
insert into details (detailName, detailCode) values ('Pedal', 'BI-002');
insert into details (detailName, detailCode) values ('Gear', 'BI-002');
insert into details (detailName, detailCode) values ('Seat', 'CH-001');
insert into details (detailName, detailCode) values ('Arm', 'CH-002');
insert into details (detailName, detailCode) values ('Head', 'HO-001');
insert into details (detailName, detailCode) values ('Hoof', 'HO-002');

This statement uses a partial match on the primary key of details:

select detailName, detailCode from details
where detailCode starting with 'HO';

This statement does not use the partial index match:

select c.coreName, d.detailName, d.detailCode
from core c join details d
on d.detailCode starting with c.coreCode;


The machine I'm using at the moment is not running
Firebird, so I haven't tested this, for which I apologize.
And if this test does use the index, then you'll need
a test with ddl and possibly data from the original author.


Best regards,

Ann


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