【Oracle Database】シーケンスとROWNUM・ROWID、MySQL/PostgreSQLとの違い

Oracle Database
2023.12.22

こんにちは、かつコーチです。

Oracle Database編の4本目です。

MySQLやPostgreSQLに慣れている方が最初に戸惑いやすいのが、連番の採番方法と、ROWNUM・ROWIDという独自の概念です。

この記事では、他のRDBMSとの違いを意識しながら、Oracle特有の書き方を解説します。

シーケンスとは？

CREATE SEQUENCE構文

シーケンスは、連番を発行するためのオブジェクトです。

-- シーケンスの作成
CREATE SEQUENCE employees_seq
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

作成したシーケンスは、.NEXTVAL（次の値を取得）と.CURRVAL（直前に取得した値を再取得）で利用します。

-- INSERT時にシーケンスから採番する
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (employees_seq.NEXTVAL, '太郎', '山田');

-- 直前にNEXTVALで取得した値を再利用する
SELECT employees_seq.CURRVAL FROM DUAL;

MySQLのAUTO_INCREMENT・PostgreSQLのSERIALとの違い

MySQLのAUTO_INCREMENTやPostgreSQLのSERIALは「特定のカラムに紐づく採番の仕組み」ですが、Oracleのシーケンスはテーブルから独立したオブジェクトである点が大きく異なります。

MySQLPostgreSQLOracle
採番の仕組みAUTO_INCREMENT（カラム属性）SERIAL（内部的にシーケンス）SEQUENCE（独立オブジェクト）
複数テーブルでの共有不可不可（テーブルごとに生成）可能（1つのシーケンスを複数テーブルで使い回せる）
値の取得方法LAST_INSERT_ID()currval().CURRVAL

Oracleのシーケンスは1つ作成しておけば複数のテーブルで共有できるため、「システム全体で一意なIDを振りたい」という要件にも柔軟に対応できます。

一方で、テーブルとシーケンスが別オブジェクトである分、テーブル作成時にシーケンスの作成も忘れずに行う必要があり、初学者はここでつまずきがちです。

IDENTITY列（12c以降）

Oracle Database 12c以降では、MySQLのAUTO_INCREMENTに近い感覚で使えるIDENTITY列という書き方も追加されました。

-- IDENTITY列：MySQLのAUTO_INCREMENTに近い書き方
CREATE TABLE employees (
    employee_id NUMBER GENERATED ALWAYS AS IDENTITY,
    first_name  VARCHAR2(50),
    last_name   VARCHAR2(50)
);

内部的にはシーケンスが自動生成されているだけなのですが、シーケンスを別途作成・管理する手間が省けるため、新規にテーブルを作る場合はIDENTITY列を使う方が現在の主流です。

既存システムの多くはシーケンス方式で作られているため、実務では両方の書き方を読めるようにしておく必要があります。

ROWNUMとROWID

ROWNUMの挙動と落とし穴

ROWNUMは、SELECT結果の各行に対して、取得された順に振られる連番の疑似列です。

MySQLのLIMITやPostgreSQLのLIMITのように「上位n件を取得したい」場面で使われますが、挙動に癖があります。

❌ Before：ROWNUMで並び替え後の上位3件を取りたいが、意図通りにならない
SELECT employee_id, salary
FROM employees
WHERE ROWNUM <= 3
ORDER BY salary DESC;

このSQLは「給与上位3件を取りたい」という意図で書かれていますが、実際にはROWNUMがORDER BYより先に評価されるため、ソート前の適当な3件が抽出されてから並び替えられてしまいます。

筆者もOracleに初めて触れたとき、この挙動を知らずに「なぜ給与トップ3が正しく出ないのか」としばらく原因を探した経験があります。

✅ After：サブクエリで先にソートしてから、外側でROWNUMを使う
SELECT employee_id, salary
FROM (
    SELECT employee_id, salary
    FROM employees
    ORDER BY salary DESC
) WHERE ROWNUM <= 3;

サブクエリで先にORDER BYを確定させ、その結果に対して外側でROWNUMを絞り込むのが正しい書き方です。

なお、Oracle 12c以降ではMySQL・PostgreSQLと似たFETCH FIRST句が使えるため、新規に書くコードではそちらを使う方が直感的でミスも起きにくくなります。

ROWIDとは

ROWIDは、テーブル内の行が物理的にどこに格納されているかを示す疑似列です。

-- ROWIDで特定の行を直接指定する
SELECT ROWID, employee_id FROM employees WHERE employee_id = 100;

MySQL・PostgreSQLには直接対応する概念がなく、Oracle独自のものです。

同一の値を持つ重複行を削除する際など、主キーだけでは1行に絞り込めない場面でROWIDを使うことがあります。

-- 重複行のうち、ROWIDが最小の1件だけを残して削除する
DELETE FROM employees e1
WHERE ROWID > (
    SELECT MIN(ROWID)
    FROM employees e2
    WHERE e1.employee_id = e2.employee_id
);

ただし、ROWIDは行の物理的な位置情報であり、テーブルの再編成などで値が変わる可能性があるため、アプリケーションのロジックとして恒久的に依存するのは避けるべきという点は覚えておいてください。

よくあるつまずきポイント・エラー対処

シーケンスの値がずれる

シーケンスは、一度NEXTVALを呼び出すと、その後にトランザクションをロールバックしても採番された値は元に戻りません。

「IDが1つ飛んでいるけどバグでは？」と相談を受けることがありますが、これはOracleのシーケンスの仕様上正常な挙動です。

連番に欠番があっても問題ない設計にしておくことが、Oracleでのテーブル設計における前提になります。

応用・一歩先の使い方

FETCH FIRST句によるモダンな書き方

先ほど触れたFETCH FIRST句は、MySQL・PostgreSQLのLIMITに近い感覚で使えます。

-- FETCH FIRST句：ORDER BY後の上位n件を直感的に取得できる
SELECT employee_id, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 3 ROWS ONLY;

ROWNUMのようにサブクエリでラップする必要がなく、可読性も高いため、Oracle 12c以降の環境では基本的にこちらを使うことをおすすめします。

まとめ

この記事のポイント

  • Oracleのシーケンスはテーブルから独立したオブジェクトで、複数テーブルで共有できる
  • MySQLのAUTO_INCREMENT感覚で使いたい場合は、12c以降のIDENTITY列が便利
  • ROWNUMはORDER BYより先に評価されるため、ソート後の上位n件が欲しい場合はサブクエリで囲む
  • ROWIDはOracle独自の物理位置情報で、重複行削除などに使えるが恒久的な依存は避ける
  • 新規コードでは、ROWNUMよりFETCH FIRST句を使う方が直感的でミスが少ない

次に読むべき記事

  • PL/SQLの基本：手続き型でロジックを書く
  • 表領域とユーザー・権限管理の基本

タグ: Oracle, 中級者向け, 基本文法

タイトルとURLをコピーしました