I have a question about the efficiency of partitions. In my organization we're using a RAID 10 - three disks stripe and mirror to three others. I've defined a partition table storage on one tablespace, and a partition bitmapped index on another tablespace. If I'm using RAID, is there any reason to use partitioning because the data is striped over three disks?
One of the benefits that Oracle talks about with respect to
partitioning, is to physically distribute your table's data over
multiple disk devices. If you are using a RAID configuration that
implements striping, these benefits can be lessened if you put all of
your table's partitions on the same RAID device. So are there still
benefits to partitioning in this case? Yes.
Partitioning also means parition pruning. Suppose I have a table called
CUSTOMER_PURCHASES, with one line for every item that every customer
purchases from my store. I partition this table on PURCHASE_DATE, with
one partition per month. If I then query the CUSTOMER_PURCHASES for all
bought items in the month of December, do I have to look at every
single row in the table? I only have to look in the DECEMBER partition
of that table. I can ignore the other eleven partitions and still
satisfy my query. Partition pruning is the elimination of partitions
that do not participate in the query. By eliminating these partitions
from the processing, my query can run much faster.
This was first published in March 2004