ORACLE11G SystemPartitioning(系统分区）

System Partitioning
As you would expect, system partitioning allows large tables to be broken down into smaller partitions,
but unlike other partitioning schemes, the database has no control over the placement of rows during insert operations.
The following example shows the creation of a system partitioned table.

SQL> CREATE TABLE system_par_tab (
  2 id NUMBER,
  3 code VARCHAR2(10),
  4 description VARCHAR2(50),
  5 created_date DATE
  6 )
  7 PARTITION BY SYSTEM
  8 (
  9 PARTITION part_1,
 10 PARTITION part_2
 11 );

Table created.

SQL> insert into system_par_tab VALUES (1, 'first','first',sysdate);
insert into system_par_tab VALUES (1, 'first','first',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> SELECT table_name, partition_name, high_value, num_rows
  2 FROM user_tab_partitions
  3 ORDER BY table_name, partition_name;
  
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------

SYSTEM_PAR_TAB PART_1 0
SYSTEM_PAR_TAB PART_2 0

SQL> insert into system_par_tab partition(part_1) values(1,'first','first',sysdate);

1 row created.

SQL> l
  1* insert into system_par_tab partition(part_1) values(1,'first','first',sysdate)
SQL> c /part_1/part_2
  1* insert into system_par_tab partition(part_2) values(1,'first','first',sysdate)
SQL> c /(1,'first','first'/(2,'second','second'
  1* insert into system_par_tab partition(part_2) values(2,'second','second',sysdate)
SQL> /

1 row created.

SQL> commit;

Commit complete.

   这里分区条件需要选择性的声明更新或者删除，但省略这些条件，将迫使所有分区进行扫描，既没有办法自动更新和删除，当分区被使用时，必须对应正确的分区进行操作。
 

SQL> DELETE FROM system_par_tab partition(part_2) where id = 1;

0 rows deleted.

SQL> update system_par_tab partition(part_1) set code='second' where id = 2;

0 rows updated.

SQL> select count(*) from system_par_tab ;

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

         2

SQL> select count(*) from system_par_tab partition(part_1);

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

         1

SQL> c /1/2
  1* select count(*) from system_par_tab partition(part_2)
SQL> /

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

         1

   * 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 statem