?
Solved

How many partitions a table should have

Posted on 2012-03-18
8
Medium Priority
?
302 Views
Last Modified: 2012-06-27
We have 4 instances RAC on 11.1.0.7 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?
0
Comment
Question by:YBSolutions
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 37734569
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.
0
 

Author Comment

by:YBSolutions
ID: 37734594
Could you provide me some links or metalink note id?
So that I could explore more with examples.
0
 
LVL 16

Accepted Solution

by:
Walter Ritzel earned 501 total points
ID: 37734632
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 23

Assisted Solution

by:David
David earned 501 total points
ID: 37734858
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.
0
 
LVL 5

Expert Comment

by:Bajwa
ID: 37735367
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.
0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 498 total points
ID: 37739187
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!
:p
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37753321
YBSolutions,

Since you are active here please return to your previous question and answer my question I asked:

http://www.experts-exchange.com/Database/Oracle/Q_27624619.html
0
 

Author Closing Comment

by:YBSolutions
ID: 37803365
Thanks it helped to make the decision.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question