OracleのROWNUMについてまとめています。
SQLのROWNUM（Oracle）を使う方法
OracleのROWNUMは、1から始まる連番を取得します。擬似的に列を発生させるため、ROWNUM疑似列と呼ばれます。
SELECT ROWNUM , Name , Age FROM users | ROWNUM | Name | Age | | ------ | ------------- | ---- | | 1 | Yamada Taro | 10 | | 2 | Suzuki Ichiro | 30 | | 3 | Sato Taro | 20 | | 4 | Tanaka Jiro | 50 | | 5 | Ito Hanako | 40 |
【関連記事】
▶【SQL】RONUMで連番を取得！Oracleで連番を取得する方法と注意点を解説
ROWNUMを使って、他のDBMSにある「limit」の代わりに使うことで、取得するレコード件数を制限することが可能です。
例えば、以下のSQLは、employees（社員）テーブルから、ROWNUMが11未満のデータ、つまり10件のみデータを取得します。
SELECT * FROM employees WHERE ROWNUM < 11;
同様に取得したレコードに連番を付与する関数として、ROW_NUMBER()があります。ROW_NUMBERは、PARTITION BYやORDER BYと組み合わせてグルーピングやソートを組み合わせて、グループごとに連番を振るなどより複雑な用途に使われます。
【関連記事】
▶SQLのrow_number関数の構文 パーティション・ソートとの組み合わせ
OracleのROWNUMの注意点
以下のSQLは、行を返しません。
SELECT * FROM employees WHERE ROWNUM > 1;
最初の行のROWNUMは1のため、条件は偽となります。2行目のROWNUMも1となるため、条件は偽と判定されます。その後すべての行が偽となるんですね。
感覚的には、2行目以降すべてを抽出する条件として動作しそうなのですが、上記のような結果になります。同様に、ROWNUMを指定して特定の行のみ取り出そうとした場合にも、意図通り動きません。
OracleのROWNUMは、行の途中から結果を抽出できない
以下のSQLは、「2行目のみ取り出す」結果にならず、行を返しません。
SELECT * FROM employees WHERE ROWNUM = 2;
ROWNUMで行の途中から結果を抽出したい場合は、サブクエリと組み合わせることで実現は可能です。
SELECT
column1
FROM
(
SELECT
column1
, ROWNUM AS rn
FROM
hoge
)
WHERE
rn BETWEEN 10 AND 20
参考）OracleにないLIMITの代わりにROWNUMを使う場合の罠
なお、指定した行数以降を抽出する場合は、Oracle 12以降でOFFSETが実装されたため、そちらを使用するのが良いでしょう。以下の例は、usersテーブルから、age（年齢）が30以上のレコードを年齢の昇順にソートして10件目以降を抽出します。
SELECT * FROM users WHERE age > 30 ORDER BY age OFFSET 10;
ROWNUMの連番付与後にorder byによるソートが行われる点に注意
ROWNUMとorder byを組合わて使った場合、ROWNUMの連番が振られたあとに、order byによるソートが実行されます。
SELECT ROWNUM , Name , Age FROM users ORDER BY Age DESC | ROWNUM | Name | Age | | ------ | ------------- | ---- | | 4 | Tanaka Jiro | 50 | | 5 | Ito Hanako | 40 | | 2 | Suzuki Ichiro | 30 | | 3 | Sato Taro | 20 | | 1 | Yamada Taro | 10 |
上記のように、ROWNUMがバラバラになってしまうんですね。意図通りにソートを行なうには、ROWNUMではなく、ROW_NUMBER分析関数を使用します。
ROWNUMは指定件数以下に抽出数を絞るなどシンプルな使用にとどめ、複雑なSQLにはROW_NUMBERを使用するのが良さそうです。
ROWNUMが遅くなるケース
ROWNUMを使ったviewにselect文を実行すると、処理が遅くなるというケースがあるようです。viewの定義部分のselect分は以下の通り。
select
t1.col1
, t2.col2
, rownum as col3
from
table1 t1
inner join table2 t2
on t1.pk = t2.pk;
参考）SELECT句にrownumを指定するとフルスキャンとなる場合がある – Qiita
2016年時点（Oracle11）では、ROWNUMは、結果セットすべてに連番を付与するためにテーブルフルスキャンが発生していたため、処理が遅くなっていたようです。
viewで結果セットにROWNUMを付与し、さらにselect文でviewを抽出する入れ子構造になっています。rownumにはindexを付与できないため、データ件数によっては遅くなるんですね。
最新のOracleでは、オプティマイザによって最適化される可能性もあります。入れ子構造で使用する際には、実行計画を確認しましょう。
ROWNUM=1で、データの存在チェックを行なう方法
データの存在チェックをおこなう場合に、余計なテーブルアクセスを発生させないためにROWNUM=1を指定するケースがあります。対象テーブルに何百万件格納されていても、アクセスは一件のみとなるんですね。
SELECT COUNT(*) INTO ln_count
FROM tab_a
WHERE ROWNUM = 1; --←ここを追加
--
IF ln_count = 0 THEN
dbms_output.put_line('ERROR!');
RAISE NO_DATA_FOUND;
END IF;
関連）Tips1. Oracleのパフォーマンス問題～存在チェックが遅い～
対象テーブルのデータサイズが大きくて、存在チェックに時間がかかるようなら試してみても良いでしょう。
まとめ
- ROWNUMは、Oracleの抽出結果に連番を付与できる
- order byと組み合わせるとROWNUM連番後にソートが実行される
- 複雑なSQLでは、ROW_NUMBER分析関数が使えないか検討する
[\comment]
