程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 大數據分區表，數據分區表

大數據分區表，數據分區表

編輯：Oracle教程

大數據分區表，數據分區表

2014-08-22 BaoXinjian

一、摘要

PLSQL_性能優化系列09_Oracle Partition Table

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/

oracle 怎快速地備份一批具有大數據量的分區表及其數據（表結構、索引、分區及數據要一致）？

如一樓童鞋說的，可以使用exp命令來滿足你的要求。
--exp id/password@instance_name
C:\Users\Limbo>exp scott/tiger@mydb
Export: Release 10.2.0.4.0 - Production on Sun Nov 27 14:10:14 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--默認敲回車即可
Enter array fetch buffer size: 4096 >
--錄入導出的dmp存放的物理路徑
Export file: EXPDAT.DMP > f:\temp.dmp
--導出的是用戶還是表？默認是用戶，這裡我選擇了表；
(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 3
--詢問是否導出表數據，默認是yes
Export table data (yes/no): yes >

Compress extents (yes/no): yes >
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
--錄入要導出的表名
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > emp
. . exporting table EMP 14 rows exported
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > dept
. . exporting table DEPT 4 rows exported
--導出完成後，再回車即可
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >
Export terminated successfully with warnings.

這樣結束後，導出的dmp文件裡面就是你要的那些表，將來需要恢復的時候使用imp命令即可，前提是要imp的數據的表已經存在才可以imp，且需要滿足約束條件。

oracle數據庫分區表備份

create table par_tab(
data_date varchar2(8),
col_n varchar2(20)
)
partition by range(data_date)(
partition part_201000 values less than ('201001') tablespace tab_201000,
partition part_201001 values less than ('201002') tablespace tab_201003,
partition part_201002 values less than ('201003') tablespace tab_201003,
partition part_201003 values less than ('201004') tablespace tab_201003,
partition part_201004 values less than ('201005') tablespace tab_201006,
partition part_201005 values less than ('201006') tablespace tab_201006,
partition part_201006 values less than ('201007') tablespace tab_201006,
partition part_201007 values less than ('201008') tablespace tab_201009,
partition part_201008 values less than ('201009') tablespace tab_201009,
partition part_201009 values less than ('201010') tablespace tab_201009,
partition part_201010 values less than ('201011') tablespace tab_201012,
partition part_201011 values less than ('201012') tablespace tab_201012,
partition part_201012 values less than ('201013') tablespace tab_201012,
partition part_201099 values less than ('201099') tablespace tab_201099,
partition part_201100 values less than ('201001') tablespace tab_201000,
partition part_201101 values less than ('201002') tablespace tab_201003,
partition part_201102 values less than ('201003') tablespace tab_201003,
partition part_201103 valu......余下全文>>