Oracle 分区表（一）
一. 分區表理論知識
?????? Oracle提供了分區技術以支持VLDB(Very Large DataBase)。分區表通過對分區列的判斷，把分區列不同的記錄，放到不同的分區中。分區完全對應用透明。
?????? Oracle的分區表可以包括多個分區，每個分區都是一個獨立的段（SEGMENT），可以存放到不同的表空間中。查詢時可以通過查詢表來訪問各個分區中的數據，也可以通過在查詢時直接指定分區的方法來進行查詢。
?
When to Partition a Table什么時候需要分區表，官網的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個分區：
????? Tables can be partitioned into up to 1024K-1 separate partitions
?
聯機文檔上有關分區表和索引的說明：
????? Partitioned Tables and Indexes
???????http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/partconc.htm#sthref2604
?
分區提供以下優點：
?????? （1）由于將數據分散到各個分區中，減少了數據損壞的可能性；
?????? （2）可以對單獨的分區進行備份和恢復；
?????? （3）可以將分區映射到不同的物理磁盤上，來分散IO；
?????? （4）提高可管理性、可用性和性能。
?
Oracle 10g提供了以下幾種分區類型：
?????? （1）范圍分區（range）；
?????? （2）哈希分區（hash）；
?????? （3）列表分區（list）；
?????? （4）范圍－哈希復合分區（range-hash）；
?????? （5）范圍－列表復合分區（range-list）。
?
Range分區：
Range分區是應用范圍比較廣的表分區方式，它是以列的值的范圍來做為分區的劃分條件，將記錄存放到列值所在的range分區中。
?????? 如按照時間劃分，2010年1月的數據放到a分區，2月的數據放到b分區，在創建的時候，需要指定基于的列，以及分區的范圍值。
?????? 在按時間分區時，如果某些記錄暫無法預測范圍，可以創建maxvalue分區，所有不在指定范圍內的記錄都會被存儲到maxvalue所在分區中。
?
如：
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分區：
對于那些無法有效劃分范圍的表，可以使用hash分區，這樣對于提高性能還是會有一定的幫助。hash分區會將表中的數據平均分配到你指定的幾個分區中，列所在分區是依據分區列的hash值自動分配，因此你并不能控制也不知道哪條記錄會被放到哪個分區中，hash分區也可以支持多個依賴列。
?
如：
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
);
在這里，我們指定了每個分區的表空間。
?
List分區：
List分區也需要指定列的值，其分區值必須明確指定，該分區列只能有一個，不能像range或者hash分區那樣同時指定多個列做為分區依賴列，但它的單個分區對應值可以是多個。
在分區時必須確定分區列可能存在的值，一旦插入的列值不在分區范圍內，則插入/更新就會失敗，因此通常建議使用list分區時，要創建一個default分區存儲那些不在指定范圍內的記錄，類似range分區中的maxvalue分區。
?
在根據某字段，如城市代碼分區時，可以指定default，把非分區規則的數據，全部放到這個default分區。
?
如：
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)
)
?
組合分區：
?????? 如果某表按照某列分區之后，仍然較大，或者是一些其它的需求，還可以通過分區內再建子分區的方式將分區再分區，即組合分區的方式。
組合分區呢在10g中有兩種：range-hash，range-list。注意順序，根分區只能是range分區，子分區可以是hash分區或list分區。
?
如：
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中，組合分區功能這塊有所增強，又增加了range-range,list-range,
list-list,list-hash，并且 11g里面還支持Interval分區和虛擬列分區。
?
這塊可以參考Blog：
???? Oracle 11g 新特性簡介
???????http://blog.csdn.net/tianlesoftware/archive/2010/01/06/5134819.aspx
?
?????? 分區表 之 Interval分區 和 虛擬列 按星期分區表
???????http://blog.csdn.net/tianlesoftware/archive/2010/06/10/5662337.aspx
?
?
二.? 普通表轉分區表方法
?
將普通表轉換成分區表有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 來實現。 當然在創建分區表的時候可以一起插入數據，也可以創建好后在insert 進去。 這種方法采用DDL語句，不產生UNDO，只產生少量REDO，建表完成后數據已經在分布到各個分區中。
????
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創建，這種方法對沒有寫全的分區會自動創建。 比如我這里只寫了1月日期，如果插入的數據有其他月份的，會自動生成對應的分區。
?
/* 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里面，我需要寫全所有的分區。
?
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;
表已創建。
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>
?
現在分區表已經建好了，但是表名不一樣，需要用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 . 交換分區：Partition exchange method
???? 這種方法只是對數據字典中分區和表的定義進行了修改，沒有數據的修改或復制，效率最高。適用于包含大數據量的表轉到分區表中的一個分區的操作。盡量在閑時進行操作。
?
交換分區的操作步驟如下：
???? 1. 創建分區表，假設有2個分區，P1，P2.
???? 2. 創建表A存放P1規則的數據。
???? 3. 創建表B 存放P2規則的數據。
???? 4. 用表A 和P1 分區交換。 把表A的數據放到到P1分區
???? 5. 用表B 和p2 分區交換。 把表B的數據存放到P2分區。
?
創建分區表：
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? );
表已創建。
?
注意：我這里只創建了2個分區，沒有創建存放其他數據的分區。
?
創建2個分別對應分區的基表：
SQL> CREATE TABLE dba_p1 as SELECT id,time_fee FROM dba_old WHERE? time_fee<TO_DATE('2010-09-1', 'YYYY-MM-DD');
表已創建。
?
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');
表已創建。
?
SQL> select count(*) from dba_p1;
? COUNT(*)
----------
?? 1536020
SQL> select count(*) from dba_p2;
? COUNT(*)
----------
??? 365932
?
SQL>
?
講2個基表與2個分區進行交換：
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個分區：
SQL> select count(*) from p_dba partition(p1);
? COUNT(*)
----------
?? 1536020
SQL> select count(*) from p_dba partition(p2);
? COUNT(*)
----------
??? 365932
注意：數據和之前的基表一致。
?
查詢原來的2個基表：
SQL> select count(*) from dba_p2;
? COUNT(*)
----------
???????? 0
SQL> select count(*) from dba_p1;
? COUNT(*)
----------
???????? 0
注意： 2個基表的數據變成成0。
?
?????? 在這里我們看一個問題，一般情況下，我們在創建分區表的時候，都會有一個其他分區，用來存放不匹配分區規則的數據。 在這個例子中，我只創建了2個分區，沒有創建maxvalue分區。 現在我來插入一條不滿足規則的數據，看結果：
?
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 行出現錯誤:
ORA-14400: 插入的分區關鍵字未映射到任何分區
SQL> insert into p_dba values(999999,to_date('2009-12-29','yyyy-mm-dd'));
已創建 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>
?
?????? 通過這個測試可以清楚，如果插入的數據不滿足分區規則，會報ORA-14400錯誤。
?
2.3 . 使用在線重定義：DBMS_REDEFINITION
?
?????? 在線重定義能保證數據的一致性，在大部分時間內，表都可以正常進行DML操作。只在切換的瞬間鎖表，具有很高的可用性。這種方法具有很強的靈活性，對各種不同的需要都能滿足。而且，可以在切換前進行相應的授權并建立各種約束，可以做到切換完成后不再需要任何額外的管理操作。
?
關于DBMS_REDEFINITION的介紹，參考官方連接：
??????http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_redefi.htm#CBBFDJBC
?
關于用在線重定義創建分區表，參考：
?????? 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）修改表的存儲參數；
?????? （2）將表轉移到其他表空間；
?????? （3）增加并行查詢選項；
?????? （4）增加或刪除分區；
?????? （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）創建基礎表A，如果存在，就不需要操作。
?????? （2）創建臨時的分區表B。
?????? （3）開始重定義，將基表A的數據導入臨時分區表B。
?????? （4）結束重定義，此時在DB的 Name Directory里，已經將2個表進行了交換。即此時基表A成了分區表，我們創建的臨時分區表B 成了普通表。 此時我們可以刪除我們創建的臨時表B。它已經是普通表。
?
?
下面看一個示例：
?
1. 創建基本表和索引
sql> conn icd/icd;
已連接。
sql> create table unpar_table (
? 2? id number(10) primary key,
? 3? create_date date
? 4? );
表已創建。
sql> insert into unpar_table select rownum, created from dba_objects;
已創建72288行。
sql> create index create_date_ind on unpar_table(create_date);
索引已創建。
sql> commit;
提交完成。
?
2. 收集表的統計信息
sql> exec dbms_stats.gather_table_stats('icd', 'unpar_table', cascade => true);
pl/sql 過程已成功完成。
?
3. 創建臨時分區表
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));
表已創建。
?
4. 進行重定義操作
?
4.1 檢查重定義的合理性
sql> exec dbms_redefinition.can_redef_table('icd', 'unpar_table');
pl/sql 過程已成功完成。
?
4.2 如果4.1 沒有問題，開始重定義，這個過程可能要等一會。
?
這里要注意：如果分區表和原表列名相同，可以用如下方式進行：
SQL> BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => 'ICD',?
orig_table => 'unpar_table',
int_table => 'par_table');
END;
/
?????? 如果分區表的列名和原表不一致，那么在開始重定義的時候，需要重新指定映射關系：
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(
'ICD',
'unpar_table',
'par_table',
'ID ID, create_date TIME', -- 在這里指定新的映射關系
DBMS_REDEFINITION.CONS_USE_PK);
?
這一步操作結束后，數據就已經同步到這個臨時的分區表里來了。
?
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 創建索引，在線重定義只重定義數據，索引還需要單獨建立。
sql> create index create_date_ind2 on par_table(time);
索引已創建。
?
4.5 收集新表的統計信息
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 和臨時分區表par_table 進行了交換。 此時臨時分區表par_table成了普通表，我們的基表unpar_table成了分區表。
?
?????? 我們在重定義的時候，基表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>
與50位技術專家面對面20年技術見證，附贈技術全景圖總結
以上是生活随笔為你收集整理的Oracle 分区表（一）的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： ORACLE sqlplus set 命
- 下一篇： Oracle分区表 (二)