partition table and partition index

l         增加可用性，一個分割槽有問題，不影響其他的分割槽

l         減少維護工作量

l         均衡I/O，減少爭用。

l         提高查詢速度

l         分割槽對user是透明

Table

1).range partition

create table part1(

id number primary key,

name varchar2(10),

dt date

)

partition by range(dt)

(

partition p1 values less than (to_date('2012/01/01','yyyy/mm/dd')) tablespace users,

partition p2 values less than (to_date('2013/01/01','yyyy/mm/dd')) tablespace test,

partition p3 values less than (maxvalue) tablespace users

)

SQL> insert into part1 values(4,'a',sysdate);

已建立 1 個資料列.

SQL> insert into part1 values(5,'b',sysdate-200);

已建立 1 個資料列.

SQL> insert into part1 values(6,'c',sysdate+200);

已建立 1 個資料列.

SQL> commit;

確認完成.

SQL> select * from part1 partition(p2);

        ID NAME                 DT

---------- -------------------- --------------

         4 a                    01-7月 -12

         1 a                    01-7月 -12

Range partition可以是兩個欄位或多個欄位

create table part2(

id number primary key,

dt date,

des varchar2(20)

)

partition by range(id,dt)

(

partition p1 values less than (1,to_date('2012/01/01','yyyy/mm/dd')) tablespace users,

partition p2 values less than (10,to_date('2013/01/01','yyyy/mm/dd')) tablespace test,

partition p3 values less than (maxvalue,maxvalue) tablespace users

)

2).hash 分割槽      à雜湊分割槽

主要用去取值不確定的情況下采用的方法

create table part_hash(

id number primary key,

name varchar2(20),

dt date)

partition by hash(id)

(

partition p1 tablespace users,

partition p2 tablespace test

)

SQL> select * from part_hash partition (p1);

        ID NAME                                     DT

---------- ---------------------------------------- --------------

         2 b                                        30-6月 -12

SQL> select * from part_hash partition (p2);

        ID NAME                                     DT

---------- ---------------------------------------- --------------

         1 a                                        01-7月 -12

         3 c                                        28-6月 -12

或者採用如下方法：

create table part_hash1(

id number primary key,

name varchar2(20),

dt date)

storage(initial 1024k)              --表空間的初始分配1024k

partition by hash(id)

partitions 3

store in (users,test,system);

3).list partition  à列表分割槽

主要是用於某個列的值是可以列舉的

create table part4

(id number(1),

 name varchar2(10)

 )

 partition by list(id)

 (partition p1 values (1) tablespace users,

  partition p2 values (2) tablespace test,

  partition p3 values (default) tablespace users

 )

insert into part4 values(1,'A')

insert into part4 values(2,'B')

insert into part4 values(3,'B')

select * from part4 partition (p3)

4).組合分割槽

a.Range-hash partition

b.range-list partition

c.range-range partition

d.list-range partition

e.list-list partition

f.list-hash partition

eg:

create table part5

(id int,

 name varchar2(10)

)

partition by range(id)

subpartition by hash(name)

subpartitions 2 store in (users,test)

(partition p1 values less than (10) tablespace users,

 partition p2 values less than (20) tablespace test,

 partition p3 values less than (maxvalue) tablespace users

)

先進行range partition，然後子分割槽按照hash進行。

5).interval分割槽11g新增)

Interval分割槽是range分割槽的一種加強版本。可以實現equi-sized範圍分割槽的自動化。隨著資料的增加會分配更多的分割槽，並自動建立新的分割槽和本地索引。

create table part6

(id int,

 name varchar2(20),

 dt date

 )

partition by range(dt)

interval (numtoyminterval(1,'month'))

(

partition p1 values less than(to_date('2012/01/01','yyyy/mm/dd'))

)

6).外來鍵分割槽(11g新增)

SQL> create table part1

  2  (id int,name varchar2(10))

  3  partition by range(id)

  4  (partition p1 values less than (100),

  5   partition p2 values less than (200),

  6   partition p3 values less than (maxvalue)

  7  );

SQL> alter table part1 add primary key(id);

Table altered.

SQL> create table part7

  2  (id int not null,                      à這個必須要，否則出現ora-14652

  3   name varchar2(20),

  4   constraint part7_fk foreign key(id) references part1(id)

  5  )

  6  partition by reference (part7_fk)

  7  /

Table created.

7).虛擬列分割槽

11g以前的版本，只有分割槽鍵存在於表中才可以對該表實施分割槽操作。而11g則不需要。

create table part3

(id int,

 name varchar2(20),

 id1 int generated always as (to_number(substr(to_char(id),1,1)))

)

partition by list(id1)

( partition p1 values (3),

  partition p2 values (5),

  partition p3 values (default)

)

8).表分割槽的管理

Range partition:

增加partition:

增加分割槽

SQL> alter table part1 add partition p3 values less than (to_date('2014/01/01','yyyy/mm/dd')) [tablespace xxx];

已更改表格.

SQL> alter table part1 add partition p4 values less than (maxvalue) [tablespace xxx];

合併分割槽(hash partition)

SQL> alter table part_hash coalesce partition;

SQL> alter table part5 modify partition p1 coalesce subpartition;

刪除分割槽

SQL> alter table part1 drop partition p3;

如果有全域性index，刪除partition之後，index會標識為unusable，需要重建index。Alter index xxx rebuild

如果有約束：alter table xxx disable constraints cons_xxx

Alter table xxxx drop partition px;

Alter table xxx enable constraints cons_xxx

融合分割槽

create index part1_index on part1(id,name)

 local

 (partition p1,

  partition p2,

  partition p3,

  partition p4

 )

SQL> alter table part1 merge partitions p3,p4 into partition p5;

索引變成無效

SQL> select status from user_indexes where index_name='PART1_INDEX'

  2  ;

STATUS

----------------

N/A

SQL> alter table part1 modify partition p5 rebuild unusable local indexes;

非分割槽錶轉換為分割槽表

1).檢測是否可以轉換

SQL> exec dbms_redefinition.can_redef_table('scott','test')

已順利完成 PL/SQL 程式.

2).建立臨時表並化分割槽

create table test_temp

(owner varchar2(30) not null,

 object_name varchar2(30) not null,

 object_id number not null,

 object_type varchar2(30)

 )

partition by range(object_id)

(partition p1 values less than (400) tablespace users,

 partition p2 values less than (950) tablespace test,

 partition p3 values less than (maxvalue) tablespace users

)

3).開始重定義表

SQL> exec dbms_redefinition.start_redef_table('scott','test','test_temp')

已順利完成 PL/SQL 程式.

4).建立索引限制及觸發器

SQL> var v_num number

SQL> exec dbms_redefinition.copy_table_dependents('scott','test','test_temp',dbms_redefinition.cons_orig_params,true,true,true,true,:v_num,true);

已順利完成 PL/SQL 程式.

http://yangtingkun.itpub.net/post/468/494878 (其中遇見問題參考)

5).同步表內容

如果同步的資料很大，則可以考慮透過prebuilt table的方法建立物化檢視

Exec dbms_redefinition.sync_interim_table(‘scott’,’test’,’test_temp’)

6).結束轉換過程

SQL> exec dbms_redefinition.finish_redef_table('scott','test','test_temp')

已順利完成 PL/SQL 程式.

7).刪除臨時表test_temp

Drop table test_temp

其中可以使用dbms_redefinition.abort_redef_table(‘scott’,’test’,’test_temp’)中途退出該過程。

Index

Local index:

l         本地索引一定是分割槽索引，分割槽鍵等同於表的分割槽鍵，分割槽數等同於表的分割槽數目，本地索引的分割槽機制和表的分割槽機制一樣。

l         如果本地索引的的索引列伊分割槽鍵開頭，則稱之為字首索引

l         如果本地索引的索引列不是以分割槽鍵開頭，不包含分割槽鍵之，稱之為非字首索引

l         點陣圖索引只能為本地分割槽索引

l         本地所有多應用於資料倉儲環境中olap。

l         本地所有隻支援分割槽內的唯一性，無法支援表上的唯一性，如果要使用本地索引給表做唯一性約束，則約束中必須要包括分割槽鍵列。

create index test_index on test(object_id)

local

(

partition p1 tablespace users,

partition p2 tablespace test,

partition p3 tablespace tbs

)

alter table test add primary key(object_Id) using index test_index

或者:

create index test_index1 on test(object_id) local

select dbms_metadata.get_ddl('INDEX','TEST_INDEX1','SCOTT')  from dual

create index test_index2 on test(object_type) local

非字首索引，索引中不包含分割槽鍵

select dbms_metadata.get_ddl('INDEX','TEST_INDEX2','SCOTT')  from dual

SQL> select index_name,table_name,partitioning_type,locality,alignment from user_part_indexes where table_name='TEST';

INDEX_NAME      TABLE_NAME           PARTITIONING_T LOCALITY     ALIGNMENT

--------------- -------------------- -------------- ------------ ------------------------

TEST_INDEX1     TEST                 RANGE          LOCAL        PREFIXED

TEST_INDEX2     TEST                 RANGE          LOCAL        NON_PREFIXED

Global index:

l         全域性索引的分割槽鍵和分割槽數和表的分割槽鍵和分割槽數可能都不相同，表和全域性索引的分割槽機制不一樣。

l         全域性索引可以分割槽，也可以不分割槽索引。全域性索引必須是字首索引，即全域性索引的索引列必須是一索引分割槽鍵作為前幾列。

l         全域性分割槽索引的索引條目可能指向若干個分割槽，因此，對於全域性分割槽索引，即使只截斷一個分割槽中的資料，都需要rebuild若干個分割槽甚至是整個索引。

l         全域性索引多用於oltp系統中。

l         全域性分割槽索引只按範圍或者雜湊hash分割槽，hash分割槽是10g以後才支援。

l         9i後對分割槽表組move或者truncate時可以用update global indexes語句來同步更新全域性分割槽索引，用消耗一定資源來換可用性。

l         全域性分割槽索引只能是B樹索引，而且只能是字首索引。

Eg:

create table test1

(id int,

 name varchar2(20),

 desc1 varchar2(20)

)

partition by range(id)

(

partition p1 values less than (1000) tablespace users,

partition p2 values less than (5000) tablespace test,

partition p3 values less than (maxvalue) tablespace tbs

)

建立索引:

create index test1_global_index on test1(id) global

partition by range(id)    

(partition p1 values less than (4000) tablespace users,

 partition p2 values less than (maxvalue) tablespace test

 )

SQL> alter table test1 drop partition p3;

已更改表格.

索引的狀態發生變化

SQL> select index_name,partition_name,status from user_ind_partitions where index_name='TEST1_GLOBAL_INDEX';

INDEX_NAME           PARTITION_ STATUS

-------------------- ---------- ----------------

TEST1_GLOBAL_INDEX   P1         UNUSABLE

TEST1_GLOBAL_INDEX   P2         UNUSABLE

必須加上前置碼

SQL> l

  1  create index test1_global_index1 on test1(object_type) global

  2  partition by range(id)

  3  (partition p1 values less than (4000),

  4   paritition p2 less than (maxvalues)

  5* )

SQL> /

partition by range(id)

                     *

 ERROR 在行 2:

ORA-14038: GLOBAL 分割索引必須加上前置碼

不能和點陣圖索引使用

SQL> l

  1  create bitmap index test1_global_index1 on test1(object_type) global

  2  partition by range(id)

  3  (partition p1 values less than (4000),

  4   paritition p2 less than (maxvalues)

  5* )

SQL> /

create bitmap index test1_global_index1 on test1(object_type) global

*

 ERROR 在行 1:

ORA-25113: GLOBAL 可能無法與點陣圖索引一起使用

操作索引部分

SQL> alter index test1_global_index drop partition p1;

已更改索引.

SQL> alter index test1_global_index rebuild partition p2;

已更改索引.

SQL> alter index test1_global_index rename partition p2 to p1;

已更改索引.

user_part_tables           分割槽表資訊

user_tab_partitions        分割槽分層資訊

user_tab_subpartitions     子分割槽分層資訊

user_part_key_columns       分割槽表的分割槽鍵列

user_subpart_key_columns    組合分割槽表的子分割槽鍵列

    表的分割槽的列統計資訊和直方圖

user_part_col_statistics

user_part_histograms       表的分割槽上的直方圖的直方圖的直方圖資料

user_subpart_histograms

user_part_indexes           分割槽索引的分割槽資訊

user_ind_partitions     分割槽層次的分割槽資訊，分割槽的儲存引數

user_ind_subpartitions