RE: Question about Oracle Partitioning (When to consider it?)
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- Date: Thu, 17 Dec 2009 15:16:40 -0600
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.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Taylor, Chris David
Sent: Thursday, December 17, 2009 3:30 PM
To: 'oracle-l@xxxxxxxxxxxxx'
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
Email: chris.taylor@xxxxxxxxxxxxxxx
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.
Other related posts:
