phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Simon Lee (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (PHOENIX-2782) Index hint is ignored if table alias is used in the query
Date Fri, 18 Mar 2016 01:18:33 GMT

     [ https://issues.apache.org/jira/browse/PHOENIX-2782?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Simon Lee updated PHOENIX-2782:
-------------------------------
    Description: 
It is not clear to me whether this issue is a bug or an expected behavior. Anyway, Index hint
is ignored if table alias is used in the query

To reproduce the problem,

1. Create an HBase table with a column family. The column family has 4 columns (a1, a2, a3,
a4). Create an index on (a1, a2).
2. Populate the table with the following data
{code}
a1  a2     a3      a4
--  -----  -----   -----
1   Small  Red     USA
1   Small  Yellow  UK
1   Small  Green   China
1   Small  Green   Australia
{code}

3. Create a query with table alias, and use the alias in the select clause and where clause.
In this situation, the index hint is ignored. Phoenix uses full range scan instead.
*query*
{code}
select /*+ INDEX(add_index_hint_here) */  t.a1, t.a2, t.a3, t.a4 from my_table t where t.a1
= 1 and t.a2 = 'Small' and t.a3 = 'Green' 
{code}

*explain plan*
{code}
+------------------------------------------+
|                   PLAN                   |
+------------------------------------------+
| CLIENT 126-CHUNK PARALLEL 1-WAY RANGE SCAN OVER my_table [[53,54,49,45,72,89,71,45,57,51,55]]
|
|     SERVER FILTER BY (A.A1 = 1 AND A.A2 = 'Small' AND A.A3 = 'Green'  |
+------------------------------------------+
{code}

4. The workaround is to remove the table alias from the query. After that, the index hint
takes effect

*query*
{code}
select /*+ INDEX(add_index_hint_here) */  a1, a2, a3, a4 from my_table where a1 = 1 and a2
= 'Small' and a3 = 'Green' 
{code}

*explain plan*
{code}
+------------------------------------------+
|                   PLAN                   |
+------------------------------------------+
| CLIENT 394-CHUNK PARALLEL 1-WAY FULL SCAN OVER my_table |
|     SERVER FILTER BY (...) |
|     SKIP-SCAN-JOIN TABLE 0               |
|         CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN OVER my_index [[53,54,49,45,72,89,71,45,57,51,55],
...|
|             SERVER FILTER BY FIRST KEY ONLY |
|     DYNAMIC SERVER FILTER BY ("..."
+------------------------------------------+
{code}




  was:
It is not clear to me whether this issue is a bug or an expected behavior. Anyway, Index hint
is ignored if table alias is used in the query

To reproduce the problem,

1. Create an HBase table with a column family. The column family has 4 columns (a1, a2, a3,
a4). Create an index on (a1, a2).
2. Populate the table with the following data
{code}
a1  a2     a3      a4
--  -----  -----   -----
1   Small  Red     USA
1   Small  Yellow  UK
1   Small  Green   China
1   Small  Green   Australia
{code}

3. Create a query with table alias, and use the alias in the select clause and where clause.
In this situation, the index hint is ignored. Phoenix uses full range scan instead.
*query*
{code}
select /*+ INDEX(add_index_hint_here) */  t.a1, t.a2, t.a3, t.a4 from table t where t.a1 =
1 and t.a2 = 'Small' and t.a3 = 'Green' 
{code}

*explain plan*
{code}
+------------------------------------------+
|                   PLAN                   |
+------------------------------------------+
| CLIENT 126-CHUNK PARALLEL 1-WAY RANGE SCAN OVER my_table [[53,54,49,45,72,89,71,45,57,51,55]]
|
|     SERVER FILTER BY (A.A1 = 1 AND A.A2 = 'Small' AND A.A3 = 'Green'  |
+------------------------------------------+
{code}

4. The workaround is to remove the table alias from the query. After that, the index hint
takes effect

*query*
{code}
select /*+ INDEX(add_index_hint_here) */  a1, a2, a3, a4 from table  where a1 = 1 and a2 =
'Small' and a3 = 'Green' 
{code}

*explain plan*
{code}
+------------------------------------------+
|                   PLAN                   |
+------------------------------------------+
| CLIENT 394-CHUNK PARALLEL 1-WAY FULL SCAN OVER my_table |
|     SERVER FILTER BY (...) |
|     SKIP-SCAN-JOIN TABLE 0               |
|         CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN OVER my_index [[53,54,49,45,72,89,71,45,57,51,55],
...|
|             SERVER FILTER BY FIRST KEY ONLY |
|     DYNAMIC SERVER FILTER BY ("..."
+------------------------------------------+
{code}





> Index hint is ignored if table alias is used in the query
> ---------------------------------------------------------
>
>                 Key: PHOENIX-2782
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-2782
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 4.4.0
>         Environment: Linux
>            Reporter: Simon Lee
>            Priority: Minor
>
> It is not clear to me whether this issue is a bug or an expected behavior. Anyway, Index
hint is ignored if table alias is used in the query
> To reproduce the problem,
> 1. Create an HBase table with a column family. The column family has 4 columns (a1, a2,
a3, a4). Create an index on (a1, a2).
> 2. Populate the table with the following data
> {code}
> a1  a2     a3      a4
> --  -----  -----   -----
> 1   Small  Red     USA
> 1   Small  Yellow  UK
> 1   Small  Green   China
> 1   Small  Green   Australia
> {code}
> 3. Create a query with table alias, and use the alias in the select clause and where
clause. In this situation, the index hint is ignored. Phoenix uses full range scan instead.
> *query*
> {code}
> select /*+ INDEX(add_index_hint_here) */  t.a1, t.a2, t.a3, t.a4 from my_table t where
t.a1 = 1 and t.a2 = 'Small' and t.a3 = 'Green' 
> {code}
> *explain plan*
> {code}
> +------------------------------------------+
> |                   PLAN                   |
> +------------------------------------------+
> | CLIENT 126-CHUNK PARALLEL 1-WAY RANGE SCAN OVER my_table [[53,54,49,45,72,89,71,45,57,51,55]]
|
> |     SERVER FILTER BY (A.A1 = 1 AND A.A2 = 'Small' AND A.A3 = 'Green'  |
> +------------------------------------------+
> {code}
> 4. The workaround is to remove the table alias from the query. After that, the index
hint takes effect
> *query*
> {code}
> select /*+ INDEX(add_index_hint_here) */  a1, a2, a3, a4 from my_table where a1 = 1 and
a2 = 'Small' and a3 = 'Green' 
> {code}
> *explain plan*
> {code}
> +------------------------------------------+
> |                   PLAN                   |
> +------------------------------------------+
> | CLIENT 394-CHUNK PARALLEL 1-WAY FULL SCAN OVER my_table |
> |     SERVER FILTER BY (...) |
> |     SKIP-SCAN-JOIN TABLE 0               |
> |         CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN OVER my_index [[53,54,49,45,72,89,71,45,57,51,55],
...|
> |             SERVER FILTER BY FIRST KEY ONLY |
> |     DYNAMIC SERVER FILTER BY ("..."
> +------------------------------------------+
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message