Skip to Content

HANA: SQL statement preparation on SPS <= 10 causes all partitions loading to memory

Oct 20, 2016 at 09:50 AM


avatar image


There was a thread in old SCN: HANA loads the right partition for range-partitioned table only for queries in SQL plan cache in which HANA SPS8 ambiguous behavior of partitions loading was discussed. The question was "why HANA loads to memory all partitions of hash+range partitioned table during SQL statement praparation despite the fact that there are restrictions in WHERE clause that met range partitioning scheme, and only specific partitions must be loaded to memory in that case". For example my table is HASH-partitioned by DOCNR on 1st level and RANGE-partitioned by CALMONTH on 2nd level (201501, 201502, 201503 and so on). I make a SELECT query with "WHERE calmonth = 201501" and I expect that only the relevant partitions (for calmonth 201501) will be loaded to memory due to "partition pruning". But in fact all partitions are loaded. In the original thread there are SQL scripts in *.txt attachments for making detailed tests.

The problem of unwanted partitions loading is observed when all of the following conditions are satisfied:

1. HANA SPS <= 10;

2. SQL query that causes all partitions loading is missing in SQL plan cache;

3. Table unload priority is set to 1 - 5.

If any of those condition is not satisfied, only the relevant partitions are loaded to memory according to WHERE restrictions (this is expected behavior). When all conditions are satisfied - all partitions are loaded regardless WHERE restriction.

There are unanswered questions:

1. Why unload priority affects the behavior of partitions loading? This is not documented in SAP help or SAP notes;

2. Which process exactly causes loading of unwanted partitions to memory? Tracing indicates that partitiong pruning works (it shows that only relevant partitons are identified), but unwanted partitions are loaded anyway;

3. Starting from SPS11 the behavior of partitions loading was improved or fixed? I cannot find any info about this in SAP notes.

Thanks in advance.

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

0 Answers