Skip to Content

Strange partition loading behavior

Hi folks,

I thought this was strange behavior but wanted to see what you think. I'm on rev 82.

1) I have a test MSEG table partitioned by range on MJAHR (Material Document Year which is NVARCHAR(4)) creating 12 partitions for past 11 years plus 1 REST partition for any data does not match the partition ranges.

2) I unload the table from memory so that all partitions are empty

3) I run query SELECT DISTINCT BLART from MSEG WHERE MJAHR = '2010'

Results = Only partition for 2010 is loaded into memory. Success!

Alternately if I make a mistake and run query SELECT DISTINCT BLART from MSEG WHERE MJAHR = 2010

(NOTE: No single quotes around the year)



It then primes ALL partitions into memory. To me this means if somebody accidentally makes a typo in their query and puts a bad value they can suddenly prime entire table into memory. This seems wrong to me. At worst shouldn't it only be loading the REST partition? In this case partition 12 only.

Thanks,

-Patrick

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on May 20, 2015 at 06:59 PM

    Hi Patrick,

    unfortunately that is the behavior as implemented (at least up to SPS 8 - never checked again with SPS 9).

    I actually describe this in the SAP HANA Administration book (chapter 9.3.5 partition pruning).

    The PARTITIONING trace (level debug) will show you, that the data types used in the query and in the partitioning definition need to match.

    In case of a mismatch the only correct choice is of course to not do partition pruning at all and to read from all partitions.

    So yes, this is something to be aware of when relying on partition pruning.

    - Lars

    Add a comment
    10|10000 characters needed characters exceeded

    • Ok it took me a while of searching through my really old HANA Administration Guide for section 9.3.5 with no luck and then tried the latest SPS 09 and then a bit more time trying to find the elusive SPS 08 (now out-dated guide) and found it in none of them. Then I realized you said BOOK and not GUIDE and that you've written a book! You just went way up the hero scale for me. I've always thought I would like to write a book if only I did not require sleep like Donald Trump or took performance enhancing drugs. Anyways kudos to you and thanks for your help.

      -Patrick

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.