Oracle 11g new System Partitioning feature

Oracle 11g is an exciting new release in terms of new features. There are tons of new features when it comes to partitioning. From virtual column partitioning, to new combinations of subpartitioning.

One of those new partition features is system partitioning that enables you to create a table that in practice it’s dynamically partitioned. You just have to select the names and number of partitions and then at the DML stage you decide.

Let’s take it slowly. First create the table with two partitions, each in a different tablespace. Here we take two default tablespaces, but you should use “real” permanent tablespaces:

  2  (ncol number, xar varchar2(80))
  4  (partition P1 tablespace users,
  5   partition P2 tablespace example);

Table created.

The key is that DML is done in a different fashion, because you have to decide in which partition you’ll put your rows into. Let’s imagine you don’t know this is a system partitioned table and you’ll do a “normal” insert. In this case you get the following error:

SQL> insert into SMALL_PARTS_ISOLATED values (1,'Nomeansno');
insert into SMALL_PARTS_ISOLATED values (1,'Nomeansno')
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on
tables partitioned by the System method

Wow! This error message wasn’t produced at the regular shop where Oracle uses to make them! Why? Because it’s crystal clear.
And now the right way to do DML in a system partitioned table by using the PARTITION keyword:

SQL> insert into SMALL_PARTS_ISOLATED PARTITION (p2) values (1,'YesMan');

1 row created.

SQL> insert into SMALL_PARTS_ISOLATED PARTITION (p1) values (2,'TwiceIsCheaper');

1 row created.

When you select from it this is a regular table like any other:

SQL> select * from small_parts_isolated;

      NCOL XAR
---------- --------------------------------------------------------------------------------
         2 TwiceIsCheaper
         1 YesMan

But how the heck to we know the name of the partition in order to insert it into the right place? This last table had partition P1 and P2, so it should be right to assume that it might have a partition called “P3″:

SQL> insert into SMALL_PARTS_ISOLATED PARTITION (p3) values (30,'NonExistentPartition');
insert into SMALL_PARTS_ISOLATED PARTITION (p3) values (30,'NonExistentPartition')
ERROR at line 1:
ORA-02149: Specified partition does not exist

Oopsy dasy… Looks like our assumption was wrong…

The way I see this, Oracle Corp. should have had the idea to incorporate partition’s names at the DESCRIBE command, but this is asking too much from those folks. So describing the table won’t help a thing:

SQL> desc small_parts_isolated
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NCOL                                               NUMBER
 XAR                                                VARCHAR2(80)

Looks like we’re going to have a trip to the dreadfull Oracle data dictionary. For this we used a query from 24th June 1997:

SQL> select partition_name
  2  from user_tab_partitions
  3  where table_name = 'SMALL_PARTS_ISOLATED';

About these ads

One thought on “Oracle 11g new System Partitioning feature

Comments are closed.