INTERVAL partitions in multiple tablespaces

When the “STORE IN” clause is mentioned for partitioned tables, most people’s mind turn toward hash partitioning, because the usage of “STORE IN” within the documentation almost exclusively limited to hash partition examples.

SQL> create table t1
  2      ( x int, y int, z int )
  3  partition by hash ( x )
  4  partitions 8
  5  store in  ( ts1,ts2,ts3,ts4,ts5,ts6,ts7,ts8 );

Table created.

Nominating tablespaces at partition level is perhaps a throwback to the days of JBOD servers, where DBAs would laboriously and carefully distribute their database files across disks to ensure good performance because striping, RAID levels and logical volume management in general were yet to be commonplace. As storage (and DBAs) evolved, tablespaces morphed into more of a logical construct, namely, you would separate database objects into separate tablespaces to solve business-related requirements rather than physical limitations of disk drives. It is for this reason, that for many customers, simply putting all of your data into a single tablespace will be just fine. It is when particular requirements such as read-only subsets of data, or transporting data at file level, where tablespace design still comes into play.

However, it is worth noting that the STORE IN clause is not specifically limited to HASH partitioning, and one place you might find a use for it is with INTERVAL based partitioning schemes. Since interval partitions are created dynamically you lose the ability to nominate a particular tablespace for a partition (other than the default one for the table).

Here’s an example of that in action. First I’ll create 8 tablespaces. (They are small in size for the second part of this blog post).

SQL> create tablespace ts1 datafile 'X:\ORACLE\ORADATA\DB19\PDB1\TS1.DBF' size 50m;

Tablespace created.

SQL> create tablespace ts2 datafile 'X:\ORACLE\ORADATA\DB19\PDB1\TS2.DBF' size 50m;

Tablespace created.

SQL> create tablespace ts3 datafile 'X:\ORACLE\ORADATA\DB19\PDB1\TS3.DBF' size 50m;

Tablespace created.

SQL> create tablespace ts4 datafile 'X:\ORACLE\ORADATA\DB19\PDB1\TS4.DBF' size 50m;

Tablespace created.

SQL> create tablespace ts5 datafile 'X:\ORACLE\ORADATA\DB19\PDB1\TS5.DBF' size 50m;

Tablespace created.

SQL> create tablespace ts6 datafile 'X:\ORACLE\ORADATA\DB19\PDB1\TS6.DBF' size 50m;

Tablespace created.

SQL> create tablespace ts7 datafile 'X:\ORACLE\ORADATA\DB19\PDB1\TS7.DBF' size 50m;

Tablespace created.

SQL> create tablespace ts8 datafile 'X:\ORACLE\ORADATA\DB19\PDB1\TS8.DBF' size 50m;

Tablespace created.

Now I’ll create an interval partitioned table using the STORE IN clause

SQL> create table t
  2  ( x int, y int, z int )
  3  partition by range ( x )
  4  interval ( 100 ) store in  ( ts1,ts2,ts3,ts4,ts5,ts6,ts7,ts8 )
  5  ( partition p1 values less than ( 0 )
  6  );

Table created.

So let’s see how the partitions get allocated to tablespaces as we populate the table.

SQL> insert into t
  2  select rownum,rownum,rownum
  3  from dual
  4  connect by level <= 10000;

10000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL> select partition_name, tablespace_name
  2  from dba_segments
  3  where tablespace_name like 'TS%';

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------
SYS_P61879                     TS1
SYS_P61880                     TS2
SYS_P61881                     TS3
SYS_P61882                     TS4
SYS_P61883                     TS5
SYS_P61884                     TS6
SYS_P61885                     TS7
SYS_P61886                     TS8
SYS_P61887                     TS1
SYS_P61888                     TS2
SYS_P61889                     TS3
SYS_P61890                     TS4
...
(snip)
...
SYS_P61872                     TS2
SYS_P61873                     TS3
SYS_P61874                     TS4
SYS_P61875                     TS5
SYS_P61876                     TS6

101 rows selected.

As (hopefully) you would expect, the partitions are allocated in a round robin fashion.

You might be wondering – Why would anyone want to round robin the partitions across tablespaces?

I was wondering the same thing, but the customer that reached out to me with this setup is doing some heavy duty data loading, namely, each of their tablespaces is at the bigfile tablespace limit of 32TB (for an 8k blocksize). Their table is hundreds of terabytes in size, so they have 32 tablespaces in their STORE IN clause in order to accommodate a table size of a petabyte!

But it is important to remember that allocating a bucket of tablespaces to a table does not circumvent the standard rule for all Oracle database segments, namely that a segment belongs to a tablespace and one tablespace only. Once an interval partition is created in one of the round-robin tablespaces, that is the only tablespace in which that partition will ever exist. Specifying multiple tablespaces does not mean random extents from any partition can now be placed into any of the available tablespaces.

Here’s an example of that in action. I’ll drop my table and recreate it with very wide rows to fill up space quickly.

SQL> drop table t purge;

Table dropped.

SQL> create table t
  2  ( x int, y char(2000), z char(2000) )
  3  partition by range ( x )
  4  interval ( 10000000 ) store in  ( ts1,ts2,ts3,ts4,ts5,ts6,ts7,ts8 )
  5  ( partition p1 values less than ( 0 )
  6  );

Table created.

I’ve nominated all 8 tablespaces as being candidates for this table, and now I’ll start pushing data into it until I get an error

SQL> insert into t
  2  select rownum,rownum,rownum
  3  from dual
  4  connect by level <= 10000;
insert into t
*
ERROR at line 1:
ORA-01688: unable to extend table T partition SYS_P61918 by 128 in tablespace TS2

You can see that I ran out of space in TS2, but did the database do this once it had also used up all of the space in the other tablespaces? No. In fact, the only tablespace that contains any data at all is TS2, because this is the only partition that I loaded data into.

SQL> select partition_name, tablespace_name
  2  from dba_segments
  3  where tablespace_name like 'TS%';

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------
SYS_P61918                     TS2

So whilst multiple tablespaces for a partitioned table can be useful, be aware that it does not create an arbitrarily large sized “bucket of space” into which any and all data can be placed into. You still need to monitor for individual tablespaces filling, even if others remain empty.