oracle11g_system partition系統分割槽

oracle11g開始,支援system partition;

-----system partition的概念
Use this clause to create system partitions. System partitioning does not entail any partitioning key columns, nor do system partitions have any range or
list bounds or hash algorithms. Rather, they provide a way to equipartition dependent tables such as nested table or domain index storage tables with
partitioned base tables.
  和之前的hash,range,list partition不同,不用指定分割槽列;也沒有hash,range,list的演算法機制;它透過分割槽的基表把依賴表的資料平均分佈;
  它適用於不能按range,list,hash進行分割槽,又想把資料均分在不同的分割槽中,可採用此種方式;

If you specify only PARTITION BY SYSTEM, then the database creates one partition with a system-generated name of the form. SYS_Pn.
  由系統產生分割槽名字,如:SYs_pN

If you specify PARTITION BY SYSTEM PARTITIONS integer, then the database creates as many partitions as you specify in integer, which
can range from 1 to 1024K-1.
  指定partition by system partitioins inteter,產生integer分割槽;分割槽數量自1-1024k-1

----system partition的語法
SQL> create table t_system_partition(a int,b int)
  2  partition by system partitions 2;

Table created.

---報錯:原因對於system partition的表在dml必須指定分割槽名字,因為system partition沒有顯式指定分割槽邊界
SQL> insert into t_system_partition values(1,1);
insert into t_system_partition values(1,1)
            *
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on
tables partitioned by the System method

---指定分割槽後dml正常執行
SQL> insert into t_system_partition partition(sys_p41) values(1,1);

1 row created.

SQL> insert into t_system_partition partition(sys_p42) values(1,1);

1 row created.

SQL> insert into t_system_partition partition(sys_p42) values(3,3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_system_partition;

         A          B
---------- ----------
         1          1
         1          1
         3          3

SQL> select * from t_system_partition partition(sys_p41);

         A          B
---------- ----------
         1          1

SQL> select * from t_system_partition partition(sys_p42);

         A          B
---------- ----------
         1          1
         3          3

-----system partition的操作限制
---iot或集表不能構建system partition
You cannot system partition an index-organized table or a table that is part of a cluster.
 
---組合分割槽不支援system partition
Composite partitioning is not supported with system partitioning.
 
---不能拆分system partition
You cannot split a system partition.
 
---用create table as select不能使用system partition
You cannot specify system partitioning in a CREATE TABLE ... AS SELECT statement.
 
---如何想用insert into as select 把資料插入到system partition中,必須在insert into指定分割槽表
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.

 

 

 

後記:關於oracle11g分割槽新特性,可參考: