Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Phoenix functional secondary index with hint and multiple secondary index

Highlighted

Phoenix functional secondary index with hint and multiple secondary index

New Contributor

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?

6 REPLIES 6
Highlighted

Re: Phoenix functional secondary index with hint and multiple secondary index

Explorer

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!

Highlighted

Re: Phoenix functional secondary index with hint and multiple secondary index

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)
Highlighted

Re: Phoenix functional secondary index with hint and multiple secondary index

New Contributor

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?

Highlighted

Re: Phoenix functional secondary index with hint and multiple secondary index

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.

Highlighted

Re: Phoenix functional secondary index with hint and multiple secondary index

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).

Highlighted

Re: Phoenix functional secondary index with hint and multiple secondary index

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).

Don't have an account?
Coming from Hortonworks? Activate your account here