hedgezzz的部落格

如果有大量資料 , 作 fragment 的技巧對 performance 就非常重要 ,
除了 PDQ 設定參數之外 , fragment 的語法也很重要 ,
還有就是 attache fragment and detach fragment !!~~~

create table customer (id int, state char (2)) fragment by expression
partition az_part (state = "AZ") in dbspace1,
partition ca_part (state = "CA") in dbspace1,
partition wa_part (state = "WA") in dbspace1,
partition ny_part (state = "NY") in dbspace1,
remainder in dbspace1;

create index state_ind on customer (state ) fragment by expression
partition az_part (state = "AZ") in dbspace2,
partition ca_part (state = "CA") in dbspace2,
partition wa_part (state = "WA") in dbspace2,
partition ny_part (state = "NY") in dbspace2,
remainder in dbspace2;

然後是 alter fragment 語法 , 關於 table and index 的部分:

alter fragment on table customer add partition part_or (state = "OR")
in dbspace1 before ca_part;

alter fragment on index state_ind add partition part_or (state = "OR")
in dbspace2 before ca_part;

最後是 attach 跟 detach fragment 指令, 這部分只有 table 有,index 沒有...

alter fragment on table customer attach customer_or as
partition part_3(state = "OR");

注意: customer_or 是一個 table , 內含資料要合乎 state = "OR" 的需求!!!
customer_or裡面的資料會 insert 到 customer 裡面 !!
有 attach 就會有 detach :

alter fragment on table customer detach partition part_3 customer_or;
把 part_3 的 partition 資料及結構又轉出來成 customer_or table , 不過 ,
attach and detach 都沒有作到 index !!!

最後...重整 index :

alter fragment on index state_ind init fragment by expression
partition az_part (state = "AZ") in dbspace2,
partition ca_part (state = "CA") in dbspace2,
partition wa_part (state = "WA") in dbspace3,
partition ny_part (state = "NY") in dbspace3,
remainder in dbspace3 ;