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';
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?
HI @Josh Elser could you shed some light on this issue? Is there a way to use hints for the functional indexes? Or the hints are only usable for the normal (built for columns, not functional) secondary indexes? Thanks!
SELECT /*+ INDEX(S1.TABLE1 IDX_UPPER) */ * FROM S1.TABLE1 WHERE UPPER(FIRST_NAME) = 'ABC';
It looks like that you are trying to project all the columns of the table but your functional index is not covered(means does not include all the columns of the table).
You have following options:-
* Either project the pk columns and function
select pk,pk2,pk3.., UPPER(FIRST_NAME) from S1.table1
* Or create a covered index.
CREATE INDEX IDX_UPPER ON S1.TABLE1 (UPPER(FIRST_NAME)INCLUDE(COL1,COL2...)
* Or if you are sure that you will have fewer rows for UPPER(FIRST_NAME) , then you can create non-covered local index.(this will fetch the remaining columns from data table automatically)
CREATE LOCAL INDEX IDX_UPPER ON S1.TABLE1 (UPPER(FIRST_NAME)
Thanks for your reply.
As you can tell, we have already used your option1 as we do a select * from the table.
We are using hint already to avoid creating of covered index as the app team is doing select *.
Any other suggestions?
Select * is projecting all the columns, option1 suggest you can project only your pk columns and indexed column if you really want to use the non-covered global functional index. Ah, you mean, hint is also not helping. Let me check this at my side.
Yeah, it seems like a bug that hint is ignored in the case of global functional index. As a workaround, you can include all columns in covered index (I mean option#2).
Yeah, it seems like a bug that hint is ignored in the case of global functional index. As a workaround, you can include all columns in the covered index (I mean option#2).