Exceeding 1 million partitions

Posted by

In my previous post we saw that the partition number in the execution plan might not align with the partition position in the data dictionary when it comes to interval partitions.  As we saw, the partition numbers are preordained based on the low boundary and the interval size.

That also creates an interesting scenario that can catch people out – you might exceed the allowable number of partitions, with an empty table !

SQL> create table t ( x int )
  2  partition by range(x) interval(1)
  3  ( partition p0 values less than (1) );

Table created.

SQL> insert into t values (2000000);
insert into t values (2000000)
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

A value 2,000,000 is attempting to instantiate the “2,000,000th partition” even though all the preceeding partitions do not exist, which exceeds limit for partitions on a table.

So be careful when choosing your interval sizes.

One comment

  1. Just a work around for anyone finding this via google. If you know you are going to be skipping lots of values between your latest partition key and your next (and you’re not planning on filling out the gaps), you can temporarily disable the interval, add a manual partition that covers the range and then reenable the interval.
    alter table x set interval ();
    alter table x add partition p_workaround values less than (1048574);
    alter table x set interval (1);
    This will mean that no new partitions will be automatically created for partition keys less than 1048574, which hopefully isn’t a problem. If it is a problem, then automatic list partitioning might be a better fit.

Got some thoughts? Leave a comment

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

WordPress.com Logo

You are commenting using your WordPress.com 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.