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]
