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
