[
https://issues.apache.org/jira/browse/PHOENIX-3328?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15519309#comment-15519309
]
William Yang commented on PHOENIX-3328:
---------------------------------------
The patch handles PK ranges OR operation only for the FIRST PK column. If there
are other PK columns in the WHERE clause, it cannot handle. For example:
{code:sql}
where (pk1 = 5 and pk2 > 10 and pk2 < 20) or (pk = 6 and pk2 > 60 and pk2 <
80);
{code}
this WHERE clause indicate four ranges:
5 10
5 20
6 60
6 80
which can be optimized to SkipScanFilter for 4 ranges [5,10] - [6, 80]
But this does not always happen, see the following case:
{code:sql}
where (pk1 = 5 and pk2 > 10 and pk2 < 20) or (pk1 = 1 and pk2 > 60 and pk2 <
80);
{code}
we have to do two separate skip scan range scans instead of one, because the
value of PK1 in the RHS of OR is smaller than the LHS', but range of PK2 is
greater than the left. I think it's OK for us to be able to handle the first
PK only.
> Skip scan optimization failed for multi pk columns
> --------------------------------------------------
>
> Key: PHOENIX-3328
> URL: https://issues.apache.org/jira/browse/PHOENIX-3328
> Project: Phoenix
> Issue Type: Bug
> Reporter: William Yang
> Priority: Minor
> Attachments: PHOENIX-3328.patch
>
>
> {code:sql}
> create table t1 (pk integer primary key, a integer);
> create table t2 (pk1 integer not null, pk2 integer not null, a integer
> constraint pk primary key (pk1, pk2));
> explain select * from t1 where (pk > 10 and pk < 20) or (pk > 30 and pk < 40);
> explain select * from t2 where (pk1 > 10 and pk1 < 20) or (pk1 > 30 and pk1 <
> 40);
> {code}
> The first SELECT statement will use skip scan filter for two ranges, so as
> the second one. But actually the WhereOptimizer failed doing so and using a
> full table scan instead. This happens for tables have multi PK columns.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
