Partitioning tablespace defaults

Partitioning is a great tool for large database implementations and is quite easy to maintain if implemented properly in the first place. The example bellow will show the importance of setting the correct defaults for table and index tablespace defaults.
We'll start with a table that stores my favourite songs, partitioned by month, because every month I have a different set of favourite songs (I guess any reason is good enough for me to partition a table).


CREATE TABLE t1 (
my_favorite_song VARCHAR2(100),
my_date DATE NOT NULL)
PARTITION BY RANGE (my_date) (
PARTITION month_20090101 VALUES LESS THAN (TO_DATE('01-01-2009','DD-MM-YYYY'))
TABLESPACE part_test,
PARTITION month_20090201 VALUES LESS THAN (TO_DATE('01-02-2009','DD-MM-YYYY'))
TABLESPACE part_test,
PARTITION month_20090301 VALUES LESS THAN (TO_DATE('01-03-2009','DD-MM-YYYY'))
TABLESPACE part_test,
PARTITION month_max VALUES LESS THAN (MAXVALUE) TABLESPACE part_test);

If we inspect the tablespace details of the partitions like so:

SELECT table_name, partition_name, tablespace_name
FROM user_tab_partitions
WHERE table_name = 'T1';

we get:

TABLE_NAME PARTITION_NAME TABLESPACE_NAME

T1.....................MONTH_20090101...PART_TEST
T1.....................MONTH_20090201...PART_TEST
T1.....................MONTH_20090301...PART_TEST
T1.....................MONTH_MAX............PART_TEST

If we split the max partition:

ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-04-2009','DD-MM-YYYY'))
INTO (PARTITION month_20090401, PARTITION month_max);

we get by running the same query as above:

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
T1.....................MONTH_20090101...PART_TEST
T1.....................MONTH_20090201...PART_TEST
T1.....................MONTH_20090301...PART_TEST
T1.....................MONTH_20090401...PART_TEST
T1.....................MONTH_MAX............PART_TEST

Now lets say that I want my new partition to be created in tablespace users as opposed to part_test.


ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-05-2009','DD-MM-YYYY'))
INTO (PARTITION month_20090501 TABLESPACE users, PARTITION month_max);

This does the trick:

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
T1.....................MONTH_20090101...PART_TEST
T1.....................MONTH_20090201...PART_TEST
T1.....................MONTH_20090301...PART_TEST
T1.....................MONTH_20090401...PART_TEST
T1.....................MONTH_20090501...USERS
T1.....................MONTH_MAX............PART_TEST

And if I want both new partitions to be created in the new tablespace:


ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-06-2009','DD-MM-YYYY'))
INTO (PARTITION month_20090601 TABLESPACE users, PARTITION month_max TABLESPACE users);

and we check:

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
T1.....................MONTH_20090101...PART_TEST
T1.....................MONTH_20090201...PART_TEST
T1.....................MONTH_20090301...PART_TEST
T1.....................MONTH_20090401...PART_TEST
T1..................... MONTH_20090501...USERS
T1.....................MONTH_20090601...USERS
T1.....................MONTH_MAX...........USERS

and on the next split, even if I don't specify the tablespaces:


ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-07-2009','DD-MM-YYYY'))
INTO (PARTITION month_20090701, PARTITION month_max);

we get:

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
T1.....................MONTH_20090101...PART_TEST
T1.....................MONTH_20090201...PART_TEST
T1.....................MONTH_20090301...PART_TEST
T1.....................MONTH_20090401...PART_TEST
T1.....................MONTH_20090501...USERS
T1.....................MONTH_20090601...USERS
T1.....................MONTH_20090701...USERS
T1.....................MONTH_MAX...........USERS

But now lets say that I want to go back to the previous tablespace and I set the table default tablespace:


ALTER TABLE t1 MODIFY DEFAULT ATTRIBUTES TABLESPACE part_test;

and then I split again:


ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-08-2009','DD-MM-YYYY'))
INTO (PARTITION month_20090801, PARTITION month_max);

if I check the results:

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
T1.....................MONTH_20090101...PART_TEST
T1.....................MONTH_20090201...PART_TEST
T1.....................MONTH_20090301...PART_TEST
T1.....................MONTH_20090401...PART_TEST
T1.....................MONTH_20090501...USERS
T1.....................MONTH_20090601...USERS
T1.....................MONTH_20090701...USERS
T1.....................MONTH_20090801...USERS
T1.....................MONTH_MAX...........USERS

Not what I wanted. I was hoping for the at least the 0801 partition to be created in part_test tablespace. Even more confusing is the behaviour of the default tablespace for a local index. If I create an index on the table like so:


CREATE INDEX i1 ON t1 (my_favorite_song) LOGGING LOCAL;

and inspect the results:


SELECT index_name, partition_name, tablespace_name
FROM user_ind_partitions
WHERE index_name= 'I1'
ORDER BY partition_position;

we get for each index partition the same tablespace as for the table partitions. That stays the case if I split the table again:


ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-09-2009','DD-MM-YYYY'))
INTO (PARTITION month_20090901, PARTITION month_max);

and we check the table and the index:

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
T1.....................MONTH_20090101...PART_TEST
T1.....................MONTH_20090201...PART_TEST
T1.....................MONTH_20090301...PART_TEST
T1.....................MONTH_20090401...PART_TEST
T1.....................MONTH_20090501...USERS
T1.....................MONTH_20090601...USERS
T1.....................MONTH_20090701...USERS
T1.....................MONTH_20090801...USERS
T1.....................MONTH_20090901...USERS
T1.....................MONTH_MAX...........USERS

INDEX_NAME PARTITION_NAME TABLESPACE_NAME
I1.....................MONTH_20090101...PART_TEST
I1.....................MONTH_20090201...PART_TEST
I1.....................MONTH_20090301...PART_TEST
I1.....................MONTH_20090401...PART_TEST
I1.....................MONTH_20090501...USERS
I1.....................MONTH_20090601...USERS
I1.....................MONTH_20090701...USERS
I1.....................MONTH_20090801...USERS
I1.....................MONTH_20090901...USERS
I1.....................MONTH_MAX...........USERS

But if I change the index default tablespace:


ALTER INDEX i1 MODIFY DEFAULT ATTRIBUTES TABLESPACE part_test;

and then we split the table:

ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-10-2009','DD-MM-YYYY'))
INTO (PARTITION month_20091001, PARTITION month_max);

we get for the table:

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
T1.....................MONTH_20090101...PART_TEST
T1.....................MONTH_20090201...PART_TEST
T1.....................MONTH_20090301...PART_TEST
T1.....................MONTH_20090401...PART_TEST
T1.....................MONTH_20090501...USERS
T1.....................MONTH_20090601...USERS
T1.....................MONTH_20090701...USERS
T1.....................MONTH_20090801...USERS
T1.....................MONTH_20090901...USERS
T1.....................MONTH_20091001...USERS
T1.....................MONTH_MAX...........USERS

but for the index:

INDEX_NAME PARTITION_NAME TABLESPACE_NAME
I1.....................MONTH_20090101...PART_TEST
I1.....................MONTH_20090201...PART_TEST
I1.....................MONTH_20090301...PART_TEST
I1.....................MONTH_20090401...PART_TEST
I1.....................MONTH_20090501...USERS
I1.....................MONTH_20090601...USERS
I1.....................MONTH_20090701...USERS
I1.....................MONTH_20090801...USERS
I1.....................MONTH_20090901...USERS
I1.....................MONTH_20091001...PART_TEST
I1.....................MONTH_MAX...........PART_TEST

Even if you change the user default tablespace, the new table partitions will still be created in tablespace USERS. The only way to change the default is to move the max partition:


ALTER TABLE t1 MOVE PARTITION month_max TABLESPACE part_test;

and then split:

ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-11-2010','DD-MM-YYYY'))
INTO (PARTITION month_20101101, PARTITION month_max);

to get:

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
T1.....................MONTH_20090101...PART_TEST
T1.....................MONTH_20090201...PART_TEST
T1.....................MONTH_20090301...PART_TEST
T1.....................MONTH_20090401...PART_TEST
T1.....................MONTH_20090501...USERS
T1.....................MONTH_20090601...USERS
T1.....................MONTH_20090701...USERS
T1.....................MONTH_20090801...USERS
T1.....................MONTH_20090901...USERS
T1.....................MONTH_20091001...USERS
T1.....................MONTH_20101101...PART_TEST
T1.....................MONTH_MAX..........PART_TEST

But if we move back the max partition:


ALTER TABLE t1 MOVE PARTITION month_max TABLESPACE users;

and then split again:


ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-12-2010','DD-MM-YYYY'))
INTO (PARTITION month_20101201, PARTITION month_max);

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
...
T1.....................MONTH_20090701...USERS
T1.....................MONTH_20090801...USERS
T1.....................MONTH_20090901...USERS
T1.....................MONTH_20091001...USERS
T1.....................MONTH_20101101...PART_TEST
T1.....................MONTH_20101201...USERS
T1.....................MONTH_MAX..........USERS

we're back to where we started from. But if we drop a partition:


ALTER TABLE t1 DROP PARTITION month_max;

and then we add it back without specifying a tablespace


ALTER TABLE t1 ADD PARTITION MONTH_MAX VALUES LESS THAN (MAXVALUE);

and we then check the tablespaces we can see that:

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
T1.....................MONTH_20090501...USERS
T1.....................MONTH_20090601...USERS
T1.....................MONTH_20090701...USERS
T1.....................MONTH_20090801...USERS
T1.....................MONTH_20090901...USERS
T1.....................MONTH_20091001...USERS
T1.....................MONTH_20101101...PART_TEST
T1.....................MONTH_20101201...USERS
T1.....................MONTH_MAX...........PART_TEST

So the default that we set up earlier is taken into account for partition add but not for partition split. To summarize:
By default:
A partition split will create the two new segments in the tablespace of the segment being split;
A partition add will create the new segment in the default tablespace of the table;

After a split an index partition will be created in the tablespace of the table segment if default index tablespace is not defined.

Hoping this makes partitioning a favourite of yours too.