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:

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> 
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;

PART_END_DT
--------------------
02-mar-2016

SQL> 
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> 
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.

SQL> 

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> 
SQL> select table_name, partition_name, subpartition_count
  2  from user_tab_partitions;

TABLE_NAME           PARTITION_NAME       SUBPARTITION_COUNT
-------------------- -------------------- ------------------
HASH_BY_INTERVAL     SYS_P45714                         1024
HASH_BY_INTERVAL     IP0                                1024

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

TABLE_NAME           PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT SUBPARTITIONING_KEY_COUNT
-------------------- --------- --------- --------------- ---------------------- -------------------------
HASH_BY_INTERVAL     RANGE     HASH              1048575                   1024                         1

SQL> 
SQL>

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