津島博士のパフォーマンス講座　第37回　新しいSQLについて

津島博士のパフォーマンス講座　Indexページ ▶▶

 

---

皆さんこんにちは、今年もゴールデンウィークが終わってしまいましたね。今年は少し肌寒い感じの天候でしたが、楽しい連休を過ごすことはできましたでしょうか。
今回はOracle Database 12c（Oracle12c）からの新しいSQLについていくつか説明しようと思います。第36回で説明した「メモリの使用が少ないSQL」の続きとして左相関のSQLも含めて説明しますので、参考にしてください。

1. 左相関について
第36回の「メモリの使用が少ないSQL」では、効果的に索引を使用するようなSQLについて説明しましたが、簡単にSQLを作成することができない場合もあります。特に、副問合せ内でORDER BYを行うような場合などは、表結合とORDER BYの両方で索引を使用して、メモリの使用を少なくするのが難しくなります（これは、通常はテーブルをアクセスするときに、使用する索引が一つだけになるからです）。そこで、それを簡単に行えるようにするのが左相関のSQLになります。
左相関は、FROM句の左側で指定されたテーブルの列などに対して、副問合せ（インライン・ビュー）などから参照することをいいます（右側で指定されたテーブルなどを参照することはできません）。ただし、これまではインライン・ビュー内で指定されていないテーブルをアクセスすることはできないので、以下のように’ORA-00904’エラーが出力されます。

これをOracle12cから以下の句を使用することで、左相関することができるように拡張されています。

• LATERAL句
• CROSS APPLY句とOUTER APPLY句

それでは、それぞれについて説明していきます。

 

(1)LATERAL句
まずは、ANSI準拠のLATERAL句について説明します。
LATERAL句は、ANSI標準に準拠している構文として、以下のように指定することが可能です（WHERE句以外でも参照することが可能です）。

SQL> SELECT * FROM tab1 A, LATERAL(SELECT * FROM tab2 WHERE A.c1 = tab2.c1) B;

これを使用することで、相関しているテーブルの1行ごとに、副問合せを実行することが可能になります（この例では、テーブル’tab1’の1行ごとに、副問合せ’B’を実行するようになります）。つまり、副問合せと索引のネステッド・ループ結合を効果的に行うことができます。
この例のような通常の結合ではビュー・マージされてしまうので、使用するにはビューをマージできないようなインライン・ビューにする必要があります。例えば、上位N件を取得するために、インライン・ビュー内でORDER BYを行うような場合などです。これを上位2件を取得するSQLとして作成すると以下のようになります（左側がLATERAL句を使用したSQL、右側が第24回で説明したROW_NUMBERファンクションを使用したSQLです）。

SQL> SELECT * FROM tab1 A, LATERAL(   2    SELECT *   3    FROM (SELECT * FROM tab2   4          WHERE tab2.c1 = A.c1 ORDER BY tab2.c2)   5    WHERE ROWNUM <= 2);

SQL> SELECT * FROM tab1 A, (   2    SELECT * FROM (   3      SELECT tab2.*,ROW_NUMBER() OVER   4                    (PARTITION BY c1 ORDER BY c2) rn   5      FROM tab2)   6    WHERE rn <= 2) B   7  WHERE B.c1 = A.c1;

 

右側のROW_NUMBERファンクションは、QUERY_PARTITION句のPARTITION BY（連番を振る行グループ）を指定する必要があり少し複雑です。これが左側だとLATERAL句を使用するだけで、インライン・ビュー内でテーブル’tab1’の行ごとに、テーブル’tab2’をORDER BYしてくれます。つまり、大量のソート領域が必要なくなります（このSQLでは上位2件だけ格納されます）。
LATERAL句を使用したSQLの実行計画は、以下のようにビュー’VW_LAT_A18161FF’を作成します。このビュー内でソート処理’SORT ORDER BY STOPKEY’を行っていますが、最小限のソート領域だけで行っていることが分かります（この例の場合は、上位2件を格納するだけなので、4096バイトのメモリ使用量になっています）。

それに対して、ROW_NUMBERファンクションの実行計画は以下のようになります。第36回で説明したように、インライン・ビュー内の処理を行ってから（すべてのデータに対する上位2件を取得してから）結合する必要があるので、大量のソート領域を使用しています（この例の場合は、102Kバイトのメモリ使用量になっています）。

これまでは、このようなソート領域を使用しないようにするにはPL/SQLなどで作成する必要がありましたが、これでSQLだけで同じことが簡単に作成できるようになります。
それから、TABLEコレクション式と同じように、最後に'(+)’を指定することで、左外部結合（左外結合）させることができます。実行計画には、以下のように’NESTED LOOPS OUTER’が出力されます（ビュー’VW_LAT_A18161FF’内は同じなので省略しています）。

 

（２）CROSS APPLY句とOUTER APPLY句
次に、CROSS APPLY句とOUTER APPLY句について説明します。
これもLATERAL句と同じことが可能ですが、左相関を利用したクロス結合と左外部結合を別々に指定する必要があります。クロス結合にはCROSS APPLY句、左外部結合にはOUTER APPLY句を使用して、以下のように指定します（実行計画はLATERAL句と同じになります）。

LATERAL句との違いは、通常のテーブルと同じようにPIVOT句、UNPIVOT句などを指定することも可能なことです（PIVOT句は第24回で説明しましたよね）。 例えば、第36回で説明したテーブル・ファンクション’test_pkg.func01’をOUTER APPLY句とPIVOT句を使用したSQLにすると以下のようになります。このようにPIVOT句と組み合わせることで、dtime1とdtime2を横展開することも一つのSQLで行えます。

SQL> SELECT * FROM sales A OUTER APPLY (
  2    SELECT dt, ROWNUM rn
  3    FROM (SELECT dtime dt FROM sales B 
  4          WHERE B.tenpo = A.tenpo AND B.prod = A.prod AND B.cust = A.cust AND B.dtime < A.dtime
  5          ORDER BY B.dtime DESC)
  6    WHERE ROWNUM <= 2) PIVOT(dt FOR rn IN (1 AS dtime1, 2 AS dtime2))
  7  WHERE dtime = TO_DATE('140101','YYMMDD');

dtime     tenpo  prod   cust   dtime1   dtime2
--------  ------ ------ ------ -------- --------
14-01-01  xxxxx  xxxxx  xxxxx  13-12-31 13-12-30
…

以下は、索引’IX_SALE01’を作成されている場合の実行計画になります。PIVOTについては、実行計画に’SORT GROUP BY NOSORT PIVOT’（ソート処理を行わないPIVOTのためのGROUP BY）と出力されているので、ソート領域を使用する必要がなくなっています。

LATERAL句とCROSS APPLY句およびOUTER APPLY句のどちらを使用しても問題ありませんが、用途に合わせて使い分けるのが良いかと思います。このようにSQLで作成できることが増えたので、パラレル実行が難しいPL/SQLなどに簡単にしないようにしてください。

 

2. その他の新しいSQL
次に、その他の新しいSQLについて説明しましょう。
その他に、以下のSQLが追加されています。これは、これまでも指定できる機能を、別の方法で指定できるように拡張された機能になります。

• FETCH FIRST句およびOFFSET句
• WITHのPL/SQLファンクション
• Oracle固有の外部結合の拡張

それでは、それぞれについて説明していきます。

（１）FETCH FIRST句およびOFFSET句
まずは、ANSI標準に準拠しているFETCH FIRST句とOFFSET句について説明します。 このFETCH FIRST句とOFFSET句は、行制限（上位N件などを取得）するときに使用する新しいSQLで、以下のように指定します。

SELECT … [OFFSET <行数> ROWS] FETCH {FIRST|NEXT} [<行数>|<パーセント> PERCENT] ROWS {ONLY|WITH TIES} ;

これは、第24回で説明したROW_NUMBERファンクションなどでも行うことができるので、どのように異なるか分かりやすいように、実行計画を見ながら説明していきます。
左側がROW_NUMBERファンクション、右側がFETCH FIRST句を使用したSQLになります。実行計画を見るとROW_NUMBERファンクションを使用した場合と変わらないことが分かります（右側は、ROW_NUMBERファンクションの結果を “from$_subquery$_002”.”rowlimit_$$_rownumber”として実行しています）。見ての通り新しいSQLの方がシンプルで視認性が良いように思います。

SQL> SELECT * FROM (   2   SELECT tab2.*, ROW_NUMBER() OVER(ORDER BY c2) rn   3   FROM tab2) WHERE rn <= 5; 実行計画 —————————————— | Id  | Operation                | Name  | —————————————— |   0 | SELECT STATEMENT         |       | |*  1 |  VIEW                    |       | |*  2 |   WINDOW SORT PUSHED RANK|       | |   3 |    TABLE ACCESS FULL     | TAB2  | —————————————— Predicate Information (identified by operation id): —————————————————    1 – filter(“RN”<=5)    2 – filter(ROW_NUMBER() OVER ( ORDER BY “C2”)<=5)

SQL> SELECT * FROM tab2 ORDER BY c2 FETCH FIRST 5 ROWS ONLY; 実行計画 —————————————– | Id  | Operation                | Name | —————————————– |   0 | SELECT STATEMENT         |      | |*  1 |  VIEW                    |      | |*  2 |   WINDOW SORT PUSHED RANK|      | |   3 |    TABLE ACCESS FULL     | TAB2 | —————————————– Predicate Information (identified by operation id): —————————————————    1 – filter(“from$_subquery$_002″.”rowlimit_$$_rownumber”<=5)    2 – filter(ROW_NUMBER() OVER ( ORDER BY “TAB2”.”C2″)<=5)

OFFSET句を指定することで、先頭以外からも取得することが可能です（スキップする行数を指定できます）。以下のSQLは、3行目から3行を検索します。これも実行計画を見ると何行目からかを求めるために'”from$_subquery$_002″.”rowlimit_$$_rownumber”>2’を行っていますが、ROW_NUMBERファンクションを使用した場合と変わらないことが分かります（’Predicate Information’以外は同じなので省略しています）。

ただし、何行目までかを求めるために’CASE WHEN (2>=0) THEN 2 ELSE 0 END +3‘を行っています（’OFFSET 2 ROWS’と’FISRT 3 ROWS’の値が入るようになっています）。つまり、以下のようなSQLを実行していることになります。

FETCH FIRST句のONLYの代わりにWITH TIESを指定すると、最後の行のORDER BYキーと同じ値の行がすべて出力されます（最後の同順位のデータをすべて出力します）。これの実行計画を見るとRANKファンクションを使用しています（これも第24回で説明しましたよね）。

検索する行数をPERCENT（<パーセント>）でも指定することができます。これの実行計画を見るとファンクションは使用していなく、合計行数'”from$_subquery$_002″.”rowlimit_$$_total”‘からパーセントの行数を求めているだけになっています。

ただし、これはファンクションだけでは行うことができいので、以下のように行う必要があります。

SQL> SELECT * FROM (SELECT tab2.*,ROW_NUMBER() OVER(ORDER BY c2) rn FROM tab2) A, 
  2                (SELECT COUNT(*) total FROM tab2) B
  3  WHERE rn <= CEIL(total *50/100);

新しいSQLの方がシンプルで視認性が良いように思いますが、これについては使いやすい方を使用するのが良いと思います。

（２）WITHのPL/SQLファンクション
次に、WITH句のPL/SQLファンクションについて説明します。
第11回で説明した「ユーザ定義ファンクション」は、これまでPL/SQLファンクションで作成する必要がありましたが、Oracle12cから副問合せのWITH句に定義して、通常のPL/SQLファンクションとして使用することができます（PL/SQLファンクションを副問合せのように作成するのが可能になるということです）。ただし、第11回で説明したパラレル実行させるためのPARALLEL_ENABLEは指定する必要ありません（指定するとエラーになります）。
これによって、SQLを実行するために必要なロジック（複雑な計算など）をSQLだけで行うことが可能になるので、読取り専用データベースなどでも使用しやすくなります。
以下に、この機能を使用して簡単な例（年齢を計算するSQL）を作成してみました。このように複雑な計算をファンクション化することで、SELECT文を複雑にしないようにすることも可能です（私は、この使い方が非常に気に入っています）。

SQL> WITH
  2    FUNCTION get_age(birthday DATE) RETURN NUMBER IS
  3      wage BINARY_INTEGER;
  4    BEGIN
  5      wage := TO_CHAR(SYSDATE,'YYYY') - TO_CHAR(birthday,'YYYY');
  6      IF TO_CHAR(SYSDATE,'MMDD') < TO_CHAR(birthday,'MMDD') THEN
  7        wage := wage - 1;
  8      END IF;
  9      RETURN wage;
 10    END;
 11  SELECT get_age(birthday) FROM tab01;
 12  /

 

複数のSQLで共有するようなファンクションであればPL/SQLファンクションを使用して、そうでなければこの機能を使用するのが良いと思います。

（３）Oracle固有の外部結合の拡張
次に、Oracle固有の外部結合の拡張について説明します。
左側に複数のテーブルがある左外部結合を行う場合に（以下の例のように、テーブル’t1’とテーブル’t2’にテーブル’t3’を外部結合する場合などです）、これまではOracle固有の外部結合を行うと以下のように’ORA-01417’エラーが出力されます。

SQL> SELECT * FROM t1, t2, t3 WHERE t1.c1 = t2.c1 AND t1.c2 = t3.c2 (+) AND t2.c3 = t3.c3 (+);

ORA-01417: 表が少なくとも1つの他の表に外部結合されている可能性があります。

そのため、以下のようにANSI準拠の外部結合を使用する必要がありました。

SQL> SELECT * FROM t1 JOIN t2 USING(c1) LEFT JOIN t3 ON (t1.c2 = t3.c2 AND t2.c3 = t3.c3);

Oracle12cからはOracle固有の外部結合でも実行できるように拡張されました。実行計画も以下のように同じになるので、使いやすい方を使用できます。これはANSI準拠のSQLを使用すれば良いので、あまり影響はないと思いますが、知っておいた方が良いかと思い載せておきました。

SQL> SELECT * FROM t1, t2, t3 WHERE t1.c1 = t2.c1 AND t1.c2 = t3.c2 (+) AND t2.c3 = t3.c3 (+);

実行計画
------------------------------------
| Id  | Operation           | Name |
------------------------------------
|   0 | SELECT STATEMENT    |      |
|*  1 |  HASH JOIN OUTER    |      |
|*  2 |   HASH JOIN         |      |
|   3 |    TABLE ACCESS FULL| T1   |
|   4 |    TABLE ACCESS FULL| T2   |
|   5 |   TABLE ACCESS FULL | T3   |
------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T2"."C3"="T3"."C3"(+) AND "T1"."C2"="T3"."C2"(+))
   2 - access("T1"."C1"="T2"."C1")

 

3. おわりに
今回はOracle12cの新しいSQLについて説明しましたが、少しは参考になりましたでしょうか。また機会があれば他のことについても説明したいと思います。これからもよろしくお願いします。
それでは、次回まで、ごきげんよう。

 

---

ページトップへ戻る▲ 

 

津島博士のパフォーマンス講座　Indexページ ▶▶