読者です 読者をやめる 読者になる 読者になる

小さい頃はエラ呼吸

いつのまにやら肺で呼吸をしています。


【Oracle】パーティション表を用いた高速なDelete処理

oracle
はじめに

OracleのDelete処理は時間がかかります。そのため、大量のレコードを削除する際はあれこれ工夫が必要です。
この記事では、パーティション表を使った高速なデータ削除の方法を紹介します。

パーティション表とは?

パーティション化された表(テーブル)のことです。大きな表やインデックスを、パーティションというより小さくて管理しやすい部分に分割します。パーティション化を行うと、大きなデータベースに対する管理を簡素化できます。

パーティション化とは、大規模な表や索引を、パーティションというより小さくて管理しやすい部分に分割して、この種の表や索引をサポートするときの主な問題に対処します。パーティション表にアクセスする際、SQL問合せとDML文を変更する必要はありません。ただしパーティションを定義すると、DDL文は表や索引全体ではなく、個々のパーティションへのアクセスやその操作ができるようになります。パーティション化を行うと、このようにしてラージ・データベース・オブジェクトの管理を簡素化できます。また、パーティション化は、アプリケーションに対して完全に透過的です。
パーティション表とパーティション索引 はてなブックマーク - パーティション表とパーティション索引

パーティションの種類

パーティションにはいくつか種類があります。
範囲で分割するレンジ・パーティションや指定した値で分割するリストパーティションなど、パーティションの種類は以下のページにわかりやすくまとめられています。

パーティション表を用いた高速なDelete処理

はじめに、パーティション表を用意します。今回はリスト・パーティションを作成します。

create table table01
(
 id char(8),           --ID
 name varchar(50),     --名前
 furigana varchar(50), --ふりがな
 seibetsu varchar(3),  --性別
 birthday char(8),     --生年月日
 primary key( id )
)
PARTITION BY LIST(seibetsu)
(
 PARTITION part1 VALUES('男') TABLESPACE USERS,
 PARTITION part2 VALUES('女') TABLESPACE USERS
);

このテーブルは性別カラムを持ち、男性はパーティション1(part1)、女性はパーティション2(part2)というようにパーティションを分割します。
つづいて、このテーブルにテストデータを100万件登録します。男女比は半々になるように、行番号の偶数奇数で男女を分けます。

truncate table table01;
set timing on
insert /*+ APPEND */ into table01 nologging (
id, name, furigana, seibetsu, birthday) 
  (select 
  lpad(rownum, 8, '0'),
  concat('テスト 太郎', TO_MULTI_BYTE(rownum)),
  concat('てすと たろう', TO_MULTI_BYTE(rownum)),
  CASE MOD(rownum, 2)
    WHEN 0 THEN '男'
    ELSE '女'
  END,
  TO_CHAR(TO_DATE('20130101','YYYYMMDD')
    + FLOOR(DBMS_RANDOM.VALUE(0, 365)), 'YYYYMMDD')
  from (select 0 from all_catalog where rownum <= 1000),
       (select 0 from all_catalog where rownum <= 1000)
  );
commit;

この状態から男性のレコードだけを削除しています。

  1. 方式1 delete from whereを使った通常のdelete
delete from table01 where seibetsu = '男';
  1. 方式2 drop partitionを使ったパーティションの削除

一度パーティションを削除して、作り直します。

alter table table01 drop partition part1;
alter table table01 add partition part1 VALUES ('男') TABLESPACE USERS;
性能測定結果
試行回数 通常のdelete(秒) パーティションのdelete(秒) 処理時間差
1st 32.984 2.219 -93.27%
2st 37.156 3.532 -90.49%
3st 34.657 2.25 -93.51%
avg 34.93 2.67 -92.37%

f:id:replication:20150319200429p:plain
50万レコードの削除処理が35秒→2.7秒(92%改善)されました。

おわりに

パーティションテーブルを導入できれば削除処理を高速化することができます。
テーブルの設計時にパーティションテーブルを使えるか、削除パフォーマンス等を含め、考慮に入れておきたいですね。