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');

1 row created.

SQL> insert into t values (date '2000-01-20');

1 row created.

SQL> insert into t values (date '2000-03-30');

1 row created.

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));

Table created.

SQL> insert into t values(1,2);
1 row created.

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.

SQL> insert into t partition (P2) values ('y');

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

SQL> var partition_id number
SQL> exec :partition_id := 55852

PL/SQL procedure successfully completed.

SQL> insert into t
partition (dataobj_to_partition("T",:partition_id))
values ('x');

1 row created.
SQL> exec :partition_id := 55853

PL/SQL procedure successfully completed.

SQL> insert into t
partition (dataobj_to_partition("T",:partition_id))
values ('x');

1 row created.

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

4 thoughts on “Oracle Database 11g: The Top Features for DBAs and Developers

  1. Derya Oktay

    Hi Laurent, Is there way of creating range-hash-list composite partition?
    I mean partition, sub partition and sub sub partition. If not is there a way ıf simulating this,or a workaround.
    Sincerely, Derya.

  2. Laurent Schneider Post author

    Hi Derya,

    There is no such thing as a sub sub partition.

    You could create multiple table with subpartition, then union all.

    create view v as
    select * from t_2009 where date between 1-1-2009 and 31-12-2009
    union all
    select * from t_2010…

    where t_2009,t_2010,… are subpartitioned tables.

    This view is actually called a partitioned view. It is the poor man partitioning option and it is documented in Oracle7 tuning guide :
    http://download.oracle.com/docs/cd/A57673_01/DOC/server/doc/A48506/partview.htm

Comments are closed.