.comment-link {margin-left:.6em;}

Oracle Sponge -- Now Moved To Wordpress

Please use http://oraclesponge.wordpress.com

Thursday, November 10, 2005

Choosing an Ordering Method for Best Table Compression

Some comments here: http://www.phpbbserver.com/phpbb/viewtopic.php?t=614&mforum=dizwellforum

3 Comments:

At 4:32 PM, Blogger Jeff Moss said...

I wrote myself a little PL/SQL procedure which takes the following:

OWNER
TABLE NAME
PARTITION_NAME
SAMPLE SIZE

and builds a set of temp tables which are ordered by each column in the given table using the sample size. It then returns the number of blocks/rows/space used in each ordering method so that I can determine the individually best column compression orders based on empirical data. You can then choose the best column(s) to order your data by - I consider both the most compressable order by column(s) and the most used access path column(s) depending on the requirement.

Code available on request....might stick it on my blog...

 
At 3:07 PM, Blogger David Aldridge said...

Nice one Jeff.

Jeff's blog on the subject is here: http://oramossoracle.blogspot.com/2005/11/table-compression-order-for-maximum.html

 
At 5:08 PM, Blogger Jeff Moss said...

I've extended the code as you suggested - can supply prefix columns that you know are common access path(s) and it works out the compressability of all the other columns when appended to the given prefix columns in the ORDER BY. Let me know what you think whenever you get time to try it.

 

Post a Comment

Links to this post:

Create a Link

<< Home