You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Mike Matrigali (JIRA)" <ji...@apache.org> on 2013/07/25 02:43:48 UTC

[jira] [Commented] (DERBY-6301) SQL layer should push down IN list predicates to store when doing a scan

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

Mike Matrigali commented on DERBY-6301:
---------------------------------------

An IN-LIST is just a sequence of OR's and the existing store interface allows for an unbounded number of or's to be passed in, so this should not be too hard 
of a project. 

 Some thought should be made to whether is makes sense to push the IN-LIST in the case when a multi-probe query plan is picked.  I think it likely that it
should not, which likely makes implementation harder - not sure when pushing qualifier decision is made vs. multi-probe query plan.   
                
> SQL layer should push down IN list predicates to store when doing a scan
> ------------------------------------------------------------------------
>
>                 Key: DERBY-6301
>                 URL: https://issues.apache.org/jira/browse/DERBY-6301
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.10.1.1
>            Reporter: Mike Matrigali
>
> The store interface allows for OR and AND qualifiers to be passed down to store as part of either
> a heap or btree scan.  It is more efficient to qualify the rows at the lowest levels.  The SQL level
> does not seem to  push any qualifier in the case of IN lists.
> This does not matter if the optimizer choses the multi-probe execution strategy for the IN list as that also
> qualifies the row at the lowest level.
> The problem arises when the optimizer chooses not to do multi-probe, for instance if it determines there
> are too many terms in the in-list relative to the size of the table and the cardinality of the terms.  In this
> case it chooses a scan with no qualifiers which results in all rows being returned to the sql layer and qualified there.  
> In addition to performance considerations this presents a locking problem with respect to the repeatable read isolation level.   It is optimal in repeatable read to not maintain locks on those
> rows that do not qualify.  Currently this locking optimization only takes place for those rows that
> are qualified in the store vs. those qualified in the upper SQL layer.  So in the case of a non-multi-probe IN-LIST plan all non-qualified rows looked at as part of the execution will remain locked in repeatable 
> read.

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