Oracleのダイレクトパスインサートを使ってDelete処理を高速化する

はじめに

Oracle DBで大量のレコードを削除したいときに、delete文を使うと時間がかかります。
テーブル内のすべてのレコードを削除するならtruncateを使えば良いのですが、条件を指定して一部のレコードだけを削除したい場合にtruncateは使用できません。
Oracleのダイレクトパスインサートという機能を使うと、条件付きのDelete処理を高速化することができます。

新・門外不出のOracle現場ワザエキスパートが明かす運用・管理の極意 (DB Selection)

posted with amazlet at 13.03.23

小田圭二大塚信男五十嵐建平谷敦雄宮崎博之神田達成村方仁
翔泳社
売り上げランキング: 115,808

Amazon.co.jpで詳細を見る

データベースバッファキャッシュとダイレクトパス

通常のデータ検索では、最初にデータベースバッファキャッシュ（以下バッファキャッシュ）*1が検索されます。バッファキャッシュに目的のデータが存在すれば、それを返却します。（図の２）
バッファキャッシュにデータが存在しない場合、ストレージ（ディスク）を読みにいきます。そして、読み込んだ結果をバッファキャッシュに書き込み、クエリの結果として返却します。（図の３と４）

ダイレクトパスとは、上記のバッファキャッシュを経由せずに、直接ストレージを読み込みにいくことを言います。ダイレクトパスインサートは、ダイレクトパスを使用したデータ挿入のことを指します。
大量のデータを格納したデータベースの場合、キャッシュヒット率が悪くなるため、ダイレクトパスを使用したほうが性能が良くなるそうです。

一般的なデータウェアハウスでは、データ量に比べてデータベース・バッファ・キャッシュが小さいのでキャッシュ・ヒット率が悪くなります。そのため大量のデータをSELECTする場合にはキャッシュによる性能向上は期待できず、通常のSELECTよりもキャッシュ管理のコストが省けるダイレクト・パス読取りしたほうが速くなることが多いです。
シバタツ流！ DWHチューニングの極意　第4回　SQL書き換えテクニック

事前準備

どれだけ速くなるかを検証するため、以下のような構造のテーブルにデータを用意します。このテーブルからNAMEがダミーデータであるレコードのみを削除してみます。

このテーブルに対して、PL/SQLプロシージャでテストデータを50万件ほど挿入します。

BEGIN
  INSERT INTO table01(ID, NAME, furigana) VALUES (1, '山田　太郎', 'やまだ　たろう');
  INSERT INTO table01(ID, NAME, furigana) VALUES (2, '佐藤　太郎', 'さとう　たろう');
  INSERT INTO table01(ID, NAME, furigana) VALUES (3, '鈴木　太郎', 'すずき　たろう');
  FOR i IN 4..500000 LOOP
   INSERT INTO table01(ID, NAME, furigana) VALUES (i, 'ダミーデータ', 'だみーでーた');
  END LOOP;
  commit;
END;
/

ちなみに、データ削除に以下のクエリを使った場合、４０秒程度かかります。

delete table01 where name = 'ダミーデータ';

ダイレクトパスインサートを使ってDelete処理を高速化する

１．以下のSQLを実行します。

CREATE TABLE table01_new NOLOGGING PARALLEL
 AS SELECT * FROM table01 WHERE name <> 'ダミーデータ';

削除対象外（NAMEがダミーデータ以外）のレコードがtable01_newという新規作成されたテーブルにダイレクトパスインサートにより挿入されます。
NOLOGGINGという指定を行うことにより、REDOログの出力を抑止しています。加えてPARALLELを指定することで並列処理を行い、高速化を図っています。

２．元のテーブルをtable01_oldという名前にリネームします。