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"