Information, tips, tricks and sample code for Big Data Warehousing in an autonomous, cloud-driven world

So why do I need "so much extra space" for my partition SPLIT operation?

Hermann Baer
Senior Director Product Management

After many many moons of ignoring blogging with the excuse of not having time for it I finally reached the point where I cannot excuse myself anymore ... thanks to my colleagues. So I figured for beginners I jump on something I know a little bit about - Partitioning - and share a short customer discussion/question that I happened to have several times over the last couple of weeks ...

Imagine you are having several RANGE partitioned tables, created back in the old days before 11g and Interval Partitioning - and you defined the most upper partition as "LESS THAN (MAXVALUE)", or, in other words open ended; although we have monthly (weekly, daily) partitions we do not want to have our end users or even the boss calling because someone gets an "ORA--14400: inserted partition key does not map to any partition"; being open ended solves that one for me.

Sounds familiar? Then read on.

So the day comes where you want to split this partition using an ALTER TABLE .. SPLIT PARTITION command, manually or automated through a job, like probably 99.9% of the rest of the world, and you all of a sudden get an "ORA-01658: unable to create INITIAL extent for segment in tablespace XX". What's going on here? I have some space left in the tablespace, and I do not add any data here ...

Well, the answer is that can happen (it does not have to), and that this is expected behavior. Let me explain what's going on here and what optimizations Oracle has using a simple example. I am using an upper bound of 'X' in my example since the behavior is generic for any SPLIT PARTITION operation, but it happens most commonly with MAXVALUE, probably the most common partition that is split.

Let's assume we have an original partitioning P_X, that currently is 500GB, upper bound(X) shall be split at Y
into P_Y and P_Xnew, where Y < X
. A simple example in SQL lingo could look like:

SQL> select partition_name, high_value from user_tab_partitions where table_name='X';

-------------------------  --------------------
P1                         MAXVALUE

And here is where the segment resides

SQL> select partition_name, header_file, header_block from dba_segments where segment_name='X' and owner= 'SCOTT';

---------------------- -----------  ------------
P1                     11           57

And now we do the split

SQL> alter table x split partition p1 at (6) into (partition p1, partition p2);

The first thing the kernel does is to probe internally whether (or not) the existing partition P_X contains data
EXCLUSIVELY for one of the new partitions only, e.g. all-data < Y (P_Y) or all data >=Y
(P_Xnew) .. or, in other words, if one of the new partitions will be completely

Depending on the outcome the database now does the split:

If the existing data in P_X maps exclusively to one of the new partitions only,
then we do what is known as FAST SPLIT, meaning we create a new, empty segment for the
new empty partition and change the metadata for the existing one; the
existing partition P_X will either become P_Xnew or P_Y, depending on its
data content. No data movement is required here since the data
placement is correct
, and we only create a new empty segment for the new empty partition.

You can see that one of the segments is indeed the old one - it's at the same physical location:

SQL> select partition_name, header_file, header_block from dba_segments where segment_name='X' and owner= 'SCOTT';

-------------------------- -----------  ------------
P1                         11           57
P2                         11           81

If the existing partition P_X contains data for both the new P_X new
and P_Y, we obviously have to separate the data for the two new target
partitions.Partitioning guarantees the proper placement of data into the segments that represent the logical partitions. Now we have to create new segments with the correct
data containment and you need appr. the same amount of spcae in addition,
namely 500GB in this example.

The database does two CTAS (Create Table As Select) commands under the cover to create the new two segments for the partitions. Note that while this is done, the old data is still readable, but cannot be modified. This could look as follows

P_Y SELECT FROM P_X where data < Y
that might take up 400GB
of the original 500GB and CREATE P_Xnew
SELECT FROM P_X where data >=Y
, consuming the rest of the data, 100GB in size.

Obviously we are not
going to drop the original partition P_X unless the two new target partitions are created, so we will temporarily need the space for P_X, P_Xnew, and P_Y .. a total of appr. 2*500GB in this example.
This is because (A) the data is kept accessible for queries and (B) simply because of such a DDL is atomic - it succeeds - or fails.

As soon as P_Xnew and P_Y are created successfully we internall "munge" around in the metadata, establish the two new partitions, and drop the old segment P_X . Done, back to the space consumption of 500GB with two new partitions P_Xnew and P_Y.

I hope this little example helps to shed some light on what's going on under the covers for a PARTITION SPLIT command. Needless to say that the scenario can be more complicated, for example when some or all partitions will be compressed as part of the split, or one of the tablespaces is also moved to a different tablespace, but the conceptual process is the same.

Hope you liked this little first blog about splitting partitions; there's more to come, and if you have any comments or wishes for future blogs, let me know.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.