Tech Racho エンジニアの「?」を「!」に。
  • Ruby / Rails関連以外

OracleにないLIMITの代わりにROWNUMを使う場合の罠

OracleにはLIMIT句がない

OracleにはMySQLやPostgreSQLのようなLIMIT句がないので、

SELECT * FROM hoge LIMIT N;

とか書くとエラーになります。

行1でエラーが発生しました。:
ORA-00933: SQLコマンドが正しく終了されていません。

ROWNUM

では、指定した件数のみ取得したい場合どうするかというと、OracleにはSELECTの結果の行番号を返す ROWNUM という擬似列があるので、それを使います。

例えば2件だけ取得したい場合、

SELECT * FROM hoge WHERE ROWNUM <= 2

とすると、行番号が2以下のレコードだけ選択されるので、2件だけレコードを取ってくることができます。

ROWNUMの罠

例えばWHEREにROWNUM <= 2ではなく、ROWNUM = 2 を指定した場合、2番目のレコードが返って来ると思いきや、何も返ってきません。

SQL > SELECT * FROM hoge WHERE ROWNUM = 2

レコードが選択されませんでした。

同様に ROWNUM >= 2 とか書いても何も返ってきません。

SELECT * FROM hoge WHERE ROWNUM >= 2

レコードが選択されませんでした。

なぜこうなるかというと、ROWNUMは結果セットの中での行番号を表しているからです。
言葉だと説明しにくいので、コードで書くと下記のような感じになります。(実際の実装ではありません)

rownum = 1 # ROWNUMには最初1が入っている

records.each do |record|
  if select(record) # 行が選択された場合のみROWNUMの値がインクリメントされる
    rownum += 1
  end
end

1件目は ROWNUM = 2 の条件を満たさないので選択されず、2件目以降もROWNUMは1のままで、何も返ってこないという結果になります。

サブクエリを使う

では、ROWNUMでOFFSETみたいなことがやりたい時はどうするかというと、このように先にサブクエリでROWNUMを確定させてから範囲指定する必要があります。

SELECT
  column1 
FROM
  ( 
    SELECT
      column1
      , ROWNUM AS rn 
    FROM
      hoge
  ) 
WHERE
  rn BETWEEN 10 AND 20

OFFSET/FETCH 構文

Oracle バージョン12cでOFFSET/FETCHという構文が追加されたそうです。

参考: 第37回 新しいSQLについて

例えば10行目から20行目を取得したい場合はOFFSET 10 ROWS FETCH FIRST 20 ROWS ONLYのように書けます。

SELECT column1 FROM hoge ORDER BY column1 DESC
OFFSET 10 ROWS FETCH FIRST 20 ROWS ONLY;

こっちを使ったほうがサブクエリを使う必要がないので、簡潔に書くことができます。

ということで、OracleにLIMIT句がないという話は以上です。


CONTACT

TechRachoでは、パートナーシップをご検討いただける方からの
ご連絡をお待ちしております。ぜひお気軽にご意見・ご相談ください。