oracle 分表分區
一、 查詢表所佔存儲空間
每張表都是作為“段”來存儲的，可以通過user_segments視圖查看其相應信息。 段（segments）的定義：如果創建一個堆組織表，則該表就是一個段。
<code>SELECT segment_name AS TABLENAME,BYTES FROM user_segments WHERE segment_name='表名';/<code>
解釋： segment_name 就是要查詢的表名（大寫），BYTES 為表存儲所佔用的字節數。本sql的意思就是查詢出表名和表所佔的存儲空間大小。
二、 分表
如果歷史表中存儲了很多年的數據，會造成嚴重的數據冗餘。那如果將歷史表分表存儲，比如每年創建一個表，數據存儲到對應的年表中，必定會減少很多數據量。
三、 分區
1. 基礎
Oracle提供了分區技術以支持VLDB(Very Large DataBase)。分區表通過對分區列的判斷，把分區列不同的記錄，放到不同的分區中。分區完全對應用透明。
Oracle的分區表可以包括多個分區，每個分區都是一個獨立的段（SEGMENT），可以存放到不同的表空間中。查詢時可以通過查詢表來訪問各個分區中的數據，也可以通過在查詢時直接指定分區的方法來進行查詢。
When to Partition a Table什麼時候需要分區表，官網的2個建議如下：
- Tables greater than 2GB should always be considered for partitioning.
- 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
2. 分區優點
減少SQL操作的數據量，從而提升查詢效率。表分區後，邏輯上仍然是一張表，只不過將表中的數據在物理上存放到多個表空間上。這樣在查詢數據時，會查詢相應分區的數據，避免了全表掃描。
- 1) 增強可用性：如果表的某個分區出現故障，表在其他分區的數據仍然可用；
- 2) 維護方便：如果表的某個分區出現故障，需要修復數據，只修復該分區即可；
- 3) 均衡I/O：可以把不同的分區映射到磁盤以平衡I/O，改善整個系統性能；
- 4) 改善查詢性能：對分區對象的查詢可以僅搜索自己關心的分區，提高檢索速度
3. 分區類型
- 水平分區
就是對行進行分區，舉個例子來說，就是一個表中有1000萬條數據，每100萬條數據劃一個分區，這樣就將表中數據分到10個分區中去。水平分區要通過某個特定的屬性列進行分區，比如Date時間。 - 垂直分區
通過對錶垂直劃分來減少表的寬度，從而提升查詢效率。比如一個學生表中，有他相關的信息列，還有論文列以CLOB存儲。這些以CLOB存儲的論文並不會經常被訪問到，這時候就要把這些不經常使用的CLOB劃分到另一個分區，需要訪問時再調用它。
4. 分區方法
- 1） 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) ) - 2） Hash分區
對於那些無法有效劃分範圍的表，可以使用hash分區，這樣對於提高性能還是會有一定的幫助。hash分區會將表中的數據平均分配到你指定的幾個分區中，列所在分區是依據分區列的hash值自動分配，因此你並不能控制也不知道哪條記錄會被放到哪個分區中，hash分區也可以支持多個依賴列。 - 3） List分區
- 4） 組合分區
如果某表按照某列分區之後，仍然較大，或者是一些其它的需求，還可以通過分區內再建子分區的方式將分區再分區，即組合分區的方式。
四、 使用ORACLE在線重定義將普通表改為分區表
將普通錶轉換成分區表有4種方法：
- Export/import method
- Insert with a subquery method
- Partition exchange method
- DBMS_REDEFINITION
另外，INTERVAL分區是Oracle11g新增的特性，它是針對Range類型分區的一種功能拓展。對連續數據類型的Range分區，如果插入的新數據值與當前分區均不匹配，Interval-Partition特性可以實現自動的分區創建。 INTERVAL分區：由range分區派生而來，以定長寬度創建分區（比如年、月、具體的數字（比如100、500等）），分區字段必須是number或date類型。用戶其實根本不用關心其屬於哪個分區，也感覺不到，Oracle會自動管理並使其發揮分區的作用。 具體參考：https://www.cnblogs.com/flowerszhong/p/4535206.html
此處主要講解在線重定義：DBMS_REDEFINITION。
1、首先建立測試表，並插入測試數據：
<code>create table myPartition(id number,code varchar2(5),identifier varchar2(20));
insert into myPartition values(1,'01','01-01-0001-000001');
insert into myPartition values(2,'02','02-01-0001-000001');
insert into myPartition values(3,'03','03-01-0001-000001');
insert into myPartition values(4,'04','04-01-0001-000001');
commit;
alter table myPartition add constraint pk_test_id primary key (id);/<code>
2.檢查下這張表是否可以在線重定義，無報錯表示可以，報錯會給出錯誤信息：
<code>--管理員權限執行begin
SQL> exec dbms_redefinition.can_redef_table('scott', 'myPartition');
PL/SQL procedure successfully completed
–管理員權限執行end/<code>
3. 建個和源表表結構一樣的分區表，作為中間表：
<code>create table t_temp(id number,code varchar2(5),
identifier varchar2(20)) partition by range(id)(
partition TAB_PARTOTION_01 values less than (2),
partition TAB_PARTOTION_02 values less than (3),
partition TAB_PARTOTION_03 values less than (4),
partition TAB_PARTOTION_04 values less than (5),
partition TAB_PARTOTION_OTHER values less THAN (MAXVALUE)
);
alter table t_temp add constraint pk_temp_id2 primary key (id);/<code>
技巧：使用Navicat導出源表的結構sql，改下源表名為新表名，在命令行上跑這些sql語句即可。
4.啟動在線重定義：
<code>--管理員權限執行sql命令行執行
exec dbms_redefinition.start_redef_table('scott', 'myPartition', 't_temp');
--管理員權限執行sql命令行執行/<code>
這裡dbms_redefinition包的start_redef_table模塊有3個參數，分別是SCHEMA名字、原表的名字、中間表的名字。
5.啟動在線重定義後，中間表就可以查到原表的數據。
<code>select * from t_temp;/<code>
6.由於在生成系統中，在線重定義的過程中原數據表可能會發生數據改變，向原表中插入數據模擬數據改變。
<code>insert into myPartition values(5,'05','05-01-0001-000001');
commit;/<code>
7.此時原表被修改，中間表並沒有更新。
<code>select * from myPartition;
select * from t_temp;/<code>
8.使用dbms_redefinition包的sync_interim_table模塊刷新數據後，中間表也可以看到數據更改
<code>--管理員權限執行sql命令行執行,同步兩邊數據
exec dbms_redefinition.sync_interim_table('scott', 'myPartition', 't_temp');
--管理員權限執行sql命令行執行/<code>
查詢同步後的兩邊數據是否一致：
<code>select * from myPartition;
select * from t_temp;/<code>
9.結束在線重定義
<code>--管理員權限執行sql命令行執行，結束重定義
exec dbms_redefinition.finish_redef_table('scott', 'myPartition', 't_temp');
--管理員權限執行sql命令行執行/<code>
10.驗證數據
<code>select * from myPartition;
select * from t_temp;/<code>
11.查看各分區數據是否正確
<code>-- table_name必須大寫
select table_name, partition_name from user_tab_partitions where table_name = 'myPartition';
select * from myPartition partition(TAB_PARTOTION_01);/<code>
12.在線重定義後，中間表已經沒有意義，可留作備份或者刪掉
<code>drop table t_temp purge; /<code>
13.轉成分區表後，原普通表的增刪改查語句可以一成不動，可以平穩過渡。
*注意: * 如果執行在線重定義的過程中出錯,可以在執行dbms_redefinition.start_redef_table之後到執行dbms_redefinition.finish_redef_table之前的時間裡執行：DBMS_REDEFINITION.abort_redef_table('test', 't', 't_new')以放棄執行在線重定義。
五、 本地索引和全局索引
分區表創建好了之後，如果需要最大化分區表的性能就需要結合索引的使用，分區表有兩種索引：本地索引和全局索引。既然存在著兩種的索引類型，相信存在即合理。既然存在就會有存在的原因，也就是在特定的場景中就更能發揮出索引的性能的
- 當查詢的條件是需要跨分區查詢內容的時候，LOCAL INDEX的效率比GLOBAL INDEX的效率要低
- 如果查詢的條件是在單個分區裡面查詢的時候，那麼LOCAL INDEX的效率比GLOBAL INDEX的效率要高。
參考鏈接: https://blog.csdn.net/sunbocong/article/details/80648209
分享到:
閱讀更多 土土豆jin 的文章
