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.
