Zenn
Open2

OracleDatabase 覚え書き

SQL
Oracle
yub0nyub0n

遅いINSERT文を高速化する方法

INSERT INTO ~ SELECTの場合はSELECT文を高速化する

基本的なことだが、以下のような方法で
SELECT文を改善することで結果的にINSERTにかかる時間が短くなる。

  • SELECT対象のテーブルに適切なINDEXを付加する
  • INDEXを有効活用できるようなクエリの書き方を行う
  • WHERE句で絞り込んで一度にINSERTするデータ量を減らす

INSERT対象のテーブルのINDEXを一時的に外す

データをINSERTする際はINDEXの更新も同時に行われる。
INDEXが多いとその分INDEXの更新にかかる時間も長くなるので、
対象テーブルのINDEXを一時的に外すことでINSERTが速くなる可能性がある。

ダイレクト・パス・インサート

データベースバッファを経由せずデータファイルへ直接データを書き込むことが可能。
通常のINSERTと比較して数分の1程度の時間で挿入できる。

INSERT /*+ APPEND */ INTO table1 SELECT * FROM table2;
-- APPEND_VALUESヒントは Oracle11gR2から利用可能
INSERT  /*+ APPEND_VALUES */ INTO table1 VALUES (...);

ただし、以下のような制約があるため利用する際は注意が必要。

  • テーブル（or パーティション）自体が排他ロックされる（通常のINSERTは行ロック）
  • HWM[1]以降のブロックにデータが書き込まれる（DELETEで空いた領域は空いたままになる）
  • 通常のINSERTより一時表領域を多く消費する

CREATE TABLE ～ AS SELECT (CTAS)

"新しくテーブルを作成し、既存のテーブルからデータを挿入する"という操作を一気にできる構文。
この構文を利用する場合もダイレクト・パス・インサートでデータが挿入される。

CREATE TABLE table1 AS SELECT * FROM table2;
-- データを複製したくない場合はWHEREで0件に絞り込む
CREATE TABLE table1 AS SELECT * FROM table2 WHERE 1 = 0;

ただし、PRIMARY KEYやINDEX、COMMENTなどはコピーされないため利用する際は注意が必要。
（PRIMARY KEYやINDEXをコピーしていないことでINSERTの高速化が実現できているとも言える）
NOT NULL制約はコピーされる。

NOLOGGINGを指定

ダイレクト・パス・インサートを行うINSERT文では
NOLOGGINGを指定することでREDOログ[2]の書き込みを行わないようにでき、
その分INSERTを高速化することが可能。

INSERT /*+ APPEND */ INTO table1 NOLOGGING SELECT * FROM table2;
-- CREATE TABLE ～ AS SELECT 構文でも利用可能（表の作成はREDOログに記録される）
CREATE TABLE table1 NOLOGGING AS SELECT * FROM table2;

ただし、REDOログの書き込みを行わない＝リカバリができないということなので、
利用する際は注意が必要。

パラレルDML[3]

1つのクエリに対して複数のプロセスで並列に処理できる機能。
パラレルDMLを利用するためには「パラレルを有効にすること」と
「明示的にパラレルを利用すること」を宣言しなければならない。

-- ヒント句を利用（PARALLELの後のカッコ内の数字は並列度）
INSERT /*+ ENABLE_PARALLEL_DML PARALLEL(4) */ INTO table1 SELECT * FROM table2;
-- セッションで有効化 ＋ APPENDヒント
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ APPEND */ INTO table1 SELECT * FROM table2;
 -- セッションでパラレルDMLが有効化されていれば、通常のINSERTもパラレル可
INSERT /*+ NOAPPEND PARALLEL */ INTO table1 SELECT * FROM table2;
 -- INSERT INTO ～ SELECT 構文では、パラレル問合せとパラレルDMLの併用も可
INSERT /*+ PARALLEL(table1) */ INTO table1 
SELECT /*+ PARALLEL(table2) */ *  FROM table2;

パラレルDMLは勿論、MERGE、UPDATEおよびDELETE文でも利用可能。

脚注

  1. HWM ... ハイウォーターマーク（High Water Mark）。「使用済」の最終ブロックの位置を表す。 ↩︎

  2. REDOログ ... データベースに対して行なった操作をREDO(再実行)するためのログ。
    　　　　　　（＝作業履歴情報） ↩︎

  3. DML ... データ操作言語（Data Manipulation Language）。
    　　　　SELECT,INSERT,UPDATE,DELETEなどのデータを操作する命令のこと。 ↩︎

yub0nyub0n

遅いDELETE文を高速化する方法

WHERE句で絞り込み、削除対象のデータ量を減らす

基本的なことだが、削除対象のデータ量を減らすことでDELETEにかかる時間は短縮される。
WHERE句で絞り込む際はSELECT文と同様に、INDEXを意識して絞り込みを行うこと。
DELETE前に一時的にINDEXを作成し、DELETE完了後にINDEXを削除するという方法もある。

TRUNCATE TABLE ~

全件削除しても問題ない場合はTRUNCATE（切り捨て）を使用するという方法もある。
テーブル内のデータを全件削除するDDL[1]で、通常のDELETEと比較して圧倒的に速い。
DELETEと違い、データ割り当て領域やHWMが解放される。

TRUNCATE TABLE table1;

ただし、以下のような制約があるため利用する際は注意が必要。

  • 削除したデータのUNDO[2]、REDOログが生成されない（rollbackできない）
    フラッシュバッククエリで追うことができなくなる
  • 作業の前後でコミットされる（トランザクションの要素として利用できない）
  • DROP TABLE できる権限が必要（DELETE権限では実行できない）

CREATE TABLE ～ AS SELECT を利用する

INSERT文の高速化でも触れたが、
CREATE TABLE ～ AS SELECT構文を利用して削除対象ではないデータを新規テーブルに退避させて
元のテーブルに戻す、もしくは元のテーブルを削除することで、
結果的にDELETEと同じ操作を実現できる。
【元のテーブルに戻すパターン】

-- 削除対象ではないデータをワークテーブルに退避
CREATE TABLE table_temp [NOLOGGING][PARALLEL] AS 
SELECT * FROM table_origin WHERE col1 = 'xxx';
-- 元テーブルのデータを切り捨て
TRUNCATE TABLE table_origin;
-- 退避させていたデータを元テーブルに戻す
INSERT /*+ APPEND */ INTO table_origin SELECT * FROM table_temp;

【元のテーブルを削除するパターン】

-- 削除対象ではないデータをワークテーブルに退避
CREATE TABLE table_temp [NOLOGGING][PARALLEL] AS 
SELECT * FROM table_origin WHERE col1 = 'xxx';
-- 元テーブルを削除（Recycle Bin[=ゴミ箱]を経由せずに直接削除するにはPURGE句を付ける）
DROP TABLE table_origin [PURGE];
-- ワークテーブルの名称を元テーブルの名称へ変更
RENAME table_temp TO table_origin; -- PRIMARY KEYやINDEX, COMMENTは別途追加が必要

パラレルDML

INSERT文の高速化でも触れたが、
1つのクエリに対して複数のプロセスで並列に処理できる機能。
パラレルDMLを利用するためには「パラレルを有効にすること」と
「明示的にパラレルを利用すること」を宣言しなければならない。

-- ヒント句を利用
DELETE /*+ ENABLE_PARALLEL_DML PARALLEL */ FROM table1;
-- セッションで有効化 ＋ PARALLELヒント（カッコ内の数字は並列度）
ALTER SESSION ENABLE PARALLEL DML;
DELETE /*+ PARALLEL(10) */ FROM table1 WHERE col1 = 'xxx';
脚注

  1. DDL ... データ定義言語（Data Definition Language）。
    　　　　CREATE,DROP,ALTERなどのオブジェクトの生成や変更・削除を行う命令のこと。 ↩︎

  2. UNDO ... データベースに対して行なった操作をUNDO(取り消し)するためのデータ。
    　　　　　（＝更新前のデータそのもの） ↩︎