Strange partition loading behavior

[patrickbachmann]

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

[lbreddemann · Accepted solution]

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

[patrickbachmann · Accepted solution]

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