Oracle Database 11g: The Top Features for DBAs and Developers

I am always delighted to read the top features by Arup Nanda.

He started his 11g series : Oracle Database 11g: The Top Features for DBAs and Developers

There are many partitioning enhancements. The most exciting feature for me is the INTERVAL partitioning. A huge cause of downtime and waste of storage is the range partitioning. In 10g and before, a partitioning by dates required that the partition are defined before values are inserted.

Now we have automatic partition creation 😀


create table t(d date)
partition by range(d)
interval(interval '1' month)
(partition p1 values less than (date '0001-01-01'));


One partition must be created manually, here the partition will contain all dates from 1-JAN-4712BC to 31-DEC-0000 (which is not a legal date by the way)

There is also new syntax to query the partition
SQL> insert into t values (date '2000-01-10');

SQL> select * from t partition for (date '2000-01-01');
D
-------------------
10.01.2000 00:00:00
20.01.2000 00:00:00


Note the syntax can be used in any form of partitioning. Here in a list-list composite

SQL> create table t(x number, y number)
partition by list(x)
subpartition by list(y)
subpartition template (
subpartition sp1 values(1),
subpartition sp2 values(2))
(partition values(1), partition values(2));

SQL> select * from t subpartition for (1,2);
X Y
---------- ----------
1 2

Ok, one more feature Arup introduced is the REF partitioning, where you have a schema with both the parent and child tables partitioned, and you want to partition on a column of the parent table that is not in the child table (as you had bitmap join indexes, you have now ref partitions). Check it on his site.

Finally Arup explained SYSTEM partitioning, which is not inconceivable, but will hardly be used.

Imagine you have a table containing just one single LOB column, and a LOB cannot be used as a partition key.

SQL> create table t(x clob)
partition by system (
partition p1,
partition p2,
partition p3,
partition p4);

Table created.


So far this seems fine. So what the problem? You cannot insert in that table!
SQL> insert into t values(1);
insert into t values(1)
*
ERROR at line 1:
ORA-14701: partition-extended name or bind variable
must be used for DMLs on tables partitioned by the
System method


so you must define in which partition you want to add data. For example round robin. Or random. Whatever.


SQL> insert into t partition (P1) values ('x');

1 row created.


If you want to use bind variable, you can use dataobj_to_partition

SQL> select object_id
from user_objects
where object_name='T'
and subobject_name is not null;
OBJECT_ID
----------
55852
55853
55854
55855

1 row created.


Actually, SYSTEM partitioning is misleading, YOU are responsible for choosing the partition in which you want to insert, not the system