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?
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.