Oracle Scratchpad

January 2, 2007

Partition Count

Filed under: Partitioning,Performance — Jonathan Lewis @ 11:28 am BST Jan 2,2007

A little while ago, I received the following question by email:

What is the optimum level of partitioning in Oracle 10g, as in what is an efficient number of partitions that you can have in a single table? What are the advantages and disadvantages of having more than 3,000 partitions in a table?

These are actually questions I addressed seven years ago in my first book. The answers you might come up with for any specific occasion may vary, but the analysis does not change.

Start by asking why you need partitioning. What are you expecting to do with the partitioned tables? What benefits do you expect to see?

Are you thinking of range, list, hash, or composite partitioning? Are you thinking of local and global indexes, or just local, or just global? Are you expecting to see partition elimination in queries, what about partition-wise joins? Do you expect your queries to access low volumes of data or high volumes of data? Are you loading data using real-time (OLTP) methods or batch (DSS) methods? Are you expecting to eliminate old data by dropping partitions? Who (or what) is writing the SQL that hits the partitioned tables? What will the impact be in “scenario X” if it doesn’t work properly?

Moving from 8i to 10g, there are only three significant changes in partitioning technology:

  1. The optimizer is better at identifying options for partition elimination and partition-wise joins.  But that doesn’t stop it from being baffled by the bizarre SQL that comes from generic “point and click” tools.
  2. There is an option for maintaining global indexes reasonably efficiently when you do partition maintenance such as dropping a partition.
  3. Oracle no longer renumbers every single (higher) partition individually when you drop a partition of a partitioned table.

The last feature is the one that makes it reasonable to use larger numbers of partitions for a single table in 10g than you would in 8i.  So (assuming I had done all the other analysis properly) I probably wouldn’t worry too much about having 3,000 partitions in a table with a dozen indexes in 10g, when I used to start worrying in 8i after I reached about 1,000 and only three or four indexes.


  1. Mumble!

    This post make me think!

    Not on the statements that you say but on a question a bit more general:

    are there Oracle’s patterns?

    In other words: are there standard strategies developed on how optimizer works and how Oracle stores data?

    Another: point 3 happed in Oracle 9? :-|

    Comment by Antonio — January 2, 2007 @ 1:41 pm BST Jan 2,2007 | Reply

  2. Antonio, I think the closest thing you may find would be the scattering of “best practices” papers that Oracle’s CoE has published over the years on Metalink.

    Point 2 appeared in 9i with an optional change in syntax appearing in 10g, but point 3 only appeared in 10g. There is a (probably small)price to pay for it.

    The data dictionary views that reports partitions still display sequential partition numbers, which they derive by applying the analytic function rownumber() to the underlying data. So if you regularly query things like the dba_tab_partitions view, then the resource usage for some of your queries may jump significantly – especially if you have large numbers of partitions in your system.

    Comment by Jonathan Lewis — January 3, 2007 @ 8:29 am BST Jan 3,2007 | Reply

  3. Some time ago we had table with more than 90 000 segments (range + list composite partitioning). You wouldn’t believe how much time it took to parse query for the first time…

    Comment by Alexander Fatkulin — January 5, 2007 @ 9:25 am BST Jan 5,2007 | Reply

  4. Alexander, Oh yes I would!

    Comment by Jonathan Lewis — January 5, 2007 @ 9:29 am BST Jan 5,2007 | Reply

  5. on Oracle 9i R2

    We are planning to create 8 HASH partitions. Looking only at PERFORMANCE would be there be any improvements, if we go for 16 or 32 partitions (maintainance and availability is not a problem in our case). There are only 2 indexes on our 350M table – one is LOCAL, another non-partitioned index.

    Jon…any advice that you would give..?

    – Karteek

    Comment by Karteek — May 6, 2007 @ 11:07 am BST May 6,2007 | Reply

  6. […] Infrastructure — Jonathan Lewis @ 9:06 pm UTC May 7,2007 A question recently appeared on an article I wrote about partitioning a few months ago: We are planning to create 8 HASH partitions. Looking only at PERFORMANCE would be […]

    Pingback by Hash Partitions « Oracle Scratchpad — May 7, 2007 @ 9:07 pm BST May 7,2007 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by