ORACLE 範圍分割槽 partition-range分割槽

xulongxc發表於2014-03-12
Oracle
範圍分割槽的用法說明
Range partitioning maps data to partitions based on ranges of partition key values that   you establish for each partition. It is the most common type of partitioning and is often used with dates. For example, you might want to partition sales data into monthly
partitions.
When using range partitioning, consider the following rules:
■ Each partition has a VALUES LESS THAN clause, which specifies a noninclusive upper bound for the partitions. Any values of the partition key equal to or higher than this literal are added to the next higher partition.
■ All partitions, except the first, have an implicit lower bound specified by the VALUES LESS THAN clause on the previous partition.
■ A MAXVALUE literal can be defined for the highest partition. MAXVALUE represents a virtual infinite value that sorts higher than any other possible value for the partition key, including the null value.
       由此可知，range分割槽是應用最廣的分割槽方式，它是以列的值的範圍來做為分割槽的劃分條件，將記錄存放到列值所在的 range 分割槽中，因此在建立的時候呢，需要你指定基於的列，以及分割槽的範圍值，如果某些記錄暫無法預測範圍，可以建立 maxvalue 分割槽，所有不在指定範圍內的記錄都會被儲存到 maxvalue 所在分割槽中，並且支援多列做為依賴列。每個分割槽內儲存的資料小於該分割槽指定的values less than數值，除第一個分割槽外，其它分割槽都有最小值且等於上一分割槽指定的values less than數值。
1、建立range分割槽
語法如下： 
column:分割槽依賴列（如果是多個，以逗號分隔）；
partition:分割槽名稱；
values less than：後跟分割槽範圍值（如果依賴列有多個，範圍對應值也是多個，以逗號分隔開）；
tablespace_clause：分割槽的儲存屬性，例如所在表空間等屬性（可為空），預設繼承基表所有表空間的屬性。
Range Partition Example
SQL> edit
已寫入 file afiedt.buf
  1 create table t_partition_range(id number,name varchar2(20))
  2 partition by range(id)(
  3 partition p1 values less than(10),
  4 partition p2 values less than(20),
  5 partition p3 values less than(30),
  6 partition pmax values less than(maxvalue)
  7* )
SQL> /
表已建立。
通過資料字典“user_part_tables記錄分割槽表的資訊”，“user_tab_partitions記錄表分割槽的資訊”可查詢建立分割槽的資訊。。
例如：
SQL> select table_name,partitioning_type,partition_count
  2 from user_part_tables where table_name='T_PARTITION_RANGE';
TABLE_NAME PARTITI PARTITION_COUNT
------------------------------ ------- ---------------
T_PARTITION_RANGE RANGE 4

SQL> set linesize 200
SQL> column partition_name  format a20; 
SQL> column high_value format 999;
SQL> column tablespace_name  format a30; 
SQL> edit
已寫入 file afiedt.buf
  1 select partition_name,high_value,tablespace_name
  2 from user_tab_partitions where table_name='T_PARTITION_RANGE'
  3* order by partition_position
SQL> /
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------------------------------------------------------------------
P1 10 JJJG
P2 20 JJJG
P3 30 JJJG
PMAX MAXVALUE JJJG
2、range分割槽表上建立索引
2.1、建立global索引range分割槽：
SQL> edit
已寫入 file afiedt.buf
  1 create index idx_parti_range_id on t_partition_range(id)
  2 global partition by range(id)(
  3 partition i_p1 values less than(10),
  4 partition i_p2 values less than(20),
  5 partition i_p3 values less than(30),
  6* partition i_pmax values less than(maxvalue))
SQL> /
索引已建立。
由上例可以看出，建立 global 索引的分割槽與建立表的分割槽語句格式完全相同，而且其分割槽形式與索引 所在表的分割槽形式沒有關聯關係。
注：上例是range分割槽表建立range分割槽的 global索引，並不表示range分割槽表只能建立range分割槽global索引，也可建立hash分割槽的global索引。
查詢索引的分割槽資訊可通過user_part_indexes,user_ind_partitions兩個資料字典。
2.2、建立Local分割槽索引
SQL> create index idx_parti_range_id on t_partition_range(id) local;
索引已建立。
SQL> select index_name,partitioning_type,partition_count
  2 from user_part_indexes
  3 where index_name='IDX_PARTI_RANGE_ID';
INDEX_NAME PARTITI PARTITION_COUNT
------------------------------ ------- ---------------
IDX_PARTI_RANGE_ID RANGE 4
SQL> edit
已寫入 file afiedt.buf
  1 select partition_name,high_value,tablespace_name
  2 from user_ind_partitions
  3 where index_name='IDX_PARTI_RANGE_ID'
  4* order by partition_position
SQL> /
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------------------------------------------------------------------
P1 10 JJJG
P2 20 JJJG
P3 30 JJJG
PMAX MAXVALUE JJJG
可以看出， local 索引的分割槽完全繼承表的分割槽的屬性，包括分割槽型別，分割槽的範圍值即不需指定也不
能更改，即local 索引的分割槽維護完全依賴於其索引所在表，不過分割槽名稱及分割槽所在的表空間是可以自定義的，如下：
SQL> create index IDX_PART_RANGE_ID ON T_PARTITION_RANGE(id) local (
2 partition i_range_p1 tablespace tbspart01,
3 partition i_range_p2 tablespace tbspart01,
4 partition i_range_p3 tablespace tbspart02,
5 partition i_range_pmax tablespace tbspart02
6 );
3、分割槽表的管理
3.1、增加表分割槽（add partition）
增加表分割槽，適用於所有分割槽形式，語法為alter table tbname add partition……
值得注意的是像list，range這種存在範圍值的分割槽，所要增加的分割槽值必須大於當前分割槽中的最大值（如果當前存在maxvalue或default的
分割槽，add partition會報錯，這種情況只能使用spilt），hash分割槽則無此限制。
例如：
SQL> alter table t_partition_range add partition p4 values less than(40);
alter table t_partition_range add partition p4 values less than(40)
                                            *
第 1 行出現錯誤:
ORA-14074: 分割槽界限必須調整為高於最後一個分割槽界限。
如前面所說，range分割槽存在maxvalue時，add partition會報錯，下面我們先進行分割槽拆分。
3.2、拆分表分割槽（split partition）
split partition即將一個分割槽拆分成二個，其用途非常廣泛，如通常發現一個分割槽過大，就可以將其分解成多個分割槽，常最見的還是
split maxvalue/default的分割槽。
該命令的語法針對不同分割槽會有不同形式，
For range partition ： alter table tbname split partition ptname at (value) into (partition newpt1
tbs_clause,partition newpt2 tbs_clause);
For list partition : alter table tbname split partition ptname values (v1,v2...vn) into (partition newpt1
tbs_clause,partition newpt2 tbs_clause);
上述兩項，如果是操作子分割槽，則將 partition 關鍵字換成 subpartition 即可。舊分割槽中符合新定義值的
記錄會儲存到指定的第一個分割槽中，其它的記錄儲存到第二個分割槽。
例：
查詢表現有分割槽
SQL> edit
已寫入 file afiedt.buf
  1 select partition_name,high_value,tablespace_name
  2 from user_tab_partitions where table_name='T_PARTITION_RANGE'
  3* order by partition_position
SQL> /
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------------------------------------------------------------------
P1 10 JJJG
P2 20 JJJG
P3 30 JJJG
PMAX MAXVALUE JJJG

對range分割槽表的maxvalue分割槽進行拆分
SQL> alter table t_partition_range split partition pmax at(40) into(
  2 partition p4 ,partition pmax);
表已更改。
拆分後，查詢表分割槽如下
SQL> select partition_name,high_value,tablespace_name
  2 from user_tab_partitions where table_name='T_PARTITION_RANGE'
  3 order by partition_position;
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------------------------------------------------------------------
P1 10 JJJG
P2 20 JJJG
P3 30 JJJG
P4 40 JJJG
PMAX MAXVALUE JJJG
即小於40的存放p4分割槽，其它存放pmax分割槽。
提示：
     1、split partition/subpartition 不能用於 hash 分割槽或 hash 子分割槽 (hash 的話，直接用 add partition 就好 了 )
     2、split partition/subpartition 視被分隔的分割槽資料量多少，可能需要花費不小的代價，相當於該分割槽資料的全掃描，我們也許可以形容為： full partition scan:) ，除非：
    Split 後的兩個分割槽中，至少有一個是空的，並且非空的那個分割槽的儲存屬性與 split 前的儲存屬性完全相同 。
    如果 split 的分割槽包含 lob 欄位， split 後非空的那個分割槽中該欄位的儲存屬性也必須與 split 前 的儲存屬性完全相同。 
    這種情況下的 split partition/subpartition 也會非常高效， oracle 會自動進行優化，此時的分割槽操作類 似於 add partition 。 
    通常情況下，如果在執行 split partition/subpartition 時，如果沒有指定 update indexes 子句，都會造成 local 和 global 索引的失效。注意，我們說的是通常，如果你 split partition/subpartition 的是個空分割槽， 或 者沒有觸發任何資料移動或變化，那麼即使不加 update indexes ，也不會影響到索引。當然，保險起見，建議你還是執行完之後，查詢一下資料字典，確認一下當前索引的狀態。
3.3、刪除表分割槽（drop partition）
刪除表分割槽包含兩種操作，分別是：
刪除分割槽：alter table[tablename] drop partition[ptname];
刪除子分割槽：alter table[tablename] drop subpartition[ptname];
除hash分割槽和hash子分割槽外，其它分割槽格式都可以支援這項操作。
例：刪除上面split partition新增的p4分割槽
SQL> alter table t_partition_range drop partition p4;
表已更改。
SQL> edit
已寫入 file afiedt.buf
  1 select partition_name,high_value,tablespace_name
  2 from user_tab_partitions where table_name='T_PARTITION_RANGE'
  3* order by partition_position
SQL> /
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------------------------------------------------------------------
P1 10 JJJG
P2 20 JJJG
P3 30 JJJG
PMAX MAXVALUE JJJG
注意：刪除分割槽時，該分割槽記憶體儲的資料也將同時刪除。
    由於是ddl操作，這種刪除比較迅速，因此如果你確認某個分割槽的資料都要被刪除，使用drop partition會比delete更高效，
如果你的本意是希望刪除掉指定分割槽但保留資料，你應該使用merge partition，接下來就會講到。
    同樣，如果你在執行該語句時沒有指定 update indexes 子句，也會導致 global 索引的失效，至於 local 索引嘛，刪除分割槽時對應的索引分割槽會被同時刪除，但其它分割槽的local 索引不會受到影響。
3.4、合併表分割槽（merge partitions）
合併二個分割槽成一個分割槽，適用於除hash分割槽之外的所有分割槽形式（hash分割槽此功能用coalesce partition收縮表分割槽 代替）
語法： alter table tbname merge partitions/subpartitions pt1,pt2 into partition/subpartition pt3; 
同樣也支援 update indexes 子句以避免單獨執行造成索引失效的問題。
注意，要合併的兩個分割槽必須是連續的，合併分割槽操作不會造成資料丟失，另外如果想為新分割槽指定屬性，在語句末尾處可增加儲存屬性（
如果不指定，則新分割槽預設繼續表的儲存屬性）。
例：
--range分割槽表 t_partition_range先split pmax分割槽，即新增p4分割槽
SQL> edit
已寫入 file afiedt.buf
  1 alter table t_partition_range split partition pmax at(40) into (
  2* partition p4,partition pmax)
SQL> /
表已更改。
SQL> edit
已寫入 file afiedt.buf
  1 select partition_name,high_value,tablespace_name
  2 from user_tab_partitions where table_name='T_PARTITION_RANGE'
  3* order by partition_position
SQL> /
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------------------------------------------------------------------
P1 10 JJJG
P2 20 JJJG
P3 30 JJJG
P4 40 JJJG
PMAX MAXVALUE JJJG
--執行merge partition操作
SQL> edit
已寫入 file afiedt.buf
  1 alter table t_partition_range merge partitions p4,pmax into
  2* partition pmax
SQL> /
表已更改。
--檢視合併後表分割槽情況
SQL> edit
已寫入 file afiedt.buf
  1 select partition_name,high_value,tablespace_name
  2 from user_tab_partitions
  3 where table_name='T_PARTITION_RANGE'
  4* order by partition_position
SQL> /
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------------------------------------------------------------------
P1 10 JJJG
P2 20 JJJG
P3 30 JJJG
PMAX MAXVALUE JJJG
3.5、交換表分割槽（Exchange Partitions）
Exchange Partitions就是遷移資料。其 提供了一種方式，讓你在表與表或分割槽與分割槽之間遷移資料，注意不是將錶轉換成 
分割槽或非分割槽的形式，而僅只是遷移表中資料 ( 互相遷移 ) ，由於其號稱是採用了更改資料字典的方式，因此效率最高 ( 幾乎不涉及 io 操作 ) 。 Exchange partition 適用於所有分割槽格式，你可以將資料從分割槽表遷移到非分割槽表，也可以從非分割槽表遷移至分割槽表，或者從 hash partition 到 range partition 諸如此類吧。
其語法很簡單： alter table tbname1 exchange partition/subpartition ptname with table tbname2;
例：先給t_partition_range表插入幾條資料。
SQL> insert into t_partition_range values(11,'a');
已建立 1 行。
SQL> insert into t_partition_range values(12,'b');
已建立 1 行。
SQL> insert into t_partition_range values(13,'c');
已建立 1 行。
SQL> commit;
提交完成。
SQL> select * from t_partition_range;
        ID NAME
---------- --------------------
        11 a
        12 b
        13 c
--建立非分割槽表，結構與t_partition_range表相同
SQL> create table t_partition_range_tmp(id number,name varchar2(50));
表已建立。
SQL> edit
已寫入 file afiedt.buf
  1 alter table t_partition_range exchange partition p2
  2* with table t_partition_range_tmp
SQL> /
alter table t_partition_range exchange partition p2
*
第 1 行出現錯誤:
ORA-14097: ALTER TABLE EXCHANGE PARTITION 中的列型別或大小不匹配
--交換失敗，因表結構列型別不同，檢視二表結構
SQL> desc t_partition_range;
 名稱 是否為空? 型別
 ----------------------------------------------------------------------------------- -------- ------
 ID NUMBER
 NAME VARCHAR2(20)
SQL> desc t_partition_range_tmp;
 名稱 是否為空? 型別
 ----------------------------------------------------------------------------------- -------- ------
 ID NUMBER
 NAME VARCHAR2(50)
--修改t_partition_range_tmp表name欄位長度
SQL> alter table t_partition_range_tmp modify name varchar2(20);
表已更改。
SQL> alter table t_partition_range exchange partition p2
  2 with table t_partition_range_tmp;
表已更改。
SQL> select * from t_partition_range;
未選定行
SQL> select * from t_partition_range_tmp;
        ID NAME
---------- --------------------
        11 a
        12 b
        13 c
--執行exchange partition，交換資料
SQL> alter table t_partition_range exchange partition p2
  2 with table t_partition_range_tmp;
表已更改。
SQL> select * from t_partition_range;
        ID NAME
---------- --------------------
        11 a
        12 b
        13 c
SQL> select * from t_partition_range_tmp;
未選定行
--t_partition_range_tmp插入資料，包含t_partition_range 非p2分割槽的資料
SQL> insert into t_partition_range_tmp values(14,'d');
已建立 1 行。
SQL> insert into t_partition_range_tmp values(9,'e');
已建立 1 行。
SQL> insert into t_partition_range_tmp values(31,'f');
已建立 1 行。
SQL> commit;
提交完成。
SQL> select * from t_partition_range;
        ID NAME
---------- --------------------
        11 a
        12 b
        13 c
SQL> select * from t_partition_range_tmp;
        ID NAME
---------- --------------------
        14 d
         9 e
        31 f

--再次執行exchange partition
SQL> alter table t_partition_range exchange partition p2
  2 with table t_partition_range_tmp;
with table t_partition_range_tmp
           *
第 2 行出現錯誤:
ORA-14099: 未對指定分割槽限定表中的所有行
--交換失敗，因為 t_partition_range_tmp表中值9,31不在 t_partition_range表中p2分割槽內，附加without validation可交換成功
SQL> alter table t_partition_range exchange partition p2
  2 with table t_partition_range_tmp without validation;
表已更改。
SQL> select * from t_partition_range partition(p2);
        ID NAME
---------- --------------------
        14 d
         9 e
        31 f
SQL> select * from t_partition_range_tmp;
        ID NAME
---------- --------------------
        11 a
        12 b
        13 c
--刪除交換後的 t_partition_range表中非p2分割槽內的值9,31，竟然刪除0行。
SQL> delete from t_partition_range where id in(9,31);
已刪除0行。
--指定9,31數值查詢也查不到
SQL> select * from t_partition_range where id in(9,31);
未選定行
--指定p2分割槽查詢能查出來
SQL> select * from t_partition_range partition(p2);
        ID NAME
---------- --------------------
        14 d
         9 e
        31 f
--直接對錶做delete操作，記錄可全刪除
SQL> delete from t_partition_range;
已刪除3行。
SQL> rollback;
回退已完成。
SQL> delete from t_partition_range where id=9;
已刪除0行。
SQL> delete from t_partition_range where id=14;
已刪除 1 行。
SQL> rollback;
回退已完成。
注意：
    1、涉及交換的兩表之間表結構必須一致，除非附加 with validation 子句 ;
    2、如果是從非分割槽表向分割槽表做交換，非分割槽表中的資料必須符合分割槽表中指定分割槽的規則，除非 附加 without validation 子句 ，交換後，分割槽表中指定交換的分割槽資料含有不符合該分割槽定義的資料，這些資料無法直接刪除或查詢（如上例內容所示）。
    3、如果從分割槽表向分割槽表做交換，被交換的分割槽的資料必須符合分割槽規則，除非附加 without validation 子句 ; 
    Global 索引或涉及到資料改動了的 global 索引分割槽會被置為 unusable ，除非附加 update indexes 子 句。 
提示：
    一旦附加了 without validation 子句，則表示不再驗證資料有效性，因此指定該子句時務必慎重。
3.6、截斷表分割槽（Truncate Partition)
Truncate partition 就像 truncate table 一樣，直接從頭部截斷資料，用來刪除資料那是效率超高無比。 但 是如果該表有外來鍵引用的話， 
ddl 的 truncate 就不好使了，只能要麼使用delete ，要麼先disable掉外來鍵關聯再truncate 了。同樣，在不指定 update indexes 子句的情況下， truncate partition 也會造成分割槽所在表的 global索引失效。 
語法非常簡單： alter table tbname truncate partition/subpartition ptname;
例：
SQL> select * from t_partition_range partition(p2);
        ID NAME
---------- --------------------
        11 a
        12 b
        13 c

SQL> alter table t_partition_range truncate partition p2;
表被截斷。
SQL> select * from t_partition_range partition(p2);
未選定行
3.7、移動表分割槽（Move partition）
Move partition 與 modify partition 的功能相似，但又比之更加強勁，比如可以修改分割槽所在表空間等等 ， 與 move table 的操作很類似，某些時間也非常有用，比如降低行遷移。另外，move partition修改分割槽所在表空間時，分割槽對應的local索引表空間不會被修改。
語法很簡單： Alter table tbname move partition/subpartition ptname .....;
SQL> select partition_name,tablespace_name from user_tab_partitions
  2 where table_name='T_PARTITION_RANGE';
PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------
P1 JJJG
P2 JJJG
P3 JJJG
PMAX JJJG
SQL> alter table t_partition_range move partition p3 tablespace jjjg_data;
表已更改。
SQL> select partition_name,tablespace_name from user_tab_partitions
  2 where table_name='T_PARTITION_RANGE';
PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------
P1 JJJG
P2 JJJG
P3 JJJG_DATA
PMAX JJJG

SQL> edit
已寫入 file afiedt.buf
  1 select partition_name,tablespace_name from user_ind_partitions
  2* where index_name='IDX_PARTI_RANGE_ID'
SQL> /
PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------
P1 JJJG
P2 JJJG
P3 JJJG
PMAX JJJG
提示： move partition/subpartiton 時會鎖表，並且 move partition/subpartiton 視被移動分割槽中資料量的多 少，會帶來相應的 IO 操作。同時還需要注意，如果在 move partition/subpartiton 時沒有指定 update indexes 子句，則被移動分割槽所在的 local 索引以及全域性索引都會失效，需要手工 rebuilding 。
3.8、重新命名錶分割槽（Rename Partition）
就是改名，跟表改名，列改名類似，語法很簡單：
alter table tbname rename patition ptname to newptname;
例：
SQL> select partition_name,tablespace_name from user_tab_partitions
  2 where table_name='T_PARTITION_RANGE';
PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------
P1 JJJG
P2 JJJG
P3 JJJG
PMAX JJJG
SQL> alter table t_partition_range rename partition p3 to p4;
表已更改。
SQL> select partition_name,tablespace_name from user_tab_partitions
  2 where table_Name='T_PARTITION_RANGE';
PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------
P1 JJJG
P2 JJJG
P4 JJJG
PMAX JJJG

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29440247/viewspace-1108135/，如需轉載，請註明出處，否則將追究法律責任。

相關文章