---------- Original Message -----------
From: "Rick Debay" <rdebay@...>

> Damn. Anyone know why the heck FB won't use an index in a join with
> 'starting with'? FWIW, I ran the natural part of the query to generate
> a bunch of "starting with 'xyx' or" and appended them to query the
> other table. Luckily I didn't hit any query limits.

------- End of Original Message -------

My experiments on FB 2.1.5 snapshot 18479:

set statistics index IX_BT_REF_FILE_NUM_ASC; -- varchar(50), nothing special
set statistics index IX_DCHRONEXPL_FILENUM; -- varchar(20), nothing special
commit;

select rdb$statistics from rdb$indices where rdb$index_name =
'IX_BT_REF_FILE_NUM_ASC'; -- 0.000062
select rdb$statistics from rdb$indices where rdb$index_name =
'IX_DCHRONEXPL_FILENUM'; -- 0.142857
select count(*) from bt_ref; -- 16211
select count(*) from bt_dchronexpl; -- 16

select * from bt_dchronexpl where filenumber = '47996'
--> PLAN (BT_DCHRONEXPL INDEX (IX_DCHRONEXPL_FILENUM))

select * from bt_ref where file_num = '47996';
--> PLAN (BT_REF INDEX (IX_BT_REF_FILE_NUM_ASC))

select 1 from bt_dchronexpl inner join bt_ref on bt_ref.file_num =
bt_dchronexpl.filenumber;
--> PLAN JOIN (BT_DCHRONEXPL NATURAL, BT_REF INDEX (IX_BT_REF_FILE_NUM_ASC))
-- so it CAN use an index, but why not both? just the size imbalance?

select 1 from bt_dchronexpl full outer join bt_ref on bt_ref.file_num =
bt_dchronexpl.filenumber
--> PLAN JOIN (BT_REF NATURAL, BT_DCHRONEXPL NATURAL)
-- why no index at all?

select 1 from bt_dchronexpl a full outer join bt_dchronexpl b on a.filenumber =
b.filenumber;
--> PLAN JOIN (B NATURAL, A NATURAL)
select 1 from bt_ref a full outer join bt_ref b on a.file_num = b.file_num;
--> PLAN JOIN (B NATURAL, A NATURAL)
-- it's not about size, eh?

select 1 from bt_dchronexpl left join bt_ref on bt_ref.file_num starts with
bt_dchronexpl.filenumber;
--> PLAN JOIN (BT_DCHRONEXPL NATURAL, BT_REF INDEX (IX_BT_REF_FILE_NUM_ASC))
-- makes sense

select 1 from bt_dchronexpl inner join bt_ref on bt_ref.file_num starts with
bt_dchronexpl.filenumber;
--> PLAN JOIN (BT_DCHRONEXPL NATURAL, BT_REF INDEX (IX_BT_REF_FILE_NUM_ASC))
-- makes sense

select 1 from bt_dchronexpl inner join bt_ref on bt_ref.file_num >=
bt_dchronexpl.filenumber;
--> PLAN JOIN (BT_DCHRONEXPL NATURAL, BT_REF INDEX (IX_BT_REF_FILE_NUM_ASC))
-- makes sense given the size differences, and is more or less what a starts-
with is doing

select 1 from bt_dchronexpl inner join bt_ref on bt_dchronexpl.filenumber
starting with bt_ref.file_num;
--> PLAN JOIN (BT_REF NATURAL, BT_DCHRONEXPL INDEX (IX_DCHRONEXPL_FILENUM))
-- it can go either way!

select 1 from bt_dchronexpl right join bt_ref on bt_ref.file_num starts with
bt_dchronexpl.filenumber;
--> PLAN JOIN (BT_REF NATURAL, BT_DCHRONEXPL NATURAL)
-- why not a reversed looping lookup?

select 1 from bt_dchronexpl left|inner|right join bt_ref on bt_ref.file_num
starts with bt_dchronexpl.filenumber
PLAN JOIN (BT_DCHRONEXPL INDEX(IX_DCHRONEXPL_FILENUM), BT_REF INDEX
(IX_BT_REF_FILE_NUM_ASC))
--> ERROR: index IX_DCHRONEXPL_FILENUM cannot be used in the specified plan

select 1 from bt_dchronexpl inner join bt_ref on bt_ref.file_num >=
bt_dchronexpl.filenumber
PLAN JOIN (BT_DCHRONEXPL INDEX (IX_DCHRONEXPL_FILENUM), BT_REF INDEX
(IX_BT_REF_FILE_NUM_ASC))
--> ERROR: index IX_DCHRONEXPL_FILENUM cannot be used in the specified plan


--Philip