[
https://issues.apache.org/jira/browse/DERBY-6226?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13666015#comment-13666015
]
Dag H. Wanvik commented on DERBY-6226:
--------------------------------------
In DERBY-47 an approach using unions was considered but rejected due to worries
about optimizer stack overflow and performance if the IN-list were long. It
seems to me that this case is a better candidate as it logically is closer to a
union (it cant be solved using just a single pass over one index in contrast to
the IN-case) - we need two contribution from accessing two different indexes.
For the optimizer the cost would then be the sum of the cost of the unions. But
would rewriting to unions work for a DELETE, though? It seems it should be
straight forward for the SELECT case.
> enhance optmizer to use multiple probes into multiple indexes to satisfy OR
> queries on different columns.
> ---------------------------------------------------------------------------------------------------------
>
> Key: DERBY-6226
> URL: https://issues.apache.org/jira/browse/DERBY-6226
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.8.3.1, 10.11.0.0
> Reporter: Mike Matrigali
>
> For queries of the type:
> select * from a where col1 = ? or col2 = ?
> and good indexes exist on col1 and col2 it would optimal if derby could
> execute a plan that
> did an index scan first on the col1 index and then an index scan on col2
> index.
> Currently it looks like derby will do a full index scan if col1 and col2 are
> in any index, or a full table
> scan otherwise.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
