System Partitioning - Oracle 11g New Feature

There are scenarios where a database developer or database designer is not able to make a logical way to partition a huge table. Oracle 11g has provided us a way to define partitions in an intelligent manner by System Partitioning, where application needs to control destination partition for a specific record. The DBA just needs to define the partitions.

Example:

create table departments
(
  department_id    number(4),
  department_name  varchar2(30),
  manager_id       number(6),
  location_id      number(4)
)
partition by system
(
   partition p1 tablespace users01,
   partition p2 tablespace users02
);

By system partitioning the table is physically divided into two segments. The indexes created on this table are also partitioned automatically.

Because the partitions bounds are not known to database so the insert statement needs to be re-written in partition-aware syntax. Oracle provides partition-aware syntax to Update and delete statements also.

For Insert:

SQL> insert into departments partition (p1) values (1,'sales', 1, 10);

For Delete:

SQL> delete departments partition (p1) where department_id = 1;

For Update:

SQL> update departments partition (p1) set location_id=2 where department_id = 1;

Note: Update and delete statements need not to provide partition-aware syntax but it will leads Oracle to scan all the partitions for fetching the row required.

For Delete:

SQL> delete departments where department_id = 1;

For Update:

SQL> update departments set location_id=2 where department_id = 1;

Related Links:
- New Features for Developers in Oracle 11g