NissyBlog

Life goes on.

OracleでLIMIT句を使ったかのような抽出結果を出す方法

LIMIT句を付けたクエリ、便利ですよね。
ID順にソートした結果をIDの昇順に10件取得する時、とても便利ですよね。
ID順にソートした結果をIDの昇順に10件取得し、取得した行を対象にUPDATE文を実行したい時、
とっても便利ですよね。
とあるきっかけでそんなクエリをOracleに投げたくなる時がありました。

まず、LIMIT句で取得する行数を制限するために、
LIMIT句による取得行数を制限するクエリの書き方を調べました。

MySQLで書くとこうなりますし、www.dbonline.jp

PostgreSQLで書くとこうなります。aroun-d.com

そこで、Oracleだとどう書くか調べてみました。blog.asial.co.jp

中身を読んでいくと、

OracleってLIMITが無い

という記述がありましたので実際に投げてみました。

SELECT * FROM HOGE_TABLE 
WHERE CREATED_AT > '2015-11-11' 
ORDER BY HOGE_ID 
LIMIT 10;

ORA-00933: SQLコマンドが正しく終了されていません。
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:
行41でエラー

本当に投げられませんでした。
ちなみにLIMIT句なしで下記のようなクエリを流すと成功します。

SELECT * FROM HOGE_TABLE 
WHERE CREATED_AT > '2015-11-11' 
ORDER BY HOGE_ID;

では、LIMIT句を使いたい時はどうした良いのでしょうか??

調べてみると、
ROWNUMを指定することでLIMIT句を使った時と
同じようなことが出来ることが分かりました。
そこで、下記のようなクエリを投げてみます。

SELECT * FROM HOGE_TABLE 
WHERE CREATED_AT > '2015-11-11'
AND ROWNUM <= 10 
ORDER BY HOGE_ID;

ID順にソートした後に複数件のレコードを取得することが取得出来ました。

続いて取得結果をアップデートするために取得結果を
サブクエリにしたUPDATE文も書いてみます。

UPDATE HUGA_TABLE
SET HUGA_FLG=1
WHERE HOGE_ID IN (
  SELECT HOGE_ID 
  FROM HOGE_TABLE 
  WHERE CREATED_AT > '2015-11-11'
  AND ROWNUM <= 10 
  ORDER BY HOGE_ID;
);

コマンド行 : 8 列 : 1 でのエラー
エラー・レポート -
SQLエラー: ORA-00907: 右カッコがありません。
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:

なんとエラーが出ました。

方々調べてみた結果、

UPDATE HUGA_TABLE
SET HUGA_FLG=1
WHERE HOGE_ID IN (
  SELECT HOGE_ID 
  FROM (
    SELECT HOGE_ID 
    FROM HOGE_TABLE 
    WHERE CREATED_AT > '2015-11-11' 
    ORDER BY HOGE_ID
    )
  WHERE ROWNUM <= 10
);

こちらのSQL文で実行出来ました。
ORDER BYでソートした結果から10行抽出し、
抽出したレコードに対してUPDATE文を実行する
というイメージですね。

ですが、このクエリにはひとつ注意しなくてはいけないケースがあります。
それは、検索対象が大量にある場合です。

このSQL文はまず、全件分のレコードを昇順にソートした後、
ソート済の全件分のIDから10件のレコードを取得。
取得したレコードに対してUPDATE文を実行します。

つまり、テーブルに格納されているレコード全件分を
対象にしたクエリを二回実行した後、
UPDATE文を実行しているため、
対象件数が多いとその分重たいクエリとなってしまうのです。

数千行程度なら問題ないですが、
大量のレコードが格納されたテーブルに対して実行する時は注意が必要です。

※参考
ROWNUM - オラクル・Oracleをマスターするための基本と仕組み
Oracleでrownum order byを使ったページング処理の注意点 - 大人になったら肺呼吸