SQLチューニング（第45回）「ROWNUMの理解と関連SQL性能問題の理解」（5/6回）

今回は、「ROWNUMの理解と関連SQL性能問題の理解」第5回目です。では、始めましょう。

---

12.５　ROWNUM<=1は常に速くない

　ROWNUM <= 1条件は普通SQLで抽出されるデータのうちWhere節条件を満足する1件だけ迅速に抽出しようと思う時に使います。 しかしROWNUM <= 1条件があるからと言って、SQL実行速度が常にはやい訳ではありません。

　もし、Where節に使われた条件カラムにインデックスがあり抽出されるデータが多くて、1件を速かに抽出できるならば早く実行されるでしょう。

　しかし、インデックスで多くのデータが抽出されるが、Where節にある別の条件カラムがインデックスに含まれおらず、NUM_DISTINCT値が高く最終抽出件数が少ないならば、ROWNUM <= 1条件は絶対に高速に実行できません。

なぜなら、インデックスで抽出されたすべてのデータに対してテーブルにあるデータなのかを確認してみてこそWhere節条件を満足するデータなのか分かるためです。 これは結局すべてのデータを処理したのと同じことであるから、Where節にROWNUM <= 1条件があるが、はやい照会にならないのです。

　それならROWNUM <= 1条件をWhere節に適用したSQL中はやい照会と遅い照会に対して確認してみることにしましょう。

Script. テスト データ生成用


drop table ROWNUM_T2 purge ;

■生成要件

-テーブル データ件数は900,000ロー
-カラムC1は値の種類が100,000種類(1 ～ 100,000)
-カラムC2は値の種類が26種類

■テーブル生成

create table rownum_t2
as
select LEVEL as c1, CHR(65+MOD(LEVEL,26)) as c2, LEVEL+99999 as c3
from DUAL


connect by LEVEL <= 100000 ;

■追加データ入力

BEGIN
      FOR i IN 1..8 LOOP
           INSERT INTO ROWNUM_T2 SELECT * FROM ROWNUM_T2;
           COMMIT;
      END LOOP;
END;
/

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

create index rownum_t2_idx_01 on rownum_t2 ( c2 ) ;
create index rownum_t2_idx_03 on rownum_t2 (c2,c3) ;

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

　テスト[1]. はやい照会になる場合(条件がなかったりインデックス構成カラムだけ存在する時)

[条件がない場合]


SELECT *
  FROM ROWNUM_T2
 WHERE ROWNUM <= 1 ;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.03          7          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.03          7          4          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  COUNT STOPKEY (cr=4 pr=7 pw=0 time=31640 us)
      1   TABLE ACCESS FULL ROWNUM_T2 (cr=4 pr=7 pw=0 time=31633 us)

[インデックス構成カラムだけ存在する場合]

SELECT *
  FROM rownum_t2
 WHERE c2 IN ('P', 'S', 'H')
   AND rownum <= 1 ;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          5          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.01          0          5          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  COUNT STOPKEY (cr=5 pr=0 pw=0 time=164 us)
      1   INLIST ITERATOR  (cr=5 pr=0 pw=0 time=159 us)
      1    TABLE ACCESS BY INDEX ROWID ROWNUM_T2 (cr=5 pr=0 pw=0 time=156 us)
      1     INDEX RANGE SCAN ROWNUM_T2_IDX_01 (cr=4 pr=0 pw=0 time=135 us)

　テスト[2]. 遅い照会になる場合(ROWNUM_T2_IDX_01構成カラムはC2カラムである)

SELECT /*+ INDEX(ROWNUM_T2 ROWNUM_T2_IDX_01) */  *
  FROM ROWNUM_T2
 WHERE c2 IN ('P', 'S', 'H')
   AND c1 = 1004
   AND ROWNUM <= 1 ;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Pars         1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      7.42      36.50      54656     190562          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      7.44      36.50      54656     190562          0           0

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  COUNT STOPKEY (cr=190562 pr=54656 pw=0 time=36505086 us)
      0   INLIST ITERATOR  (cr=190562 pr=54656 pw=0 time=36505080 us)
      0 TABLE ACCESS BY INDEX ROWID ROWNUM_T2 (cr=190562 pr=54656 …)
2953728 INDEX RANGE SCAN ROWNUM_T2_IDX_01 (cr=12155 pr=6759 …)

　テスト[2]のトレース結果を見れば、インデックスでC2カラム条件で2,953,728件を抽出しているのがわかります。 そしてC1カラム条件を処理するためにテーブルをアクセスした後データを取り除きます。 だが、残念なことに上記SQLの最終抽出結果は0件です。 すなわち、データを全部読む時までROWNUM <= 1を満足できなくて全体データを処理したのです。 したがって上記SQLはROWNUM <= 1条件があるが早く実行されることができないことになります。

　それではテスト[2]の性能改善のためには既存のC2カラムで構成されていたインデックスをC2 + C1カラムで構成された結合インデックスに変更しなければなりません。 改善後トレース結果を確認してみましょう。

　新しいインデックス生成後SQL再実行(インデックス構成カラム:C2 —> C2,C1)

CREATE INDEX rownum_t2_idx_02 ON rownum_t2 ( c2, c1 ) ;

SELECT /*+ INDEX(ROWNUM_T2 ROWNUM_T2_IDX_02) */  *
  FROM ROWNUM_T2
 WHERE c2 IN ('P', 'S', 'H')
   AND c1 = 1004
   AND ROWNUM <= 1 ;
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.06          6          9          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.07          6          9          0           0

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  COUNT STOPKEY (cr=9 pr=6 pw=0 time=68691 us)
      0   INLIST ITERATOR  (cr=9 pr=6 pw=0 time=68684 us)
      0    TABLE ACCESS BY INDEX ROWID ROWNUM_T2 (cr=9 pr=6 pw=0 time=68670 us)
      0     INDEX RANGE SCAN ROWNUM_T2_IDX_02 (cr=9 pr=6 pw=0 time=68640 us)

前で調べてみた通りROWNUM <= 1条件を持つSQLの性能を早く実行するためには、Where節の条件に適切なインデックスが構成されていなければなりません。 したがってこれからはSQLにROWNUM <= 1条件があることだけで実行速度が速いだろうという判断が間違えるということを覚えておくことが必要です。

---

　いかがでしたでしょうか？次回は、「ROWNUMの理解と関連SQL性能問題の理解」の最終回です。ご期待ください。では　See youu ^^