Skip to Content
0

HANA/ECC and Index Covering

Feb 16, 2017 at 09:22 AM

12

avatar image

Hi Gurus

Technical Context : HANA 1 SP11 / ECC . Yet the ORCL/Exdata environnement offers better performances than HANA.

One of the issue is about a SELECT TOP1 query which represents 10% of the overall executed queries and that takes 460µs at execution time (where some others query takes less than 100 µs). Note that in this case TOP1 clause limit the result set size and thus does not impact the query elapsed time.

The faulty query uses 4 predicats on 4 different columns which are all included in PK index but... this PK contains 8 columns.

In another words the index 50% bigger than the informations needed to qualify the rows specified in the WHERE Clause.

__Question is the follwing

If we create a CPBTREE 2ndary index which includes ONLY the used columns in the WHERE clause (ie a Covering Index and smaller that the PK) could bring a better performances to this query

The query is

SELECT TOP 1 COUNT(*) FROM "EKBE" WHERE "MANDT" = ? AND "EBELN" = ? AND "EBELP" = ? AND "VGABE" = ?

PK /

PK Column Columns Used by the query "MANDT" X "EBELN" X "EBELP" X "ZEKKN" "VGABE" X "GJAHR" "BELNR", "BUZEI"
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers