一 .? 分區(qū)表理論知識

Oracle 提供了分區(qū)技術以支持 VLDB(Very Large DataBase) 。分區(qū)表通過對分區(qū)列的判斷，把分區(qū)列不同的記錄，放到不同的分區(qū)中。分區(qū)完全對應用透明。

Oracle 的分區(qū)表可以包括多個分區(qū)， 每個分區(qū)都是一個獨立的段（ SEGMENT ），可以存放到不同的表空間中 。查詢時可以通過查詢表來訪問各個分區(qū)中的數(shù)據(jù)，也可以通過在查詢時直接指定分區(qū)的方法來進行查詢。

?

When to Partition a Table 什么時候需要分區(qū)表，官網(wǎng)的 2 個建議如下：

（ 1 ） Tables greater than 2GB should always be considered for partitioning.

（ 2 ） Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.

?

在 oracle 10g 中最多支持： 1024k-1 個分區(qū)：

Tables can be partitioned into up to 1024K-1 separate partitions

?

聯(lián)機文檔上有關分區(qū)表和索引的說明：

Partitioned Tables and Indexes

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/partconc.htm#sthref2604

?

分區(qū)提供以下優(yōu)點：

（ 1 ）由于將數(shù)據(jù)分散到各個分區(qū)中，減少了數(shù)據(jù)損壞的可能性；

（ 2 ）可以對單獨的分區(qū)進行備份和恢復；

（ 3 ）可以將分區(qū)映射到不同的物理磁盤上，來分散 IO ；

（ 4 ）提高可管理性、可用性和性能。

?

Oracle 10g 提供了以下幾種分區(qū)類型：

（ 1 ）范圍分區(qū)（ range ）；

（ 2 ）哈希分區(qū)（ hash ）；

（ 3 ）列表分區(qū)（ list ）；

（ 4 ）范圍－哈希復合分區(qū)（ range-hash ）；

（ 5 ）范圍－列表復合分區(qū)（ range-list ）。

?

Range 分區(qū)：

Range 分區(qū)是應用范圍比較廣的表分區(qū)方式，它是以列的值的范圍來做為分區(qū)的劃分條件，將記錄存放到列值所在的 range 分區(qū)中。

如按照時間劃分， 2010 年 1 月的數(shù)據(jù)放到 a 分區(qū)， 2 月的數(shù)據(jù)放到 b 分區(qū)，在創(chuàng)建的時候，需要指定基于的列，以及分區(qū)的范圍值。

在按時間分區(qū)時， 如果某些記錄暫無法預測范圍，可以創(chuàng)建 maxvalue 分區(qū)，所有不在指定范圍內(nèi)的記錄都會被存儲到 maxvalue 所在分區(qū)中。

?

如：

create table pdba (id number, time date) partition by range (time)

(

partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),

partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),

partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),

partition p4 values less than (maxvalue)

)

?

Hash 分區(qū)：

　　對于那些無法有效劃分范圍的表，可以使用 hash 分區(qū)，這樣對于提高性能還是會有一定的幫助。 hash 分區(qū)會將表中的數(shù)據(jù)平均分配到你指定的幾個分區(qū)中，列所在分區(qū)是依據(jù)分區(qū)列的 hash 值自動分配，因此你并不能控制也不知道哪條記錄會被放到哪個分區(qū)中， hash 分區(qū)也可以支持多個依賴列。

?

如：

create table test

(

transaction_id number primary key,

item_id number(8) not null

)

partition by hash(transaction_id)

(

partition part_01 tablespace tablespace01,

partition part_02 tablespace tablespace02,

partition part_03 tablespace tablespace03

);

在這里，我們指定了每個分區(qū)的表空間。

?

List 分區(qū)：

List 分區(qū)也需要指定列的值，其分區(qū)值必須明確指定，該分區(qū)列只能有一個，不能像 range 或者 hash 分區(qū)那樣同時指定多個列做為分區(qū)依賴列，但它的單個分區(qū)對應值可以是多個。

　　在分區(qū)時必須確定分區(qū)列可能存在的值，一旦插入的列值不在分區(qū)范圍內(nèi)，則插入 / 更新就會失敗， 因此通常建議使用 list 分區(qū)時，要創(chuàng)建一個 default 分區(qū)存儲那些不在指定范圍內(nèi)的記錄 ，類似 range 分區(qū)中的 maxvalue 分區(qū)。

?

在根據(jù)某字段，如城市代碼分區(qū)時，可以指定 default ，把非分區(qū)規(guī)則的數(shù)據(jù)，全部放到這個 default 分區(qū)。

?

如：

create table custaddr
(

id?varchar2(15 byte)?not null,

areacode varchar2(4 byte)
)

partition by list (areacode)
( partition t_list025 values ('025'),?
partition t_list372 values ('372') ,?
partition t_list510 values ('510'),

partition p_other values (default)

)

?

組合分區(qū)：

如果某表按照某列分區(qū)之后，仍然較大，或者是一些其它的需求，還可以通過分區(qū)內(nèi)再建子分區(qū)的方式將分區(qū)再分區(qū)，即組合分區(qū)的方式。

　　組合分區(qū)呢在 10g 中有兩種： range-hash ， range-list 。注意順序，根分區(qū)只能是 range 分區(qū)，子分區(qū)可以是 hash 分區(qū)或 list 分區(qū)。

?

如：

create table test

(

transaction_id number primary key,

transaction_date date

)

partition by range(transaction_date) subpartition by hash(transaction_id)

subpartitions 3 store in (tablespace01,tablespace02,tablespace03)

(

partition part_01 values less than(to_date(’2009-01-01’,’yyyy-mm-dd’)),

partition part_02 values less than(to_date(’2010-01-01’,’yyyy-mm-dd’)),

partition part_03 values less than(maxvalue)

);

?

create table emp_sub_template (deptno number, empname varchar(32), grade number)

partition by range(deptno) subpartition by hash(empname)

subpartition template

(subpartition a tablespace ts1,

subpartition b tablespace ts2,

subpartition c tablespace ts3,

subpartition d tablespace ts4

)

(partition p1 values less than (1000),

partition p2 values less than (2000),

partition p3 values less than (maxvalue)

);

?

?

create table quarterly_regional_sales

(deptno number, item_no varchar2(20),

txn_date date, txn_amount number, state varchar2(2))

tablespace ts4

partition by range (txn_date)

subpartition by list (state)

(partition q1_1999 values less than (to_date('1-apr-1999','dd-mon-yyyy'))

(subpartition q1_1999_northwest values ('or', 'wa'),

subpartition q1_1999_southwest values ('az', 'ut', 'nm'),

subpartition q1_1999_northeast values ('ny', 'vm', 'nj'),

subpartition q1_1999_southeast values ('fl', 'ga'),

subpartition q1_1999_northcentral values ('sd', 'wi'),

subpartition q1_1999_southcentral values ('ok', 'tx')

),

partition q2_1999 values less than ( to_date('1-jul-1999','dd-mon-yyyy'))

(subpartition q2_1999_northwest values ('or', 'wa'),

subpartition q2_1999_southwest values ('az', 'ut', 'nm'),

subpartition q2_1999_northeast values ('ny', 'vm', 'nj'),

subpartition q2_1999_southeast values ('fl', 'ga'),

subpartition q2_1999_northcentral values ('sd', 'wi'),

subpartition q2_1999_southcentral values ('ok', 'tx')

),

partition q3_1999 values less than (to_date('1-oct-1999','dd-mon-yyyy'))

(subpartition q3_1999_northwest values ('or', 'wa'),

subpartition q3_1999_southwest values ('az', 'ut', 'nm'),

subpartition q3_1999_northeast values ('ny', 'vm', 'nj'),

subpartition q3_1999_southeast values ('fl', 'ga'),

subpartition q3_1999_northcentral values ('sd', 'wi'),

subpartition q3_1999_southcentral values ('ok', 'tx')

),

partition q4_1999 values less than ( to_date('1-jan-2000','dd-mon-yyyy'))

(subpartition q4_1999_northwest values ('or', 'wa'),

subpartition q4_1999_southwest values ('az', 'ut', 'nm'),

subpartition q4_1999_northeast values ('ny', 'vm', 'nj'),

subpartition q4_1999_southeast values ('fl', 'ga'),

subpartition q4_1999_northcentral values ('sd', 'wi'),

subpartition q4_1999_southcentral values ('ok', 'tx')

)

);

?

?

在 Oracle 11g 中，組合分區(qū)功能這塊有所增強，又增加了 range-range,list-range,

list-list,list-hash ，并且 ? 11g 里面還支持 Interval 分區(qū)和虛擬列分區(qū)。

?

這塊可以參考 Blog ：

Oracle 11g? 新特性簡介

http://blog.csdn.net/tianlesoftware/archive/2010/01/06/5134819.aspx

?

分區(qū)表 ? 之 ?Interval 分區(qū) ? 和 ? 虛擬列 ? 按星期分區(qū)表

http://blog.csdn.net/tianlesoftware/archive/2010/06/10/5662337.aspx

?

?

二 .? 普通表轉(zhuǎn)分區(qū)表方法

?

將普通表轉(zhuǎn)換成分區(qū)表有 4 種方法：

1. Export/import method

2. Insert with a subquery method

3. Partition exchange method

4. DBMS_REDEFINITION

具體參考：

How to Partition a Non-partitioned Table [ID 1070693.6]

http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218704.aspx

?

邏輯導出導入這里就不做說明，我們看看其他三種方法。

?

2.1? 插入： ? Insert with a subquery method

這種方法就是使用 insert? 來實現(xiàn)。 ? 當然在創(chuàng)建分區(qū)表的時候可以一起插入數(shù)據(jù)，也可以創(chuàng)建好后在 insert? 進去。 ? 這種方法采用 DDL 語句，不產(chǎn)生 UNDO ，只產(chǎn)生少量 REDO ，建表完成后數(shù)據(jù)已經(jīng)在分布到各個分區(qū)中。

?

SQL> select count(*) from dba;

COUNT(*)

----------

2713235

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

會話已更改。

?

SQL> select time_fee from dba where rownum<5;

TIME_FEE

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

2011-02-17 19:29:09

2011-02-17 19:29:15

2011-02-17 19:29:18

2011-02-17 19:29:20

SQL>

?

2.1.1?Oracle 11g 的 Interval

在 11g 里的 Interval 創(chuàng)建，這種方法對沒有寫全的分區(qū)會自動創(chuàng)建。?比如我這里只寫了 1 月日期，如果插入的數(shù)據(jù)有其他月份的，會自動生成對應的分區(qū)。

?

/* Formatted on 2011/03/02 15:41:09 (QP5 v5.115.810.9015) */

CREATE ? TABLE ?intervaldave

PARTITION ? BY ? RANGE ? ( time_fee )

INTERVAL ? ( ? NUMTOYMINTERVAL ? ( 1 , ? 'MONTH' ) ? )

(PARTITION ?part1

VALUES ? LESS ? THAN ? (TO_DATE ? ( '01/12/2010' , ? 'MM/DD/YYYY' )))

AS

SELECT ? ID, ?TIME_FEE? FROM ? DAVE ;

?

?

SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVALDAVE';

?

TABLE_NAME?PARTITION_NAME

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

INTERVALDAVE?PART1

INTERVALDAVE?SYS_P24

INTERVALDAVE?SYS_P25

INTERVALDAVE?SYS_P26

INTERVALDAVE?SYS_P33

INTERVALDAVE?SYS_P27

INTERVALDAVE?SYS_P28

?

2.1.2?Oracle 10g? 版本

在 10g 里面，我需要寫全所有的分區(qū)。

?

sql> create table pdba (id, time) partition by range (time)

2 (partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),

3?partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),

4?partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),

5?partition p4 values less than (maxvalue))

6?as select id, time_fee from dba;

表已創(chuàng)建。

SQL> select table_name,partition_name from user_tab_partitions where table_name='PDBA';

TABLE_NAME?PARTITION_NAME

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

PDBA?P1

PDBA?P2

PDBA?P3

PDBA?P4

sql> select count(*) from pdba partition (p1);

count(*)

----------

1718285

sql> select count(*) from pdba partition (p2);

count(*)

----------

183667

sql> select count(*) from pdba partition (p3);

count(*)

----------

188701

sql> select count(*) from pdba partition (p4);

count(*)

----------

622582

sql>

?

現(xiàn)在分區(qū)表已經(jīng)建好了，但是表名不一樣，需要用 rename 對表重命名一下：

SQL> rename dba to dba_old;

表已重命名。

SQL> rename pdba to dba;

表已重命名。

SQL> select table_name,partition_name from user_tab_partitions where table_name='DBA';

TABLE_NAME?PARTITION_NAME

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

DBA?P1

DBA?P2

DBA?P3

DBA?P4

?

2.2 .? 交換分區(qū)： Partition exchange method

這種方法只是對數(shù)據(jù)字典中分區(qū)和表的定義進行了修改，沒有數(shù)據(jù)的修改或復制，效率最高。適用于包含大數(shù)據(jù)量的表轉(zhuǎn)到分區(qū)表中的一個分區(qū)的操作。盡量在閑時進行操作。

?

交換分區(qū)的操作步驟如下：

1.? 創(chuàng)建分區(qū)表，假設有 2 個分區(qū)， P1 ， P2.

2.? 創(chuàng)建表 A 存放 P1 規(guī)則的數(shù)據(jù)。

3.? 創(chuàng)建表 B? 存放 P2 規(guī)則的數(shù)據(jù)。

4.? 用表 A? 和 P1? 分區(qū)交換。 ? 把表 A 的數(shù)據(jù)放到到 P1 分區(qū)

5.? 用表 B? 和 p2? 分區(qū)交換。 ? 把表 B 的數(shù)據(jù)存放到 P2 分區(qū)。

?

創(chuàng)建分區(qū)表：

sql> create table p_dba

2?(id number,time date)

3?partition by range(time)

4?(

5?partition p1 values less than (to_date('2010-09-1', 'yyyy-mm-dd')),

6?partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd'))

7?);

表已創(chuàng)建。

?

注意：我這里只創(chuàng)建了 2 個分區(qū)，沒有創(chuàng)建存放其他數(shù)據(jù)的分區(qū)。

?

創(chuàng)建 2 個分別對應分區(qū)的基表：

SQL> CREATE TABLE dba_p1 as SELECT id,time_fee FROM dba_old? WHERE?time_fee<TO_DATE('2010-09-1', 'YYYY-MM-DD');

表已創(chuàng)建。

?

SQL> CREATE TABLE dba_p2 as SELECT id,time_fee FROM dba_old? WHERE?time_fee<TO_DATE('2010-11-1', 'YYYY-MM-DD') and time_fee>TO_DATE('2010-09-1', 'YYYY-MM-DD');

表已創(chuàng)建。

?

SQL> select count(*) from dba_p1;

COUNT(*)

----------

1536020

SQL> select count(*) from dba_p2;

COUNT(*)

----------

365932

?

SQL>

?

講 2 個基表與 2 個分區(qū)進行交換：

SQL> alter table p_dba exchange partition p1 with table dba_p1;

表已更改。

SQL> alter table p_dba exchange partition p2 with table dba_p2;

表已更改。

?

查詢 2 個分區(qū)：

SQL> select count(*) from p_dba partition(p1);

COUNT(*)

----------

1536020

SQL> select count(*) from p_dba partition(p2);

COUNT(*)

----------

365932

注意：數(shù)據(jù)和之前的基表一致。

?

查詢原來的 2 個基表：

SQL> select count(*) from dba_p2;

COUNT(*)

----------

0

SQL> select count(*) from dba_p1;

COUNT(*)

----------

0

注意： ?2 個基表的數(shù)據(jù)變成成 0 。

?

在這里我們看一個問題，一般情況下，我們在創(chuàng)建分區(qū)表的時候，都會有一個其他分區(qū)，用來存放不匹配分區(qū)規(guī)則的數(shù)據(jù)。?在這個例子中，我只創(chuàng)建了 2 個分區(qū)，沒有創(chuàng)建 maxvalue 分區(qū)。?現(xiàn)在我來插入一條不滿足規(guī)則的數(shù)據(jù)，看結果：

?

SQL> insert into p_dba values(999999,to_date(' 2012-12-29 ','yyyy-mm-dd'));

insert into p_dba values(999999,to_date('2012-12-29','yyyy-mm-dd'))

*

第 ?1? 行出現(xiàn)錯誤 :

ORA-14400:? 插入的分區(qū)關鍵字未映射到任何分區(qū)

SQL> insert into p_dba values(999999,to_date('2009-12-29','yyyy-mm-dd'));

已創(chuàng)建 ?1? 行。

SQL> select * from p_dba where id=999999;

?

ID TIME

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

999999 29-12 月 -09

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

會話已更改。

SQL> select * from p_dba where id=999999;

?

ID TIME

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

999999 2009-12-29 00:00:00

SQL>

?

通過這個測試可以清楚，如果插入的數(shù)據(jù)不滿足分區(qū)規(guī)則，會報 ORA-14400 錯誤。

?

2.3 .? 使用在線重定義： DBMS_REDEFINITION

?

在線重定義能保證數(shù)據(jù)的一致性，在大部分時間內(nèi)，表都可以正常進行 DML 操作。 只在切換的瞬間鎖表，具有很高的可用性 。這種方法具有很強的靈活性，對各種不同的需要都能滿足。而且，可以在切換前進行相應的授權并建立各種約束，可以做到切換完成后不再需要任何額外的管理操作。

?

關于 DBMS_REDEFINITION 的介紹，參考官方連接：

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_redefi.htm#CBBFDJBC

?

關于用在線重定義創(chuàng)建分區(qū)表，參考：

How To Partition Existing Table Using DBMS_Redefinition [ID 472449.1]

http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218693.aspx

?

這個功能只在 9.2.0.4 以后的版本才有，在線重定義表具有以下功能：

（ 1 ）修改表的存儲參數(shù)；

（ 2 ）將表轉(zhuǎn)移到其他表空間；

（ 3 ）增加并行查詢選項；

（ 4 ）增加或刪除分區(qū)；

（ 5 ）重建表以減少碎片；

（ 6 ）將堆表改為索引組織表或相反的操作；

（ 7 ）增加或刪除一個列。

?

使用在線重定義的一些限制條件：

（ 1 ） ?There must be enough space to hold two copies of the table.

（ 2 ） ?Primary key columns cannot be modified.

（ 3 ）? Tables must have primary keys.

（ 4 ） ?Redefinition must be done within the same schema.

（ 5 ） ?New columns added cannot be made NOT NULL until after the redefinition operation.

（ 6 ） ?Tables cannot contain LONGs, BFILEs or User Defined Types.

（ 7 ） ?Clustered tables cannot be redefined.

（ 8 ）? Tables in the SYS or SYSTEM schema cannot be redefined.

（ 9 ） ?Tables with materialized view logs or materialized views defined on them cannot be redefined.

（ 10 ） ?Horizontal sub setting of data cannot be performed during the redefinition.

?

在 Oracle 10.2.0.4 和 11.1.0.7? 版本下，在線重定義可能會遇到如下 bug ：

Bug 7007594 - ORA-600 [12261]

http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218681.aspx

?

在線重定義的大致操作流程如下：

（ 1 ）創(chuàng)建基礎表 A ，如果存在，就不需要操作。

（ 2 ）創(chuàng)建臨時的分區(qū)表 B 。

（ 3 ）開始重定義，將基表 A 的數(shù)據(jù)導入臨時分區(qū)表 B 。

（ 4 ）結束重定義，此時在 DB 的? Name Directory 里，已經(jīng)將 2 個表進行了交換。即此時基表 A 成了分區(qū)表，我們創(chuàng)建的臨時分區(qū)表 B? 成了普通表。?此時我們可以刪除我們創(chuàng)建的臨時表 B 。它已經(jīng)是普通表。

?

?

下面看一個示例：

?

1.? 創(chuàng)建基本表和索引

sql> conn icd/icd;

已連接。

sql> create table unpar_table (

2?id number(10) primary key,

3?create_date date

4?);

表已創(chuàng)建。

sql> insert into unpar_table select rownum, created from dba_objects;

已創(chuàng)建 72288 行。

sql> create index create_date_ind on unpar_table(create_date);

索引已創(chuàng)建。

sql> commit;

提交完成。

?

2.? 收集表的統(tǒng)計信息

sql> exec dbms_stats.gather_table_stats('icd', 'unpar_table', cascade => true);

pl/sql? 過程已成功完成。

?

3.? 創(chuàng)建臨時分區(qū)表

sql> create table?par_table (id number primary key, time date) partition by range (time)

2?(partition p1 values less than (to_date('2004-7-1', 'yyyy-mm-dd')),

3?partition p2 values less than (to_date('2005-1-1', 'yyyy-mm-dd')),

4?partition p3 values less than (to_date('2005-7-1', 'yyyy-mm-dd')),

5?partition p4 values less than (maxvalue));

表已創(chuàng)建。

?

4.? 進行重定義操作

?

4.1? 檢查重定義的合理性

sql> exec dbms_redefinition.can_redef_table('icd', 'unpar_table');

pl/sql? 過程已成功完成。

?

4.2? 如果 4.1? 沒有問題，開始重定義，這個過程可能要等一會。

?

這里要注意：如果分區(qū)表和原表列名相同，可以用如下方式進行：

SQL> BEGIN

DBMS_REDEFINITION.start_redef_table(

uname => 'ICD',

orig_table => 'unpar_table',

int_table => 'par_table');

END;

/

如果分區(qū)表的列名和原表不一致，那么在開始重定義的時候，需要重新指定映射關系：

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(

'ICD',

'unpar_table',

'par_table',

'ID ID, create_date TIME', --? 在這里指定新的映射關系

DBMS_REDEFINITION.CONS_USE_PK);

?

這一步操作結束后，數(shù)據(jù)就已經(jīng)同步到這個臨時的分區(qū)表里來了。

?

4.3? 同步新表，這是可選的操作

SQL> BEGIN

2?dbms_redefinition.sync_interim_table(

3?uname => 'ICD',

4?orig_table => 'unpar_table',

5?int_table => 'par_table');

6?END;

7?/

PL/SQL? 過程已成功完成。

?

4.4? 創(chuàng)建索引，在線重定義只重定義數(shù)據(jù)，索引還需要單獨建立。

sql> create index create_date_ind2 on par_table(time);

索引已創(chuàng)建。

?

4.5? 收集新表的統(tǒng)計信息

sql> exec dbms_stats.gather_table_stats('icd', 'par_table', cascade => true);

pl/sql? 過程已成功完成。

?

4.6? 結束重定義

SQL> BEGIN

2?dbms_redefinition.finish_redef_table(

3?uname => 'ICD',

4?orig_table => 'unpar_table',

5?int_table => 'par_table');

6?END;

7?/

PL/SQL? 過程已成功完成。

?

結束重定義的意義：

基表 unpar_table? 和臨時分區(qū)表 par_table? 進行了交換。?此時臨時分區(qū)表 par_table 成了普通表，我們的基表 unpar_table 成了分區(qū)表。

?

我們在重定義的時候，基表 unpar_table 是可以進行 DML 操作的。?只有在 2 個表進行切換的時候會有短暫的鎖表。

?

5.? 刪除臨時表

SQL> DROP TABLE par_table;

表已刪除。

?

6.? 索引重命名

SQL> ALTER INDEX create_date_ind2 RENAME TO create_date_ind;

索引已更改。

?

7.? 驗證

sql> select partitioned from user_tables where table_name = 'UNPAR_TABLE';

par

---

yes

sql> select partition_name from user_tab_partitions where table_name = 'UNPAR_TABLE';

partition_name

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

p1

p2

p3

p4

sql> select count(*) from unpar_table;

count(*)

----------

72288

sql> select count(*) from unpar_table partition (p4);

count(*)

----------

72288

sql>

?

?

三 .? 分區(qū)表的其他操作

3.1? 添加新的分區(qū)

添加新的分區(qū)有 2 中情況：

（ 1 ）原分區(qū)里邊界是 maxvalue 或者 default 。?這種情況下，我們需要把邊界分區(qū) drop 掉，加上新分區(qū)后，在添加上新的分區(qū)。或者采用 split ，對邊界分區(qū)進行拆分。

（ 2 ）沒有邊界分區(qū)的。?這種情況下，直接添加分區(qū)就可以了。

?

以邊界分區(qū)添加新分區(qū)示例：

（ 1 ）分區(qū)表和索引的信息如下：

SQL> create table custaddr

2?(

3?id?varchar2(15 byte)?not null,

4?areacode?varchar2(4 byte)

5?)

6?partition by list (areacode)

7?(

8?partition t_list556 values ('556') tablespace icd_service,

9?partition p_other values (default)tablespace icd_service

10?);

表已創(chuàng)建。

SQL> create index ix_custaddr_id on custaddr(id)

2?local (

3?partition t_list556?tablespace icd_service,

4?partition p_other tablespace icd_service

5?);

索引已創(chuàng)建。

?

（ 2 ）插入幾條測試數(shù)據(jù)：

SQL> insert into custaddr values('1','556');

已創(chuàng)建 ?1? 行。

SQL> insert into custaddr values('2','551');

已創(chuàng)建 ?1? 行。

SQL> insert into custaddr values('3','555');

已創(chuàng)建 ?1? 行。

SQL> commit;

提交完成。

SQL> select * from custaddr;

ID?AREA

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

1?556

2?551

3?555

SQL> select * from custaddr partition(t_list556);

ID?AREA

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

1?556

SQL>

?

（ 3 ）刪除 default 分區(qū)

sql> alter table custaddr drop partition p_other;

表已更改。

sql> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

table_name?partition_name

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

custaddr?t_list556

?

（ 4 ）添加新分區(qū)

SQL> alter table custaddr add partition t_list551 values('551') tablespace icd_service;

表已更改。

SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

?

TABLE_NAME?PARTITION_NAME

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

CUSTADDR?T_LIST556

CUSTADDR?T_LIST551

（ 5 ）添加 default? 分區(qū)

SQL> alter table custaddr add partition p_other values (default)?tablespace icd_service;

表已更改。

SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

?

TABLE_NAME?PARTITION_NAME

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

CUSTADDR?T_LIST556

CUSTADDR?T_LIST551

CUSTADDR?P_OTHER

?

（ 6 ）對于局部索引， oracle 會自動增加一個局部分區(qū)索引。驗證一下：

sql> select owner,index_name,table_name,partitioning_type from? dba_part_indexes? where index_name='ix_custaddr_id';

owner?index_name?table_name

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

icd?ix_custaddr_id?custaddr

sql> select index_owner,index_name,partition_name from ?dba_ind_partitions ?where index_name='ix_custaddr_id';

index_owner?index_name?partition_name

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

icd?ix_custaddr_id?p_other

icd?ix_custaddr_id?t_list551

icd?ix_custaddr_id?t_list556

?

分區(qū)索引自動創(chuàng)建了。

?

3.2?split? 分區(qū)拆分

在 3.1? 中，我們說明了可以使用 split 的方式來添加分區(qū)。?這里我們用 split 方法繼續(xù)上面的實驗。

?

sql> alter table custaddr split partition p_other? values('552')? into (partition t_list552 tablespace icd_service, partition p_other tablespace icd_service);

表已更改。

-- 注意這里紅色的地方，如果是 Range 類型的，使用 at ， List 使用 Values 。

SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

TABLE_NAME?PARTITION_NAME

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

CUSTADDR?T_LIST556

CUSTADDR?T_LIST551

CUSTADDR?T_LIST552

CUSTADDR?P_OTHER

?

SQL> select index_owner,index_name,partition_name from dba_ind_partitions?where index_name='IX_CUSTADDR_ID';

?

index_owner?index_name?partition_name

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

icd?ix_custaddr_id?p_other

icd?ix_custaddr_id?t_list551

icd?ix_custaddr_id?t_list552

icd?ix_custaddr_id?t_list556

?

注意：分區(qū)表會自動維護局部分區(qū)索引。全局索引會失效，需要進行 rebuild 。

?

3.3? 合并分區(qū) Merge

相鄰的分區(qū)可以 merge 為一個分區(qū)，新分區(qū)的下邊界為原來邊界值較低的分區(qū)，上邊界為原來邊界值較高的分區(qū)， 原先的局部索引相應也會合并，全局索引會失效，需要 rebuild 。

?

SQL>? alter table custaddr merge partitions t_list552,p_other into partition p_other;

表已更改。

SQL> select index_owner,index_name,partition_name from dba_ind_partitions?where index_name='IX_CUSTADDR_ID';

index_owner?index_name?partition_name

--------------------?------------------------------ ------------------

icd?ix_custaddr_id?p_other

icd?ix_custaddr_id?t_list551

icd?ix_custaddr_id?t_list556

?

SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

?

table_name?partition_name

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

custaddr?t_list556

custaddr?t_list551

custaddr?p_other

?

3.4 .? 移動分區(qū)

SQL> alter table custaddr move partition P_OTHER tablespace system;

?

表已更改。

SQL> alter table custaddr move partition P_OTHER tablespace icd_service;

表已更改。

?

注意： 分區(qū)移動會自動維護局部分區(qū)索引， oracle 不會自動維護全局索引，所以需要我們重新 rebuild 分區(qū)索引，具體需要 rebuild 哪些索引，可以通過 dba_part_indexes,dba_ind_partitions 去判斷。

?

SQL> Select index_name,status From? user_indexes ?Where table_name='CUSTADDR';

?

INDEX_NAME?STATUS

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

IX_CUSTADDR_ID?N/A

?

?

3.5. Truncate 分區(qū)

SQL> select * from custaddr partition(T_LIST556);

ID?AREA

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

1?556

SQL> alter table custaddr truncate partition(T_LIST556);

表被截斷。

SQL> select * from custaddr partition(T_LIST556);

未選定行

?

說明：

Truncate 相對 delete 操作很快，數(shù)據(jù)倉庫中的大量數(shù)據(jù)的批量數(shù)據(jù)加載可能會有用到； 截斷分區(qū)同樣會自動維護局部分區(qū)索引，同時會使全局索引 unusable, 需要重建

?

3.6.?Drop 分區(qū)

SQL> alter table custaddr drop partition T_LIST551;

表已更改。

?

SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

TABLE_NAME?PARTITION_NAME

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

CUSTADDR?T_LIST556

CUSTADDR?P_OTHER

同樣會自動維護局部分區(qū)索引，同時會使全局索引 unusable, 需要重建

?

?

四 .? 分區(qū)表的索引

分區(qū)索引分為本地 (local index) 索引和全局索引 (global index) 。 局部索引比全局索引容易管理 ,? 而全局索引比較快。

?

與索引有關的表：

dba_part_indexes? 分區(qū)索引的概要統(tǒng)計信息，可以得知每個表上有哪些分區(qū)索引，分區(qū)索引的類型 (local/global)

dba_ind_partitions? 每個分區(qū)索引的分區(qū)級統(tǒng)計信息

dba_indexes/dba_part_indexes? 可以得到每個表上有哪些非分區(qū)索引

?

Local 索引肯定是分區(qū)索引， Global 索引可以選擇是否分區(qū)，如果分區(qū)，只能是有前綴的分區(qū)索引。

?

分區(qū)索引 分 2 類： 有前綴 (prefix) 的分區(qū)索引 和 無前綴 (nonprefix) 的分區(qū)索引 :

（ 1 ）有前綴的分區(qū)索引指包含了 分區(qū)鍵 ，并且將其作為引導列的索引。

如：

create index i_id_global on? PDBA(id) ?global? -- 引導列

2?partition by? range(id)?-- 分區(qū)鍵

3?(partition p1 values less than (200),

4?partition p2 values less than (maxvalue)

5?);

這里的 ID? 就是分區(qū)鍵，并且分區(qū)鍵 id? 也是索引的引導列。

?

（ 2 ）無前綴的分區(qū)索引的 列 不是以分區(qū)鍵開頭， 或者不包含分區(qū)鍵列 。

如：

create index ix_custaddr_local_id_p on custaddr(id)

local (

partition t_list556 tablespace icd_service,

partition p_other tablespace icd_service

)

?

這個分區(qū)是按照 areacode 來的。但是索引的引導列是 ID 。?所以它就是非前綴分區(qū)索引。

?

全局分區(qū)索引不支持非前綴的分區(qū)索引，如果創(chuàng)建，報錯如下：

SQL> create index i_time_global on? PDBA(id) ?global? -- 索引引導列

2?partition by range (time) -- 分區(qū)建

3?(partition p1 values less than (TO_DATE('2010-12-1', 'YYYY-MM-DD')),

4?partition p2 values less than (maxvalue)

5?);

partition by range(time)

*

第 ?2? 行出現(xiàn)錯誤 :

ORA-14038: GLOBAL? 分區(qū)索引必須加上前綴

?

?

4.1?Local? 本地索引

對于 local 索引，當表的分區(qū)發(fā)生變化時，索引的維護由 Oracle 自動進行。

?

注意事項：

（ 1 ）? 局部索引一定是分區(qū)索引 ， 分區(qū)鍵等同于表的分區(qū)鍵 。

（ 2 ） ? 前綴和非前綴索引都可以支持索引分區(qū)消除，前提是查詢的條件中包含索引分區(qū)鍵。

（ 3 ）?局部索引只支持分區(qū)內(nèi)的唯一性，無法支持表上的唯一性，因此如果要用局部索引去給表做唯一性約束，則約束中必須要包括分區(qū)鍵列。

（ 4 ）?局部分區(qū)索引是對單個分區(qū)的，每個分區(qū)索引只指向一個表分區(qū)；全局索引則不然，一個分區(qū)索引能指向 n 個表分區(qū)，同時，一個表分區(qū)，也可能指向 n 個索引分區(qū)，對分區(qū)表中的某個分區(qū)做 truncate 或者 move ， shrink 等，可能會影響到 n 個全局索引分區(qū)，正因為這點，局部分區(qū)索引具有更高的可用性。

（ 5 ）?位圖索引必須是局部分區(qū)索引。

（ 6 ）?局部索引多應用于數(shù)據(jù)倉庫環(huán)境中。

（ 7 ） ?B 樹索引和位圖索引都可以分區(qū)，但是 HASH 索引不可以被分區(qū)。

?

?

示例：

sql> create index ix_custaddr_local_id on custaddr(id) local;

索引已創(chuàng)建。

?

和下面 SQL? 效果相同，因為 local 索引就是分區(qū)索引：

create index ix_custaddr_local_id_p on custaddr(id)

local (

partition t_list556 tablespace icd_service,

partition p_other tablespace icd_service

)

?

SQL> create index ix_custaddr_local_areacode on custaddr(areacode) local;

索引已創(chuàng)建。

?

驗證 2 個索引的類型：

SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name='CUSTADDR';

?

index_name?table_name partition locali alignment

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

ix_custaddr_local_areacode?custaddr?list?local?prefixed

ix_custaddr_local_id?custaddr?list?local?non_prefixed

?

因為我們的 custaddr 表是按 areacode 進行分區(qū)的，所以索引 ix_custaddr_local_areacode 是有前綴的索引（ prefixed ）。而 ix_custaddr_local_id 是非前綴索引。

?

4.2?Global 索引

對于 global 索引，可以選擇是否分區(qū)，而且索引的分區(qū)可以不與表分區(qū)相對應。 全局分區(qū)索引只能是 B 樹索引 ，到目前為止 (10gR2) ， oracle 只支持有前綴的全局索引。

另外 oracle 不會自動的維護全局分區(qū)索引，當我們在對表的分區(qū)做修改之后，如果對分區(qū)進行維護操作時不加上 update global indexes 的話，通常會導致全局索引的 INVALDED ，必須在執(zhí)行完操作后 ?REBUILD 。

?

注意事項：

（ 1 ）全局索引可以分區(qū)，也可以是不分區(qū)索引， 全局索引必須是前綴索引 ，即全局索引的索引列必須是以索引分區(qū)鍵作為其前幾列。

（ 2 ）全局索引可以依附于分區(qū)表；也可以依附于非分區(qū)表。

（ 3 ）全局分區(qū)索引的索引條目可能指向若干個分區(qū)，因此，對于全局分區(qū)索引，即使只截斷一個分區(qū)中的數(shù)據(jù)，都需要 rebulid 若干個分區(qū)甚至是整個索引。

（ 4 ）全局索引多應用于 oltp 系統(tǒng)中。

（ 5 ）全局分區(qū)索引只按范圍或者散列分區(qū)， hash 分區(qū)是 10g 以后才支持。

（ 6 ）? oracle9i 以后對分區(qū)表做 move 或者 truncate 的時可以用 update global indexes 語句來同步更新全局分區(qū)索引，用消耗一定資源來換取高度的可用性。

（ 7 ）?表用 a 列作分區(qū)，索引用 b 做局部分區(qū)索引，若 where 條件中用 b 來查詢，那么 oracle 會掃描所有的表和索引的分區(qū)，成本會比分區(qū)更高，此時可以考慮用 b 做全局分區(qū)索引。

?

?

注意： Oracle 只支持 2 中類型的全局分區(qū)索引：

range partitioned? 和 ?Hash Partitioned.

?

官網(wǎng)的說明如下：

Global Partitioned Indexes

Oracle offers two types of global partitioned index: range partitioned and hash partitioned.

（ 1 ） Global Range Partitioned Indexes

Global range partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table's partitioning method. They are commonly used for OLTP environments and offer efficient access to any individual record.

The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE. This ensures that all rows in the underlying table can be represented in the index. Global prefixed indexes can be unique or nonunique.

You cannot add a partition to a global index because the highest partition always has a partition bound of MAXVALUE.? If you wish to add a new highest partition, use the ALTER INDEX SPLIT PARTITION statement. If a global index partition is empty, you can explicitly drop it by issuing the ALTER INDEX DROP PARTITION statement . If a global index partition contains data, dropping the partition causes the next highest partition to be marked unusable. You cannot drop the highest partition in a global index.

（ 2 ） Global Hash Partitioned Indexes

Global hash partitioned indexes improve performance by spreading out contention when the index is monotonically growing. In other words, most of the index insertions occur only on the right edge of an index.

?

（ 3 ） Maintenance of Global Partitioned Indexes

By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:

ADD (HASH)

COALESCE (HASH)

DROP

EXCHANGE

MERGE

MOVE

SPLIT

TRUNCATE

?

示例 1? 全局索引，全局索引對所有分區(qū)類型都支持：

sql> create index ix_custaddr_ global_id on custaddr(id) global;

索引已創(chuàng)建。

?

示例 2 ：全局分區(qū)索引，只支持 Range? 分區(qū)和 Hash? 分區(qū)：

?

（ 1 ）創(chuàng)建 2 個測試分區(qū)表：

sql> create table pdba (id number, time date)? partition by range (time)

2?(

3?partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),

4?partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),

5?partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),

6?partition p4 values less than (maxvalue)

7?);

表已創(chuàng)建。

?

SQL> create table Thash

2?(

3?id number primary key,

4?item_id number(8) not null

5?)

6? partition by hash(id)

7?(

8?partition part_01,

9?partition part_02,

10?partition part_03

11?);

?

表已創(chuàng)建。

?

（ 2 ）創(chuàng)建分區(qū)索引

?

示例 2 ：全局分區(qū)索引

?

SQL> create index i_id_global on? PDBA(id) ?global

2?partition by? range(id)

3?(partition p1 values less than (200),

4?partition p2 values less than (maxvalue)

5?);

索引已創(chuàng)建。

-- 這個是有前綴的分區(qū)索引。

?

SQL> create index i_time_global on? PDBA(id) ?global

2?partition by range (time)

3?(partition p1 values less than (TO_DATE('2010-12-1', 'YYYY-MM-DD')),

4?partition p2 values less than (maxvalue)

5?);

partition by range(time)

*

第 ?2? 行出現(xiàn)錯誤 :

ORA-14038: GLOBAL? 分區(qū)索引必須加上前綴

?

?

SQL> create index i_time_global on? PDBA(time)? global

2?partition by? range(time)

3?(partition p1 values less than (TO_DATE('2010-12-1', 'YYYY-MM-DD')),

4?partition p2 values less than (maxvalue)

5?);

索引已創(chuàng)建。

-- 有前綴的分區(qū)索引

?

SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name='PDBA';

index_name?table_name partition locali alignment

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

i_id_global?pdba?range?global? prefixed

i_time_global?pdba?range?global? prefixed

?

SQL> CREATE INDEX ix_hash ON? PDBA (id,time) ?GLOBAL

2?PARTITION BY? HASH (id)

3?(PARTITION p1,

4?PARTITION p2,

5?PARTITION p3,

6?PARTITION p4);

索引已創(chuàng)建。

?

只要索引的引導列包含分區(qū)鍵，就是有前綴的分區(qū)索引。

?

?

4.3? 索引重建問題

?

（ 1 ）分區(qū)索引

對于分區(qū)索引，不能整體進行重建，只能對單個分區(qū)進行重建。語法如下：

Alter index idx_name rebuild partition index_partition_name [online nologging]

說明：

online: 表示重建的時候不會鎖表。

nologging ：表示建立索引的時候不生成日志，加快速度。

?

如果要重建分區(qū)索引，只能 drop 表原索引，在重新創(chuàng)建：

SQL>create index loc_xxxx_col on xxxx(col) local tablespace SYSTEM;

這個操作要求較大的臨時表空間和排序區(qū)。

?

示例：

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

INDEX_NAME?PARTITION_NAME

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

I_TIME_GLOBAL?P1

I_TIME_GLOBAL?P2

?

SQL>?alter index I_TIME_GLOBAL rebuild partition p1 online nologging;

索引已更改。

SQL> alter index I_TIME_GLOBAL rebuild partition p2 online nologging;

索引已更改。

?

（ 2 ）全局索引

Oracle? 會自動維護分區(qū)索引，對于全局索引，如果在對分區(qū)表操作時，沒有指定 update?index ，則會導致全局索引失效，需要重建。

?

SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME='IX_PDBA_GLOBAL';

?

owner?index_name?table_name status

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

sys?ix_pdba_global?pdba?valid

?

刪除一個分區(qū)：

SQL> alter table pdba drop partition p2;

表已更改。

?

SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME='IX_PDBA_GLOBAL';

owner?index_name?table_name status

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

sys?ix_pdba_global?pdba? valid

?

split? 分區(qū)：

SQL> alter table pdba split partition P4 at(TO_DATE('2010-12-21 00:00:00','YYYY-MM-DD HH24:MI:SS')) into (partition P4, partition P5);

表已更改。

?

SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME='IX_PDBA_GLOBAL';

owner?index_name?table_name status

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

sys?ix_pdba_global?pdba? valid

?

drop? 分區(qū)時使用 update indexes

SQL> alter table pdba drop partition P4 UPDATE INDEXES;

表已更改。

SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME='IX_PDBA_GLOBAL';

owner?index_name?table_name status

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

sys?ix_pdba_global?pdba?valid

?

?

做了幾個 drop 分區(qū)操作，全局索引沒有失效，有點奇怪。?不過如果在生產(chǎn)環(huán)境中，還是小心點。

?

重建全局索引命令如下：

Alter index idx_name rebuild [online nologging]

示例：

SQL> Alter index ix_pdba_global rebuild online nologging;

索引已更改。

?

?

?

補充一點，分區(qū)表存儲空間的問題：

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='DBA';

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
---------- ------------------------------ ------------------------------
DBA P1 SYSTEM
DBA P2 SYSTEM
DBA P3 SYSTEM
DBA P4 SYSTEM

通過 user_tab_partitions? 表可以查看到每個分區(qū)對應的 tablesapce_name.? 但是，如果通過 all_tables? 表，卻查不到分區(qū)表對應表空間的信息。

分區(qū)表：
SQL> select owner,table_name,tablespace_name,cluster_name from all_tables where table_name='DBA';

OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAME
----- ---------- ------------------------------ -----------------------------------------------------
SYS DBA

普通表：
SQL> select owner,table_name,tablespace_name,cluster_name from all_tables where table_name='DAVE';

OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAME
----- ---------- ------------------------------ ---------------------------------------------------
SYS DAVE SYSTEM

?

oracle_表分區(qū)