Q

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? 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
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close