[jira] [Created] (PHOENIX-3899) Phoenix functional secondary index with hint and multiple secondary index is not working as expected

["Ze Wang (JIRA)" <ji...@apache.org>]

Ze Wang created PHOENIX-3899:
--------------------------------

             Summary: Phoenix functional secondary index with hint and multiple secondary index is not working as expected
                 Key: PHOENIX-3899
                 URL: https://issues.apache.org/jira/browse/PHOENIX-3899
             Project: Phoenix
          Issue Type: Bug
    Affects Versions: 4.7.0
            Reporter: Ze Wang


We have Phoenix 4.7 with HDP 2.5.3. Two questions came up when we worked on secondary indexes on phoenix tables:

1. With functional global secondary index, even though we are using index hint, the secondary index is not being used in the query execution plan. 

For example, create a functional index first: 

create index IDX_UPPER on S1.TABLE1 (UPPER(FIRST_NAME));

EXPLAIN SELECT /*+ INDEX(S1.TABLE1 IDX_UPPER) */ * FROM S1.TABLE1 WHERE UPPER(FIRST_NAME) = 'ABC';

Execution plan:

CLIENT 10-CHUNK 0 ROWS 0 BYTES PARALLEL 10-WAY ROUND ROBIN FULL SCAN OVER S1.TABLE1

SERVER FILTER BY UPPER(FIRST_NAME) = 'ABC'

SERVER 500 ROW LIMIT

CLIENT 500 ROW LIMIT

How can we make sure functional index is forced to use in the query?

2. If we have multiple secondary indexes, what is the correct syntax? 

We tried INDEX(<table_name> <index_name1> <index_name2>), the explain plan showed that only the first index index_name1 is being used. Is this the expected behaviour?




--
This message was sent by Atlassian JIRA
(v6.3.15#6346)