db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6226) enhance optmizer to use multiple probes into multiple indexes to satisfy OR queries on different columns.
Date Fri, 24 May 2013 05:45:30 GMT

    [ 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

Mime
View raw message