DWH dis/advantage of composite partitioning
I'm working on a datawarehouse project using Oracle 9iR2 on some SUN server with a SAN.
We got a large table in which we load about 35M records a month using about 5 GB. The table is range partitioned on a monthly base an there is only one index (pk).
I saw that hash partitioning this table over a frequently used key would in many cases (90%) imporve a lot the query performance.
But before proposing it to my boss, I was wundering if any body made some bad experience with composite (range/hash) partitioning ?
I'm also interested to know how much more ressources are need when records a inserted in a table having 128 subpartitions instead of one with no subpartitions?
Thanks in advance for any reply
Last edited by mike9; 12-10-2003 at 02:38 AM.
may I know how hash partitioning improves the query performance :?
the key over which it is the partition is hashed is something like a product id havind only about 500 different values.
90% of the query are limited by that id.
but using equality operator or IN or < > <= >= :-?, i.e unique scan or range scan
or simply partition pruning
Last edited by pando; 12-10-2003 at 01:18 AM.
yes my goal with hash subpartitioning is to use partitioning pruning.
There is (for any reason) no index against this this product key so it always did a full table scan even that we have several product with only 5-10 records (out of 35M) per month.
I was thinking that hashing would be better then creating an index against this value because it would use less resource when inserting new data in the table. Also as far as i know an index access is only used when it will returne less then 5% of ther rows of the table. But I have about 10 out of 500 product which represants about 90% of the data, and I want that also for these 10 products it doesn't do any FTS anymore but a partition pruning over the hash key. In most queries only one product type is defined in the where clause so only one hash subpartition would be concerned.
Last edited by mike9; 12-10-2003 at 02:02 AM.
Click Here to Expand Forum to Full Width