db-derby-dev mailing list archives

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

    [ https://issues.apache.org/jira/browse/DERBY-6226?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13666455#comment-13666455
] 

Tony Brusseau edited comment on DERBY-6226 at 5/24/13 5:45 PM:
---------------------------------------------------------------

I think there should be a test for:

select * from a where col1 = ? or (col2 = ? and col3 = ?) 

where there are 2 indexes, one for col1 and one for (col2, col3) just to help ensure that
any fix isn't over specific to single column indexing.

I'd also be curious to know what happens in the case of:

select * from a where (col1= ? or col2 = ?) and (col3 = ?)

where there are 2 indexes (col1, col3) and (col2, col3). 


                
      was (Author: apb):
    I think there should be a test for:

select * from a where col1 = ? or (col2 = ? and col3 = ?) 

where there are 2 indexes, one for col1 and one for (col2, col3) just to help ensure that
any fix isn't over specific to single column indexing.

I'd also be curious to know what happen in the case of:

select * from a where (col1= ? or col2 = ?) and (col3 = ?)

where there are 2 indexes (col1, col3) and (col2, col3). 


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