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. Thanks, James
