db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Optimizer issue, was: interested in working on DERBY-713
Date Tue, 20 Jun 2006 18:49:50 GMT

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


View raw message