Oracle SQL調優系列之分區表用法
      
    
      
        網友投稿
        874
        2025-04-02
		    
      
    
                    
      文章目錄

      一、分區表簡介
      二、分區表優勢
      三、分區表分類
      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');
      1
      2
      3
      4
      5
      6
      7
      二、分區表優勢
      引用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;
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      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;
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      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;
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      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
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      四、分區相關操作
      （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);
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      （2）新增分區
      ALTER TABLE list_part_tab ADD PARTITION P13 VALUES LESS THAN(250);
      1
      新增子分區，子分區名稱是P13SUB1
      ALTER TABLE list_part_tab MODIFY PARTITION P13 ADD SUBPARTITION P13SUB1 VALUES(350);
      1
      （3）刪除分區
      ALTER TABLE list_part_tab DROP PARTITION P13;
      1
      刪除子分區，子分區名稱P13SUB1
      ALTER TABLE list_part_tab DROP SUBPARTITION P13SUB1;
      1
      （4）TRUNCATE分區
      TRUNCATE是指刪除分區的數據，并不會刪除分區
      ALTER TABLE list_part_tab TRUNCATE PARTITION P2;
      1
      TRUNCATE子分區
      ALTER TABLE list_part_tab TRUNCATE SUBPARTITION P13SUB1;
      1
      （5）合并分區
      合并分區是將相鄰的分區合并成一個分區，結果分區將采用較高分區的界限，值得注意的是，不能將分區合并到界限較低的分區
      ALTER TABLE list_part_tab MERGE PARTITIONS P1,P2 INTO PARTITION P2;
      1
      （6）接合分區(coalesca)
      將散列分區中的數據接合到其它分區中，當散列分區中的數據比較大時，可以增加散列分區，然后進行接合，注意接合只適用于散列分區
      ALTER TABLE list_part_tab COALESCA PARTITION;
      1
      （7）重命名分區
      ALTER TABLE SAlist_part_tabLES RENAME PARTITION P11 TO P1;
      1
      （8）交換分區
      交換分區是說交換兩張表結構一樣的表的數據，注意最好加上including indexs更新全局索引，不加的話，全局索引會失效
      alter table list_part_tab exchange partition p1 with table range_part_tab including indexs update global indexs;
      1
      五、分區相關查詢
      分區相關查詢
      （1）查詢數據庫所有分區表的信息
      select * from DBA_PART_TABLES
      1
      （2）查詢分區表類型、是否有子分區，分區總數
      select pt.partitioning_type, pt.subpartitioning_type, pt.partition_count from user_part_tables pt
      1
      2
      3
      （3）查詢分區詳細詳細：
      SELECT tab.* FROM USER_TAB_PARTITIONS tab WHERE TABLE_NAME='LIST_PART_TAB'
      1
      2
      （4）查詢分區表哪列建分區
      select column_name, object_type, column_position from user_part_key_columns where name = 'LIST_PART_TAB';
      1
      2
      3
      （5）查詢分區表大小
      select sum(bytes / 1024 / 1024) from user_segments where segment_name = 'LIST_PART_TAB';
      1
      2
      3
      （6）查詢分區表各分區的大小和分區名
      select partition_name, segment_type, bytes from user_segments where segment_name = 'LIST_PART_TAB';
      1
      2
      3
      4
      （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;
      1
      2
      3
      4
      5
      6
      7
      （8）查詢分區表的統計信息
      select table_name, partition_name, last_analyzed, partition_position, num_rows from user_tab_statistics where table_name = 'LIST_PART_TAB';
      1
      2
      3
      4
      5
      6
      7
      8
      Oracle SQL調優系列之分區表用法
      （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';
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      （10）查詢索引在哪些列上
      select index_name, column_name, column_position from user_ind_columns where table_name = 'LIST_PART_TAB';
      1
      2
      3
      4
      （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';
      1
      2
      3
      4
      5
      6
      7
      8
      （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';
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      附錄：分區表索引失效的操作
      Oracle SQL
      
版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

      
版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

      
    
      
         
        

    
       
    
                    
      
        
      
                        上一篇:怎么在excel2010中使用篩選功能（篩選怎么操作excel2010）
                    
      
        
      
                        下一篇:使用OneNote2010中的“停靠到桌面”和“鏈接筆記”功能摘錄學習資源
                    
      
    
      

      

      
    
      
        相關文章
    
      
    

      
    
                            
      
          
      
               
      
      
    
      
  
      

  

      
       
  
      
    
  
      
  
      










亚洲精品理论电影在线观看|
亚洲精品无码久久久影院相关影片
|
亚洲欧洲自拍拍偷综合|
亚洲国产成人精品无码区在线观看|
中文字幕中韩乱码亚洲大片|
亚洲国产免费综合|
亚洲AV日韩精品一区二区三区|
337P日本欧洲亚洲大胆精品|
亚洲熟女综合一区二区三区|
亚洲综合激情五月丁香六月|
亚洲中文字幕久久无码|
亚洲欧美国产国产综合一区|
亚洲变态另类一区二区三区|
亚洲av无码一区二区三区人妖|
亚洲Av无码国产一区二区|
亚洲AV无码一区二区三区牲色|
国产亚洲男人的天堂在线观看|
日韩色视频一区二区三区亚洲|
老子影院午夜伦不卡亚洲|
亚洲av午夜精品一区二区三区|
亚洲精品WWW久久久久久|
久久亚洲国产成人精品无码区|
国产亚洲人成网站在线观看|
亚洲精品无码久久千人斩|
亚洲四虎永久在线播放|
亚洲女人影院想要爱|
在线a亚洲老鸭窝天堂av高清|
亚洲人成欧美中文字幕|
女bbbbxxxx另类亚洲|
ZZIJZZIJ亚洲日本少妇JIZJIZ|
亚洲中文字幕无码久久综合网|
国产v亚洲v天堂无码网站|
亚洲国产女人aaa毛片在线|
亚洲色偷偷av男人的天堂|
亚洲激情视频图片|
久久亚洲精品无码网站|
相泽亚洲一区中文字幕|
亚洲高清在线视频|
tom影院亚洲国产一区二区|
亚洲Av无码国产一区二区
|
亚洲国产成人乱码精品女人久久久不卡|