System partitioning provides the ability to create a single table that has many physical partitions. System partitions doesn't have partition keys like other partitioning schemes.

REATE TABLE system_partitioned_tab (
  id           NUMBER,
  code         VARCHAR2(10),
  description  VARCHAR2(50),
  created_date DATE
)
PARTITION BY SYSTEM
(
  PARTITION part_1,
  PARTITION part_2
);

The partition must be explicitly defined in all insert statements or an error is produced.

INSERT INTO system_partitioned_tab VALUES (1, 'ONE', 'One', SYSDATE);
            *
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by
the System method

SQL>

The PARTITION clause is used to define which partition the row should be placed in.

INSERT INTO system_partitioned_tab PARTITION (part_1) VALUES (1, 'ONE', 'One', SYSDATE);
INSERT INTO system_partitioned_tab PARTITION (part_2) VALUES (2, 'TWO', 'Two', SYSDATE);
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'SYSTEM_PARTITIONED_TAB');

COLUMN table_name FORMAT A25
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A10

SELECT table_name, partition_name, high_value, num_rows
FROM   user_tab_partitions
ORDER BY table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE   NUM_ROWS
------------------------- -------------------- ---------- ----------
SYSTEM_PARTITIONED_TAB    PART_1                                   1
SYSTEM_PARTITIONED_TAB    PART_2                                   1

2 rows selected.

SQL>

Notice that the HIGH_VALUE for the partitions is blank.

The PARTITION clause is optional for update and delete statements, but omitting this clause will force all partitions to be scanned, since there is no way perform automatic partition pruning when the database has no control over row placement. When the PARTITION clause is used, you must be sure to perform the operation against the correct partition.

SQL> DELETE FROM system_partitioned_tab PARTITION (part_2) WHERE id = 1;

0 rows deleted.

SQL> UPDATE system_partitioned_tab PARTITION (part_1) SET code = 'TWO' WHERE id = 2;

0 rows deleted.

SQL>

The PARTITION clause can also be used in queries to target specific partitions.

SELECT COUNT(*)
FROM   system_partitioned_tab PARTITION (part_1);

  COUNT(*)
----------
         1

1 row selected.

SQL>

System partitioning gives you many of the advantages of partitioning, but leaves the decision of how the data is partitioned to the application layer.

Conditions and restrictions on system partitioning include:

• If you specify the PARTITION BY SYSTEM clause, but don't define partitions, a single partition is created with the name in the format of "SYS_Pn".
• If you specify PARTITION BY SYSTEM PARTITIONS n clause, the database creates "n" partitions with the name in the format of "SYS_Pn". The range of allowable values for "n" is from 1 to 1024K-1.
• System partitioning is not available for index-organized tables or a table that is part of a cluster.
• System partitioning can play no part in composite partitioning.
• You cannot split a system partition.
• System partitioning cannot be specified in a CREATE TABLE ... AS SELECT statement.
• To insert data into a system-partitioned table using an INSERT INTO ... AS subquery statement, you must use partition-extended syntax to specify the partition into which the values returned by the subquery will be inserted.