學如逆水行舟

主要針對現有較大的表格作分區表，目的是分散磁碟 IO 及管理考量

SQL> create table tingtest as select * from dba_objects;

已建立表格.

假設原表上有 trigger

SQL> create trigger tingtest_tr1 after insert or update or delete on tingtest
  2  declare
  3  begin
  4   null;
  5  end;
  6  /

已建立觸發程式.

假設原表上有 index 

SQL> create index tingtest_i1 on tingtest (object_id);

已建立索引.

建立另一資料表 tingtest2，來源為原始資料表 tingtest 

SQL> create table tingtest2 partition by range(object_id) (partition tingtest2_p
art1 values less than (5000) tablespace TESTTBS,partition tingtest2_part2 values
 less than (MAXVALUE) tablespace SYSTEM) as select * from tingtest;

已建立表格.

SQL> select count(*) from tingtest2;

  COUNT(*)
----------
     44277

 可針對各分區作查詢

SQL> select count(*) from tingtest2 partition (tingtest2_part1);

  COUNT(*)
----------
      4465

SQL> select count(*) from tingtest2 partition (tingtest2_part2);

  COUNT(*)
----------
     39812

SQL> select TIMESTAMP from tingtest2 partition (tingtest2_part1) where object_id
=200;

TIMESTAMP
-------------------
2002-05-12:16:18:11

SQL> select TIMESTAMP from tingtest2 partition (tingtest2_part2) where object_id
=200;

沒有任何資料列被選取

為了加快切換 Table 速度，truncate table 只會產生少量的 redo log ，不會觸發 trigger 

SQL> truncate table tingtest;

表格被截斷.

 truncate 後再 drop，因為沒有資料所以也不會有 redo log

SQL> drop table tingtest;

已刪除表格.

rename tingtest2 to tingtest 

SQL> alter table tingtest2 rename to tingtest;

已更改表格.

 重建 trigger

SQL> create trigger tingtest_tr1 after insert or update or delete on tingtest
  2  declare
  3  begin
  4   null;
  5  end;
  6  /

已建立觸發程式.

 重建索引

SQL> create index tingtest_i1 on tingtest (object_id);

已建立索引.

SQL> select count(*) from tingtest2;
select count(*) from tingtest2
                     *
 ERROR 在行 1:
ORA-00942: 表格或視觀表不存在


SQL> select count(*) from tingtest;

  COUNT(*)
----------
     44277

SQL> select count(*) from tingtest partition (tingtest2_part1);

  COUNT(*)
----------
      4465

SQL> select count(*) from tingtest partition (tingtest2_part2);

  COUNT(*)
----------
     39812
 修改 partition name 
SQL> alter table tingtest rename partition tingtest2_part1 to tingtest_part1;

已更改表格.

SQL> alter table tingtest rename partition tingtest2_part2 to tingtest_part2;

已更改表格.

SQL> select count(*) from tingtest partition (tingtest2_part1);
select count(*) from tingtest partition (tingtest2_part1)
                                         *
 ERROR 在行 1:
ORA-02149: 指定的分割不存在


SQL> select count(*) from tingtest partition (tingtest2_part2);
select count(*) from tingtest partition (tingtest2_part2)
                                         *
 ERROR 在行 1:
ORA-02149: 指定的分割不存在


SQL> select count(*) from tingtest partition (tingtest_part1);

  COUNT(*)
----------
      4465

SQL> select count(*) from tingtest partition (tingtest_part2);

  COUNT(*)
----------
     39812

結論:

Partition 好處:

1. 放在不同的磁碟可分散 IO

2. 可針對各別 partition 作查詢

3. 以邏輯面可針對各別 partition 作 drop、truncate

4. 以實體面可針對各別 partition readonly tablespace、datafile 

心得:

1. hash partition 無法刪除任一個 partition 只能用 alter table TABLE_NAME coalesce partition PARTITION_NAME;

因為 hash partition 資料分佈跟 partition 數量相關

2. range partition 對已經建立的 partition ，無法增加比最大值還小的 partition

只能從原有的 partition 建立 split partition (但是還是在同一個 tablespace 上)

3. list partition 最好要建立 default partition，否則若對應的 List value 會寫入失敗。

4. 如果 partition 有索引，在異動 partition 時記得要 update index