Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Rajeshwaran.

Asked: February 21, 2016 - 10:32 am UTC

Last updated: January 23, 2018 - 1:58 am UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

Team,

We have an application, where we receive a set of files to be processed by application in 24/7.
Each time a file recieves we generate a file_key in master_table using sequence, and take that file_key to load the file contents into this table.
This table will be accessed in the context of file_key (once the processing is done, we drop the partition using that file_key). so thought of automating the "add partition" concept with interval partition, but now we hit this error.

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

Table created.

rajesh@ORA12C> insert into t values(1048574);

1 row created.

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


For time being, we flipped this table to Range partition and we are able to proceed without any errors.

rajesh@ORA12C> alter table t set interval();

Table altered.

rajesh@ORA12C> alter table t add partition p_1048575
  2  values less than ( 1048576 );

Table altered.

rajesh@ORA12C> insert into t values(1048575);

1 row created.

rajesh@ORA12C>


But the question, is
1) Why do we get this error message in Interval but not with Range partition?
2) Looked around the docs, but not able to find anything related to this. Could you help with documentation link specific to this?

and Connor said...

There is a cap of one million partitions, even if these partitions are not yet in existence.

To explain, let's say you have:

interval (10), values less then (100)

This has in effect pre-defined all of the *potential* partitions being,

100,110,120,130,140,..... 100000000

The reason for this, is that if you insert values (say) 155 we could not simply create one partition spanning the current lower bound (100) up to the next needed upper bound (160) because it breaks the interval rule. We have to create a partition covering 151-160, and when someone later inserts 133, we create *another* partition covering 131-140. And so forth...

A range partition is different. Its upper/lower bounds are defined precisely by the *existing* partitions.

It's the same reason when you do a full scan on an interval partition table, you'll PSTART=1, and PSTOP=1048576, even though not that many partitions are yet in existence.

Hope this helps.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

One million

Rajeshwaran Jeyabal, February 22, 2016 - 3:21 pm UTC

There is a cap of one million partitions, even if these partitions are not yet in existence.

Thanks Connor. this helps. having said that one million partitions, why dont it breaks at 1000001 rather at 1048575. what is that magic number (1048575) is ?

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

Chris Saxon
February 22, 2016 - 5:00 pm UTC

1048575 = 2^20 - 1.

http://docs.oracle.com/database/121/REFRN/GUID-685230CF-63F5-4C5A-B8B0-037C566BDA76.htm#REFRN0043

You've started the partition values at 0. If you'd started at 1 (values less than 2), you'd be able to insert 1048575

Chris

And don't forget about NULLS

Michael, January 22, 2018 - 6:12 pm UTC

Also just want to add that you'll get this same ORA error when inserting a NULL value in the column that is used on interval partitioning. The one million cap led me astray for bit until I examined my data more closely and found nulls.
Connor McDonald
January 23, 2018 - 1:58 am UTC

Thanks.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.