---------- Original Message ----------- From: "Rick Debay" <rde...@accessrxs.com> > 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
