ダメ漢ジョンたろうの生存戦略

どうしたら楽に生きられるのでございましょうか

OracleでROWNUMで行数指定するSQLについて

 こんばんは。ジョンたろうでございます。今回はOracleを使う時の話で、あるテーブルの10000件目から10010件目までを取得したときとかありますよね。つまり、開始行数と終了行数を指定して、その間のデータだけ取得したいときです。アンチパターンなども交えて説明していきます。

正しい取得方法①ROWNUMを使う

 いくつかのパターンがございますので、それぞれ紹介していきます。

※テーブル名:tableX(主キー:rowA)

1.1行目からN行目まで取得する(並び替え無し)

SELECT rowA, rowB, rowC 
  FROM tableX    
 WHERE ROWNUM BETWEEN 1 AND N;

2.1行目からN行目まで取得する(並び替えあり)

/* 正解 */
SELECT rowA, rowB, rowC 
  FROM (
    SELECT tableX.*, ROWNUM 
    FROM tableX
    ORDER BY rowB 
  )
 WHERE ROWNUM BETWEEN 1 AND N;

/* アンチパターン */
SELECT rowA, rowB, rowC 
  FROM tableX
 WHERE ROWNUM BETWEEN 1 AND N
 ORDER BY rowB;

アンチパターン
 ROWNUMはOrder Byの前に評価されますので、tableXの1行目から最終行まで行番号が振られた後でOrder Byされます。つまり、並び変える前の順番でレコードを取得してしまいます。

3.S(>1)行目からT行目まで取得する(並び替え無し)

/* 正解 */
SELECT rowA, rowB, rowC 
  FROM (
    SELECT tableX.*, ROWNUM RN
    FROM tableX 
  )
 WHERE RN BETWEEN S AND T;

/* アンチパターン */
SELECT rowA, rowB, rowC 
  FROM tableX    
 WHERE ROWNUM BETWEEN S AND T;

アンチパターン
 ROWNUM対して、S(>1)以上の条件を与えると、何故か結果が1レコードも取得できなくなります。理由は分かりません(教えてください)。

4.S(>1)行目からT行目まで取得する(並び替えあり)

/* 正解 */
SELECT rowA, rowB, rowC
  FROM( 
    SELECT most_inner.*, ROWNUM RN
      FROM (
        SELECT *
          FROM tableX
         WHERE rowB = 'hoge'
         ORDER BY rowC 
      ) most_inner
     WHERE RN <= T -- パフォーマンス的に入れた方が吉 ※
  )
 WHERE RN BETWEEN S AND T;

◆補足  アンチパターンについては2~3を参考にしてください。なお、※の部分ですが、このWHERE句はパフォーマンス向上に入れた方が良いです。Oracleバージョン12c以上だと、count stop key というアルゴリズムが適用されます。ORDER句で並び変えるときに、ROWNUMで行番号の「以下」条件を指定している場合、その番号に達したら途中でソートを打ち切ってくれるというものです。

例えば、50万件のレコードが格納されているtableXをWHERE句(rowB = 'hoge')で絞り込み、更にORDER句でrowCのDESCで並び変え、そのうちの1501件目から2000件目までを取得するとします。まさに上記のSQLになりますね(S=1501, T=2000)。このとき、WHERE句によって抽出された件数が10万件になったとしましょう。その際、上記※部分の「以下」条件を入れた時と入れなかった時の動作を見てみます。

・「以下」条件なし:
 抽出した10万件全てをORDER句で並び替え、10万件すべてにROWNUMを振り、最後に1501 <= ROWNUM <= 2000 で抽出する。

・「以下」条件あり:
 抽出した10万件をORDER句で1件目から順に並び替えを開始し、2000件に到達したらその時点で並び替えを打ち切り、最後に1501 <= ROWNUM <= 2000 で抽出する。

つまり、「以下」条件なしと比べ、「以下」条件ありの場合は、2001~100000件目のソートが必要なくなるということです。並び替えは非常にコストの大きい処理ですので、パフォーマンスにもかなり差が出ます。

ROW_NUMBERを使う方法も

 以上でございます。行数指定はROWNUMの他、ROW_NUMBERを使うことが出来ます。ROW_NUMBERはSQL規格の仕様なので、MySQLPostgreSQLでも使うことができ、汎用性がありますので、次回説明しようと思います。ただし、ROWNUMとROW_NUMBERでは処理の方法が異なり、状況によってパフォーマンスにも違いが出ますので、それについても併せて解説できればと思います。ありがとうございました。