db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Optimizer issue, was: interested in working on DERBY-713
Date Tue, 20 Jun 2006 19:54:43 GMT
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.


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

View raw message