cancel
Showing results for 
Search instead for 
Did you mean: 

PAGE LOADABLE column

patrickbachmann
Active Contributor
0 Kudos

Hi folks,

I'm trying to find out more information on the PAGE LOADABLE column setting and how it works.  I'm finding very limited documentation on this in even the latest guides for SPS11.   In a normal unpartitioned table if one value of a column is queried the entire column for the entire table is loaded into memory.  My interpretation of the PAGE LOADABLE column is that somehow I can load a smaller portion of the column into memory but I'm perplexed/curious how exactly that would work and it's affects on performance etc.  I'm going to start tinkering with it but would be great if somebody had some more information to share about this.

Thanks,

-Patrick

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

HI Patrick

there is indeed no too much information available on this specific feature, which typically indicates how much it is recommended/expected to be used outside of SAP HANA development (<- that's just my opinion, let's be clear on that, ok?).

From what I understand, the feature affects how a column of a column store table is persisted. As you surely know, different column types can use different techniques to persist the data onto disk. Take LOBs for example - they can be stored purely in memory or in a hybrid fashion based on the size of the data.

Now, paged attributes (internal speak for 'single column') seem break up the whole column into chunks. For those attributes there is also some additional 'chunk-management' data structure created, that allows to access single chunks later on.

AFAIK, these chunks are only created for the main vector (not the delta storage and not for the dictionary or for inverted indexes). So upon reading the vector, the only required chunk(s) will be accessed.

In effect this could mean that you can read only a smaller piece of the whole table into memory and thereby lessen the potential effect on the rest of the system.

So far so good.

Thinking one step further, we'll find that most accesses on columns are full scans, so these single accesses likely require specific WHERE conditions (e.g. full pk predicate) to be effective.

The last time I played with paged attributes was with  SPS 6 I believe and back then these weren't even supported. Here we can say, that my 'knowledge' is outdated  and likely doesn't apply to the current (SPS 11) implementation.

What I am, however, pretty sure of is that it is apparently not _the_ killer feature for hot/warm data management as I don't see many applications making use of it.

Probably the most important reason here is that - again this is my limited understanding - a delta merge of a page loadable column doesn't happen page wise but requires all chunks to be loaded into memory.

This makes sens if you recall that a delta merge is an operation on the in-memory structure and that the chunks really only are persistence containers.

Effectively this means you cannot do what you typically want to do with hot/warm data management: over allocate the SAP HANA memory, that is, to store and process more data than what would fit into the SAP HANA server.

Ok, I know this is not the most positive sounding reply and there's heaps of caveats in it, but I thought it might be still useful.

Don't feel discouraged to try out this feature and maybe even share your experiences with it.

Once you've climbed that hill, there's more: flexible tables

Cheers,

Lars

patrickbachmann
Active Contributor
0 Kudos

Lars,

As always thanks for the great feedback.  I will be experimenting in the next week and will be sure to update this post with my findings.  Also your first inclination was my assumption too.  My boss said to me 'why dont you just post on SCN?'.  I said you don't understand I have to search exhaustively first to make sure there's not already some documentation... plus there's this guy named Lars...    I did look in your book too by the way before asking which is why I presumed this 'feature' probably wasn't going to help me much.  But I will do my due diligence next week.

Thanks again!

-Patrick

Answers (0)