--==================
-- ? Oracle 分區表
--==================
?
一、分區表：
??? 隨著表的不斷增大，對于新紀錄的增加、查找、刪除等 ( DML ) 的維護也更加困難。對于數據庫中的超大型表，可通過把它的數據分成若干個 小表，從而簡化數據庫的管理活動。對于每一個簡化后的小表，我們稱為一個單個的分區。
?
??? 對于分區的訪問，我們不需要使用特殊的 SQL 查詢語句或特定的 DML 語句，而且可以單獨的操作單個分區，而不是整個表。同時可以將不同分區的數據 放置到不同的表空間，比如將不同年份的銷售數據，存放在不同的表空間，即年的銷售數據存放到 TBS_2001 , 2002 年的銷售數據存放到 TBS_2002 , 依次類推，從而實現了分散存儲，這將大大的簡化大容量表的管理，提高查詢性能及 I / O 并發等。
???
??? 對于外部應用程序來說，雖然存在不同的分區，且數據位于不同的表空間，但邏輯上仍然是一張表
??? ?
??? 可以使用 SQL * Loader ， IMPDP ， EXPDP ， Import ， Export 等工具來裝載或卸載分區表中的數據
???
??? 關于分區表的功能實際上同 SQL server 中的分區表是同樣的概念，只不過 SQL server 中的數據存放到了文件組，相當于 Oracle 概念中的表空間，
??? 有興趣的可以參考：
??? ??? SQL server 2005 基于已存在的表創建分區
??????? SQL server 2005 切換分區表
?
二、何時分區
??? 當表達到 GB 大小且繼續增長
??? 需要將歷史數據和當前的數據分開單獨處理，比如歷史數據僅僅需要只讀，而當前數據則實現 DML
???
三、分區的條件及特性
??? 共性：不同的分區之間必須有相同的邏輯屬性，比如表名，列名，數據類型，約束等，
???
??? 個性：各個分區可以有不同的物理屬性，比如 pctfree , pctused , and tablespaces .
?
分區獨立性：即使某些分區不可用，其他分區仍然可用。
?
特殊性：含有 LONG 、 LONGRAW 數據類型的表不能進行分區
?
四、分區的優點
??? 1 、提高查詢性能：只需要搜索特定分區，而非整張表，提高查詢速度
??? 2 、節約維護時間：單個分區的數據裝載，索引重建，備份，維護等將遠小于整張表的維護時間。
??? 3 、節約維護成本：可以單獨備份和恢復每個分區
??? 4 、均衡 I / O ：將不同的分區映射到不同的磁盤以平衡 I / O ，提高并發
?
五、 ORACLE 分區類型：
??? 范圍分區、散列分區、列表分區、組合分區
??? 可以對索引和表分區，全局索引只能按范圍分區，可以將其定義在任何類型的分區或非分區表上。
??? 通常全局索引比局部索引需要更多的維護
??? 局部索引與基礎表是等同分區的，用于反映其基礎表的結構
???
??? 1. Range 分區：行映射到基于列值范圍的分區
??????? Range 分區，又成為范圍分區，基于分區鍵值的范圍將數據映射到所建立的分區上。這是最通用的分區類型。
??????? 創建范圍分區時，必須指定以下內容
??????????? 分區方法： range
??????????? 分區列
??????????? 標識分區邊界的分區描述
???????????
??????? 使用 Range 分區的時候，要記住幾條規則：
??????????? 每個分區都包含 VALUES LESS THAN 字名，定義了分區的上層邊界。任何等于和大于分區鍵值的二進制值都被添加到下一個高層分區中。
??????????? 所有的分區，除了第一個，如果低于 VALUES LESS THAN 所定義的下層邊界，都放在前面的分區中。
??????????? MAXVALUE 可以用來定義最高層的分區。 MAXVALUE 表示了虛擬的無限值
?
??????? 示例：
??????????? create table sal_range ???
??????????? ( salesman_id number ( 5 ),
??????????? salesman_name varchar2 ( 30 ),
??????????? sales_amount number ( 10 ),
??????????? sales_date date )
??????????? partition by range ( sales_date ) ? ? -- 創建基于日期的范圍分區并存儲到不同的表空間
??????????? (
??????????? partition sal_jan2000 values less than ( to_date ( '02/01/2000' ,
??????????????? 'DD/MM/YYYY' )) tablespace sal_range_jan2000 ,
??????????? partition sal_feb2000 values less than ( to_date ( '03/01/2000' ,
??????????????? 'DD/MM/YYYY' )) tablespace sal_range_feb2000 ,
??????????? partition sal_mar2000 values less than ( to_date ( '04/01/2000' ,
??????????????? 'DD/MM/YYYY' )) tablespace sal_range_mar2000 ,
??????????? partition sal_apr2000 values less than ( to_date ( '05/01/2000' ,
??????????????? 'DD/MM/YYYY' )) tablespace sal_range_apr2000
??????????? );
?
??????????? create table r ????? -- 創建基于值范圍的分區，分區子句未指定表空間時則位于缺省的表空間
??????????? ( a int )
? ?????????? partition by range ( a )
??????????? (
??????????????? partition p1 values less than ( 10 ),
??????????????? partition p2 values less than ( 20 ),
??????????????? partition p3 values less than ( 30 ),
??????????????? partition p4 values less than ( maxvalue )
??????????? );
?
??????????? select * from r partition ( p1 ) ??? -- 查看分區中的數據
???????
??????????? 一個分區的損壞不會影響其它分區的數據：
??????????? alter table r drop partiton p1
??????????? select * from r
??????????? select * from r partition ( p4 )
??????????? 除分區數據不見外，其它都正常
?
??????? partition by 用于指定分區方式
??????? range 表示分區的方式是范圍劃分
??????? partition pn 用于指定分區的名字
??????? values less than 指定分區的上界（上限）
?
??????? 添加分區：
??????????? ALTER TABLE r
??????????? add partition p5 values less than ( xxx ) tablespace xx ;
?
??????? 查看分區表相關信息：
??????????? SELECT table_name , partition_name , subpartition_count ,
??????????? tablespace_name , user_stats from user_tab_partitions ;
?
??????? 獲取創建分區表的元數據：
??????? ????? set long 10000
??????? ????? select dbms_metadata . get_ddl ( 'TABLE' , 'R' , 'SCOTT' ) from dual ;
??????????????????????????????????????? 表 ? 表名 ? 用戶名 ?? 區分大小寫
?
??? 2.Hash 分區：散列分區
??????? Hash 分區能夠很容易對數據進行分區，因為語法很簡單，很容易實現。在下面這種
??????? 情況下，使用 hash 分區比 range 分區更好：
??????????? 事先不知道需要將多少數據映射到給定范圍的時候
??????????? 分區的范圍大小很難確定，或者很難平衡的時候
??????????? Range 分區使數據得到不希望的聚集時
??????????? 性能特性，如并行 DML 、分區剪枝和分區連接很重要的時候
??????? 創建散列分區時，必須指定以下信息
??????????? 分區方法： hash
??????????? 分區列
??????????? 分區數量或單獨的分區描述
?
??????? 分裂、刪除和合并分區不能應用于 Hash 分區，但是， Hash 分區能夠合并和添加。
?
??????? 創建 hash 分區有兩種方法：一種方法是指定分區數量，另一種方法是指定分區的名字，
??????? 但兩者不能同時指定。
?
??????? 方法一：指定分區數量
??????? create table dept2 ( deptno number , deptname varchar2 ( 32 ))
??????? partition by hash ( deptno ) partitions 4 ;
?
??????? 方法二：指定分區的名字
??????? create table dept3 ( deptno number , deptname varchar2 ( 32 ))
??????? partition by hash ( deptno ) ?????????????
??????? ( partition p1 tablespace p1 ,
??????? partition p2 tablespace p2 );
?
??????? create table sales_hash
??????? ( salesman_id number ( 5 ),
??????? salesman_name varchar2 ( 30 ),
??????? sales_amount number ( 10 ),
??????? week_no number ( 2 ))
??????? partition by hash ( salesman_id )
??????? partitions 4
??????? store in ( data1 , data2 , data3 , data4 )
???????
??????? data1 , data2 , data3 , data4 為表空間名。
??????? 散列分區表的每個分區都被存儲在單獨的段中。
???????
??? 3.List 分區：列表分區
??????? List 分區可以控制如何將行映射到分區中去。可以在每個分區的鍵上定義離散的值
??????? 不同于 Range 分區和 Hash 分區，
??????????? Range 分區與分區相關聯，為分區列假設了一個值的自然范圍，故不可能將該值的范圍以外的分區組織到一起。
??????????? hash 分區時不允許對數據的劃分進行控制，因為系統使用的是散列函數來劃分數據的。
??????? List 分區的優點在于按照自然的方式將無序和不相關的數據集合分組。
??????? List 分區不支持多列分區，如果將表按列分區，那么分區鍵就只能有表的一個單獨列組成。
??????? Range 分區和 Hash 分區可以對多列進行分區。
??????? List 分區時必須指定的以下內容
??????????? 分區方法： list
??????????? 分區列
??????????? 分區描述，每個描述指定一串文字值 ( 值的列表 ), 它們是分區列 ( 它們限定將被包括在分區中的行 ) 的離散值
???????
??????? 示例：
??????? create table sales_list
??????? ( salesman_id number ( 5 ),
??????? salesman_name varchar2 ( 30 ),
??????? sales_state varchar2 ( 20 ),
??????? sales_amount number ( 10 ),
??????? sales_date date )
??????? partition by list ( sales_state )
??????? (
??????? partition sales_west values ( 'California' , 'Hawaii' ) tablespace x ,
??????? partition sales_east values ( 'New York' , 'Virginia' ) tablespace y ,
??????? partition sales_central values ( 'Texas' , 'Illinois' ) tablespace z ,
??????? partition sales_other values ( DEFAULT ) tablespace o
??????? );
?
??????? 添加分區：
??????? ?? alter table sales3 add partition hk values ( 'HK' ) tablespace xx
?
??? 4.Composite Partitioning ：合成分區、組合分區
??????? 組合分區使用 range 方法分區，在每個子分區中使用 hash 方法進行再分區。
??????? 組合分區比 range 分區更容易管理，充分使用了 hash 分區的并行優勢。組合分區支持歷史數據和條塊數據兩者。
??????? 如添加新的 RANGE 分區，同時為 DML 操作提供更高層的并行性。
??????? 創建組合分區時，需要指定如下內容：
??????????? 分區方法： range
??????????? 分區列
??????????? 標識分區邊界的分區描述
??????????? 子分區方法： hash
??????????? 子分區列
??????????? 每個分區的子分區數量，或子分區的描述
???????
??????? create table sales_composite
??????? ( salesman_id number ( 5 ),
??????? salesman_name varchar2 ( 30 ),
??????? sales_amount number ( 10 ),
??????? sales_date date )
??????? partition by range ( sales_date )
??????? subpartition by hash ( salesman_id )
??????? subpartitions 4
??????? store in ( tbs1 , tbs2 , tbs3 , tbs4 )
??????? ( partition sales_jan2000 values less than ( to_date ( '02/01/2000' , 'DD/MM/YYYY' )),
??????? partition sales_feb2000 values less than ( to_date ( '03/01/2000' , 'DD/MM/YYYY' )),
??????? partition sales_mar2000 values less than ( to_date ( '04/01/2000' , 'DD/MM/YYYY' ))
??????? );
?
??????? create table T_TRACK ?
??????? (
??????????? N_TRACK_ID ?????????? NUMBER ( 20 ) ???? NOT NULL, ?
??????????? C_COMP_CDE ?????????? VARCHAR2 ( 6 ),
??????????? T_TRACK_TM ?????????? DATE ?????????? NOT NULL,
??????????? C_CAR_NO ???????????? VARCHAR2 ( 50 )
??????? )
??????? partition by range ( T_TRACK_TM )
??????? subpartition by list ( C_COMP_CDE )
??????? (
??????????? partition P_2009_11 values less than ( to_date ( '2009-12-01' , 'yyyy-MM-dd' ))
??????????????????? (
??????????????????? subpartition P_2009_11_P1013 values ( 'P1013' )
??????????????????? )
??????? );
?
六、表分區后的相關操作
??? 1. 添加分區
??????? alter table T_TRACK add partition P_2005_04
??????? values less than ( to_date ( '2005-05-01' , 'yyyy-MM-dd' ))
??????? (
??????? ??? subpartition P_2005_04_P1013 values ( 'P1013' ),
??????? ??? subpartition P_2005_04_P1013 values ( 'P1014' ),
??????? ??? subpartition P_2005_04_P1013 values ( 'P1015' ),
??????? ??? subpartition P_2005_04_P1013 values ( 'P1016' )
??????? )
?
??? 2. 刪除分區
??????? alter table T_TRACK drop partition p_2005_04 ;
?
??? 3. 添加子分區
??????? alter table T_TRACK
??????? modify partition P_2005_01
??????? add subpartition P_2005_01_P1017 values ( 'P1017' );
?
??? 4. 刪除子分區
??????? alter table T_TRACK drop subpartition p_2005_01_p1017 ;
?
??? 5. 截斷一個分區表中的一個分區的數據：
??????? alter table sales3 ? truncate partition sp1
??????????? 這種方式會使全局分區索引無效
??????? alter table sales3 truncate partition sp1 update indexes
??????????? 這種方式全局分區索引不會無效
?
??? 6. 截斷分區表的子分區
??????? alter table comp truncate subpartition sub1
?
??? 7. 截斷帶有約束的分區表
??????? a 、禁用約束
??????? ? alter table sales disable constraint dname_sales1
??????? b 、截斷分區
??????? ? alter table sales truncate partitoin dec
??????? c 、啟用約束
??????? ? alter table sales enable constraint dname_sales1
?
??? 8. 查看一個表是不是分區表
??????? select table_name , partitioned from user_tables ;
??????? TABLE_NAME ???????????????????? PAR
??????? ------------------------------ ---
??????? DEPT ?????????????????????????? NO
??????? DEPT3 ?????? ??????????????????? YES
?
??? 9. 將一個表的分區從一個表空間移動到另一個表空間
??????? a 、查看分區在哪個表空間
??????? ? SELECT TABLE_OWNER , TABLE_NAME , PARTITION_NAME , TABLESPACE_NAME ,
??????? ? SUBPARTITION_COUNT
??????? ? FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER = 'SCOTT' ;
?
??????? b 、移動分區
??????? ? alter table sales move partiton sp1 tablespace tp ;
?
??????? c 、檢查是否移動成功
??????? ? SELECT TABLE_OWNER , TABLE_NAME , PARTITION_NAME , TABLESPACE_NAME ,
??????? ? SUBPARTITION_COUNT
??????? ? FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER = 'SCOTT' ;
?
??????? 移動表空間后，要重建索引，否則索引會變得無效
??????? alter index xxx rebuild
?
?
??? 10. 合并分區：
??????? alter table sales3 merge partitons sp1 , sp3 into partition sp3
??????? 合并后的分區名，不能是邊界值較低的那個
?
??? 11. 刪除分區：
??????? alter table scott . sales_composite drop partition SALES_JAN2000 ;
?
??? 與分區表相關的數據字典視圖：
??????? DBA_TAB_PARTITIONS
??????? DBA_IND_PARTITIONS
??????? DBA_TAB_SUBPARTITIONS
??????? DBA_IND_SUBPARTITIONS
?
???????
??????? Oracle 關于分區的在線文檔： http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/partconc.htm#sthref2604
?
七、更多參考
?
?
?
?
?
? Oracle 聯機重做日志文件(ONLINE LOG FILE)
?
?
?
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
