Partition Limits In Oracle

Partitions can be a wonderful feature in Oracle; they allow faster access to data through partition pruning and they allow for easier archiving and removal of old data. There’s not much that can go wrong with partitions, until you want to create an interval-partitioned table with hash subpartitions and you have a situation where you want use that strategy by creating daily partitions with 1,024 subpartitions. Let’s look at why that plan won’t work, but first let’s look at some logical database limits Oracle imposes on partitions and subpartitions.

The following table lists the logical limits that affect such a strategy:

Partitions Maximum number of partitions allowed per table or index 1024K – 1
Subpartitions Maximum number of subpartitions in a composite partitioned table 1024K – 1

Notice the maximum number of partitions is 1048575. Since we are creating a composite partitioned table the maximum number of subpartitions is also 1048575, which means when you compute the total subpartitions for the entire table it cannot be greater than 1048575. For this example each partition will have 1024 subpartitions, which limits the number of partitions we can create to 1023, since we cannot have a partial partition. Yes, 1023 partitions is a very large number of partitions, and with 365 days in a year that allows for 2.8 years of data in the table, again quite a lot of daily data to be stored. Processes can run happily along until the table reaches that magic number and things start to fail. The example below has been modified from one provided by Jonathan Lewis; notice the various errors it throws when logical partition/subpartition limits are reached:

SQL> set verify off echo on
SQL> column table_name format a20
SQL> column partition_name format a20
SQL> column subpartition_name format a20
SQL> column high_value format a20
SQL> column part_end_dt new_value startdt
SQL> select to_char(trunc(sysdate),'dd-mon-yyyy') part_end_dt from dual;


SQL> create table hash_by_interval (
  2          int_dt         date            not null,
  3          num_val        number(10,0)    not null,
  4          strng_dat      varchar2(100)
  5  )
  6  tablespace assm_int_part_test
  7  partition by range (int_dt) interval (numtodsinterval(1,'DAY'))
  8  subpartition by hash (num_val) subpartitions 1024
  9          (
 10                  partition ip0 values less than (to_date('&startdt','dd-mon-yyyy'))
 11          )
 12  ;

Table created.

SQL> insert into hash_by_interval values (trunc(sysdate) + 1023, 1, rpad('x',100));
insert into hash_by_interval values (trunc(sysdate) + 1023, 1, rpad('x',100))
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

SQL> insert into hash_by_interval values (trunc(sysdate) + 1022, 1, rpad('x',100));
insert into hash_by_interval values (trunc(sysdate) + 1022, 1, rpad('x',100))
ERROR at line 1:
ORA-14299: total number of partitions/subpartitions exceeds the maximum limit

SQL> insert into hash_by_interval values (trunc(sysdate) + 1021, 1, rpad('x',100));

1 row created.

SQL> commit;

Commit complete.


The first attempted insert in the example fails immediately, the second takes a while but it also fails. On the face of it the errors seem to be saying the same thing, but they’re not. Going back to the logical limits the maximum number of partitions is 1048575; the first insert error reports that the partitioning key would map to a partition outside the maximum partition limit, which would create a partition numbered 1048576, clearly a violation of the logical limits. The second insert error reports that it should go into partition number 1025, not a violation of the number of subpartitions for a single partition but it does violate the total subpartitions limit by creating a total of 1048576 subpartitions. They aren’t the same condition so they generate different errors. Both conditions do result in the same problem: no data for the current day in the partitioned table.

For the test table a few numbers:

SQL> select table_name, partition_name, subpartition_count
  2  from user_tab_partitions;

-------------------- -------------------- ------------------
HASH_BY_INTERVAL     SYS_P45714                         1024
HASH_BY_INTERVAL     IP0                                1024

SQL> select table_name, partitioning_type, subpartitioning_type, partition_count, def_subpartition_count, subpartitioning_key_count
  2  from user_part_tables;

-------------------- --------- --------- --------------- ---------------------- -------------------------
HASH_BY_INTERVAL     RANGE     HASH              1048575                   1024                         1


For interval partitioned tables Oracle sets the partition count to the maximum limit, 1048575. For hash and range partitioned tables the partition count is the actual number of partitions in the table. An interesting fact emerges with interval-partitioned tables: the first partition is always a RANGE partition, after which the interval partitioning takes over. This is why we see two partitions in the first set of query results shown above.

This example shows how important it is to have a partition archiving process in place for such tables; archiving older partitions by converting them into stand-alone tables provides the necessary ‘room’ to keep this strategy going by ensuring that the table will never reach, much less try to exceed, the logical limits on partition/subpartition counts enforced by Oracle.

Partitioning, when given careful thought and robust testing, can be extremely beneficial in both query performance and data management. Thorough testing is the key, especially at the extreme limits for the data. Devising tests that take Oracle to its logical limits may not be easy, but in cases like this they’re necessary to plan for, and, hopefully eliminate, issues that can stop production in its tracks.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles