Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

欠番情報を考慮した一意キーを採番する方法

欠番したレコードがある場合には、その中で最小の番号を取得、欠番が存在しない場合には最大値＋1 の新しい番号を採番する。また、ある番号以下は予約領域として、その番号以下の空白の番号域は欠番として扱わない。

例) 1000 番未満は予約領域とした場合の採番用 SQL

SELECT MIN(COMP.NEXT_SEQ_NO) NEXT_SEQ_NO
  FROM tablename ORIG,
       (
         SELECT 1000 NEXT_SEQ_NO FROM DUAL
         UNION ALL
         SELECT SEQ_NO+1 NEXT_SEQ_NO FROM tablename 
         WHERE SEQ_NO >= 1000
       ) COMP
   WHERE COMP.NEXT_SEQ_NO = ORIG.SEQ_NO(+) AND ORIG.SEQ_NO IS NULL ;

別の方法(こちらのほうが見た目は好みなのですが、上の SQL より若干コストが高いみたい)

SELECT MIN(NEXT_SEQ_NO) 
FROM (
       SELECT 1000 NEXT_SEQ_NO FROM DUAL
       UNION ALL
       SELECT SEQ_NO+1 NEXT_SEQ_NO FROM tablename WHERE SEQ_NO >= 1000
       MINUS
       SELECT SEQ_NO NEXT_SEQ_NO FROM tablename WHERE SEQ_NO >= 1000
);

欠番に対して神経質になる必要が無い場合で連続して大量の発番を行う場合にはシーケンスを使用した方が断然パフォーマンスは良い。

シーケンスを使用した場合のメリット

• テーブルへの検索が必要ない。
• シーケンスを使用する場合にはデータのコリジョン(一意キー制約違反)が発生しない。
• シーケンスはメモリキャッシュを利用して発番されるために非常に高速(キャッシュサイズは設定可能)。
  キャッシュを使い切ると新しいキャッシュを確保するための軽微な再帰 SQL は発生する。

シーケンスを使用した場合のデメリット

• 必要な数だけ定義する必要がある。
• シーケンスの発番は ROLLBACK できない。( COMMIT とも無関係 )
• データベースを停止すると最大でキャッシュ分(キャッシュ - 1 かも)の欠番が発生する。
• 共有メモリからエイジアウトされると欠番が発生する。⇒ 対策： DBMS_SHARED_POOL パッケージ