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. TheINSERT 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> PARTITION clause is used to define which partition the row should be placed in.Notice that theINSERT 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> 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.TheSQL> 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> PARTITION clause can also be used in queries to target specific partitions.System partitioning gives you many of the advantages of partitioning, but leaves the decision of how the data is partitioned to the application layer.SELECT COUNT(*) FROM system_partitioned_tab PARTITION (part_1); COUNT(*) ---------- 1 1 row selected. SQL> Conditions and restrictions on system partitioning include:
|