一、分區的引入
分區(partitioning)最早在Oracle 8.0中引入，這個過程是将一個表或索引實體地分解為多個更小、更可管理的部分。
對索引或表進行分區時可能發生3種情況：使用這些分區表的應用可能運作得更慢；可能運作得更快；有可能沒有任何變化。
Partition(分區)一直是 Oracle 資料庫引以為傲的一項技術，正是分區的存在讓 Oracle 高效的處理海量資料成為可能，在 Oracle 11g 中，分區技術在易用性和可擴充性上再次得到了增強。
二、分區的作用
2.1調高可用性
可用性的提高源自于每個分區的獨立性。
對象中一個分區的可用性(或不可用)并不意味着對象本身是不可用的。
優化器知道有這種分區機制，會相應地從查詢計劃中去除未引用的分區。
在一個大對象中如果一個分區不可用，你的查詢可以消除這個分區而不予考慮，這樣Oracle就能成功地處理這個查詢。
- 如果查詢必須涉及這個分區，那麼查詢報錯
- 如果查詢不涉及這個分區，那麼可以正常查詢别的分區
SQL> create tablespace p1 datafile '/app/oracle/oradata/pxdb/p1.dbf' size 100m autoextend on;SQL> create tablespace p2 datafile '/app/oracle/oradata/pxdb/p2.dbf' size 100m autoextend on;conn scott/tigercreate table emp_part(empno int,ename varchar2(20))partition by hash(empno)(partition part_1 tablespace p1,partition part_2 tablespace p2);SQL> insert into emp_part select empno,ename from emp;SQL> commit;SQL> select * from emp_part partition(part_1); EMPNO ENAME---------- -------------------- 7369 SMITH 7499 ALLEN 7934 MILLER …… …… ……8 rows selected.select * from emp_part partition(part_2); EMPNO ENAME---------- -------------------- 7521 WARD 7566 JONES 7788 SCOTT 7844 TURNER 7900 JAMES 7902 FORD
資料的“擺放”有些随機。通過使用散列分區，我們讓Oracle随機地(很可能均勻地)将資料分布到多個分區上。我們無法控制資料要分布到哪個分區上；Oracle會根據生成的散列鍵值來确定。
模拟一個分區故障。
SQL> alter tablespace p1 offline; --dba權限SQL> conn scott/tiger;SQL> select * from emp_part;select * from emp_part *ERROR at line 1:ORA-00376: file 6 cannot be read at this timeORA-01110: data file 6: '/app/oracle/oradata/pxdb/p1.dbf'SQL> select * from emp_part where empno='7844'; EMPNO ENAME---------- -------------------- 7844 TURNERSQL> select * from emp_part where empno='7876';select * from emp_part where empno='7876' *ERROR at line 1:ORA-00376: file 6 cannot be read at this timeORA-01110: data file 6: '/app/oracle/oradata/pxdb/p1.dbf'
結論：對于分區表來說，一個分區不可用并不影響其他分區的正常使用。
2.2減少管理負擔
例1：
假設資料庫中有一個10GB的索引。如果需要重建這個索引，而該索引未分區，你就必須将整個10GB的索引作為一個工作單元來重建。盡管可以線上地重建索引，但是要完全重建完整的10GB索引，還是需要占用大量的資源吧，并對全表産生鎖表操作。
如果将索引本身劃分為10個1GB的分區，各個索引的重建也更快(可能是原來的10倍)。
你可能隻需要重建全部聚集索引的10%，例如，隻是“最新”的資料(活動資料)需要重組，而所有“較舊”的資料(相當靜态)不受影響。
例2：
如果發現表中50%的行都是“移植”行，可能想進行修正。
建立一個分區表将有利于這個操作。
為了“修正”移植行，你往往必須重建對象，在這種情況下，就是要重建一個表。如果有一個100GB的表，就需要在一個非常大的“塊”(chunk)上連續地使用ALTER TABLE MOVE來執行這個操作。
另一方面，如果你有25個分區，每個分區的大小為4GB，就可以一個接一個地重建各個分區。
對于一個未分區對象所能做的工作，分區對象中的單個分區幾乎都能做到。
你甚至可能發現，移植行都集中在一個很小的分區子集中，是以，可以隻重建一兩個分區，而不是重建整個表。
2.3改善語句性能
分區最後一個總的(潛在)好處展現在改進語句(SELECT、INSERT、UPDATE、DELETE、MERGE)的性能方面。
2.3.1并行DML
修改資料庫中資料的語句有可能會執行并行DML(parallel DML，PDML)。
采用PDML時，Oracle使用多個線程或程序來執行INSERT、UPDATE或DELETE， 而不是執行一個串行程序。在一個有充足I/O帶寬的多CPU主機上，對于大規模的DML操作，速度的提升可能相當顯著。
在Oracle9i以前的版本中，PDML要求必須分區。如果你的表沒有分區，在先前的版本中就不能并行地執行這些操作。
如果表确實已經分區，Oracle會根據對象所有的實體分區數為對象指定一個最大并行度。
從很大程度上講，在Oracle9i及以後版本中這個限制已經放松，隻有一個突出的例外；如果希望在一個表上執行PDML，而且這個表的一個LOB列上有一個位圖索引，要并行執行操作就必須對這個表分區；另外并行度就限制為分區數。
不過，總的說來，使用PDML并不一定要求進行分區。
2.3.2查詢性能
在隻讀查詢(SELECT語句)的性能方面，分區對兩類特殊操作起作用：
- 分區消除(partition elimination)：處理查詢時不考慮某些資料分區。我們已經看到了一個分區消除的例子。
- 并行操作(parallel operation)：并行全表掃描和并行索引區間掃描就是這種操作的例子。
2.3.3 OLTP系統
事實上在一個OLTP系統中，查詢已經有以下特點：即索引通路相當快，是以，分區不會讓索引通路的速度有太大的提高(甚至根本沒有任何提高)。
這并不是說要絕對避免在OLTP系統中使用分區；而隻是說不要指望通過分區來提供大幅的性能提升。
盡管有效情況下分區能夠改善查詢的性能，但是這些情況在大多數OLTP應用中并不成立。
不過在OLTP系統中，你還是可以得到另外兩個可能的好處：減輕管理負擔以及有更高的可用性。
有分區的OLTP系統确實也有可能得到效率提示。例如，可以用分區來減少競争，進而提高并發度。
至于并行操作，你可能不希望在一個OLTP系統中執行并行查詢。你會慎用并行操作，而是交由DBA來完成重建、建立索引、收集統計資訊等工作。
2.3.4資料倉庫(OLAP)系統
處理許多查詢時都要求執行一個全表掃描，但是最後卻發現，一方面必須掃描數百萬條記錄，但另一方面其中大多數記錄并不适用于我們的查詢。如果使用一種明智 的分區機制，就可以實作消除分區，這樣在查詢某個給定的資料時，就可以隻對這個資料進行全面掃描。
這在所有可能的解決方案中是最佳的選擇。
在一個資料倉庫/決策支援環境中，會頻繁地使用并行查詢。是以，諸如并行索引區間掃描或并行快速全面索引掃描等操作對我們很有好處。
三、表分區機制
目前Oracle中有4種對表分區的方法：
(1)區間分區：通過資料範圍進行分區。例如，時間戳在Jan-2005内的所有記錄都存儲在分區1中，時間戳在Feb-2005内的所有記錄都存儲在分區2中，依此類推。這可能是Oracle中最常用的分區機制。
(2)散列分區：這是指在一個列(或多個列)上應用一個散列函數，行會按這個散列值放在某個分區中。
(3)清單分區：指定一個離散值集，來确定應當存儲在一起的資料。例如，可以指定STATUS列值在(’A’,’M’,’Z’)中的行放在分區1中，STATUS值在(‘D’,’P’,’Q’)中的行放在分區2中，依此類推。
(4)組合分區：這是區間分區和散列分區的一種組合，或者是區間分區與清單分區的組合。通過組合分區，你可以先對某些資料應用區間分區，再在區間中根據散列或清單來選擇最後的分區。
3.1區間(RANGE)分區
SQL > CREATE TABLE range_example( range_key_column date ,data varchar2(20))PARTITION BY RANGE (range_key_column)( PARTITION part_1 VALUES LESS THAN(to_date('01/01/2005','dd/mm/yyyy')),PARTITION part_2 VALUES LESS THAN(to_date('01/01/2006','dd/mm/yyyy')),PARTITION part_3 VALUES LESS THAN(MAXVALUE));
我們在範圍分區的時候，要注意範圍分區的值的情況。
向這個表插入一個行時，這一行肯定會放入三個分區中的某一個分區中，而不會再拒絕任何行，因為分區PART_3可以接受不能放在PART_1或PART_2中的任何RANG_KEY_COLUMN值(即使RANGE_KEY_COLUMN值為null，也會插入到這個新分區中)。
3.2散列(HASH)分區
對一個表執行散列分區(hash partitioning)時，Oracle會對分區鍵應用一個散列函數，以此确定資料應當放在N個分區中的哪一個分區中。Oracle建議N是2的一個幂(2、4、8、16等)，進而得到最佳的總體分布。
散列分區設計為能使資料很好地分布在多個不同裝置(磁盤)上，為表選擇的散列鍵應當是惟一的一個列或一組列，或者至少有足夠多的相異值，以便行能在多個分區上很好地(均勻地)分布。如果你選擇一個隻有4個相異值的列，并使用兩個分區，那麼最後可能把所有行都散列到同一個分區上，這就有悖于分區的最初目标！
SQL> CREATE TABLE hash_example( hash_key_column date,data varchar2(20))PARTITION BY HASH (hash_key_column)( partition part_1 tablespace p1,partition part_2 tablespace p2);
注意：
(1)如果使用散列分區，你将無從控制一行最終會放在哪個分區中。Oracle會應用散列函數，并根據散列的結果來确定行會放在哪裡。
(2)如果改變散列分區的個數，資料會在所有分區中重新分布(向一個散列分區表增加或删除一個分區時，将導緻所有資料都重寫，因為現在每一行可能 屬于一個不同的分區)。
(3)分區數應該是2的幂
如果分區數是2的幂，那麼分區将會均勻分布，如果不是2的幂，那麼分區将會不均勻分布。我們顯然希望得到均勻分布的分區。
3.3清單(LIST)分區
清單分區(list partitioning)是Oracle9i Release 1的一個新特性。它提供了這樣一種功能，可以根據離散的值清單來指定一行位于哪個分區。
SQL > create table list_example( state_cd varchar2(2),data varchar2(20))partition by list(state_cd)( partition part_1 values ( 'ME', 'NH', 'VT', 'MA' ),partition part_2 values ( 'CT', 'RI', 'NY' ));
(1)如果我們想插入清單分區中未指定的一個值，Oracle會向客戶應用傳回一個合适的錯誤。
SQL > insert into list_example values ( 'VA', 'data' );insert into list_example values ( 'VA', 'data' )*ERROR at line 1:ORA-14400: inserted partition key does not map to any partition SQL > alter table list_exampleadd partitionpart_3 values ( DEFAULT );SQL > insert into list_example values ( 'VA', 'data' );1 row created.
(2)關于DEFAULT的使用，有一點要注意：一旦清單分區表有一個DEFAULT分區，就不能再向這個表中增加更多的分區了。
此時必須删除DEFAULT分區，然後增加PART_4，再加回DEFAULT分區。
3.4組合分區
在組合分區中，頂層分區機制11g以前總是range分區，11g開始頂層分區可以使用list分區。
Oracle10g第二級分區機制可能是清單分區(range-list)或散列分區(range-hash)(在Oracle9i Release 1及以前的版本中，隻支援散列子分區，而沒有清單子分區)。11g後完全組合分區出現range-range,list-range,list-list,list-hash。
使用組合分區時，并沒有分區段，而隻有子分區段。
分區本身并沒有段(這就類似于分區表沒有段)。
資料實體的存儲在子分區段上，分區成為一個邏輯容器，或者是一個指向實際子分區的容器。
3.4.1 range-hash
SQL > CREATE TABLE composite_example( range_key_column date,hash_key_column int,data varchar2(20))PARTITION BY RANGE (range_key_column)subpartition by hash(hash_key_column) subpartitions 2(PARTITION part_1VALUES LESS THAN(to_date('01/01/2005','dd/mm/yyyy'))(subpartition part_1_sub_1,subpartition part_1_sub_2),PARTITION part_2VALUES LESS THAN(to_date('01/01/2006','dd/mm/yyyy'))(subpartition part_2_sub_1,subpartition part_2_sub_2));
在區間-散列組合分區中，Oracle首先會應用區間分區規則，得出資料屬于哪個區間。然後再應用散列函數，來确定資料最後要放在哪個實體分區中。
3.4.2 range-list
SQL > CREATE TABLE composite_range_list_example( range_key_column date,code_key_column int,data varchar2(20))PARTITION BY RANGE (range_key_column)subpartition by list(code_key_column)(PARTITION part_1VALUES LESS THAN(to_date('01/01/2005','dd/mm/yyyy'))(subpartition part_1_sub_1 values( 1, 3, 5, 7 ),subpartition part_1_sub_2 values( 2, 4, 6, 8 )),PARTITION part_2VALUES LESS THAN(to_date('01/01/2006','dd/mm/yyyy'))(subpartition part_2_sub_1 values ( 1, 3 ),subpartition part_2_sub_2 values ( 5, 7 ),subpartition part_2_sub_3 values ( 2, 4, 6, 8 )));
每個分區的子分區的數目可以是不一樣的。
注意：
如果用于确定分區的列有修改會發生什麼。需要考慮兩種情況：
(1)修改不會導緻使用一個不同的分區；行仍屬于原來的分區。這在所有情況下都得到支援。
(2)修改會導緻行跨分區移動。隻有當表啟用了行移動時才支援這種情況；否則，會産生一個錯誤。
(3)如果建構的系統會頻繁修改分區鍵，而且這種修改會導緻分區移動，這實在是一個糟糕的設計決策。
四、索引分區
索引與表類似，也可以分區。對索引進行分區有兩種可能的方法：
(1)随表對索引完成相應的分區：這也稱為局部分區索引(locally pertitioned index)。每個表分區都有一個索引分區，而且隻索引該表分區。一個給定索引分區中的所有條目都指向一個表分區，表分區中的所有行都表示在一個索引分區中。
(2)按區間對索引分區：這也稱為全局分區索引(globally partitioned index)。在此，索引按區間分區(或者在Oracle 10g中該可以按散列分區)，一個索引分區可能指向任何(和所有)表分區。
4.1局部索引和分區消除行為
如果查詢首先通路索引，它是否能消除分區完全取決于查詢中的謂詞。
SQL > CREATE TABLE partitioned_table( a int,b int,data char(20))PARTITION BY RANGE (a)(PARTITION part_1 VALUES LESS THAN(2) tablespace p1,PARTITION part_2 VALUES LESS THAN(3) tablespace p2);
小于2的值都在分區PART_1中，小于3的值則都在分區PART_2中。
建立一個局部字首索引LOCAL_PREFIXED和一個局部非字首索引LOCAL_NONPREFIXED。
SQL > create index local_prefixed on partitioned_table (a,b) local;Index created.SQL > create index local_nonprefixed on partitioned_table (b) local;Index created.
非字首索引在其定義中沒有以A作為其前導列(最前列)，這是這一點使之成為一個非字首索引。
SQL> insert into partitioned_tableselect mod(rownum-1,2)+1, rownum, 'x'from all_objects;48967 rows created.SQL> begindbms_stats.gather_table_stats( user,'PARTITIONED_TABLE',cascade=>TRUE );end;/PL/SQL procedure successfully completed.
向一個分區中插入一些資料，并收集統計資訊。
将表空間P2離線，其中包含用于表和索引的PART_2分區
SQL> alter tablespace p2 offline;Tablespace altered.
表空間P2離線後，Oracle就無法通路這些特定的索引分區。這就好像是我們遭遇了“媒體故障”，導緻分區不可用。現在我們查詢這個表，來看看不同的查詢需要哪些索引分區。第一個查詢編寫為允許使用局部字首索引：
查詢例1：
SQL> select * from partitioned_table where a = 1 and b = 1;A B DATA---------- ---------- --------------------1 1 x SQL> explain plan for select * from partitioned_table where a = 1 and b = 1;SQL> select * from TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
這個查詢成功了，通過檢視解釋計劃，可以看到這個查詢為什麼能成功。
使用LOCAL_PREFIXED的查詢成功了。
優化器能消除LOCAL_PREFIXED的PART_2不予考慮，因為我們在查詢中指定了A=1，而且在計劃中可以清楚地看到PSTART和PSTOP都等于1.
局部索引的限制：
要能夠實作消除分區，必須将分區鍵作為where條件
查詢例2：
SQL> select * from partitioned_table where b = 1;ERROR:ORA-00376: file 7 cannot be read at this timeORA-01110: data file 7: '/app/oracle/oradata/pxdb/p2.dbf'SQL> delete from plan_table;SQL> explain plan for select * from partitioned_table where b = 1;SQL> select * from table(dbms_xplan.display);
沒有實作消除分區，是以通路所有分區。
是否消除分區，關鍵看的是是否使用分區鍵，和字首索引、非字首索引沒有關系。
要使用非字首索引，必須使用一個允許分區消除的查詢，這樣效率才會提高。
查詢例3：
SQL> drop index local_prefixed;--删除字首索引Index dropped.SQL> delete from plan_table;SQL> select * from partitioned_table where a = 1 and b = 1;A B DATA---------- ---------- --------------------1 1 xSQL> explain plan for select * from partitioned_table where a = 1 and b = 1;SQL> select * from table(dbms_xplan.display);
結論：
非字首索引也能實作消除分區。
如果不能實作分區消除，那麼oracle将會對多個分區的多個分區索引進行掃描。
不必對非字首索引退避三舍，也不要認為非字首索引是主要的性能障礙。
重點是，要盡可能保證查詢包含的謂詞允許索引分區消除。
使用字首局部索引可以保證這一點，使用非字首索引則不能保證。
主要是因為字首索引将分區鍵作為前導列。
4.2局部索引和唯一限制
為了保證唯一性(這包括UNIQUE限制或PRIMARY KEY限制)，如果你想使用一個局部索引來保證這個限制，那麼分區鍵必須包括在限制本身中。這是局部索引的最大限制。
Oracle隻保證索引分區内部的唯一性，而不能跨分區。
這意味着不能一方面在一個TIMESTAMP字段上執行區間分區，而另一方面在ID上有一個主鍵(使用一個局部分區索引來保證)。
Oracle會利用全局索引來保證唯一性。
分區鍵建立分區以後，保證在不同的分區内，分區鍵一定是不同的。
如果建立主鍵的時候，包括分區鍵，那麼可以保證分區鍵不同的時候，一定在不同的分區内，分區鍵相同的時候，一定在同一個分區内。
這樣局部唯一性限制可以保證整個表的唯一性。
4.3全局索引
全局索引使用一種有别于底層表的機制進行分區。
表可以按一個TIMESTAMP(時間)列劃分為10個分區，而這個表上的一個全局索引可以按REGION(地區)列劃分為5個分區。
與局部索引不同，全局索引隻有一類，這就是字首全局索引(prefixed global index)。如果全局索引的索引鍵未從該索引的分區鍵開始，這是不允許的。這說明，不論用什麼屬性對索引分區，這些屬性都必須是索引鍵的前幾列。
CREATE TABLE partitioned( timestamp date,id int)PARTITION BY RANGE (timestamp)(PARTITION part_1 VALUES LESS THAN( to_date('01-jan-2000','dd-mon-yyyy') ) ,PARTITION part_2 VALUES LESS THAN( to_date('01-jan-2001','dd-mon-yyyy') ));
建立了一個分區表，分區鍵是timestamp。
SQL> create index partitioned_indexon partitioned(id)GLOBALpartition by range(id)(partition part_1 values less than(1000),partition part_2 values less than (MAXVALUE));
建立了全局索引以後，對全局索引進行了分區。
我們在進行索引搜尋的時候，首先根據where id=100，迅速的确定這個搜尋落在哪個索引分區内，實作了索引分區的消減分區。
也就是說，我們隻需要搜尋一個索引樹。
注意：
這個索引中使用了MAXVALUE。
MAXVALUE不僅可以用于索引中，還可以用于任何區間分區表中。它表示區間的“無限上界”。
在此前的所有例子中，我們都使用了區間的硬性上界(小于<< span="">某個值>的值)。不過，全局索引有一個需求，即最高分區(最後一個分區)必須有一個值為MAXVALUE的分區上界。這可以確定底層表中的所有行都能放在這個索引中。
SQL> alter table partitioned add constraint partitioned_pk primary key(id);
因為在id列上有一個全局索引，是以這個主鍵直接使用這個全局索引。
SQL> drop index partitioned_index;drop index partitioned_index*ERROR at line 1:ORA-02429: cannot drop index used for enforcement of unique/primary key
這個表示我們建立了的索引被主鍵使用。
SQL> create index partitioned_index2on partitioned(timestamp,id)GLOBALpartition by range(id)(partition part_1 values less than(1000),partition part_2 values less than (MAXVALUE));partition by range(id)*ERROR at line 4:ORA-14038: GLOBAL partitioned index must be prefixed
全局分區索引必須能夠實作當進行where條件比對的時候，首先能夠消減分區，然後在一個分區内實作索引的搜尋。
我們可以以timestamp進行分區，也可以以timestamp,id複合進行分區。
也就是必須使用索引的前導列進行分區。
create index partitioned_index2on partitioned(timestamp)GLOBALpartition by range(timestamp)(PARTITION part_1 VALUESLESS THAN( to_date('01-jan-2000','dd-mon-yyyy') ) ,PARTITION part_2 VALUESLESS THAN( MAXVALUE)); --可建立成功create index partitioned_index3on partitioned(timestamp,id)GLOBAL ; --可建立成功
五、ORACLE 11g分區表特性
5.1引用分區表
使子表使用父表的分區條件進行分區，而無需在子表中額外添加用于分區的列，造成資料逆規範化，造成空間浪費等問題。
(1)建立主表
CREATE TABLE orders(order# NUMBER PRIMARY KEY,order_date DATE NOT NULL,data VARCHAR2 (30))PARTITION BY RANGE (order_date)(PARTITION part_2016 VALUES LESS THAN (TO_DATE ('01-01-2017','dd-mm-yyyy')),PARTITION part_2017 VALUES LESS THAN (TO_DATE ('01-01-2018','dd-mm-yyyy')));SQL> insert into orders values ( 1, to_date( '01-01-2017', 'dd-mm-yyyy' ), 'xxx' );SQL> insert into orders values ( 2, to_date( '01-01-2016', 'dd-mm-yyyy' ), 'yyy' );SQL> commit;
(2)建立子表
create table order_line_items( order# number NOT NULL,line# number NOT NULL,data varchar2(30),constraint c1_pk primary key(order#,line#),constraint c1_fk_p foreign key(order#) references orders)enable row movementpartition by reference(c1_fk_p);insert into order_line_items values ( 1, 1, 'yyy' );insert into order_line_items values ( 2, 1, 'yyy' );commit;SELECT table_name, partition_name FROM user_tab_partitions WHERE table_name IN ('ORDERS', 'ORDER_LINE_ITEMS')ORDER BY table_name, partition_name;SQL> SELECT table_name, partition_name FROM user_tab_partitions WHERE table_name IN ('ORDERS', 'ORDER_LINE_ITEMS')ORDER BY table_name, partition_name; TABLE_NAME PARTITION_NAME------------------------------ ------------------------------ORDERS PART_2016ORDERS PART_2017ORDER_LINE_ITEMS PART_2016ORDER_LINE_ITEMS PART_2017
(3)删除父表partition，可以級聯删除子表分區
SQL> alter table orders drop partition part_2016 update global indexes;SQL> SELECT table_name, partition_name FROM user_tab_partitions WHERE table_name IN ('ORDERS', 'ORDER_LINE_ITEMS')ORDER BY table_name, partition_name; TABLE_NAME PARTITION_NAME------------------------------ ------------------------------ORDERS PART_2017ORDER_LINE_ITEMS PART_2017
(4)添加父表partition，可以級聯添加子表分區
alter table orders add partition part_2018 values less than (to_date( '01-01-2019', 'dd-mm-yyyy' ));SELECT table_name, partition_name FROM user_tab_partitions WHERE table_name IN ('ORDERS', 'ORDER_LINE_ITEMS')ORDER BY table_name, partition_name; TABLE_NAME PARTITION_NAME------------------------------ ------------------------------ORDERS PART_2017ORDERS PART_2018ORDER_LINE_ITEMS PART_2017ORDER_LINE_ITEMS PART_2018
(5)删除子表partition，報錯(truncate子表partition可行)
SQL> alter table order_line_items drop partition PART_2017 update global indexes;alter table order_line_items drop partition PART_2017 update global indexes *ERROR at line 1:ORA-14255: table is not partitioned by Range, List, Composite Range or Composite List method
5.2間隔分區表
定義分區規則，當有符合條件的資料插入時自動建立分區。
可以使用alter指令将現有的區間分區表修改為間隔分區，也可以使用create建立一個間隔分區。
(1)建立間隔分區表
CREATE TABLE orders2(order# NUMBER PRIMARY KEY,order_date DATE NOT NULL)PARTITION BY RANGE (order_date)INTERVAL ( NUMTOYMINTERVAL (1, 'month') )(PARTITION p201612 VALUES LESS THAN (TO_DATE ('01-01-2017', 'dd-mm-yyyy')));
- 插入資料
insert into orders2 values(1,TO_DATE ('01-12-2016', 'dd-mm-yyyy'));insert into orders2 values(2,TO_DATE ('02-01-2017', 'dd-mm-yyyy'));insert into orders2 values(3,TO_DATE ('02-02-2017', 'dd-mm-yyyy'));commit;SQL> SELECT table_name, partition_nameFROM user_tab_partitionsWHERE table_name IN ('ORDERS2')ORDER BY table_name, partition_name;TABLE_NAME PARTITION_NAME------------------------------ ------------------------------ORDERS2 P201612ORDERS2 SYS_P61ORDERS2 SYS_P62
- 删除資料已生成的分區不變
- 将原有的range分區表變更為interval分區表
ALTER TABLE orders SET INTERVAL(1000000);ALTER TABLE orders SET INTERVAL (NUMTODSINTERVAL(1,'DAY')); --NUMTODSINTERVAL常用的機關有 ('day','hour','minute','second')ALTER TABLE orders SET INTERVAL (numtoyminterval(1,'month')); --numtoyminterval常用的機關有'year','month'
- 将interval分區表轉為range分區表
ALTER TABLE orders3 SET INTERVAL();
5.3系統分區表
CREATE TABLE S_P_TEST(col1 number,name varchar2(100))PARTITION BY SYSTEM(PARTITION p1,PARTITION p2,PARTITION p3,PARTITION p4);
--插入時要指明分區
SQL> insert into S_P_TEST select object_id,object_name from dba_objects;insert into S_P_TEST select object_id,object_name from dba_objects *ERROR at line 1:ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System methodSQL> insert into S_P_TEST partition(p1) select object_id,object_name from dba_objects where object_type='TABLE';SQL> insert into S_P_TEST partition(p2) select object_id,object_name from dba_objects where object_type='INDEX';SQL> select count(1) from S_P_TEST;SQL> select count(1) from S_P_TEST partition(p1);SQL> select count(1) from S_P_TEST partition(p2);
--相同的資料可以插入到不同的分區中，這說明資料和分區沒有任何關系
SQL> insert into S_P_TEST partition(p3) values(1,'aa');SQL> insert into S_P_TEST partition(p4) values(1,'aa');SQL> commit;SQL> select * from S_P_TEST partition(p3);SQL> select * from S_P_TEST partition(p4);
系統分區表的使用場景并不多，如果對傳統的分區表資料的特性了解是：
- 資料和分區是有聯系的；
- 分區就是把很多的表粘在一起管理。那系統分區表隻有第二個特性。
5.4完整分區類型
(1)範圍分區(range)
(2)哈希分區(hash)
(3)清單分區(list)
(4)範圍－哈希複合分區(range-hash)
(5)範圍－清單複合分區(range-list)
oracle11g 新增完全組合分區
range-range,list-range,list-list,list-hash
5.5虛拟列分區
以下内容來自https://www.cnblogs.com/moonandstar08/p/5100567.html
(1)隻能在堆組織表(普通表)上建立虛拟列
(2)虛拟列不能是LOB或者RAW類型；
(3)虛拟列的值并不是真實存在的，隻有用到時，才根據表達式計算出虛拟列的值，磁盤上并不存放。
(4)可把虛拟列當做分區關鍵字建立分區表，這是ORACLE 11g的另一新特性--虛拟列分區
建立帶有虛拟列的分區表：
create table test(n1 number, c1 varchar2(80), n2 number generated always as (n1*0.8)) ---建立帶有虛拟列的分區 create table test1(n1 number, c1 varchar2(80), v1 varchar2(10) generated always as (substr(c1,1,1))) partition by list (v1) (partition v11 values('I'), partition v12 values('O'), partition v13 values('E'), partition v15 values(default) );
查詢：
select * from test1 partition(v11);