Partition gotchas

Two minor partition gotchas on partition extent sizing and on the number of partitions in an interval partitioned.

First – old news – note that initial extent size for partitioned tables changed in 11.2.0.2.
Somehow this passed me by and I was late to the large extent party – duh!
Influenced by parameter _partition_large_extents, the default was changed to 8MB rather than 64K for autoallocated ASSM extent sizing.

Pre 11.2.0.2 (non-Exadata):

alter session set "_partition_large_extents" = false;

create table t1
(col1   number not null)
partition by range (col1)    interval ( 1 )
(partition p0 values less than (1));

insert into t1 (col1) values (1);

See initial extent size of 64k:

select table_name
,      partition_name
,      high_value
,      initial_extent
,      next_extent 
from user_tab_partitions where table_name = 'T1';

TABLE_NAME PARTITION_NAME HIGH_VALUE INITIAL_EXTENT NEXT_EXTENT
---------- -------------- ---------- -------------- -----------
T1         P0             1          
T1         SYS_P133592    2          65536          1048576 

Onwards from 11.2.0.2, the following is the default behaviour:

alter session set "_partition_large_extents" = true;

drop table t1;

create table t1
(col1   number not null)
partition by range (col1)    interval ( 1 )
(partition p0 values less than (1));

insert into t1 (col1) values (1);

Back to default initial extent of 8MB:

select table_name
,      partition_name
,      high_value
,      initial_extent
,      next_extent 
from   user_tab_partitions where table_name = 'T1';

TABLE_NAME PARTITION_NAME HIGH_VALUE INITIAL_EXTENT NEXT_EXTENT
---------- -------------- ---------- -------------- -----------
T1         P0             1          
T1         SYS_P133593    2          8388608        1048576 

A friend highlighted this to me on his DB where a lot of space had been wasted on small or empty partitions.
If empty, why was space wasted? Did not deferred segment creation benefit?
Deferred segment creation was off on this DB because of historic occurrence of bug 12535346 with deferred constraints combined with other features with “deferred” behaviour (deferred segment creation or interval partition).

Second gotcha – if you are considering INTERVAL PARTITIONING, particularly with a RANGE of 1, think about the impact of the maximum number of interval partitions.

Following on from example T1 table above:

select table_name
,      partition_name
,      high_value 
from   user_tab_partitions where table_name = 'T1';

TABLE_NAME PARTITION_NAME HIGH_VALUE 
---------- -------------- -----------
T1         P0             1                                                                                
T1         SYS_P133593    2        
insert into t1 (col1) values (1048574);

1 rows inserted

insert into t1 (col1) values (1048575);

ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

The maximum number of partitions in an interval partition is documented as 1024K -1 = 1048575.

select table_name
,      partition_name
,      high_value 
from   user_tab_partitions where table_name = 'T1';

TABLE_NAME PARTITION_NAME HIGH_VALUE 
---------- -------------- -----------
T1         P0             1                                                                                
T1         SYS_P133593    2                                                                                
T1         SYS_P133594    1048575   

Although we only have two materialised partitions, the possible future partition boundaries inbetween are effectively reserved for numbers between the lower and upper boundaries.

There’s not a lot we can do about this UNLESS you know that you’re not going to need those reserved partitions OR if you’re happy for other numbers to go into existing partitions, effectively having ranges much larger than the original 1.

At the moment, if we go higher than our reserved boundaries it fails, but for example if the key “1048573” comes along with is within our reserved boundaries, it will create a new partition:

insert into t1 (col1) values (1048573);

1 row inserted

select table_name
,      partition_name
,      high_value 
,      interval
from   user_tab_partitions where table_name = 'T1';

TABLE_NAME PARTITION_NAME HIGH_VALUE INTERVAL
---------- -------------- ---------- --------
T1         P0             1          YES                                                              
T1         SYS_P133593    2          YES
T1         SYS_P133594    1048575    YES
T1         SYS_P133595    1048574    YES

But we can do is convert our existing partitions to normal RANGE partitions:

alter table t1 set interval ();

table altered

select table_name
,      partition_name
,      high_value 
,      interval
from   user_tab_partitions where table_name = 'T1';

TABLE_NAME PARTITION_NAME HIGH_VALUE INTERVAL
---------- -------------- ---------- --------
T1         P0             1          NO                                                              
T1         SYS_P133593    2          NO
T1         SYS_P133594    1048575    NO
T1         SYS_P133595    1048574    NO

And then we can set the table back to interval partitioning without affecting those existing partitions:

alter table t1 set interval (1);

Now if a new values comes along, if it falls within an existing range, it will use the existing range partition:

insert into t1 (col1) values (1048572);

1 row inserted

select table_name
,      partition_name
,      high_value 
,      interval
from   user_tab_partitions where table_name = 'T1';

TABLE_NAME PARTITION_NAME HIGH_VALUE INTERVAL
---------- -------------- ---------- --------
T1         P0             1          NO                                                              
T1         SYS_P133593    2          NO
T1         SYS_P133594    1048575    NO
T1         SYS_P133595    1048574    NO

select col1
, (select subobject_name from dba_objects where data_object_id = rd) pname 
from (
select col1
,      dbms_rowid.rowid_object(rowid) rd
from t1 where col1 IN (1048572,1048573));

      COL1 PNAME                        
---------- ------------------------------
   1048573 SYS_P133595                    
   1048572 SYS_P133595      

But if a new high comes along, we can now create our new interval partition:

insert into t1 (col1) values (1048575);

1 row inserted

select table_name
,      partition_name
,      high_value 
,      interval
from   user_tab_partitions where table_name = 'T1';

TABLE_NAME PARTITION_NAME HIGH_VALUE INTERVAL
---------- -------------- ---------- --------
T1         P0             1          NO                                                              
T1         SYS_P133593    2          NO
T1         SYS_P133594    1048575    NO
T1         SYS_P133595    1048574    NO
T1         SYS_P133596    1048576    YES

And our limit of 1048575 partitions still exists but the reserved future interval partitions can move out:

insert into t1 (col1) values (2097145);

1 row inserted

insert into t1 (col1) values (2097146);

ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

This workaround has very limited usefulness as mentioned.
Bottom line – if using interval partitioning, pay careful consideration to this limit.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: