on 11-17-2016 4:55 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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...
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 😄
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:
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... 😞
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.