Keeping rows together in a partition
I have the following situation:
a Huge table of one Tera size, is partitioned on 50 partitions of 20GB each partition. This table contains customers transactions, the partition key is the date of transaction.
most selects on the table are done by the cutomer_id which is indexed, and the transaction date which is the partition key)
the miss ratio on this table is around 30% which is very high, and causing a bottleneck in disk access.
I would like to increase the hit ratio in this table, by keeping rows who have the same customer_id in the same physical block.
This was possible on Oracle 8.i with the use of clusters and partitioned views, but on Oracle 9, partitioned views are not supported, so we can not use the cluster.
Do you think that using index organised table is a good approach for such huge table (please note that the record size is 800 bytes !!!
is there some way of using 2 levels of partitioning, like one level on the date, and the other level hash partition on the customer_id !! (there is one million customers)
Click Here to Expand Forum to Full Width