Oracle SQL調優之分割槽表

目錄

• 一、分割槽表簡介
• 二、分割槽表優勢
• 三、分割槽表分類
  • 3.1 範圍分割槽
  • 3.2 列表分割槽
  • 3.3 雜湊分割槽
  • 3.4 組合分割槽
• 四、分割槽相關操作
• 五、分割槽相關查詢
• 附錄：分割槽表索引失效的操作

一、分割槽表簡介

分割槽通過讓您將它們分解為更小且更易於管理的分割槽（稱為分割槽）來解決支援非常大的表和索引的關鍵問題。不需要修改SQL查詢和DML語句以訪問分割槽表。但是，在定義分割槽之後，DDL語句可以訪問和操作個別分割槽，而不是整個表或索引。這就是分割槽可以簡化大型資料庫物件的可管理性的方式。此外，分割槽對應用程式完全透明

其它型別的表設計可以看部落格：https://smilenicky.blog.csdn.net/article/details/90315980
普通表和分割槽表區別，分割槽表分成幾部分就有幾個segment，RANGE_PART_TAB是一個分割槽表

select segment_name,
       partition_name,
       segment_type,
       bytes / 1024 / 1024 "位元組數(M)",
       tablespace_name
  from user_segments
 where segment_name IN ('RANGE_PART_TAB', 'NOR_TAB');

二、分割槽表優勢

引用Oracle官方文件的說法，https://docs.oracle.com/cd/B19306_01/server.102/b14220/partconc.htm#sthref2604：

(1) 分割槽支援資料管理操作，例如資料載入，索引建立和重建，以及分割槽級別的備份/恢復，而不是整個表。這導致這些操作的時間顯著減少。

(2)分割槽可提高查詢效能。在許多情況下，查詢的結果可以通過訪問分割槽的子集而不是整個表來實現。對於某些查詢，此技術（稱為分割槽 修剪）可以提供效能的數量級增益。

(3)分割槽可以顯著減少計劃停機對維護操作的影響。

(4)分割槽維護操作的分割槽獨立性允許您在同一個表或索引的不同分割槽上執行併發維護操作。您還可以SELECT對不受維護操作影響的分割槽執行併發和DML操作。

(5)如果將關鍵表和索引劃分為多個分割槽以減少維護視窗，恢復時間和故障影響，則分割槽可提高任務關鍵型資料庫的可用性。

(6)無需對應用程式進行任何修改即可實現分割槽。例如，您可以將非分割槽錶轉換為分割槽表，而無需修改SELECT訪問該表的任何語句或DML語句。您無需重寫應用程式程式碼即可利用分割槽。

三、分割槽表分類

分割槽型別：分割槽分為範圍分割槽、列表分割槽、HASH分割槽、組合分割槽四種，圖來自Oracle官方網站

3.1 範圍分割槽

關鍵字partition by range

create table range_part_tab (seq number,deal_date date,unit_code number,remark varchar2(100))
partition by range (deal_date)
(
partition p1 values less than (TO_DATE('2018-11-01','YYYY-MM-DD')),
partition p2 values less than (TO_DATE('2018-12-02','YYYY-MM-DD')),
partition p3 values less than (TO_DATE('2019-01-01','YYYY-MM-DD')),
partition p4 values less than (TO_DATE('2019-02-01','YYYY-MM-DD')),
partition p5 values less than (TO_DATE('2019-03-01','YYYY-MM-DD')),
partition p6 values less than (TO_DATE('2019-04-01','YYYY-MM-DD')),
partition p7 values less than (TO_DATE('2019-05-01','YYYY-MM-DD')),
partition p8 values less than (TO_DATE('2019-06-01','YYYY-MM-DD')),
partition p9 values less than (TO_DATE('2019-07-01','YYYY-MM-DD')),
partition p10 values less than (TO_DATE('2019-08-01','YYYY-MM-DD'))
);

insert into range_part_tab
  (seq, deal_date, unit_code, remark)
  select rownum,
         to_date(to_char(sysdate-365, 'J') +
                 trunc(DBMS_RANDOM.value(0, 365)),'J'),
         ceil(dbms_random.value(210,220)),
         rpad('*', 1, '*')
    from dual
  connect by rownum <= 1000;

3.2 列表分割槽

  create table list_part_tab (seq number,deal_date date,unit_code number,remark varchar2(100))
partition by list (unit_code)
(
partition p1 values (211),
partition p2 values (212),
partition p3 values (213),
partition p4 values (214),
partition p5 values (215),
partition p6 values (216),
partition p7 values (217),
partition p8 values (218),
partition p9 values (219),
partition p10 values (220),
partition p0 values (DEFAULT) 
);

insert into list_part_tab
  (seq, deal_date, unit_code, remark)
  select rownum,
         to_date(to_char(sysdate-365, 'J') +
                 trunc(DBMS_RANDOM.value(0, 365)),'J'),
         ceil(dbms_random.value(210,220)),
         rpad('*', 1, '*')
    from dual
  connect by rownum <= 1000;
commit;

3.3 雜湊分割槽

雜湊分割槽也叫hash分割槽，partitions後接分割槽數，儘量設定為偶數，

create table hash_part_tab (seq number,deal_date date,unit_code number,remark varchar2(100))
partition by hash (deal_date)
partitions 12;

insert into hash_part_tab
  (seq, deal_date, unit_code, remark)
  select rownum,
         to_date(to_char(sysdate-365, 'J') +
                 trunc(DBMS_RANDOM.value(0, 365)),'J'),
         ceil(dbms_random.value(210,220)),
         rpad('*', 1, '*')
    from dual
  connect by rownum <= 1000;
commit;

3.4 組合分割槽

組合分割槽又稱複合分割槽，主要有兩種：oracle11之前只支援範圍列表分割槽(RANGE-LIST)和範圍雜湊分割槽(RANGE-HASH)，oracle11之後支援(範圍範圍分割槽)RANGE-RANGE、 (列表範圍分割槽)LIST-RANGE、(列表雜湊分割槽)LIST-HASH、(列表列表分割槽)LIST-LIST這幾種組合，為了避免每個主分割槽中都寫相同的從分割槽，可以用模板方式(subpartition template)

圖來自Oracle官方網站：

create table range_list_part_tab (seq number,deal_date date,unit_code number,remark varchar2(100))
partition by range (deal_date)
 subpartition by list (unit_code)
 subpartition template
 (subpartition s1 values (211),
subpartition s2 values (212),
subpartition s3 values (213),
subpartition s4 values (214),
subpartition s5 values (215),
subpartition s6 values (216),
subpartition s7 values (217),
subpartition s8 values (218),
subpartition s9 values (219),
subpartition s10 values (220),
subpartition s0 values (DEFAULT) )
(
partition p1 values less than (TO_DATE('2018-11-01','YYYY-MM-DD')),
partition p2 values less than (TO_DATE('2018-12-02','YYYY-MM-DD')),
partition p3 values less than (TO_DATE('2019-01-01','YYYY-MM-DD')),
partition p4 values less than (TO_DATE('2019-02-01','YYYY-MM-DD')),
partition p5 values less than (TO_DATE('2019-03-01','YYYY-MM-DD')),
partition p6 values less than (TO_DATE('2019-04-01','YYYY-MM-DD')),
partition p7 values less than (TO_DATE('2019-05-01','YYYY-MM-DD')),
partition p8 values less than (TO_DATE('2019-06-01','YYYY-MM-DD')),
partition p9 values less than (TO_DATE('2019-07-01','YYYY-MM-DD')),
partition p10 values less than (TO_DATE('2019-08-01','YYYY-MM-DD'))
);

insert into range_list_part_tab
  (seq, deal_date, unit_code, remark)
  select rownum,
         to_date(to_char(sysdate-365, 'J') +
                 trunc(DBMS_RANDOM.value(0, 365)),'J'),
         ceil(dbms_random.value(210,220)),
         rpad('*', 1, '*')
    from dual
  connect by rownum <= 1000;
commit;

四、分割槽相關操作

• （1） Split分割槽
  拆分分割槽，範圍分割槽和列表分割槽都適合分割槽，注意不能對HASH型別的分割槽進行拆分

  create table list_part_tab (seq number,deal_date date,unit_code number,remark varchar2(100))
partition by list (unit_code)
(
partition p1 values (211),
partition p2 values (212),
partition p3 values (213),
partition p4 values (214),
partition p5 values (215),
partition p6 values (216),
partition p7 values (217),
partition p8 values (218),
partition p9 values (219),
partition p10 values (220),
partition p0 values (DEFAULT) 
);

alter table list_part_tab split partition p10 at(220) into (PARTITION p11,PARTITION p12);

• （2）新增分割槽

ALTER TABLE list_part_tab ADD PARTITION P13 VALUES LESS THAN(250); 

新增子分割槽，子分割槽名稱是P13SUB1

ALTER TABLE list_part_tab MODIFY PARTITION P13 ADD SUBPARTITION P13SUB1 VALUES(350);

• （3）刪除分割槽

ALTER TABLE list_part_tab DROP PARTITION P13;

刪除子分割槽，子分割槽名稱P13SUB1

ALTER TABLE list_part_tab DROP SUBPARTITION P13SUB1;

• （4）TRUNCATE分割槽
  TRUNCATE是指刪除分割槽的資料，並不會刪除分割槽

ALTER TABLE list_part_tab TRUNCATE PARTITION P2;

TRUNCATE子分割槽

ALTER TABLE list_part_tab TRUNCATE SUBPARTITION P13SUB1;

• （5）合併分割槽
  合併分割槽是將相鄰的分割槽合併成一個分割槽，結果分割槽將採用較高分割槽的界限，值得注意的是，不能將分割槽合併到界限較低的分割槽

ALTER TABLE list_part_tab MERGE PARTITIONS P1,P2 INTO PARTITION P2;

• （6）接合分割槽(coalesca)
  將雜湊分割槽中的資料接合到其它分割槽中，當雜湊分割槽中的資料比較大時，可以增加雜湊分割槽，然後進行接合，注意接合只適用於雜湊分割槽

ALTER TABLE list_part_tab COALESCA PARTITION;

• （7）重新命名分割槽

ALTER TABLE SAlist_part_tabLES RENAME PARTITION P11 TO P1;

• （8）交換分割槽
  交換分割槽是說交換兩張表結構一樣的表的資料，注意最好加上including indexs更新全域性索引，不加的話，全域性索引會失效

alter table list_part_tab exchange partition p1 with table range_part_tab including indexs update global indexs;

五、分割槽相關查詢

分割槽相關查詢

• （1）查詢資料庫所有分割槽表的資訊

select * from DBA_PART_TABLES 

• （2）查詢分割槽表型別、是否有子分割槽，分割槽總數

select pt.partitioning_type, pt.subpartitioning_type, pt.partition_count
  from user_part_tables pt

• （3）查詢分割槽詳細詳細：

SELECT tab.* FROM USER_TAB_PARTITIONS tab WHERE TABLE_NAME='LIST_PART_TAB' 

• （4）查詢分割槽表哪列建分割槽

select column_name, object_type, column_position
  from user_part_key_columns
 where name = 'LIST_PART_TAB';

• （5）查詢分割槽表大小

select sum(bytes / 1024 / 1024)
  from user_segments
 where segment_name = 'LIST_PART_TAB';

• （6）查詢分割槽表各分割槽的大小和分割槽名

 select partition_name, segment_type, bytes
   from user_segments
  where segment_name = 'LIST_PART_TAB';

• （7）查詢分割槽表各索引大小

select segment_name, segment_type, sum(bytes) / 1024 / 1024
  from user_segments
 where segment_name in
       (select index_name
          from user_indexes
         where table_name = 'LIST_PART_TAB')
 group by segment_name, segment_type;

• （8）查詢分割槽表的統計資訊

select table_name,
       partition_name,
       last_analyzed,
       partition_position,
       num_rows
  from user_tab_statistics
 where table_name = 'LIST_PART_TAB';

• （9）查詢分割槽表索引情況

 select table_name,
        index_name,
        last_analyzed,
        blevel,
        num_rows,
        leaf_blocks,
        distinct_keys,
        status
   from user_indexes
  where table_name = 'LIST_PART_TAB';

• （10）查詢索引在哪些列上

select index_name, column_name, column_position
  from user_ind_columns
 where table_name = 'LIST_PART_TAB';

• （11）查詢普通表失效的索引

select ind.index_name,
       ind.table_name,
       ind.blevel,
       ind.num_rows,
       ind.leaf_blocks,
       ind.distinct_keys
  from user_indexes ind
 where status = 'INVALID';

• （12）查詢分割槽表失效的索引

select a.blevel,
       a.leaf_blocks,
       a.index_name,
       b.table_name,
       a.partition_name,
       a.status
  from user_ind_partitions a, user_indexes b
 where a.index_name = b.index_name
   and a.status = 'UNUSABLE';

附錄：分割槽表索引失效的操作

ps:表格來自《收穫，不止SQL調優》一書作者的整理
操作動作 | 操作命令 | 是否失效(全域性索引) |如何避免(全域性索引) |是否失效(分割槽索引)|如何避免(分割槽索引)
---|--- |---|---|---|---
truncate分割槽 | alter table part_tab_trunc truncate partition p1 ; | 失效 | alter table part_tab_trunc truncate partition p1 Update GLOBAL indexes; | 沒影響 | N/A
drop分割槽 | alter table part_tab_drop drop partition p1; | 失效 | alter table part_tab_drop drop partition p1 Update GLOBAL indexes; | 沒影響 | N/A
split分割槽 | alter table part_tab_split SPLIT PARTITION P_MAX at(30000) into (PARTITION p3,PARTITION P_MAX); | 失效 | alter table part_tab_split SPLIT PARTITION P_MAX at (30000) into (PARTITION p3,PARTITION P_MAX) update global indexes; | 沒影響 | N/A
add分割槽 | alter table part_tab_add add PARTITION p6 values less than (60000); | 沒影響 | N/A | 沒影響 | N/A
exchange分割槽 | alter table part_tab_exch exchange partition p1 with table normal_tab including indexes; | 失效 | alter table part_tab_exch exchange partition p1 with table normal_tab including indexes update global indexes;| 沒影響 | N/A