【Oracle】UPDATE,INSERT,DELETE文の高速化
この記事でわかる・できること
- OracleでUPDATE,INSERT,DELETE文を高速化する方法
この記事の対象者
- OracleでSQLを書く人全般
動作環境・使用するツールや言語
- Windows 10 Pro 22H2
- Oracle 12cR2 Enterprise Edition
はじめに
UPDATE,DELETE,およびINSERT INTO 【テーブル名】 SELECT ～では操作対象のレコードを特定してから更新を行います。
操作対象のレコードを特定するところは基本的に前回のSELECT文と変わらないのでそちらを参照してください。
共通
ループ処理をできるだけ避ける
よくあるのが複数レコードを更新するために「Oracleへの接続・SQLで１レコード更新・切断」の一連の処理をループで繰り返し実行しているケースです。
接続や切断は時間がかかる上、リソースに負荷がかかるのでできるだけまとめて実行しましょう。
SQL実行もPL/SQLでループ処理をするよりまとめて実行した方が高速です。ただしデータ量とメモリによってはUNDO領域が不足してエラーになったり、ディスクに書き出して低速になったりするので必ずしもこの限りではありません。
そのような場合でもループではなくデータの範囲を分割して複数回に分けて実行するという方法もあります。
不要なインデックスを削除
データを更新するとインデックスブロックも連動して更新されます。
このためインデックスが大量にあると更新が遅くなります。
特に列の多い複合インデックスやビットマップインデックスは非常に遅くなります。
あまり使われていないインデックスは確認の上削除しましょう。
インデックスの使用状況を監視することが可能です。
--インデックスの監視を開始
ALTER INDEX EMP_NAME_IX MONITORING USAGE;
--SQL文を実行
SELECT
employee_id,
last_name,
first_name,
department_name
FROM
employees e,
departments d
WHERE
e.department_id = d.department_id
AND last_name LIKE 'T%'
ORDER BY
last_name;
--監視の終了
ALTER INDEX EMP_NAME_IX NOMONITORING USAGE;
--結果の確認
SELECT * FROM DBA_OBJECT_USAGE;
結果は下記のようになります。USEDがYESなら使用しています。
ある程度の期間を監視して使われていないようなら削除を検討しましょう。
ただし月次や年次の処理でしか使っていないような使用頻度の低いインデックスがあるかもしれないので十分注意してください。
中間テーブルの使用
１つのSQL文が巨大で複雑になってくると様々なデメリットがあります。
- Oracleの実行計画が適切でなくなる
- 可読性が下がる
- 複数の処理が組み合わさっているので改修やテストがしづらい
速度の観点から言えば、Oracleは処理手順、メモリ、インデックス、統計情報などを考慮してできるだけ効率的な処理をするように実行計画を作成していますが、SQLが複雑になると適切な実行計画を作成してくれなくなる可能性が高くなります。
人間がヒント句で実行計画を制御することも可能ですが、そもそも可読性が低いので人間も適切なヒント句を与えるのが難しくなります。
このような場合は中間テーブルを作成して処理を分割します。
これはSELECT文でも有効です。
バッチ処理の場合はCTAS（CREATE TABLE AS SELECT）でデータを作成します。
CREATE TABLE 【テーブル名】 NOLOGGING AS SELECT ～;
--一連の処理を実行
DROP TABLE 【テーブル名】;
NOLOGGINGを指定するとREDOログが生成されないので多少高速になります。中間テーブルなので復元は必要ないためREDOログは不要です。
また、PARALLELを指定すると並列処理が可能ですが、場合によっては遅くなったりリソースを食いつぶすこともあるのでご注意ください。
CREATE TABLEではダイレクトパスインサートを使用しています。
ダイレクトパスインサートはサイズの小さいバッファを経由せず直接データファイルに書き込むので高速です。通常のインサートはバッファを経由しています。
ダイレクトパスインサートは色々と制限がありますが、CREATE TABLEの場合は気にしなくて大丈夫です。
・実行直後はCOMMITかROLLBACKをかける必要がある
⇒DDLなので自動的にCOMMITされる
・参照整合性制約などがあると無効化されて通常のインサートになる
⇒NOT NULL制約以外の制約はコピーされない
・表ロックをかける
⇒新規作成なので関係ない
NOT NULL制約以外の制約はコピーされないと書きましたが、その他インデックスや統計情報も空になります。
データを投入する時は制約やインデックスは無い方が高速です。
必要に応じて投入後に作成・収集してください。
中間テーブルは物理ディスクに書き込むのでI/Oが増加するデメリットがあります。
しかし１つのSQLを分割した時に巨大な中間テーブルが作成される場合、大抵メモリに入りきらないので一時表領域に書き出されます。
そのためどちらにしても物理アクセスは必要になる可能性が高いでしょう。
オンライン処理の場合は複数のユーザが同時に使用するためCREATE TABLE AS SELECTやDROP TABLEを発行するわけにはいきません。
この場合は中間テーブルをあらかじめ作成しておいて、あとからINSERTして使うのがいいでしょう。他のユーザと区別するためにユーザIDやセッションIDなどの列が必要になります。
Oracleでは他のセッションとデータを共有しない（定義は共有する）テンポラリテーブルもあります。
オプションでセッション終了時にデータを削除するかトランザクション終了時にデータを削除するか選択できます。
--トランザクション終了時にデータ削除
CREATE GLOBAL TEMPORARY TABLE 【テーブル名】 ～
ON COMMIT DELETE ROWS;
--セッション終了時にデータ削除
CREATE GLOBAL TEMPORARY TABLE 【テーブル名】 ～
ON COMMIT PRESERVE ROWS;
パラレル処理
UPDATE,INSERT,DELETE以外にもSELECT,IMPORT,EXPORT,RMAN,CREATE TABLE,CREATE INDEX,統計情報収集などもパラレル処理が可能です。
DMLはセッション単位かSQL単位でパラレル化できます。
ただしパラレルDMLは内部的にはダイレクトパスインサートを使用しているため、実行後はCOMMITかROLLBACKを実行する必要があります。
RAIDのストライピングやASMなどでストレージを分散しないとあまり効果が出ません。
なおここでのパラレル処理はOracleの機能によるものですが、シャーディングして並列実行したりApache HadoopやSparkを使用するなどやり方は色々あります。
ALTER SESSION ENABLE PARALLEL DML;
パラレル処理を強制する場合はFORCEです。
ALTER SESSION FORCE PARALLEL DML;
INSERT /*+ ENABLE_PARALLEL_DML */ INTO ～;
DBサーバの負荷分散
ストレージの負荷分散もそうですが、DBサーバも負荷分散したいということがあります。
しかし一般的にデータベースは正しいものがただ一つなので、リードレプリカのように読み取りを分散させることはできても書き込みの分散は困難です。
OracleではRACによってノード間でバッファを同期し、スケールアウトできる機能がありますが、構築や保守運用の難易度はかなり高くなっています。
その他、関連性のないデータを分割して各サーバに分けたりNoSQLを利用するということも考えられます。
INSERT文の高速化
ダイレクトパスインサート
中間テーブルの作成のところでも書きましたが、サイズの小さいバッファを経由せずデータファイルに直接書き込むので高速です。
INSERT /*+ APPEND */ INTO 【テーブル名】 SELECT～
・実行直後はCOMMITかROLLBACKをかける必要がある
・参照整合性制約などがあると無効化されて通常のインサートになる
・表ロックをかける
・HWMより上にインサートする
など様々な制約があるのでご注意ください。
CREATE TABLE AS SELECTと同様、NOLOGGINGを指定することもできます。
SQL Loader
CSVなどテキストデータからデータを投入する場合はSQL文よりSQL Loaderが有効です。
sqlplusの接続と同じような構文でCONTROLのところに制御ファイルを指定します。
SQLLDR 【ユーザ名】/【パスワード】@【サービス名】CONTROL=SQLLDR.ctl
制御ファイルの書き方は公式のドキュメントを参照してください。
DIRECT=TRUEを指定していればダイレクトパスインサートになります。
DELETE文の高速化
テーブルの大部分のデータを削除する場合は一度TRUNCATE TABLEでテーブルを空にしてからINSERT文で残りのデータを投入する方法があります。
DELETE文ではダイレクトパスインサートという考え方がないのでINSERT文でダイレクトパスインサートを利用することで高速化できる可能性があります。
--削除対象以外のデータを保存
CREATE TABLE NOLOGGING W_TEMP AS
SELECT * FROM 【テーブル名】
WHERE 【列名】 <> 【削除対象】;
--元表をTRUNCATE
TRUNCATE TABLE 【テーブル名】;
--削除対象以外のデータを戻す
INSERT /*+ APPEND */ INTO 【テーブル名】
SELECT * FROM W_TEMP;
COMMIT;
DROP TABLE W_TEMP;
テーブル間に親子関係があってTRUNCATEできない場合は、一度外部キー制約を削除してから後で戻す方法があります。
UPDATE文の高速化
DELETE+INSERT
DELETEとINSERTを組み合わせ、更新対象の行を削除した後に更新後のデータをダイレクトパスインサートで投入することで高速化できる可能性があります。