Hi Mike,
Right.Your proposal is what I had in mind when I was talking about
nextKey. Like you, I would imagine beefing up the existing node that
scans indexes. This solution does give rise to a smaller tree that's
easier to understand. I think it's a little more complicated than the
tinker-toy solution:
o it involves work in the cost model
o it involves code generation changes to support nextKey
o it involves query execution changes to support nextKey
Possibly, it's more efficient at run-time too, though for small IN lists
that might be hard to measure. I do like this solution.
Regards,
-Rick
Mike Matrigali wrote:
Rick Hillegas wrote:
Hi Brent,
...
Plan 3:
Join
/ \
Union TableScan
/ \
IndexScan IndexScan
Each IndexScan would probe for a single key value. If you could just
get the Opimizer to consider this plan, then I'm cautiously
optimistic that the existing cost-based decisions would favor Plan 3
over Plans 1 and 2.
I had assumed that a fix for IN list would create a new node which
would return all rows that fit the in list, doing multiple probes into
the
index. I guess the above would work, but had assumed that it would be
implemented by a single open scan on the index using the "reposition
scan" routines that already exist to reposition a scan within an
existing open scan on index.
The work would then involve:
o coding new node (or maybe it is just an extension of existing node),
seems a pretty simple change to take existing index lookup logic and
make it work for multiple lookups rather than one.
o getting optimizer to consider new node and build queries based on it
o some new query plan directives/runtime stat output
o costing the node
o I think just multiplying existing costs for doing a single probe
by number of probes in the IN list
