SQLチューニング（第46回）

「ROWNUMの理解と関連SQL性能問題の理解」（6/6回）

今回のSQLチューニングは「 ROWNUMの理解と関連SQL性能問題の理解 」の最終回となります。
「 インラインビューにROWNUMを追加する時は注意しよう 」と題してお送りします。

では、早速始めて行きましょう。

12.6　　インラインビューにROWNUMを追加する時は注意しよう

インラインビューにROWNUMを使用すると、Optimizerは実行計画を確立するときに、まずインラインビューのすべてのデータにROWNUMシーケンスを付与してから結合接続を行う必要があるため、ビュー外の条件がビュー内に侵入してSQLを解釈するQuery Transformation の種類の一つであるView Predicating機能を発揮できなくなります。
この場合、パフォーマンスの問題が発生する可能性があるため注意してください。 以下は、ビュー内にROWNUMを追加した場合とそうでない場合の例です。

Script. テスト データ用


drop table rownum_t3 purge ;

< ROWNUM_T3 >

■ 生成要件

- ROWNUM_T2は既存テーブル使用
- ROWNUM_T3テーブルのデータは合計100,000ロー
- C1課C3はUnique下やC2は26種類値が存在する。

■ テーブル生成

create table rownum_t3
as
SELECT level AS c1, chr(65+mod(level,26)) AS c2, level+99999 AS c3
from DUAL


CONNECT BY level <= 100000 ;

■ 各カラムにインデックス生成および統計情報収集

CREATE INDEX rownum_t3_idx_01 ON rownum_t3 (c1) ;

EXEC dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'rownum_t3',cascade=>TRUE,estimate_percent=>100) ;

SQL[1]. インライン ビューにROWNUMを追加した時

var b1 varchar2(2)
var b2 number
exec :b1 := 'A';
exec :b2 := 102530;

SELECT t3.*, t2.*
  FROM ROWNUM_T2 t2,
       ( SELECT ROWNUM rnum, rownum_t3.*
           FROM ROWNUM_T3 ) t3
 WHERE t2.c1 = t3.c1
   AND t2.c2 = :b1
   AND t2.c3 = :b2 ;

--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  |  Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |     5 |    225 |   315   (1)| 00:00:04 |
|*  1 |  HASH JOIN                    |                  |     5 |    225 |   315   (1)| 00:00:04 |
|   2 |   TABLE ACCESS BY INDEX ROWID | ROWNUM_T2        |     5 |     55 |   259   (0)| 00:00:04 |
|*  3 |    INDEX RANGE SCAN           | ROWNUM_T2_IDX_03 |   256 |        |     3   (0)| 00:00:01 |
|   4 |   VIEW                        |                  |   100K|   3320K|    54   (2)| 00:00:01 |
|   5 |    COUNT                      |                  |       |        |            |          |
|   6 |     TABLE ACCESS FULL         | ROWNUM_T3        |   100K|   1074K|    54   (2)| 00:00:01 |
--------------------------------------------------------------------------------------------------

SQL[2]. インライン ビュー外部にROWNUMを追加した時

var b1 varchar2(2)
var b2 number
exec :b1 := 'A';
exec :b2 := 102530;

SELECT rownum,
       t3.*, 
       t2.*
  FROM rownum_t2 t2,
       ( SELECT *
           FROM rownum_t3  ) t3
 WHERE t2.c1 = t3.c1
   AND t2.c2 = :b1
   AND t2.c3 = :b2 ;

---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             |  Rows | Bytes | Cost (%CPU)|  Time    |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |     5 |   110 |   269   (0)| 00:00:04 |
|   1 |  COUNT                         |                  |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID  | ROWNUM_T3        |     1 |    11 |     2   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |                  |     5 |   110 |   269   (0)| 00:00:04 |
|   4 |     TABLE ACCESS BY INDEX ROWID| ROWNUM_T2        |     5 |    55 |   259   (0)| 00:00:04 |
|*  5 |      INDEX RANGE SCAN          | ROWNUM_T2_IDX_03 |   256 |       |     3   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN           | ROWNUM_T3_IDX_01 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

SQL[1],SQL[2]は同じデータを抽出するがROWNUMの実行位置により性能に大きい差を見せています。

従って、単純にインラインビューの結果セットに順番を付与するために使われるROWNUMを使う場合には、
ROWNUMの使用位置を慎重に考慮しなければなりません。注意しましょう。

●　まとめ

---

「 ROWNUMの理解と関連SQL性能問題の理解 」について、全6回のシリーズでお届けしてきました。どうでしたか？
ROWNUMとは、ユーザーの利便性とパフォーマンス向上を目的としてOracleが提供する仮想列のことです。
作成者の意図を正確に把握して使用することで、パフォーマンスの向上に繋げることができます。
しかしそれとは逆に、意図を正確に把握せずに使用すると、パフォーマンスに悪影響を及ぼすことがあります。
更には、SQLを構成している各要素が「SQL内でどのような役割を果たしているか？」を正確に理解して使用できれば、
その効果を最大化することができるはずです。

そのため、ROWNUMがSQLで実行する役割を理解し、他の条件（Where節、Order By節など）と一緒に使用されるときに
どのような順序で解釈されるのか？を先行して理解しなければ、SQLを効率的に作成することができません。
今回のテーマで説明した内容を踏まえてROWNUMについてよく理解し、効率的に使用できることを願っています。

SQLチューニングブログ（第46回）　終

　今回の Oracle SQLチューニングブログ はいかがでしたか？

　次回からは「画面PAGINATION処理する時これは守ろう」を7回シリーズでお送りします。

---

「 画面PAGINATION処理する時これは守ろう 」の各論に入る前の概論

---