皆さんこんにちは、だいぶ寒くなってきましたが体調はいかがでしょうか。私は多少寒気がするのですが、どうにか頑張っております。
今回は、以前に説明して好評だったので「パフォーマンスの良いSQL文ついて」の続きを説明しようと思いますので、参考にして下さい。
■1．SQL文について
まずは、続きで良いSQL文についてですが、以前にも言いましたが無駄なリソースをできるだけ使用しないことです。無駄なリソースを使用しないとは、同じ処理や不必要な処理をできるだけ実行しないことです。そのため、以下のような無駄な処理を行わないや視認性の良いSQL文を記述するのが良いと思います。視認性が良いことでミスを防げたり無駄な処理を見つけることができたりします。
今回はこのような作成する上での役に立つポイントをまとめてみました。
- 無駄なソート処理をしない
- ファンクションを使用する
- 繰り返し使用する副問合せはWITH句を
- 中間テーブルは一時表で
- 更新可能な結合ビューについて
それでは、それぞれについて説明していきます。
（1）無駄なソート処理をしない
第9回「良いSQL」でも説明しましたが、ソート処理は負荷が大きい処理なので少ないデータにするか、できることなら行わないようにすることです。そのため、無駄な（意味のない）ソート処理をしないようにして下さい。ORDER BY句だけでなく、暗黙的にソート処理が行われる指定（DISTINCT、UNION、INTERSECT、MINUSなど）について本当に必要なのか検討して下さい。このような指定のことを知っていないと知らずにソート処理を行われていることもあるので注意して下さい。
例えば、重複データが無いのにDISTINCTを指定していると以下のように一意性処理（HASH UNIQUE）が行われます（一意性処理は、以前のバージョンではソート処理のみで行っていましたが、現在はより負荷の軽いハッシュ処理でも行います）ので、この処理は無駄なものになります。このとき、一意索引があると一意性処理は行われません。
SQL> SELECT DISTINCT * FROM tab1; 実行計画 -------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH UNIQUE | | | 2 | TABLE ACCESS FULL | TAB1 |
UNIONも以下のように一意にするためにソート処理（SORT UNIQUE）を行いますので、一意にする必要がない場合はUNION ALL（以下のようにソート処理がない）にすることを検討して下さい。
SQL> SELECT * FROM tab1 UNION SELECT * FROM tab2; 実行計画 ------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT UNIQUE | | | 2 | UNION-ALL | | | 3 | TABLE ACCESS FULL | TAB1 | | 4 | TABLE ACCESS FULL | TAB2 |
SQL> SELECT * FROM tab1 UNION ALL SELECT * FROM tab2; 実行計画 ------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | UNION-ALL | | | 2 | TABLE ACCESS FULL | TAB1 | | 3 | TABLE ACCESS FULL | TAB2 |
（2）ファンクションを使用する
同一行内のデータによる演算をするような処理であれば殆どの処理をSQL文で記述できます。複雑な処理の場合でも無暗にストアードプロシージャー等にせず、ユーザ定義ファンクションを検討すると良いと思います。ファンクションを使用して記述することによって、以下のようにSELECT文などから直接呼び出すことができ、視認性が良くなります。そして、多重処理（パラレル処理）などもし易くなります。
SQL> SELECT fn_01(c1,c2) FROM tab1 WHERE ....;
CREATE OR REPLACE FUNCTION fn_01(
a1 IN DATE,
a2 IN VARCHAR2
) RETURN NUMBER parallel_enable deterministic result_cache
IS
.....
パラレル処理でユーザ定義ファンクションを使用するときは、ファンクションをパラレル実行させるための記述（parallele_enable）が必要になりますので注意して下さい。その他にもdeterministic（再実行されるのではなく、以前に計算した結果の使用が可能なかぎり利用する）、result_cache（ファンクションの結果をキャッシュする）などもパフォーマンスに効果がある場合があるので知っておくと便利かと思います。
（3）繰り返して使用する副問合せはWITH句を
SQL文の中で同じ問合せを何回か実行しなければいけない場合があると思います。例えば、部門単位の売り上げを求めて、その売り上げの平均よりも少ない部門をすべて出力するような場合などがあります。そのような場合には、以下のようなSQL文で行うと思います。これは性能（テーブル’売上表’を2回アクセスしている）と視認性（同じ副問合せが2回記述されている）の問題があると思います。
上記SQL文の例では副問合せは2回しか使用していませんが、もっと多く使用する場合もあると思います。このように同じ副問合せを複数回使用するような場合は以下のSQL文のようにWITH句を使用すると良いでしょう。実行計画を見ていただけると分かるように、内部的にTEMPテーブル（SYS_TEMP_0FD9D6607_203E5B）を作成して行っています。
以下のように中間テーブルを作成すれば同じようにできますが、視認性の問題や管理などで面倒になると思いますので、WITH句の使い方を覚えておくと便利かと思います。だたし、他のSQL文でも副問合せ結果を使用する場合には中間テーブルを作成するのが良いかと思います。
SQL> create table w_abc AS select 部門,sum(売上) 部門売上 from 売上表 group by 部門; SQL> select * from w_abc where 部門売上 < (select avg(部門売上) from w_abc); SQL> drop table w_abc;
（4）中間テーブルは一時表で
バッチ処理などで多重実行を行う時に困るのが中間テーブルを作成している時だと思います。単純に実行すると同じテーブルにアクセスしてしまい正しく動作しなくなりますので、それぞれで別々のテーブルにアクセスするようにする必要があります。これが複雑になってしまいパラレル実行を諦めたりするケースも有るかと思います。そのような場合には一時表（GLOBAL TEMPORARY TABLE）を使用すると良いでしょう。
一時表の作成の際には、
- トランザクション固有（DELETE ROWS：commitが行われると格納されているデータが削除される）
- セッション固有（PRESERVE ROWS：セッションの終了時に格納されているデータが削除される）と指定できます。
データを格納したセッションからしか参照することができなくなるので（テーブル内のデータをセッション毎に区別してくれます）、先ほどの別テーブルにするような処理が不要になります。また、一時表を使用するとREDOログの出力を削減できる（UNDOのためのREDOログは出力される）のもメリットだと思います。パラレルDMLを使用できないときはこれを使用すると良いと思います。
SQL> CREATE GLOBAL TEMPORARY TABLE t_tab1 (....) ON COMMIT [DELETE ROWS | PRESERVE ROWS];
（5）更新可能な結合ビューについて
更新可能な結合ビューについて説明します。これは性能というよりは使用方法の説明になります。結合したビューを更新する場合には、すべての列が更新できない場合もありますので注意が必要です。例えば、以下のようなビューを更新しようとするとエラー「ORA-01779」になります。これは、結合ビューで更新可能でない列（キー保存表でない列）を更新しようとしたからです。キー保存表とは、結合後でも主キーが存在している表をいいます（以下の例ですとempテーブルの主キーempnoは一意性は存在するが、deptテーブルの主キーdeptnoは一意性は失われているので更新すると複数行が対象になり意図しないデータが更新されてしまう可能性があるからです）。以前のバージョンではBYPASS_UJVCヒント文を使用すると実行できたのですが、現在は意図しないデータが更新されてしまうために使用できなくなりました（本ヒント文は無視されます）ので注意して下さい。これはインライン・ビューの場合でも同様です。あるテーブルのデータを使用して更新するような処理はよく行われたりすると思いますので注意して下さい。
SQL> CREATE VIEW v_empdept AS 2 SELECT A.empno, A.ename, B.depno, B.dname, B.loc FROM emp A,dept B WHERE A.deptno = B.deptno; SQL> UPDATE v_empdept SET loc = '大阪' WHERE ename = '鈴木'; ORA-01779: キー保存されていない表にマップする列は変更できません。
結合ビューの列が更新可能かどうかは以下のSQL文で確認することができます（updatable列がYESのものが更新可能です）。
このような更新の場合は以下のようにMERGE文で行うことで可能です。また、視認性も良くなります。MERGE文はUPDATEとINSERTを条件によって行ってくれるだけではなく、条件によってUPDATEだけを行うような使い方もできるので、知っておくと便利かと思います。
SQL> MERGE INTO dept B 2 USING (SELECT deptno FROM emp WHERE ename = '鈴木') A 3 ON (A.deptno = B.deptno) 4 WHEN MATCHED THEN UPDATE SET loc = '大阪';
■2. PL/SQLについて
最後に、どうしても一つのSQL文にできない処理もあると思いますので、その場合はPL/SQLプロシージャで行うことになると思いますが、それについても以下のような参考になることを少し説明します。
- バルク処理を行う
- パラレル処理について
それでは、それぞれについて説明していきます。
（1）バルク処理を行う
バッチ処理などをPL/SQLプロシージャで作成する場合には、大量のデータを処理すると思いますので、できるだけ以下のようにバルク処理（FORALL,BULK COLLECT）を行うようにして下さい。これによりSQL文を実行する回数を削減でき高速に実行することができますので、知っておくと便利かと思います。
FORALL i IN l_c1.FIRST..l_c1.LAST DELETE FROM tab1 WHERE c1 = l_c1(i); OPEN c1; LOOP FETCH c1 BULK COLLECT INTO vtab01 LIMIT 200; EXIT WHEN c1%NOTFOND; END LOOP; CLOSE c1; FORALL i IN 1..l_count INSERT INTO tab1 VALUES (l_c1(i),l_c2(i),l_c3(i),...);
このときに、INSERT文で第10回「パーティションについて」に説明したダイレクト・パス・インサートを実行する場合にはAPPENDヒントでは動作しませんので注意して下さい。このようなVALUES句が指定されているINSERT文には、Oracle Database 11gR2からのAPPEND_VALUESヒントを指定する必要があります。以下のように使い分ける必要があります。
INSERT /*+ APPEND */ INTO tab1 SELECT * FROM tab2; INSERT /*+ APPEND_VALUES */ INTO tab1 VALUES (....);
（2）パラレル処理
何回か説明したと思いますが、バッチ処理を速くするためにはパラレルで実行する必要があります。PL/SQLプロシージャーでバッチ処理を行う場合に、パラレル実行するのは作り込みが大変だったりしますので（ファンクションは1レコード毎に実行されるのでパラレル化は簡単ですが、プロシージャーは1回の実行ですべてのデータを処理するのでパラレル化は難しいため、シリアルで実行するなどのことになったりします）、ここでは簡単な（PL/SQLで提供されている）方法を説明します。
Oracle Database 11gR2からのDBMS_PARALLEL_EXECUTEを使用することでパラレル実行することができます。これは、アクセスするテーブルのデータを指定したチャンク単位に（ROWIDまたは列のデータ範囲で分割して）パラレル実行してくれるものです。これを自分で作り込むとなると大変だと思いますので、利用できるようでしたら使用して下さい。以下に使用例を載せておきますので参考にして下さい。
/* tab1から指定ROWIDのデータを検索して、データ処理後にtab2へINSERTする。 */
CREATE OR REPLACE PROCEDURE test_batch(s_rowid rowid,e_rowid rowid)
IS
TYPE ttab1 IS TABLE OF tab1%ROWTYPE INDEX BY BINARY_INTEGER;
vtab1 ttab1;
....
CURSOR c_tab1 IS
select * from tab1 where ROWID between s_rowid and e_rowid and ...;
begin
OPEN c_tab1;
loop
FETCH c_tab1 BULK COLLECT INTO vtab1 LIMIT 50;
EXIT WHEN vtab1.COUNT=0;
....
FORALL i IN 1..vtab1.COUNT
INSERT INTO tab2 VALUES vtab1(i);
end loop;
CLOSE c_tab1;
end;
/
/* 行数"chks"でtab1を分割してtest_batchをパラレルで実行する。 */
CREATE OR REPLACE PROCEDURE parallel_job(chks in number)
IS
l_sql_stmt VARCHAR2(1000);
l_try NUMBER;
l_status NUMBER;
begin
DBMS_PARALLEL_EXECUTE.CREATE_TASK('test_task');
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('test_task','test','tab1',TRUE,chks);
l_sql_stmt := 'BEGIN test_batch(:start_id,:end_id); END;';
DBMS_PARALLEL_EXECUTE.RUN_TASK('test_task',l_sql_stmt,DBMS_SQL.NATIVE);
l_try := 0;
l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('test_task');
WHILE(l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED)
LOOP
l_try := l_try + 1;
DBMS_PARALLEL_EXECUTE.RESUME_TASK('test_task');
l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('test_task');
END LOOP;
DBMS_PARALLEL_EXECUTE.DROP_TASK('test_task');
end;
/
■3. おわりに
今回は第9回「良いSQL」に続きSQL文のノウハウについて説明しました。また機会があれば他のことについても説明したいと思います。次回も頑張りますのでよろしくお願いします。
それでは、次回まで、ごきげんよう。