/scripts/ahub.form.attachments.js
0

HANA - Index Usage (when no index is created)

Nov 17, 2016 at 04:55 AM

567

avatar image
Former Member

Hi,

I created a temporary table to check how partitioning works and while going through the execution plan, I saw that the 'Basic Predicate' node had "Inverted Index - used".

I did not create any index and the index details is also empty when I look at the table definition. So, how does the plan show the index as used. Does HANA create any temporary indexes during execution?

System- HANA SPS10

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
avatar image
Former Member
Nov 17, 2016 at 07:31 PM
2

Hi Benedict,

You're probably either have a primary key (composite maybe?) on your table or you're joining tables with more than one column as combining key. That's very well explained on this presentation from Lars.

Regards,

Lucas de Oliveira

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks for fishing out that pre-historic piece of powerpoint dabbling... I'm equally grateful and surprised that this document actually survived the recent tsunami called 1DX community migration...

2
Lars Breddemann
Nov 29, 2016 at 12:17 PM
1

Once again, my first answer didn't seem to have been saved...*grrrr*

Anyhow. Thanks for pushing the amazing PSEUDODSO tables into the limelight again. They had been gone for too long :-D

I tried to reproduce the effect on a HCP 1.00.112.04 system but didn't succeed. The index on ZDATE got always used, whenever the filter would have actually reduced the result set and when the data was read from the main store.

Maybe it's revision/version dependent!?

Another point (which is likely to answer the initial question... *cough*): when a primary key on multiple columns gets created, SAP HANA adds the following:

  • $trexexternalkey$ column
  • inverted index on the $trexexternalkey$ column
    AND
  • inverted indexes on every column in the primary key

This is the reason that you have seen the 'index used' after defining a PK and only referencing a single column in the WHERE clause.

I had completely forgotten this - admittedly well documented (see the SAP FAQ note!) - and was surprised when I saw this. Looks like I'm getting forgetful... :(

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thanks Lars. You said "Can you provide the example....?" and I was sure that I have made a mistake :)

It never occurred to me to check INDEX_COLUMNS tables to see the indexes

0
avatar image
Former Member Nov 17, 2016 at 10:14 PM
0

Thank Lucas. I saw the presentation and also the 'further playing...' one. In it Lars specifically mentions the creation of inverted index and how it shows up in the PlanViz.

Its true that my key is composite and I see the composite values in 'trexexternalkey'. But I am only using one field(key) in my join. Is the 'trexexternalkey' accessed nevertheless and what shows as index access?

Share
10 |10000 characters needed characters left characters exceeded
Lars Breddemann
Nov 18, 2016 at 03:36 AM
0

As 2160391 - FAQ: SAP HANA Indexes lists, partial index qualifications are not supported by indexes on HANA column store tables.

So, if you have a primary key or index over say MANDT, BUCHDAT, LINENO and only specify MANDT and LINENO in your query WHERE condition, then the index or primary key won't be used.

Show 3 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hello Lars,

Thanks for commenting.

I ran a few more simple SELECT tests and this is what I observed,

Even when a single field in the composite pk is used in a WHERE condition or in as a (inner)join field, the inverted index is used.

When a index is created on a non pk column and it is used in a WHERE condition, then the index is used. Also, the plan before and after index creation looks the same except that the filter on the index fields are rearranged to execute first.

0

Can you provide the example where you see a single column condition on a multi-column index is evaluated by that index?

Would be really interesting to see that...

0
Former Member
Lars Breddemann

Hello Lars,

I couldn't get back to you on this earlier. Since the original conditions contained customer information, I created a similar scenario and here's the results,

Does the SQL look familiar :) ?

I created a additional index on a non-pk field in the second table. Also, the first table is partitioned.

But, I was wrong about the second point in my previous reply. The extra index is not used here and I do not understand why. Maybe you can throw some light on it.

Benedict

1-pseudo-i.png (32.0 kB)
2-index.png (20.3 kB)
5-zdate-index.png (33.9 kB)
0
Skip to Content