深入解析partition-list 分割槽
List partitioning enables you to explicitly control how rows map to partitions. You do this by specifying a list of discrete values for the partitioning key in the description for each partition. This is different from range partitioning, where a range of values is
associated with a partition and from hash partitioning, where a hash function controls the row-to-partition mapping. The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way.
Unlike range and hash partitioning, multicolumn partition keys are not supported for list partitioning. If a table is partitioned by list, the partitioning key can only consist of a single column of the table.
The DEFAULT partition enables you to avoid specifying all possible values for a list-partitioned table by using a default partition, so that all rows that do not map to any other partition do not generate an error.
由此可見,list分割槽需明確指定列值,不同於range分割槽只需指定列值範圍即可。且不同於hash分割槽,因為明確指定的分割槽值,能控制每條記錄儲存在哪個分割槽。另外,它的分割槽列只能有一個,而不能像range或hash分割槽那樣同時指定多個列做為分割槽依賴列。
若插入的列值不在指定的list分割槽範圍內就會報錯,為避免這種情況,使用list分割槽時可建立一個default分割槽。default 分割槽用來儲存那些不在指定範圍內的記錄,類似於range分割槽的maxvalue分割槽。
1、建立list partition
語法如下:
需要我們指定的有:
column: 分割槽依賴列(只能是一個) ;
partition: 分割槽名稱 ;
literal: 分割槽對應值(每個分割槽可以對應多個值) ;
tablespace_clause: 分割槽的儲存屬性,例如所在表空間等屬性 ( 可為空 ) ,預設繼承基表所在表空間的屬性。
例:
SQL> edit
已寫入 file afiedt.buf
1 create table t_partition_list(id number,name varchar2(20))
2 partition by list(id)(
3 partition t_list_p1 values(1,3,5,7,9) tablespace tbs01,
4 partition t_list_p2 values(2,4,6,8,10) tablespace tbs02,
5 partition t_list_p3 values(21,23,25,27,29) tablespace tbs03,
6* partition t_list_default values(default) tablespace tbs03)
SQL> /
表已建立。
SQL> select partition_name,tablespace_name from user_tab_partitions
2 where table_name='T_PARTITION_LIST';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T_LIST_DEFAULT TBS03
T_LIST_P1 TBS01
T_LIST_P2 TBS02
T_LIST_P3 TBS03
2、list分割槽表上建立索引
list分割槽與range和hash分割槽不同,它不能建立global分割槽索引,只能建立local分割槽索引,如下例所示:
SQL> edit
已寫入 file afiedt.buf
1 create index idx_t_part_list_id on t_partition_list(id)
2 global partition by list(id)(
3 partition i_list_p1 values(1,3,5,7,9) tablespace tbs01,
4 partition i_list_p2 values(2,4,6,8,10) tablespace tbs02,
5 partition i_list_p3 values(21,23,25,27,29) tablespace tbs03,
6* partition i_list_default values(default) tablespace tbs03)
SQL> /
global partition by list(id)(
*
第 2 行出現錯誤:
ORA-14151: 無效的表分割槽方法
SQL> create index idx_t_part_list_id on t_partition_list(id) local;
索引已建立。
SQL> select partition_name,tablespace_name from user_ind_partitions
2 where index_name='T_PARTITION_LIST';
未選定行
SQL> EDIT
已寫入 file afiedt.buf
1 select partition_name,tablespace_name from user_ind_partitions
2* where index_name='IDX_T_PART_LIST_ID'
SQL> /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T_LIST_DEFAULT TBS03
T_LIST_P1 TBS01
T_LIST_P2 TBS02
T_LIST_P3 TBS03
list分割槽的local分割槽索引與前二篇提到的range和hash的local分割槽索引一樣,其分割槽形式完全依賴於所屬表的分割槽形wv式。
3、list分割槽表的管理
3.1、新增表分割槽(add partition)
語法:alter table tbname add partition values……
當list分割槽存在default分割槽時,add partition會報錯,此時只能使用split分割槽代替。例:
SQL> edit
已寫入 file afiedt.buf
1 select partition_name,high_value,tablespace_name from user_tab_partitions
2* where table_name='T_PARTITION_LIST'
SQL> /
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ---------------------------------------------------------------------
T_LIST_DEFAULT default TBS03
T_LIST_P1 1, 3, 5, 7, 9 TBS01
T_LIST_P2 2, 4, 6, 8, 10 TBS02
T_LIST_P3 21, 23, 25, 27, 29 TBS03
SQL> alter table t_partition_list add partition values(20,22,24);
alter table t_partition_list add partition values(20,22,24)
*
第 1 行出現錯誤:
ORA-14323: 在 DEFAULT 分割槽已存在時無法新增分割槽
3.2、分隔表分割槽(split partition)
語法:alter table tbname split partition ptname values(v1,v2……vn) into(partition newpt1 tbs_clause,partition newpt2 tbs_clause);
split partition是將一個分割槽拆分成二個,list分割槽中最常用來拆分default分割槽,如下例所示:
SQL> alter table t_partition_list split partition t_list_default values(20,22,24)
2 into(partition t_list_p4,partition t_list_default);
表已更改。
SQL> select partition_name,high_value,tablespace_name from user_tab_partitions
2 where table_name='T_PARTITION_LIST';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ---------------------------------------------------------------------
T_LIST_DEFAULT default TBS03
T_LIST_P1 1, 3, 5, 7, 9 TBS01
T_LIST_P2 2, 4, 6, 8, 10 TBS02
T_LIST_P3 21, 23, 25, 27, 29 TBS03
T_LIST_P4 20, 22, 24 TBS03
3.3、刪除表分割槽(drop partition)
刪除分割槽:alter table[tablename] drop partition[ptname];
刪除子分割槽:alter table[tablename] drop subpartition[ptname];
例:
--刪除default分割槽
SQL> alter table t_partition_list drop partition t_list_default;
表已更改。
SQL> select partition_name,high_value,tablespace_name from user_tab_partitions
2 where table_name='T_PARTITION_LIST';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ---------------------------------------------------------------------
T_LIST_P1 1, 3, 5, 7, 9 TBS01
T_LIST_P2 2, 4, 6, 8, 10 TBS02
T_LIST_P3 21, 23, 25, 27, 29 TBS03
T_LIST_P4 20, 22, 24 TBS03
--新增default分割槽
SQL> alter table t_partition_list add partition t_list_default values(default) tablespace tbs03;
表已更改。
3.4、合併表分割槽(merge partitions)
語法: alter table tbname merge partitions/subpartitions pt1,pt2 into partition/subpartition pt3;
例:
SQL> select partition_name,high_value,tablespace_name from user_tab_partitions
2 where table_name='T_PARTITION_LIST';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ---------------------------------------------------------------------
T_LIST_DEFAULT default TBS03
T_LIST_P1 1, 3, 5, 7, 9 TBS01
T_LIST_P2 2, 4, 6, 8, 10 TBS02
T_LIST_P3 21, 23, 25, 27, 29 TBS03
T_LIST_P4 20, 22, 24 TBS03
SQL> alter table t_partition_list merge partitions t_list_p4,t_list_default into partition t_list_de fault;
表已更改。
SQL> select partition_name,high_value,tablespace_name from user_tab_partitions
2 where table_name='T_PARTITION_LIST';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ---------------------------------------------------------------------
T_LIST_DEFAULT default JJJG
T_LIST_P1 1, 3, 5, 7, 9 TBS01
T_LIST_P2 2, 4, 6, 8, 10 TBS02
T_LIST_P3 21, 23, 25, 27, 29 TBS03
3.5、交換表分割槽(exchange partition)
語法:alter table tbname1 exchange partition/subpartition ptname with table tbname2;
注意事項同前面二章(range與hash分割槽)此功能一樣,在此不多述,具體用法參見下例。
--insert into list partition
SQL> insert into t_partition_list values(1,'a');
已建立 1 行。
SQL> insert into t_partition_list values(2,'b');
已建立 1 行。
SQL> insert into t_partition_list values(33,'c');
已建立 1 行。
SQL> commit;
提交完成。
SQL> select * from t_partition_list;
ID NAME
---------- --------------------
1 a
2 b
33 c
SQL> select * from t_partition_list partition(t_list_p2);
ID NAME
---------- --------------------
2 b
--在此借用range patition中exchange partition例項中的t_range_partition_tmp表,進行list partiiton的exchange partition操作
SQL> select * from t_partition_range_tmp;
ID NAME
---------- --------------------
11 a
SQL> insert into t_partition_range_tmp values(4,'c');
已建立 1 行。
SQL> insert into t_partition_range_tmp values(6,'d');
已建立 1 行。
SQL> commit;
提交完成。
SQL> select * from t_partition_range_tmp;
ID NAME
---------- --------------------
11 a
4 c
6 d
SQL> alter table t_partition_list exchange partition t_list_p2
2 with table t_partition_range_tmp;
with table t_partition_range_tmp
*
第 2 行出現錯誤:
ORA-14099: 未對指定分割槽限定表中的所有行
SQL> delete from t_partition_range_tmp where id=11;
已刪除 1 行。
SQL> commit;
提交完成。
SQL> alter table t_partition_list exchange partition t_list_p2
2 with table t_partition_range_tmp;
表已更改。
SQL> select * from t_partition_list;
ID NAME
---------- --------------------
1 a
4 c
6 d
33 c
SQL> select * from t_partition_range_tmp;
ID NAME
---------- --------------------
2 b
3.6、修改list表分割槽(modify partition)
3.6.1、add values新增指定分割槽的value值
此命令僅應用於 list 分割槽或 list 子分割槽,語法也非常簡單:
Alter table tbname modify partition/subpartition ptname add values (v1,v2....vn);
例如:
SQL> select partition_name,high_value from user_tab_partitions
2 where table_name='T_PARTITION_LIST';
PARTITION_NAME HIGH_VALUE
------------------------------ ----------------------------------------------------
T_LIST_DEFAULT default
T_LIST_P1 1, 3, 5, 7, 9
T_LIST_P2 2, 4, 6, 8, 10
T_LIST_P3 21, 23, 25, 27, 29
SQL> alter table t_partition_list modify partition t_list_p1 add values(11,12);
表已更改。
SQL> select partition_name,high_value from user_tab_partitions
2 where table_name='T_PARTITION_LIST';
PARTITION_NAME HIGH_VALUE
------------------------------ ----------------------------------------------------
T_LIST_DEFAULT default
T_LIST_P1 1, 3, 5, 7, 9, 11, 12
T_LIST_P2 2, 4, 6, 8, 10
T_LIST_P3 21, 23, 25, 27, 29
注意: 要新增的新value值不能存在於當前任何分割槽中,並且當前表也不能存在記錄值為新值的記錄,特別是當你建立了default分割槽的時候,有必要先檢查一下當前表不存在要新增的值,不 然執行會出錯,如下例所示:
SQL> alter table t_partition_list modify partition t_list_p1 add values(2);
alter table t_partition_list modify partition t_list_p1 add values(2)
*
第 1 行出現錯誤:
ORA-14312: 值 2 已經存在於分割槽 2 中
SQL> alter table t_partition_list modify partition t_list_p1 add values(33);
alter table t_partition_list modify partition t_list_p1 add values(33)
*
第 1 行出現錯誤:
ORA-14324: 所要新增的值已存在於 DEFAULT分割槽之中
--查詢表t_partition_list記錄,存在id為33的記錄
SQL> select * from t_partition_list;
ID NAME
---------- --------------------
1 a
4 c
6 d
33 c
3.6.2、drop values刪除指定分割槽的value值
與上面的add values類似,只適用於list分割槽或list子分割槽,但功能相反,語法如下:
alter table tbname modify partition/subpartition ptname drop values(v1,v2……vn);
例:將上面例子中t_parition_list表t_list_p1分割槽中新增的11,12值刪除
SQL> alter table t_partition_list modify partition t_list_p1 drop values(11,12);
表已更改。
SQL> select partition_name,high_value from user_tab_partitions
2 where table_name='T_PARTITION_LIST';
PARTITION_NAME HIGH_VALUE
------------------------------ -------------------------------------------------
T_LIST_DEFAULT default
T_LIST_P1 1, 3, 5, 7, 9
T_LIST_P2 2, 4, 6, 8, 10
T_LIST_P3 21, 23, 25, 27, 29
注意:在刪除list分割槽中的values值時,要確認當前分割槽存在指定的value值,且沒有應用該值的記錄,否則會報錯,如下例所示:
SQL> alter table t_partition_list modify partition t_list_p1 drop values(2);
alter table t_partition_list modify partition t_list_p1 drop values(2)
*
第 1 行出現錯誤:
ORA-14313: 值 2 不在分割槽 T_LIST_P1 中
SQL> alter table t_partition_list modify partition t_list_p1 drop values(1);
alter table t_partition_list modify partition t_list_p1 drop values(1)
*
第 1 行出現錯誤:
ORA-14518: 分割槽包含的某些行對應於已刪除的值
3.7、截斷表分割槽(truncate partition)
語法:alter table tbname truncate partition ptname
SQL> select * from t_partition_list;
ID NAME
---------- --------------------
1 a
4 c
6 d
33 c
SQL> select partition_name,high_value from user_tab_partitions
2 where table_name='T_PARTITION_LIST';
PARTITION_NAME HIGH_VALUE
------------------------------ ----------------------------------------
T_LIST_DEFAULT default
T_LIST_P1 1, 3, 5, 7, 9
T_LIST_P2 2, 4, 6, 8, 10
T_LIST_P3 21, 23, 25, 27, 29
SQL> alter table t_partition_list truncate partition t_list_p1;
表被截斷。
SQL> select * from t_partition_list;
ID NAME
---------- --------------------
4 c
6 d
33 c
3.8、移動表分割槽(move partition)
語法:alter table tbname move partition ptname ……
前二章講range和hash分割槽時,都提到move partition,其用法功能都一樣,在此不多述,直接看例子:
SQL> select partition_name,tablespace_name from user_tab_partitions
2 where table_name='T_PARTITION_LIST';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T_LIST_DEFAULT JJJG
T_LIST_P1 TBS01
T_LIST_P2 JJJG
T_LIST_P3 TBS03
SQL> alter table t_partition_list move partition t_list_p2 tablespace tbs02;
表已更改。
SQL> select partition_name,tablespace_name from user_tab_partitions
2 where table_name='T_PARTITION_LIST';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T_LIST_DEFAULT JJJG
T_LIST_P1 TBS01
T_LIST_P2 TBS02
T_LIST_P3 TBS03
3.9、重新命名錶分割槽(rename partition)
語法:alter table tbname rename partition ptname to newptname;
例:
SQL> select partition_name,tablespace_name from user_tab_partitions
2 where table_name='T_PARTITION_LIST';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T_LIST_DEFAULT JJJG
T_LIST_P1 TBS01
T_LIST_P2 TBS02
T_LIST_P3 TBS03
SQL> alter table t_partition_list rename partition t_list_p1 to t_list_p4;
表已更改。
SQL> select partition_name,tablespace_name from user_tab_partitions
2 where table_name='T_PARTITION_LIST';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T_LIST_DEFAULT JJJG
T_LIST_P2 TBS02
T_LIST_P3 TBS03
T_LIST_P4 TBS01
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/21251711/viewspace-1119937/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 深入解析partition-range分割槽
- 深入解析partition-hash分割槽
- 轉:深入解析MySQL分割槽(Partition)功能MySql
- 深入學習Oracle分割槽表及分割槽索引Oracle索引
- 深入學習分割槽表及分割槽索引(1)索引
- 深入學習分割槽表及分割槽索引(5)--建立range-hash組合分割槽(續)索引
- 深入理解硬碟的 Linux 分割槽(轉)硬碟Linux
- 如何查詢分割槽表的分割槽及子分割槽
- 全面學習分割槽表及分割槽索引(10)--交換分割槽索引
- PLSQL根據分割槽表的分割槽名批次truncate分割槽SQL
- Linux主分割槽,擴充套件分割槽,邏輯分割槽Linux套件
- Oracle分割槽表及分割槽索引Oracle索引
- INTERVAL分割槽表鎖分割槽操作
- Oracle帶區域性分割槽索引的分割槽表刪除舊分割槽新增新分割槽Oracle索引
- 詳細解析kafka之kafka分割槽和副本Kafka
- 全面學習分割槽表及分割槽索引(13)--分隔表分割槽索引
- 使用split對分割槽表再分割槽
- 簡單ORACLE分割槽表、分割槽索引Oracle索引
- rebuild分割槽表分割槽索引的方法Rebuild索引
- 分割槽表及分割槽索引建立示例索引
- oracle分割槽表和分割槽表exchangeOracle
- Linux分割槽方案、分割槽建議Linux
- 全面學習分割槽表及分割槽索引(9)--刪除表分割槽索引
- 全面學習分割槽表及分割槽索引(11)--合併表分割槽索引
- 全面學習分割槽表及分割槽索引(12)--修改list表分割槽索引
- Linux主分割槽,擴充套件分割槽,邏輯分割槽[final]Linux套件
- 學習筆記】分割槽表和分割槽索引——新增表分割槽(二)筆記索引
- 【學習筆記】分割槽表和分割槽索引——管理索引分割槽(四)筆記索引
- 深入原始碼理解Spark RDD的資料分割槽原理原始碼Spark
- Linux 分割槽擴容(根分割槽擴容,SWAP 分割槽擴容,掛載新分割槽為目錄)Linux
- Oracle分割槽表基礎運維-07增加分割槽(2 HASH分割槽)Oracle運維
- 全面學習分割槽表及分割槽索引(17)--其它索引分割槽管理操作索引
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- PostgreSQL/LightDB 分割槽表之分割槽裁剪SQL
- 非分割槽錶轉換成分割槽表
- ORACLE 範圍分割槽 partition-range分割槽Oracle
- 分割槽表分割槽索引查詢效率探究索引
- oracle分割槽表和非分割槽表exchangeOracle