RE: Question about Oracle Partitioning (When to consider it?)

From: Ric Van Dyke <>
Date: Thu, 17 Dec 2009 15:16:40 -0600
Message-ID: <>

The general guide line is a table over 2 Gig is a candidate for partitioning.  

That being said the goal of partition is either for Administrative or Performance reasons. Both might be accomplished but one of the two will need to take precedence.  

If it's a Admin thing, like rolling off data, then partitioning by something like date could make sense. This way you can easily drop off partitions as they "age out".  

If you are trying to improve performance then the partitioning key is likely more tricky. You will need to partition by something that is often (maybe always) used in a query's where clause. The classic example here is account number. If you are always selected by account number that might be a good key. I worked on a system years ago where they were able to do an account number like field and a date field for the partition key (it was range/hash as I recall). Just by partitioning the table a particular critical query went from 64,000 LIOs down to 64. Many others went down as well, some stayed the same and no query went up as I recall.  

So the first question is, why are you partitioning? Admin or Performance  

Then if it's an admin thing, what do you want to do? Roll off data? Help affinity for RAC? Just organize the data into smaller pieces?  

If it's Performance, what are the key fields that are used in your critical queries? Can you construct a partition key based on these fields that will reduce the number of blocks scanned for your queries?  

Some times you can help both, but don't count on it.  

Ric Van Dyke

Hotsos Enterprises

Hotsos Symposium

March 7 - 11, 2010

Be there.  

[] On Behalf Of Taylor, Chris David Sent: Thursday, December 17, 2009 3:30 PM To: ''
Subject: Question about Oracle Partitioning (When to consider it?)  

I'm curious if there are any "rules of thumb" about when to consider moving to Oracle partitioning. What warning signs could one look for in a non-partitioned server that might indicate a need for Oracle partitioning?  

Our largest table is 2.8GB. I've worked in shops with unpartitioned tables as large as 12GB. (I know some of you work in shops with tables much larger than that)  

When considering Oracle partitioning, what questions should be asked before jumping into it?  

Chris Taylor

Sr. Oracle DBA

Ingram Barge Company

Nashville, TN 37205

Office: 615-517-3355

Cell: 615-354-4799


CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.

Received on Thu Dec 17 2009 - 15:16:40 CST

Original text of this message