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.