Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Index usage in HANA

Hi

I'm learning HANA and I have a question about the usage of indexes and query plans. I use HANA SPS6 Developer Edition VM on Cloudshare for my tests.

I populated a column table with 15,000,000 of rows and I'm trying to test some queries. A simple example:

select * from "LEONID"."CDRs" where NUM_ORIG = '9054052020680';

This query returns one row, even though values in NUM_ORIG column (defined as VARCHAR(15)) doesn't have to be unique. The query consistently takes 15 ms. to finish.

Then, I create an index on NUM_ORIG column:

create index idx_CDRs_NUM_ORIG on "LEONID"."CDRs"(NUM_ORIG);

From INDEXES view, I can see that HANA chooses "INVERTED VALUE" index type for the index. Then, I run my query again, and it consistently returns in 1 ms. Apparently, the index get used because it is the only change that made between the tests. If I drop the index, the query response time returns to 15 ms.

To my great surprise, I don't find any mention of index usage in the query plan. Actually, the query plans for the query with and without indexes looks exactly the same. Is it an expected behavior? Should I monitor the index usage in other place? See the output from EXPLAIN_PLAN_TABLE below, I omitted some obvious information to make it slightly more compact:

OPERATOR_NAME,OPERATOR_DETAILS,TABLE_SIZE,OUTPUT_SIZE,SUBTREE_COST,OPERATOR_ID,PARENT_OPERATOR_ID,LEVEL,POSITION

"COLUMN SEARCH","CDRs.CDR_ID, CDRs.CC_ORIG, CDRs.AC_ORIG, CDRs.NUM_ORIG, CDRs.CC_DEST, CDRs.AC_DEST, CDRs.NUM_DEST, CDRs.STARTTIME, CDRs.ENDTIME, CDRs.DURATION (LATE MATERIALIZATION)",?,1,0.000008691042685,1,?,1,1

"  COLUMN TABLE","FILTER CONDITION: CDRs.NUM_ORIG = '9054052020680'",15000000,1,?,2,1,2,1

I tried to examine the query plan also using SAP HANA Studio, but still see the same plan with and without index and there is no mention of index usage in the plan.

Another related question: is there any best practices for indexing in HANA? I'm asking about it because in other SAP column-based RDBMS, Sybase IQ, the approach for indexing is quite different from traditional row-based RDBMSs, such as Sybase ASE, Oracle or SQL Server.

Thanks in advance

Tags:
Former Member

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question