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
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.
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.
