分割槽partition知識點

Buttonwood發表於2018-04-04

好處

  1. 分割槽級(partition level)進行資料載入、索引建立及重建、或備份恢復等資料管理操作，而非整個表上。
  2. 提高查詢效能
  3. 縮短維護時間
  4. 分割槽獨立操作
  5. 提高可用性
  6. 程式碼邏輯並不改變

分類

根據分割槽鍵自動選擇分割槽，支援插入、更新及刪除等操作。
分割槽鍵：

  1. 1-16個資料列順序構成
  2. 不能包含LEVEL、ROWID或MLSLABEL虛列(pseudocolumn)，也不能包含型別為ROWID的列
  3. 不能包含可為空(NULL)的列

一個表最多由1024K-1個分割槽構成。任何表都能夠被分割槽。可採用壓縮形式儲存表及分割槽表。

1. 範圍分割槽
CREATE TABLE ex1(
    range_key_column date,
    data varchar2(20)
)    PARTITION BY RANGE (range_key_column) //指定分割槽鍵
(
    PARTITION p1 VALUES LESS THEN (to_date(`01/01/2015`,`dd/mm/yyyy`)),
     PARTITION p2 VALUES LESS THEN (to_date(`01/01/2016`,`dd/mm/yyyy`)),
     PARTITION p1 VALUES LESS THEN (MAXVALUE)
);

select table_name,partitioning_type,partition_count,status from user_part_tables where table_name=`ex1`

select table_name,partition_name,tablespace_name from user_tab_partitions where table_name=`ex1`

也可以將子分割槽放在不同的表空間下，對於減少IO爭用有好處。

2. 列表分割槽

根據離散的值列表來指定一行位於哪個分割槽。

CREATE TABLE ex2(
    state_name varchar2(20),
    data varchar2(20)
) PARTITION BY LIST(state_name)(
    PARTITION p1 values (`New York`,`Virginia`),
    PARTITION p2 values (`California`,`Oregon`),
    PARTITION p3 values (`Illionis`,`Texas`)
);

select * from ex2 partition(p1);

alter table ex2 add partition p4 values(default);
// 一旦列表分割槽中有一個default分割槽，就不能再向這個表中增加更多的分割槽了。此時必須先刪除default分割槽，新增新分割槽後再加回default分割槽。
3. 雜湊分割槽

雜湊分割槽是為了能使資料更好地分佈在多個不同裝置或磁碟上，為表選擇的雜湊鍵應當是唯一的一個列或一組列，或者至少有足夠多的相異值。

CREATE TABLE ex3(
    hash_key date,
    data varchar2(20)
) PARTITION BY HASH(hash_key)(
    PARTITION p1 tablespace tbs1,
    PARTITION p2 tablespace tbs2,
    PARTITION p3 tablespace tbs3,
    PARTITION p4 tablespace tbs4
);

// 雜湊分割槽，無法控制資料行最終放在哪個分割槽中
// 改變分割槽個數，資料會在所有分割槽中重新分佈，向一個雜湊中增加或刪除一個分割槽時，將導致所有資料都重寫 
// 分割槽數應該是2的冪數，儘量保證資料均勻分佈
/
4. 組合分割槽

上層是區間分割槽，下層可能是雜湊或列表分割槽。

CREATE TABLE ex4(
    range_key date,
    hash_key int,
    data varchar2(20)
)
    PARTITION BY RANGE(hash_key)
    SUBPARTITION BY HASH(hash_key) subpartitions 2
(
    PARTITION p1 VALUES LESS THEN (to_date(`01/01/2015`,`dd/mm/yyyy`))(
        SUBPARTITION p1s1,
        SUBPARTITION p1s2
    )
     PARTITION p2 VALUES LESS THEN (to_date(`01/01/2016`,`dd/mm/yyyy`))(
         SUBPARTITION p2s1,
        SUBPARTITION p2s2
     )
);

分割槽鍵修改導致行跨分割槽移動(啟用行移動，不要太頻繁)

select rowid from ex1 where range_key = to_date(`31-Dec-2014`,`dd-mon-yyyy`);
alter table ex1 enable row movement;
update ex1 sed range_key=to_date(`31-Dec-2016`,`dd-mon-yyyy`) where range_key= to_date(`31-Dec-2014`,`dd-mon-yyyy`);

Others

Learn Oracle from Oracle Certified Master

pctfree
pctused

manageability
availability

相關文章