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)
