System Partitioning in 11g

System partitioning allows application level control for partitioning a table or index.  This partitioning option offers the same benefits as other methods of partitioning.   However, the partitions are completely controlled by the application without partition keys or boundaries.  This option should be used carefully, and all references to a system partitioned table must include extended partition syntax to specify a partition.  Also, for inserts into a system partitioned table, the partition must be specified to avoid receiving an error.  For all other operations, omitting the partition will cause all partitions to be scanned. 

Below is an example of a table that might benefit from system partitioning is a large lookup table owned by the development group.  Consider the following table:

create table
misc_lookups (
   lookup_name varchar2(30),
   lookup_meaning varchar2(30)
); 

If this table is very large and there is no obvious way to partition it, system partitioning might be a practical option.  This option would be suitable if the table was primarily used in automated processes, such as PL/SQL packages.  In either case, the developers would need to determine the rules for deciding how to choose which partitions to use for all operations on this table.  In order to enable system partitioning with three partitions in different tablespaces, the table is created using the following syntax:

create table
misc_lookups (
   lookup_name varchar2(30),
   lookup_meaning varchar2(30)
)
partition by system
(
   partition misc_lookups_p0 tablespace tablespaceA,
   partition misc_lookups_p1 tablespace tablespaceB,
   partition misc_lookups_p2 tablespace tablespaceC
); 

It is the developers responsibility to use the extended partition syntax to specify the partition when using the table.  This is a requirement when inserting a row to a system partitioned table since the database has no means for choosing which partition to store new rows.  Failing to specify a partition when inserting a row will raise an error:

SQL> insert into misc_lookups values ('test', 'test meaning')
  2  /
insert into misc_lookups values ('test', 'test meaning')
            *
ERROR at line 1:

ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method 

Therefore, a partition must be specified when inserting into a system partitioned table such as:

SQL> insert into misc_lookups partition (misc_lookups_p0) values ('test', 'test meaning')
  2  / 

1 row created.

When selecting, updating, or deleting data from a system partitioned object, the best practice is to always specify the partition to avoid scanning all partitions.  The use of system partitioning should be viewed with scrutiny by a DBA since it transfers responsibility of optimal use of partitions away from the DBA and to the developers and applications.  Nevertheless, the introduction of this new feature helps to extend the use of partitioning for any business situation.