PLSQL_效能最佳化系列09_Oracle Partition Table大資料分區表

標籤：des   style   blog   http   color   io   os   使用   ar   

2014-08-22 BaoXinjian

一、摘要

1、分區表：

    隨著表的不斷增大，對於新紀錄的增加、尋找、刪除等(DML)的維護也更加困難。對於資料庫中的超大型表，可通過把它的資料分成若干個小表，從而簡化資料庫的管理活動。對於每一個簡化後的小表，我們稱為一個單個的分區

    對於分區的訪問，我們不需要使用特殊的SQL查詢語句或特定的DML語句，而且可以單獨的操作單個分區，而不是整個表。同時可以將不同分區的資料放置到不 同的資料表空間，比如將不同年份的銷售資料，存放在不同的資料表空間，即年的銷售資料存放到TBS_2001,2002年的銷售資料存放到TBS_2002,依次 類推，從而實現了分散儲存，這將大大的簡化大容量表的管理，提高查詢效能及I/O並發等。

    對於外部應用程式來說，雖然存在不同的分區，且資料位元於不同的資料表空間，但邏輯上仍然是一張表

    可以使用SQL*Loader，IMPDP，EXPDP，Import，Export等工具來裝載或卸載分區表中的資料

    關於分區表的功能實際上同SQL server 中的分區表是同樣的概念，只不過SQL server中的資料存放到了檔案組，相當於Oracle概念中的資料表空間

    分區資訊管控表：DBA_TAB_SUBPARTITIONS

 

2、何時分區

    當表達到GB大小且繼續增長

    需要將曆史資料和當前的資料分開單獨處理，比如曆史資料僅僅需要唯讀，而當前資料則實現DML

 

3、分區的條件及特性

• 共性：不同的分區之間必須有相同的邏輯屬性，比如表名，列名，資料類型，約束等，
• 個性：各個分區可以有不同的物理屬性，比如pctfree, pctused, and tablespaces.
• 分區獨立性：即使某些分區不可用，其他分區仍然可用。
• 特殊性：含有LONG、LONGRAW資料類型的表不能進行分區

 

4、分區的優點

• 提高查詢效能：只需要搜尋特定分區，而非整張表，提高查詢速度
• 節約維護時間：單個分區的資料裝載，索引重建，備份，維護等將遠小於整張表的維護時間。
• 節約維護成本：可以單獨備份和恢複每個分區
• 均衡I/O：將不同的分區映射到不同的磁碟以平衡I/O，提高並發

 

5、分區表類型

• 定界分割表
• 列表分區表
• 雜湊分割表
• 組合分區表

 

6、 分區索引類型

• 全域索引
• 全域分區索引
• 本地分區索引

 

7、 分區表建立四種方式文法

(1)、定界分割表

CREATE TABLE range_example(   range_key_column   DATE,   DATA               VARCHAR2 (20),   ID                 INTEGER)PARTITION BY RANGE (range_key_column)   (PARTITION part01       VALUES LESS THAN          (TO_DATE (‘2008-07-1 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘))       TABLESPACE tbs01,    PARTITION part02       VALUES LESS THAN          (TO_DATE (‘2008-08-1 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘))       TABLESPACE tbs02,    PARTITION part03       VALUES LESS THAN          (TO_DATE (‘2008-09-1 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘))       TABLESPACE tbs03);

(2)、列表分區表

CREATE TABLE list_example (dname VARCHAR2 (10), DATA VARCHAR2 (20))PARTITION BY LIST (dname)   (PARTITION part01       VALUES (‘ME‘, ‘PE‘, ‘QC‘, ‘RD‘),    PARTITION part02       VALUES (‘SMT‘, ‘SALE‘));

(3)、雜湊分割表

CREATE TABLE hash_example(   hash_key_column   DATE,   DATA              VARCHAR2 (20))PARTITION BY HASH (hash_key_cloumn)   (PARTITION part01, PARTITION part02);

(4)、組合分區表

CREATE TABLE range_hash_example(   range_column_key   DATE,   hash_column_key    INT,   DATA               VARCHAR2 (20))PARTITION BY RANGE (range_column_key)   SUBPARTITION BY HASH (hash_column_key)      SUBPARTITIONS 2   (PARTITION part_1       VALUES LESS THAN (TO_DATE (‘2008-08-01‘, ‘yyyy-mm-dd‘)) (       SUBPARTITION part_1_sub_1 ,       SUBPARTITION part_1_sub_2 ,       SUBPARTITION part_1_sub_3    ),    PARTITION part_2       VALUES LESS THAN (TO_DATE (‘2008-09-01‘, ‘yyyy-mm-dd‘))    (SUBPARTITION part_2_sub_1 , SUBPARTITION part_2_sub_2 ));

 

8、 分區索引的結構圖

注:hash partitioned table 新增partition時，現有表的中所有data都有重新計算hash值，然後重新分配到分區中。所以被重新分配的分區的 indexes需要rebuild 。

 

二、案例 - 建立分區表

案例： 建立分區表bxj_emp，以性別區分資料分區方式，將資料分別存放兩個男女資料表空間中

1. 建立兩個表空空間

CREATE TABLESPACE bxj_emp_ts1 LOGGING DATAFILE ‘/opt/oracle/oradata/gavinsit/bxj_emp_data01.dbf‘  SIZE 32M  AUTOEXTEND ON  NEXT 32M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL CREATE TABLESPACE bxj_emp_ts2 LOGGING DATAFILE ‘/opt/oracle/oradata/gavinsit/bxj_emp_data02.dbf‘  SIZE 32M  AUTOEXTEND ON  NEXT 32M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL

 

2. 建立分區表，以sex欄位區分

CREATE TABLE bxj_emp_tb(   emp_id           NUMBER,   employeee_name   VARCHAR (50),   sex              VARCHAR (10),   salary           NUMBER)PARTITION BY LIST (sex)   (PARTITION bxj_emp_ts1       VALUES (‘male‘),    PARTITION bxj_emp_ts2       VALUES (‘female‘));

 

3. 建立測試資料，男女各一筆

insert into apps.bxj_emp_tb values (1, ‘gavin.bao‘, ‘male‘, 100000);       insert into apps.bxj_emp_tb values (2, ‘gavin.bao‘, ‘female‘, 200000); 

 

4.  以條件sex = male進行查詢時，系統只遍曆tablespace 1 male

 

5.  以條件sex = female進行查詢時，系統只遍曆tablespace 1 female 

 

6.  無分區條件查詢，系統需全部遍曆tablespace 1 and 2 / male and female

 

 三、 案例 - 本地分區索引

1. 建立本地分區索引

CREATE INDEX bxj_emp_tb_localindex ON apps.bxj_emp_tb(sex)LOCAL (     PARTITION idx_1 TABLESPACE bxj_emp_ts1,     PARTITION idx_2 TABLESPACE bxj_emp_ts2  );

2. 解析計劃中索引遍曆方式

 

四、案例 - 全域分區索引

1.  建立全域分區索引

CREATE INDEX bxj_emp_tb_globalindexON bxj_emp_tb (salary)GLOBAL PARTITION BY RANGE ( salary ) (   PARTITION idx_1 VALUES LESS THAN (10000)    TABLESPACE bxj_emp_ts1,   PARTITION idx_2 VALUES LESS THAN (MAXVALUE) TABLESPACE bxj_emp_ts2 );

2. 條件為salary <= 100, 索引只遍曆tablespace1 

3. 條件為salary >=100000, 索引只遍曆tablespace2

 4. 無分區條件時，索引遍曆全部

 

五、案例 - Oracle Erp交易表mtl_material_transactions的結構

 

select * from dba_tab_subpartitions

where table_name = ‘MTL_MATERIAL_TRANSACTIONS‘

 

 

 ********************　鮑建立********************

 

參考：http://www.linuxidc.com/Linux/2011-08/40763.htm

參考：http://mingyue19850801.blog.163.com/blog/static/19520820201071712231671/

PLSQL_效能最佳化系列09_Oracle Partition Table大資料分區表