程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> PLSQL_Oracle分區表和相應的分區索引管理和使用（案例）（創建交易表等大表時進行分區提高效率），oracle分區表

PLSQL_Oracle分區表和相應的分區索引管理和使用（案例）（創建交易表等大表時進行分區提高效率），oracle分區表

編輯：Oracle教程

PLSQL_Oracle分區表和相應的分區索引管理和使用（案例）（創建交易表等大表時進行分區提高效率），oracle分區表

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概念中的表空間，

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的結構

********************作者：　鮑新建********************

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

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

oracle的表空間，表分區及索引之間的關系；各位大蝦給分析一下這個建表語句；

首先，要了解下表空間和區段的關系。
1、每個表空間中可以存放一個或多個段；
2、每個段是由一個或多個區段組成；
3、每個區段是有一個或多個連續的Oracle連續的數據塊組成。
然後，了解下將表數據分區存儲以及將索引分區存儲的意義：
1、分區表
當一個表的規模很大或者並行操作非常頻繁時，可以把這個表劃分成若干個分區，每個分區為一個存儲數據的段，因其對應的物理磁盤的邏輯結構及位置不同，所以可以提高並行操作的能力。
2、索引分區
當在一個大型表上創建索引時，這個索引也會很大，所以也可以像分區表那樣分區存儲。一個索可以分布在不同的表空間上，但是每個索引分區只能存放在一個表空間中。引入索引分區的目的也是減少輸入或輸出競爭。

ORACLE分區表設置，想問下以下問題：

沒有所謂的more than，有一個defualt分區，可以存放設定條件不相符的數據。
1.第一個問題：效率的提高取決於你對該表的訪問方式，比如你要經常刪除、截斷某一個分區范圍的數據這個時候分區會極大的提高你的效率，但是要注意索引的影響，分區表的索引分為全局索引和本地索引，在上述操作執行後，全局索引會失效需要重建或者加update index子句來保證索引的有效性，而本地索引就不受此限制，但是在你對表做查詢或者與其他表做關聯的時候，數據的查詢范圍也是你需要考慮的一個方面，一般來講本地索引在查詢&關聯只訪問某一個分區的時候效率高於全局索引，如果查詢&關聯的范圍是多個分區，那麼全局索引效率高於本地索引；另外就是分區的數量，5個分區和50個分區的區別還是取決於你的業務需求以及對執行效率的綜合考慮，假如你經常做大范圍的數據查詢，那麼分區越多效率越低；如果你所做的查詢往往只有一條或者幾條記錄且一般都集中在同一個分區，那麼分區越小效率越高，當然這些效率在數據倉庫層面影響有限，往往實在聯機事務處理這種數據庫應用上能夠積累比較大的效率提升。
第2個問題，關於備份分區表同樣可以使用exp、expdp、rman、等方式進行備份，exp、expdp還支持分區表的某一個分區的備份，比較方便。
第3個問題推薦你去查一下junsansi寫的全面學習分區表分區索引的那篇文章，適合入門。網上隨便搜一下就可以找到下載。