---------- Original Message -----------
From: Dmitry Yemanov <dimitr@...>

> > 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