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: Diagnosing DERBY-47 and DERBY-713 (queries with IN (?,?) filters)
Date Sun, 08 Oct 2006 17:48:26 GMT

James Synge wrote:

> In summary, two problems are:
> 1)
>    The IN list is used to create a scan of the index, starting with the 
> lowest
>    value in the IN list, and continuing through the greatest value in
> the IN list
>    (as opposed to creating N probes into the BTree).
> 2)
>    After finding an index row in the range, rather than testing the 
> predicate
>    against the index row, the base table row is fetched before the 
> predicate
>    (restriction) is applied.
> James
I believe there was a previous discussion about approaches to this 
problem on the derby list.  I seem to remember rick suggesting some
query plan graphs of the solutions.

The two approaches I remember are:

1) create some sort of index result set which would understand "in list"
    key'ing rather than single key.  It would be nice if it could also 
be used for "or's" expressed not as in lists.  It would use existing 
    to basically do probes/scans for each key in the in list.  Doing this
    would then require new costing and probably new optimizer work to
    teach it when to choose this kind of query.  I believe writing the
    the result node to do this is relatively easy, just extend some code
    and then code up reopenScan() with each new key.  If I were doing
    this, this is probably the first thing I would try and see if I could
    get the optimizer hints to work to force this plan so I could quickly
    see if this path works.

2) somehow rewrite the query as a bunch of unions with each having one
    key from the in list.  Doing this might mean no changes necessary
    to the optimizer as it would just do whatever it does today with
    the unions.  I don't know anything about query rewrite, so don't know
    how hard or easy this approach might be.

View raw message