How many partitions a table should have

We have 4 instances RAC on and ASM.
We have multiple tables having billions of records and need to decide in how many partition a table should be partitioned to get the optimum performance.
In the given situation I have below 2 questions.

1. Which column/s (type) are best candidate for partition?
2. Which type of partition is best?

Is there any already proved method with statistics to decide the same?
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Walter RitzelSenior Software EngineerCommented:
Using my previous experience and most of the examples found in the web, the best fields for partition keys are date fields. What will be really tricky is to determine the best size for the partition: It will be a week, 2 weeks, a month, a quarter, or even a year? The answer for that lay upon how are you going to query this table, the most common date filter.... because for the query be fast, the objective is to touch the less number of partitions possible.
On the type, I would say best is the ones that generates new partitions automatically.
Partitions could be done by other fields, like numeric ones, but then the strategy seems to be arbitrary, because separate the data in numeric ranges, in terms of query does not help much to select the least possible number of partitions.
YBSolutionsAuthor Commented:
Could you provide me some links or metalink note id?
So that I could explore more with examples.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

DavidConnect With a Mentor Senior Oracle Database AdministratorCommented:
Date-based options are always popular, but the question is, what makes most sense for your organization's needs.  An example is to segregate by product line, or geographic location.
1. Which column/s (type) are best candidate for partition?

Depends upon the partition key of the table (data), your needs.  I.e. if you have a table that
gets partitioned by Region (europe, Asia, Africa, America, Australia) then you have a 5 partitions in a list partitions., then you can further partition these 5 into range partition or so forth.  But the partitioning columns are always the data depended.

Then you can decided whether partitioning the table for performance is worth or not for each table.

2. Which type of partition is best?

Again!! It depends upon your data.  Range Partition (with DSS/BI) systems is very popular mostly with date.  I.e. you keep a rolling window of last 7 years data (for HIPAA) while each month (after ETL) you archive the oldest partition.  

For an OLTP inventory system product Id ragne or Price could be partition.
MikeOM_DBAConnect With a Mentor Commented:
1. Which column/s (type) are best candidate for partition?
Taking into consideration the above suggestions, look at the histograms for the columns you will use for partitioning, specially the cardinality.

2. Which type of partition is best?
The type of partitioning will depend (as stated above) on the type and data of the columns selected. You will be able to estimate the sizes of the partitions (in rows and GB) using these histograms.

Good luck!
slightwv (䄆 Netminder) Commented:

Since you are active here please return to your previous question and answer my question I asked:
YBSolutionsAuthor Commented:
Thanks it helped to make the decision.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.