ORACLE分割槽表梳理系列（一）- 分割槽表概述、分類、使用方法及注意事項

https://www.cnblogs.com/yumiko/p/6095036.html

ORACLE分割槽表梳理系列（二）- 分割槽表日常維護及注意事項（紅字需要留意）

https://www.cnblogs.com/yumiko/p/6163523.html

一、什麼是線上重定義

要了解什麼是線上重定義技術，我想從表分割槽開始說起。在生產系統運維過程中，經常遇到的一個需求是如何把一個資料量非常大的普通表改造成分割槽表。分割槽最早在oracle8.0版本引入，支援將一個表或索引物理地分解為多個更小、更可管理的部分。

以下是線上重定義表的一些功能：

1.修改表的儲存引數；

2.可以將錶轉移到其他表空間；

3.增加並行查詢選項；

4.增加或刪除分割槽；

5.重建表以減少碎片；

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

7.增加或刪除一個列。

線上重定義好處：

- 提高資料的可用性

- 資料段變得更小，減輕了管理的負擔

- 改善某些查詢的效能

- 將資料修改分佈到多個單獨的分割槽上，減少競爭

分割槽表在各行業的資料庫都得到廣泛應用，但是有些業務系統在設計階段對系統資料和效能容量增長估計不足，或沒有考慮到運維過程中的資料歸檔需求，往往沒有對錶做分割槽設計。在生產執行經過長時間的資料積累之後，才發現表越來越大，某些查詢或插入資料的效能變得越來越慢，迫切需要做表分割槽改造。

那麼問題來了，業務系統往往都是7*24線上作業，改造的過程又必然涉及表結構的變動，如果對錶進行重建，會對系統執行產生非常大的影響，通常會設定計劃停機視窗來做這類維護操作。

當然，分割槽表的改造只是諸多資料重組織或重定義場景中的一種，在資料變動需求越來越多、越來越複雜，而系統停機的成本又顯著升高的背景下，從Oracle 8i開始就設計了有限的線上重新組織資料的功能，例如create indexes online, rebuilding indexes online。並在9i進一步擴充套件這方面的能力，引入了資料線上重定義。

線上重定義技術允許資料庫管理員在該表上有讀寫資料操作的情況下，非常靈活地修改表的物理屬性、表資料、表結構。

二、線上重定義的使用場景

有以下變更需求時，都可以考慮使用線上重定義技術，這些場景也是運維過程中經常遇到的：

- 修改表的物理屬性、儲存引數

- 將表遷移到別的表空間

- 消除表碎片、釋放空間

- 在表中增加、刪除或重新命名欄位

- 大批次改變表中的資料

三、線上重定義的實現原理

oracle提供了一個dbms_redefinition包用於線上重定義操作，主要包含如下三個過程：

dbms_redefinition.start_redef_table

這個過程首先會建立一個快速重新整理的物化檢視作為過渡表，然後將源表的資料載入到過渡表中，並在源表上建立物化檢視日誌，以支援快速重新整理同步資料。

dbms_redefinition.sync_interim_table

用來把源表中的資料同步到過渡表。

dbms_redefinition.finish_redef_table

這個過程的操作步驟比較多，也是做線上重定義時需要特別注意的，但其執行時間通常是非常短的：

1）先呼叫一次dbms_redefinition.sync_interim_table，同步資料。

2）鎖定源表，鎖定之後表資料不再允許發生變化。

3）再呼叫一次dbms_redefinition.sync_interim_table，同步資料。

4）交換源表和過渡表的表名。

5）刪除物化檢視和物化檢視日誌。

6）釋放表鎖資源。

四、實驗將普通表改造成分割槽表

下面我們透過實際案例來應用這項技術，本次實踐中我們要弄清楚幾個問題：

a. 線上重定義的操作過程。

b. 將一個2000萬資料量的表進行重定義，需要多長時間。

c. 線上重定義期間，表相關的操作是否受影響，又是如何影響的。

1. 檢查使用者許可權

執行dbms_redefinition包需要以下許可權：

- execute privilege to dbms_redefinition

- create any table

- alter any table

- drop any table

- lock any table

- select any table

- create any index

- create any trigger

grant execute on dbms_redefinition to SCOTT;

grant create any table to SCOTT;

grant alter any table to SCOTT;

grant drop any table to SCOTT;

grant lock any table to SCOTT;

grant select any table to SCOTT;

grant create any index to SCOTT;

grant create any trigger to SCOTT;

可進入使用者後執行以下SQL進行檢查確認：

select * from session_privs;

2.實驗建立一個源表，並插入資料

create table unpar_table(id number(10),create_date date,name varchar2(100),up_date date);

--使用批次繫結技術插入200萬資料初始測試資料

declare

type t_mid is table of unpar_table%rowtype index by binary_integer;

l_tab_mid t_mid;

begin

for i in 1 .. 2000000 loop

l_tab_mid(i).id := i;

l_tab_mid(i).create_date := sysdate;

l_tab_mid(i).name := lpad('a', 100, 'a');

l_tab_mid(i).up_date := sysdate;

end loop;

forall i in 1 .. l_tab_mid.count

insert into unpar_table values l_tab_mid (i);

commit;

end;

/

--給表unpar_table增加主鍵約束及建索引

alter table unpar_table add (constraint unpar_table_pk primary key (id));

create index create_date_ind on unpar_table(create_date);

注意：線上重定義方法。存在兩種重定義方法，一種是基於主鍵、另一種是基於ROWID。ROWID的方式不能用於索引組織表，而且重定義後會存在隱藏列M_ROW$$。預設採用主鍵的方式。(重定義的表如果基於主鍵，則必須要有主鍵才能進行重定義)

--收集統計資訊

exec dbms_stats.gather_table_stats(ownname => 'SCOTT',TABNAME => 'UNPAR_TABLE',cascade => true);

3.按需求建立一個已分割槽的中間表

create table par_table(id number(10),create_date date,name varchar2(100),up_date date)

partition by range(create_date)

(

partition unpar_table_1 values less than (to_date('01/01/2012','DD/MM/YYYY')),

partition unpar_table_2 values less than (to_date('01/01/2013','DD/MM/YYYY')),

partition unpar_table_3 values less than (to_date('01/01/2014','DD/MM/YYYY')),

partition unpar_table_4 values less than (to_date('01/01/2015','DD/MM/YYYY')),

partition unpar_table_5 values less than (to_date('01/01/2016','DD/MM/YYYY')),

partition unpar_table_6 values less than (to_date('01/01/2017','DD/MM/YYYY')),

partition unpar_table_7 values less than (maxvalue)

);

以上步驟完成準備工作，開始執行線上重定義過程。

4.檢查源表是否具備線上重定義的條件

exec dbms_redefinition.can_redef_table('SCOTT','UNPAR_TABLE');

--檢查耗時

SQL> exec dbms_redefinition.can_redef_table('SCOTT','UNPAR_TABLE');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.17

5.開始線上重定義，這一步相當於初始化工作，耗時比較長

exec dbms_redefinition.start_redef_table('SCOTT','UNPAR_TABLE','PAR_TABLE');

--檢查耗時

SQL> exec dbms_redefinition.start_redef_table('SCOTT','UNPAR_TABLE','PAR_TABLE');

PL/SQL procedure successfully completed.

Elapsed: 00:00:43.29

6.在中間表上建立約束和索引並收集統計資訊

這一步提前做，可以防止重定義完成後，新表沒有可用索引，而產生效能問題。

oracle提供了dbms_redefinition.copy_table_dependents過程，用於複製源表上的索引、約束、觸發器、許可權等依賴關係到中間表，但是這個包存在的BUG也不少，可以選擇性使用。

alter table par_table add (constraint unpar_table_pk2 primary key (id));

--耗時:Elapsed: 00:00:08.93

create index create_date_ind2 on par_table(create_date);

--耗時:Elapsed: 00:00:10.07

exec dbms_stats.gather_table_stats(ownname => 'SCOTT',TABNAME => 'PAR_TABLE',cascade => true);

--耗時:Elapsed: 00:00:02.89

注意：

如果在執行DBMS_REDEFINITION.START_REDEF_TABLE()過程和執行DBMS_REDEFINITION.FINISH_REDEF_TABLE()過程直接在重定義表上執行了大量的DML操作，那麼可以選擇執行一次或多次的SYNC_INTERIM_TABLE()過程，以減少最後一步執行FINISH_REDEF_TABLE()過程時的鎖定時間。

7.手工同步資料，將上一步執行中將產生的資料先做同步重新整理

exec dbms_redefinition.sync_interim_table('SCOTT','UNPAR_TABLE','PAR_TABLE');

--檢查耗時

SQL> exec dbms_redefinition.sync_interim_table('SCOTT','UNPAR_TABLE','PAR_TABLE');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.22

8.完成線上重定義過程：執行後，中間表和源表的表名互換

exec dbms_redefinition.finish_redef_table('SCOTT','UNPAR_TABLE','PAR_TABLE');

--檢查耗時

SQL> exec dbms_redefinition.finish_redef_table('SCOTT','UNPAR_TABLE','PAR_TABLE');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.64

9.刪除中間表，並將索引重新命名回來

此時的中間表已經是原來未分割槽的普通表，而源表已經變成了分割槽表

--先檢查分割槽表及普通表情況

select table_name,partition_name,num_rows from user_tab_partitions where table_name like '%PAR_TABLE%';

select a.segment_name,a.segment_type,sum(a.bytes/1024/1024) size_m from user_segments a where a.segment_name like '%PAR_TABLE%' group by a.segment_name,a.segment_type;

select table_name,index_name,status from user_indexes where table_name like '%PAR_TABLE%';

SQL> select table_name,partition_name,num_rows from user_tab_partitions where table_name like '%PAR_TABLE%';

TABLE_NAME PARTITION_NAME NUM_ROWS

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

UNPAR_TABLE UNPAR_TABLE_7 2000000

UNPAR_TABLE UNPAR_TABLE_6 0

UNPAR_TABLE UNPAR_TABLE_5 0

UNPAR_TABLE UNPAR_TABLE_4 0

UNPAR_TABLE UNPAR_TABLE_3 0

UNPAR_TABLE UNPAR_TABLE_2 0

UNPAR_TABLE UNPAR_TABLE_1 0

7 rows selected.

SQL> select a.segment_name,a.segment_type,sum(a.bytes/1024/1024) size_m from user_segments a where a.segment_name like '%PAR_TABLE%' group by a.segment_name,a.segment_type;

SEGMENT_NAME SEGMENT_TYPE SIZE_M

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

UNPAR_TABLE TABLE PARTITION 288

PAR_TABLE TABLE 280

UNPAR_TABLE_PK INDEX 35

UNPAR_TABLE_PK2 INDEX 44

SQL> select table_name,index_name,status from user_indexes where table_name like '%PAR_TABLE%';

TABLE_NAME INDEX_NAME STATUS

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

UNPAR_TABLE CREATE_DATE_IND2 VALID

UNPAR_TABLE UNPAR_TABLE_PK2 VALID

PAR_TABLE CREATE_DATE_IND VALID

PAR_TABLE UNPAR_TABLE_PK VALID

--操作

drop table par_table purge;

alter table unpar_table rename constraint unpar_table_pk2 to unpar_table_pk;

alter index unpar_table_pk2 rename to unpar_table_pk;

alter index create_date_ind2 rename to create_date_ind;

--驗證查詢

SQL> select table_name,index_name,status from user_indexes where table_name like '%PAR_TABLE%';

TABLE_NAME INDEX_NAME STATUS

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

UNPAR_TABLE CREATE_DATE_IND VALID

UNPAR_TABLE UNPAR_TABLE_PK VALID

SQL> select a.segment_name,a.segment_type,sum(a.bytes/1024/1024) size_m from user_segments a where a.segment_name like '%PAR_TABLE%' group by a.segment_name,a.segment_type;

SEGMENT_NAME SEGMENT_TYPE SIZE_M

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

UNPAR_TABLE TABLE PARTITION 288

UNPAR_TABLE_PK INDEX 44

至此，使用線上重定義進行表分割槽改造的工作已經完成。

五、線上重定義需注意的問題

使用線上重定義技術，以下情況是需要注意的：

- 如果離線操作能夠解決問題，就不要用線上重定義例如一些靜態資料、歷史資料的歸檔遷移，可使用CTAS、alter table move、或匯出匯入完成

- 表空間至少要留有比源表所用空間更大的剩餘空間

- 線上重定義的操作過程耗時較長，但對業務的影響最小

- 要注意源表上的事務操作，如果過於頻繁，可能會發生較嚴重的等待

線上重定義與普通表改為分割槽表

相關文章