oracle分區表

標籤：

官方文檔：http://docs.oracle.com/cd/E11882_01/server.112/e40540/schemaob.htm#CNCPT1514

1. oracle最大支援1024K-1個分區，列的資料類型為LONG或者LONG RAW的表不允許建立分區。CLOB和BCLOB可以。

　什麼時候需要對錶進行分區：

　① 大於2GB的表

　② 曆史資料只是用來查詢，而且不停在插入新資料的表

　1.1 range分區

　　　range分區是最常用的分區類型，通常用於對日期進行分區，必須聲明values less than子句，maxvalue代表最大值。　

create table test_range_table(       ename varchar2(30),       deptno number,       hire_date date) partition by range(hire_date)(  partition before_2014 values less than (to_date(‘2014-01-01‘,‘yyyy-mm-dd‘)),  partition before_2015 values less than (to_date(‘2015-01-01‘,‘yyyy-mm-dd‘)),  partition before_forever values less than (maxvalue));

　　如上SQL建立了分區表test_range_table,通過對hire_date分區將表分成了三個分區：before_2014(hire_date在2014年之前),before_2015(hire_date在2014和2015年之間),before_forever(hire_date在2015年之後).分區表以及分區可以通過user_tab_partitions,dba_tab_partitions,all_tab_partitions查詢。

　1.2 list分區可以對看似無序的資料按照某種規則進行整理，list分布不支援對多列進行分區，單支援為一個分區設定多個不同的值。如果往list分區表插入未在list分區定義的值，會報錯的，default關鍵字可以避免這個問題。對於分區表test_list_table,假設不指定other_region分區，執行

insert into test_list_table values(‘張三‘,10,‘張三‘);

會報錯："ORA-14400：插入的分區關鍵字未映射到任何分區"

--list分區表create table test_list_table(       ename varchar2(30),       deptno number,       region varchar2(30)) partition by list(region)(  partition china_region values(‘china‘),  partition japan_region values(‘japan‘),  partition europe_region values(‘france‘,‘germany‘),  partition other_region values(default))

　1.3 hash分區建立的時候只需要指定分區列和分區個數即可，也可以指定分區的資料表空間。

create table test_hash_table(first_name varchar2(30),       last_name varchar2(30),       hire_date date)        partition by hash(last_name)       partitions 4       store in (tb_01,tb_02,tb_03,tb_04);

　1.4 range-hash分區，子分區資訊可以通過user_tab_subpartitions,all_tab_subpartitions,dba_tab_subpartitions查詢。

--range-hash分區create table test_range_hash_table(       first_name varchar2(30),       last_name varchar2(30),       hire_date date) partition by range(hire_date)subpartition by hash(last_name)subpartition template(
subpartition sp1 ,subpartition sp2,subpartition sp3,subpartition sp4)(  partition before_2014 values less than (to_date(‘2014-01-01‘,‘yyyy-mm-dd‘)),  partition before_2015 values less than (to_date(‘2015-01-01‘,‘yyyy-mm-dd‘)),  partition before_forever values less than (maxvalue))

　 1.5 range-list分區

create table test_range_list_table(       ename varchar2(30),       region varchar2(30),       hire_date date) partition by range(hire_date)subpartition by list(region)subpartition template(  subpartition china_region values(‘china‘),  subpartition japan_region values(‘japan‘),  subpartition europe_region values(‘france‘,‘germany‘),  subpartition other_region values(default)            )( partition before_2014 values less than (to_date(‘2014-01-01‘,‘yyyy-mm-dd‘)),  partition before_2015 values less than (to_date(‘2015-01-01‘,‘yyyy-mm-dd‘)),  partition before_forever values less than (maxvalue));

 

2. 分區索引

　　分區索引分類兩類global indexes(基於整個表) 和 local indexes(基於表的分區)，通常來說OLTP系統應該使用global indexes,資料倉儲或者DSS應該使用local indexes。可以按照如下順序決定採用哪種分區索引：

　　Step-1:如果分區列是被索引列的子集，用local index，結束；否則Step-2

　　Step-2:如果索引唯一，用global index,結束；否則Step-3

　　Step-3:如果更看重維護效能，用local index，結束；否則Step-4

　　Step-4:如果是OLTP使用者看重response time用global index；如果是資料倉儲使用者看重輸送量用local index

　　2.1 LOCAL PARTITIONED INDEX

　　　　local index和表分區是一一對應的

　　　　local nonprefixed index : 索引列為非分區列的簡單索引，索引前置列為非分區列的複合索引，不一定允許分區修剪

　　　　local prefixed index: 索引列為分區列的簡單索引，索引前置列為分區列的複合索引，允許分區修剪

　　　　local index的優點：

　　　　① 當一個分區的資料失效時，不會影響其他分區

　　　　② 當移動表分區或者資料被移出分區時，只有相關分區的索引需要被rebuilt，對global來說，所有的分區索引都要被rebuilt

　　　　③ 當基於時間點的恢複發生時，可以只恢複相關分區的索引，不需要rebuilit所有分區索引。　

create index test_range_table_local_n1 on test_range_table(hire_date) local;

　　2.2 GLOBAL PARTITIONED INDEX

　　　　global range partitioned index：基於範圍的global index

　　　　global hash partitioned index：基於hash的global index

　　　　global index是獨立於分區表的，假設表基於hire_date的分區為3個（before_2014,before_2015,before_forever），可以基於hire_date建立四個分區的global_index(before_2013,before_2014,before_2015,before_forever),global index也可以對不是分區列的column進行隨意的分區。　

--global range index--drop index test_range_table_local_n1create index test_range_table_gl_range on test_range_table(hire_date) global partition by range(hire_date)(  partition before_2013 values less than (to_date(‘2013-01-01‘,‘yyyy-mm-dd‘)),  partition before_2014 values less than (to_date(‘2014-01-01‘,‘yyyy-mm-dd‘)),  partition before_2015 values less than (to_date(‘2015-01-01‘,‘yyyy-mm-dd‘)),  partition before_forever values less than (maxvalue)      )--global hash index--drop index test_range_table_gl_range;create index test_range_table_gl_hash on test_range_table(hire_date)global partition by hash(hire_date)partitions 4;

　　2.3 GLOBAL NONPARTITIONED INDEX

 

　　2.4 關於分區索引

　　　　可以通過user_ind_partitions，user_dba_partitions，user_all_partitions查詢分區索引。

　　　　只能給分區表建立local類型的位元影像索引。

　　　　global index可以是唯一索引，只有當索引列是分區列的時候local index才可以是唯一索引。

　　　　分區索引在OLTP系統中的應用：

　　　　① global index和unique local index效能更優

　　　　② 當存在對分區表的分區或者子分區維護時，local index表現更好

　　　　③ 索引單調增長的時候，hash-partitioned global index更好，因為大部分的索引插入都是在索引右側。

　　　　分區索引在DSS或者資料倉儲中的應用：

　　　　① local index在資料載入或者分區維護的時候更方便

　　　　② local index可以並行的對多個索引分割區進行掃描

 

3.分區對系統效能的提升

　　3.1 PARTITION PRUNING(分區修剪)

　　　　當謂詞條件中包含分區列時，oracle會指定把不需要的分區剪掉。當對分區列施加函數時，分區修剪失效；同樣，但對索引列施加函數是，索引失效，除非該索引是基於資料的。

　　3.2 PARTITION-WISE JOINS 

　　　　當兩個分區表做串連，串連列恰好是這個兩個分區表的分區列，則串連操作會變成並發的進行多個分區和多個分區之間的串連操作？？？

　　3.3 PARALLEL DML

oracle分區表