---------- Original Message ----------- From: Dmitry Yemanov <[email protected]> > > 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? > > It's impossible to use two indices for a single condition. If you > search for some value by its key you should have that key already > retrieved. Just think more about it and you should get the idea. ------- End of Original Message -------
No true (thinking) scotsman wouldn't get it? I see two classic approaches to joins: a) for every row in A (regardless of order), do a PK lookup in B [which, ignoring index-caching, or some implementation tweak to remember where in the index you "last were" as a best-guess for the next lookup, is wasteful, as it would restart from the root of the index each time] b) indexed join, as I was once forced to implement by hand in Cobol, using two pre-sorted streams, keeping track of which one is "ahead" and advancing the one that is "behind" (I do not at all remember what that's called in theory, I'm sure it has a nice name.) I assume (b) is what you're saying FB3 does for full-outer-joins (yay! [that explains why I've sometimes been forced to do unions of left, inner, and right joins]), but why wouldn't (b) be a valid strategy for other join types too? (That's what I was trying to convince FB to do with a PLAN. I never use explicit PLANs, is there syntactic magic to it?) I can see that for a non-equi-join (like >= or starts-with), that wouldn't help much, as you'd have to go *way* ahead, then back up (not by following the index back, but just by keeping track of the last starting position?), but for equi- join, when there are no other filters on the table? Is it just not beneficial enough to warrant it, because of index-cacheing? Still, this doesn't explain why I do see it sometimes using an index to support a starts-with join, and he doesn't. Index stats? I'm assuming it's not because the tables are small enough that indexing doesn't matter, given his statement that it's going to run "forever" without indexing. -Philip
