db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "James Synge" <james.sy...@gmail.com>
Subject Re: Diagnosing DERBY-47 and DERBY-713 (queries with IN (?,?) filters)
Date Sun, 08 Oct 2006 22:29:50 GMT
Mike Matrigali wrote:
> 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
> technology
>     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.

I've been coming to the conclusion this might be a good choice.
In particular, it avoids problems of re-writing the query where the
semantics of the query needs to be maintained.

One thing that might help would be to divide the predicate into two
portions, one which applies to the index (as the start/stop range
does today), and an optional one which applies to the base row
(i.e. for the case where the predicate references columns in both
the index and the base table).  From what Army said earlier,
I gather this would be done in "modifyAccessPaths", after
the selection of the best index to use.

I also assume that this would require generating more
functions, which I'm not yet comfortable with.

I like the optimizer hints idea as a way to make progress on
evaluating the approach.  I'll see what I can learn about them.


View raw message