System partitioning

From Oracle FAQ
Jump to: navigation, search

System partitioning is a partitioning method, introduced in Oracle 11g, that allows an application to control partition selection. System partitions doesn't have partition keys like other partitioning schemes. As such, partition extended syntax must be used when inserting rows into system partitioned tables.

The following restrictions apply:

  • Unique local indexes are not supported (no partition key)
  • CTAS into system partitioned tables (into what partition should it go?)
  • SPLIT PARTITION operations.

Examples[edit]

Create a table that is system partitioned:

CREATE TABLE syspart (c1 NUMBER, c2 NUMBER)
PARTITION BY SYSTEM (
   PARTITION p1 TABLESPACE ts1,
   PARTITION p2 TABLESPACE ts2,
   PARTITION p3 TABLESPACE ts3
);

Insert data with the partition extended syntax:

INSERT INTO syspart PARTITION (p1) VALUES (1, 2);
INSERT INTO syspart PARTITION (p2) VALUES (3, 4);