Oracle
使用 LIST-REF 複合分區的 DROP PARTITION 策略
我們有一個大型 Oracle 11gR2 實例，其中一些表最近在刪除數據時變得很麻煩。該數據庫為大型優化集群提供動力，該集群從數據庫獲取輸入元數據並儲存文件（使用 11g 的 SecureFiles）和視覺化中使用的 1D-4D 輸出數據。數據庫的讀/寫非常快，在我們需要清理數據之前，情況確實很好。
軟體工程師被允許自由執行（閱讀：我）並在沒有任何分區的情況下設置這個系統，天真地假設刪除會很好。我們的表結構如下，之前依賴於每個where
ON DELETE CASCADE刪除數據。Case``DeleteFlag = 1/* Metadata tables */ Case(CaseId, DeleteFlag, ...) On Delete Cascade CaseId OptimizationRun(OptId, CaseId, ...) On Delete Cascade OptId OptimizationStep(StepId, OptId, ...) On Delete Cascade StepId /* Data tables */ Files(FileId, CaseId, Blob) /* deletes are near instantateous here */ /* Data per run */ OnedDataX(OptId, ...) TwoDDataY1(OptId, ...) /* packed representation of a 1D slice */ /* Data not only per run, but per step */ TwoDDataY2(StepId, ...) /* packed representation of a 1D slice */ ThreeDDataZ(StepId, ...) /* packed representation of a 2D slice */ FourDDataZ(StepId, ...) /* packed representation of a 3D slice */ /* ... About 10 or so of these tables exist */扇出看起來像：1 個案例 = 50 次執行 = 2000 步 = 跨數據表的 5-35M 行。數據表的總和約為 500GB，只有 1B 行。
在大姊妹網站上的一些幫助下，我得出了一個可能適用於我們設置的分區方案（即按 分區
CaseId）。它使用 LIST-REFERENCE 複合分區，如下所示：/* Metadata tables */ Case(CaseId, DeleteFlag, ...) OptimizationRun(OptId, CaseId, ...) Partition By List (CaseId) ( partition default_case values (default) ) /* Some PL/SQL is run when we add a Case to do: * alter table OptimizationRun split partition ... */ OptimizationStep(StepId, OptId, ...) Partition By Reference (Fk_OptId) /* Data Tables */ OnedDataX(OptId, ...) Partition By Reference (Fk_OptId) ... FourDDataZ(StepId, ...) /* packed representation of a 3D slice */ Partition By Reference(Fk_StepId)設置起來很輕鬆，改進了我們通常的可疑查詢的許多計劃，並讓我們能夠刪除一個案例的所有數據
DROP PARTITION。這是我遇到問題的地方：索引重建！目前執行刪除的偽 PL/SQL 如下所示：
for casedel in (select CaseId from Case where DeleteFlag = 1) loop execute immediate 'alter table OptimizationRun' || ' drop partition case_' || casedel.CaseId || ' update indexes'; end loop; delete from Case where DeleteFlag = 1; commit;我們最終會遇到與 REFERENCE 分區中使用的外鍵無關的數據表上的某些索引的錯誤。
我真的必須為
ALTER INDEX blah REBUILD這些輔助索引中的每一個創建狀態，還是有更聰明的方法？更新：一些實際的 DDL 供您閱讀。
create table opt_case ( case_id number not null, name varchar2(240) not null, delete_date date, constraint pk_optcase primary key (case_id) using index ); create table opt_run ( opt_run_id number not null, case_id number not null, constraint pk_opt_run primary key (opt_run_id) using index, constraint fk_opt_run_case foreign key (case_id) references opt_case (case_id) ) partition by list (case_id) ( -- This is the catch-all partition. It is an error to insert into -- this partition; it will be unused. Instead this is the -- partition which is split when a new CASE_ID is added to -- OPTCASE. partition default_case values (default) ); create index idx_fk_opt_run_case on opt_run (case_id); create table opt_step ( opt_step_id number not null, opt_run_id number not null, step number not null, step_value number(7,2), constraint pk_opt_step primary key (opt_step_id) using index enable, constraint fk_opt_step_opt_run foreign key (opt_run_id) references opt_run (opt_run_id) ) -- This is the golden ticket from here on out for partitioning. -- Attach to the foreign key on OPT_RUN_ID to enjoy the -- parent table partitioning on CASE_ID. partition by reference (fk_opt_step_opt_run); create index idx_fk_opt_step_opt_run on opt_step (opt_run_id); -- One of the bigger data tables for reference create table twod_data ( opt_step_id number not null, spatial_coord_id number not null, in_group number(1,0), z_value number(3,0), y_value number(5,3), x_value number(5,1), constraint pk_twod_data primary key (opt_step_id, spatial_coord_id), constraint fk_twod_data_spat_coord foreign key (spatial_coord_id) references twod_spatial_cord (spatial_coord_id), constraint fk_2d_data_opt_step foreign key (opt_step_id) references opt_step (opt_step_id) ) partition by reference (fk_2d_data_opt_step); create index idx_fk_2d_data_opt_step on twod_data (opt_step_id);像這樣的索引
idx_fk_2d_data_opt_step最終需要在DROP PARTITION子句之後重建。插入數據表是無趣的，簡單的
INSERT INTO blah。創建過程現在Case看起來像：-- Add the CASE insert into opt_case (name) values (p_CaseName) returning case_id into v_CaseId; commit; -- Add partition for the CASE execute immediate 'alter table opt_run' || ' split partition default_case values (' || v_CaseId || ')' || ' into (partition case_' || v_CaseId ||', partition default_case)'; -- Because we split an empty partition, no index rebuild required
刪除或移動分區時，全域索引將變得不可用。這是因為在您的分區中有指向物理地址的條目不再有效。如果可以，盡量避免使用全域索引。您可以通過將它們設為本地來做到這一點。
create index idx_fk_opt_run_case on opt_run (case_id) local;應該做的伎倆。如果可以使用引用分區，則不必在表中添加冗餘數據，因為該關係已在分區定義中定義。
一個例子：
RONR SQL>create table parent (id number not null , x varchar2(10) ) partition by list (id) ( partition par_1 values (1), partition par_2 values (2) ); 2 3 4 5 6 7 8 Table created. RONR SQL>create unique index pk_parent on parent (id) local; 2 Index created. RONR SQL>alter table parent add CONSTRAINT pk_parent PRIMARY KEY (id) USING INDEX; 2 3 4 Table altered. RONR SQL>create table child ( parent number not null , y varchar2(10) , constraint fk_p_c foreign key (parent) references parent(id)) partition by reference (fk_p_c); 2 3 4 5 6 7 Table created. RONR SQL>insert into parent (id, x) values (1,'boe'); insert into parent (id, x) values (2,'oeps'); 1 row created. RONR SQL> 1 row created. RONR SQL>select * from parent; ID X ---------- ---------- 1 boe 2 oeps RONR SQL>insert into child (parent, y) values (1,'ggg'); 1 row created. RONR SQL>insert into child (parent, y) values (1,'ggg'); 1 row created. RONR SQL>insert into child (parent, y) values (2,'ppp'); 1 row created. RONR SQL>select * from child; PARENT Y ---------- ---------- 1 ggg 1 ggg 2 ppp RONR SQL>select table_name, partition_name from user_tab_partitions; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ PARENT PAR_1 PARENT PAR_2 CHILD PAR_1 CHILD PAR_2 RONR SQL>select index_name, partition_name, status from user_ind_partitions; INDEX_NAME PARTITION_NAME STATUS ------------------------------ ------------------------------ -------- PK_PARENT PAR_1 USABLE PK_PARENT PAR_2 USABLE RONR SQL>alter table parent drop partition par_1; Table altered. RONR SQL>select * from parent; ID X ---------- ---------- 2 oeps RONR SQL>select * from child; PARENT Y ---------- ---------- 2 ppp RONR SQL>select table_name, partition_name from user_tab_partitions; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ PARENT PAR_2 CHILD PAR_2 RONR SQL>select index_name, partition_name, status from user_ind_partitions; INDEX_NAME PARTITION_NAME STATUS ------------------------------ ------------------------------ -------- PK_PARENT PAR_2 USABLE我沒有看到不可用的索引。我所看到的是我可以刪除一個父分區並將其級聯到子分區。這有幫助嗎？