Oracle表分區_按步驟來，實測可用

什麼是表分區？

     類似于磁盤分區，想象一下如果電腦硬碟不分區，所有檔案都堆在一個磁盤下，在檢索檔案時就非常麻煩，浪費時間。資料庫在檢索資料時是同樣的道理。分區就是将資料按照一定的劃分規則分割成多組資料分别存放在不同的資料段内以加快資料檢索速度。

為什麼要進行表分區

     在定義中就可以知道，表分區的根本目的在于加快資料檢索速度。由于資料被劃分到不同的邏輯資料段内，是以在進行資料檢索時就可以直接去對應的資料段内進行查詢，一個邏輯資料段相對于整個表的資料量會小很多，同時在一定程度上能夠減少資料庫的I/O競争，是以能夠明顯提升資料檢索效率，資料量越大的表提升效果越明顯。

何時進行表分區

     根據經驗及Oracle推薦，有兩方面的判斷标準：1、表對象大小超過2G；2、表資料量超過3千萬。這并不是一個死标準，Oracle官方也隻推薦了表對象的大小2GB的閥值，但是根據我以往對全國對家銀行系統巡檢的經驗來看，我一般自己拟定的是3GB，資料量其實也是根據表大小而定；這裡說3千萬是指正常表，比如有些表字段有2百多個，可能一千萬資料就已經超過3GB了，那就推薦分區了；同樣我也遇見過表中4個字段資料量2.3億也才3.6G，而且對該表的查詢速度并不慢。是以這個分區的評判标準并不是一成不變的，需要根據實際使用情況進行綜合分析。總之，一個目的，就是要提升查詢速度。

     上面講了那麼多就是為了能夠更合理的使用表分區的功能，并不是說任何表都适合分區的，需要根據實際業務情況定奪。

如何進行表分區

     以下步驟全部經過生産資料庫的驗證，真實可靠。對于一些剛入門的猿們請嚴格按照步驟來實施，由于表分區要對表結構進行變更，是以資料備份是至關重要的。

     表分區有兩種方案，Oracle隻有對新建立的表才能分區，已有資料的表無法進行分區；但Oracle提供了線上重定義的方式能夠實作有資料表的分區方案，其實線上重定義隻是用Oracle自己的方式執行我們自己步驟的一種轉換，操作步驟的實質還是一樣的，線上重定義使用的是PL/SQL包的形式進行的，個人在這裡不推薦。對于很多人來說縱使這種方式顯得高大上但是自己看不到實際操作的DDL和DML心裡還是有些虛的，除了錯誤也不容易捕捉。是以還是用最原始最笨拙的方法實作最讓人放心，況且也并沒有浪費很多時間。

     接下來就該進入代碼環節，以下代碼以一張生産庫中的審計表(AUDIT_INFO)為例進行示範：

1. 導出表進行備份，防止資料丢失

exp <username>/<password>@<SID> file=audit_info.dmp log=test.log full=n rows=y buffer=10240000 tables=<username>.AUDIT_INFO
           

     由于我們生産庫中用的是GBK的字元集，與Linux預設字元集UTF-8不比對，是以會有一個EXP-00091: Exporting questionable statistics的錯誤，這個錯誤可以忽略。

2. 建立臨時表，再次備份（多備無患）

create table AUDIT_INFO_BAK tablespace users as select * from AUDIT_INFO
           

1. 根據主鍵校驗資料是否有缺失

select * from AUDIT_INFO_BAK aib where not exists (select 1 from AUDIT_INFO ai where aib.serialno=ai.serialno )
           

1. 根據分區規則提前統計分區後個分區中資料量，分區後用作資料對比（此處以表中一個時間字段作為分區次元，實際使用中以時間維多進行分區是最最最常見的分區規則，别的像什麼按行分區、清單分區、散列分區。。。适用場景太少）

select substr(audittime,1,4),count(1) from AUDIT_INFO_bak group by substr(audittime,1,4) order by substr(audittime,1,4)
           

1. 删除原表，删除前備份原表DDL，裡邊會有索引、存儲規則、序列等其他資訊分區完成後還要使用

truncate table AUDIT_INFO
drop table AUDIT_INFO
           

1. 重建表（分區表）

create table AUDIT_INFO tablespace users partition by range(AUDITTIME)
(
partition AUDIT_INFO_2010 values less than ('2010/12/31 23:59:59') tablespace users,
partition AUDIT_INFO_2012 values less than ('2012/12/31 23:59:59') tablespace users,
partition AUDIT_INFO_2014 values less than ('2014/12/31 23:59:59') tablespace users,
partition AUDIT_INFO_2016 values less than ('2016/12/31 23:59:59') tablespace users,
partition AUDIT_INFO_MAX values less than (MAXVALUE) tablespace users 
) AS 
SELECT * FROM AUDIT_INFO_BAK
           

     注： 在建立分區表時需要設定行移動

alter table AUDIT_INFO enable row movement;

這是因為在進行資料更新時如果更新字段為分區字段，并且更新後該行資料不再原來的分區中，就會觸發一次delete/insert操作，Oracle會删除原來分區中該條資料并在新的分區中插入，但是該操作不會觸發觸發器；如果不開啟該選項則在更新時會報錯。

7. 統計各分區表中資料量與第4步中查詢到的資料量進行對比

select count(1) from AUDIT_INFO partition (AUDIT_INFO_2016)
           

1. 建立索引，表分區中索引有全局索引和本地索引之分。兩種索引沒有優劣之分，需要根據業務定奪使用哪種合适，如果在業務中還需要進行跨多個分區時間段的查詢操作建議使用全局索引，如果業務場景中最多的是對單個分區内資料查詢的建議使用本地索引

CREATE INDEX IDX1_AUDIT_INFO_LOCAL ON AUDIT_INFO(SERIALNO,AUDITTIME) LOCAL--本地索引
CREATE INDEX IDX1_AUDIT_INFO_GLOBAL ON AUDIT_INFO(SERIALNO,AUDITTIME,RECORDTYPE) GLOBAL--全局索引
           

1. 添加新分區，由于是按時間分區總是有超過分區次元的時間，是以對于maxvalue分區就需要考慮在一定時間後進行分割

ALTER TABLE AUDIT_INFO SPLIT PARTITION AUDIT_INFO_MAX AT ('2017/12/31 23:59:59') INTO (PARTITION AUDIT_INFO_2017,PARTITION AUDIT_INFO_MAX);--設定了MAXVALUE
ALTER TABLE AUDIT_INFO ADD PARTITION AUDIT_INFO_2017 VALUES LESS THAN ('2017/12/31 23:59:59') tablespace users; --未設定MAXVALUE
           

     後記：一個同僚在使用表分區時用在了存儲過程中，根據業務每個月要添加一個新分區，再删除一個最老的分區。由于時間一直在變化，是以他使用了SQL的動态參數綁定，部分代碼片段形式如下：

declare 
  v_delmonth varchar2(30);
begin
  select concat('AUDIT_INFO_',TO_CHAR(ADD_MONTHS(TO_DATE('20191111','YYYYMMDD'),-23),'YYYY')) INTO v_delmonth from dual;   
  execute immediate 'ALTER TABLE AUDIT_INFO DROP PARTITION :1' using  v_delmonth;
end;
           

     在進行測試時一直會報一個

ORA-14006:invalid partition name

的錯誤，這是由于存儲過程不支援DDL語句，Oracle也不推薦在存儲過程中使用DDL；在DDL中是無法進行變量綁定的，是以要把SQL語句拼完整才能執行，應修改如下：

declare 
  v_delmonth varchar2(30);
  sqlstr varchar2(1000);
begin
  select concat('AUDIT_INFO_',TO_CHAR(ADD_MONTHS(TO_DATE('20191111','YYYYMMDD'),-23),'YYYY')) INTO v_delmonth from dual;
  sqlstr:='ALTER TABLE AUDIT_INFO DROP PARTITION '||v_delmonth;
  execute immediate sqlstr;
end;