A tweet from Connor McDonald earlier on today reminded me of a problem I managed to pre-empt a couple of years ago.
Partitioning is wonderful if done properly but it’s easy to get a little carried away and really foul things up. So company “X” decided they were going to use range/hash composite partitioning and, to minimise contention and (possibly) reduce the indexing overheads, they decided that they would create daily partitions with 1,024 subpartitions.
This, in testing, worked very well, and the idea of daily/1024 didn’t seem too extreme given the huge volume of data they were expecting to handle. There was, however, something they forgot to test; and I can demonstrate this on 12c with an interval/hash partitioned table:
rem Script: pt_interval_hash_2.sql rem Author: Jonathan Lewis column start_date new_value m_start_date select to_char(trunc(sysdate),'dd-mon-yyyy') start_date from dual; create table interval_hash ( d1 date not null, n1 number(10,0) not null, v1 varchar2(100) ) tablespace test_8k_assm_2 partition by range (d1) interval (numtodsinterval(1,'DAY')) subpartition by hash (n1) subpartitions 1024 ( partition p0 values less than (to_date('&m_start_date','dd-mon-yyyy')) ) ; insert into interval_hash values (trunc(sysdate) + 1023, 1, rpad('x',100)); insert into interval_hash values (trunc(sysdate) + 1022, 1, rpad('x',100)); insert into interval_hash values (trunc(sysdate) + 1021, 1, rpad('x',100)); commit;
I’ve started the partition range on trunc(sysdate), so it’s easy to pick a few very important future dates for insertion.
So what do you think is going to happen as I try to insert those three rows ?
insert into interval_hash 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 insert into interval_hash values (trunc(sysdate) + 1022, 1, rpad('x',100)) * ERROR at line 1: ORA-14299: total number of partitions/subpartitions exceeds the maximum limit 1 row created.
One of them succeeds – the other two fail with DIFFERENT error messages.
The limit on the number of segments allowed for a single table (or index) is 2^20 – 1 (i.e. 1,048575); and with 1,024 hash subpartitions I’m only allowed 1,023 partitions. I think that we get two different error messages because Oracle can work out that the first subpartition that it would create for sysdate + 1023 will exceed the segment limit, but it doesn’t “realise” that it’s going to go beyond the limit with sysdate + 1022 until it’s too late and it’s started working on creating the segments.
In fact, in its attempt to create the sysdate + 1022 partition Oracle actually temporarily allocated 1GB of space (I had a 1MB uniform extent size for the tablespace) and updated various bits of the data dictionary generating a total of 13,000 redo entries and 2.3MB of redo – for a single row insert!
So – if you want 1,024 hash partitions you’re only allowed 3 years of daily partitions. If you want a longer history you need fewer hash partitions (and don’t forget to stick to a power of 2) or longer durations for each partition. If 3 years is sufficient, though, make sure you test how long it takes to drop old partitions from the start of a table that is getting close to the limit.