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

いよいよ、「ROWNUMの理解と関連SQL性能問題の理解」第4回目です。では、早速始めることとします。

---

12.４　INDEX_DESCとROWNUM<=1を共に使うのをやめよう

　特定カラムの最大値または、最小値を抽出しようとする時、SQL作成方法は色々な方式が考えられます。 この中でも最近まで最も頻繁に使われている方式はINDEX_DESC (またはINDEX _ASC)ヒントとROWNUM <= 1条件を共に使用することです。

　この方法はインデックスのデータが整列しているという特性を利用する方法で、Order By節を除去しても整列作業をしないために性能上非常に有利な方法と言えます。

　上記の方法は完璧に見えますが、INDEX_DESCヒントに指定されたインデックス状態がUnusable状態や、インデックスが存在しない場合で誤ったデータが抽出されてしまうという致命的な短所を有しています。 なぜなら、この方法はインデックスの整列したデータを利用して処理しなければいけないのですが、インデックスがなかったり使うことはできない状態の場合、INDEX_DESCヒントが無視されて、無作為で1件のデータを抽出してしいデータの整合性が毀損されるという事態が発生する可能性がありますので、使用時には特別の注意が必要となります。

　INDEX_DESC (またはINDEX_ASC)ヒントとROWNUM <= 1を同じ位置に使う方法は誤ったデータを抽出する危険がありますが、速度や性能の側面で非常に効率的なので、これを代えるほどの他の方法があるのか調べてみる必要があります。

Script. テスト データ用

< ROWNUM_T1>
■生成要件

-テーブル データ件数は50,000ロー
-カラムc1は値の種類が100,000種類すなわち、Unique性格を持つカラム
-カラムc2は値の種類が26種類

■テーブル生成

create table ROWNUM_T1 as
SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS c2
  FROM DUAL CONNECT BY LEVEL <= 500000 ;

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

CREATE INDEX IDX_01 ON ROWNUM_T1 ( C2, C1 ) ;

　効率的なMIN、MAX値を抽出できる方法に対して次の三種類のSQLを通じて詳しく調べてみることにします。 SQLで使われたROWNUM_T1テーブルのC1カラムは1から次々と増加する一連のカラムで、C2カラムはA ～ Z値が繰り返し入力されています。 そして三番目のSQLはROWNUM_T1テーブルでC2が‘A’であるデータのうちにC1値が最も大きい値を抽出するように作成されています。

テーブル12-3. MAX値を抽出するSQLの類型

SQL(1)	SQL(2)	SQL(3)
SELECT /*+ INDEX_DESC(T1 IDX_01) */        c1 FROM  ROWNUM_T1 t1 WHERE t1.c2 =’A’ AND t1.c1 >= 0  ABD ROWNUM <= 1;	SELECT c1 FROM (        SELECT /*+ INDEX_DESC(T1 IDX_01) */                 c1           FROM ROWNUM_T1 t1          WHERE t1.c2 = ‘A’            AND t1.c1 >= 0          ORDER BY c2 DESC, c1 DESC      ) WHERE ROWNUM <= 1;	SELECT MAX(c1) AS c1  FROM ROWNUM_T1 t1 WHERE t1.c2 = ‘A’   AND t1.c1 >= 0;

SQL(1),SQL(2),SQL(3)は全部C2が‘A’であるデータのうちC1が最も大きい値(MAX)を抽出するのにSQLの性能は大差はありません。 SQL(1)の場合はT1のIDX_01インデックスがUnusable状態になったり、インデックスが存在しないならば、データ整合性が毀損されることになるので危険性を有していることを上で説明しました。

したがってこのような問題を補完するためのSQLはSQL(2)とSQL(3)と一緒に作成することが望ましいのです。

SQL(2)はOrder By節を先に処理した後、これをインライン ビューで作ってROWNUM <= 1条件を処理するようにして性能問題を除去したSQLです。

SQL(3)はインデックスの整列したデータを利用したMIN/MAXオペレーションで実行されるように誘導したSQLです。 一般的にインデックス構成カラムに対する条件がWhere節に適切に照会されるならばSQL(3)方式がたくさん使われます。 その理由は、SQL作成も簡単でデータ整合性および性能の側面でも問題がないためです。

それではSQL(1),SQL(2),SQL(3)のトレース結果を通じて性能を確認してみることにしましょう。

SQL(1)

SELECT /*+ INDEX_DESC (T1 IDX_01) */
       c1
  FROM ROWNUM_T1 t1
 WHERE t1.c2 = 'A'
   AND t1.c1 >= 0
   AND ROWNUM <= 1 ;

call     count       cpu    elapsed       disk      query    curren         rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.08          0          4          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.01          1          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.09          1          7          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  COUNT STOPKEY (cr=3 pr=1 pw=0 time=10912 us)
      1   INDEX RANGE SCAN DESCENDING IDX_01 (cr=3 pr=1 pw=0 time=10905 us)

SQL(2)

SELECT c1
  FROM (
         SELECT /*+ INDEX_DESC (T1 IDX_01) */
                c1
           FROM ROWNUM_T1 t1
          WHERE t1.c2 = 'A'
            AND t1.c1 >= 0
          ORDER BY c2 DESC, c1 DESC
        )
  WHERE ROWNUM <= 1 ;

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

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  COUNT STOPKEY (cr=3 pr=0 pw=0 time=17 us)
      1   VIEW  (cr=3 pr=0 pw=0 time=16 us)
      1    INDEX RANGE SCAN DESCENDING IDX_01 (cr=3 pr=0 pw=0 time=15 us)

SQL(3)

SELECT MAX(c1) AS c1
  FROM ROWNUM_T1 t1
WHERE t1.c2 = 'A'
   AND t1.c1 >= 0 ;

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

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=3 pr=0 pw=0 time=19 us)
      1   FIRST ROW  (cr=3 pr=0 pw=0 time=16 us)
1    INDEX RANGE SCAN (MIN/MAX) IDX_01 (cr=3 pr=0 pw=0 time=13 us)

SQL(1),SQL(2),SQL(3)のトレース結果を確認してみると、性能には大差ないということを確認することができます。 性能上大差がないならばデータ整合性の問題がないSQL(2),SQL(3)方式でSQLを作成するようにする習慣を持つことが大事になります。

---

今回はここまでになります。次回第5回目は、「ROWNUM<=1は常に速くない」と題して、お送りします。それまで、See you ^^

---