Ask the Expert

Efficiency of partitions

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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: