HANA - Index Usage (when no index is created)

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

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

    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

    Add comment
    10|10000 characters needed 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...

  • Nov 29, 2016 at 12:17 PM

    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... :(

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 17, 2016 at 10:14 PM

    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?

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 18, 2016 at 03:36 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

Skip to Content