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:27:50 UTC
[jira] [Created] (DERBY-6301) SQL layer should push down IN list
predicates to store when doing a scan
Mike Matrigali created DERBY-6301:
-------------------------------------
Summary: 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