Featured Database Articles

Partition Limits In Oracle

By David Fitzjarrell

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:

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