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: 行4 列1でエラー
本当に投げられませんでした。
ちなみに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を使ったページング処理の注意点 - 大人になったら肺呼吸