日付時刻＋連番の主キーをSQLだけで作りたい

質問

前提：ORACLE

「一番古いレコードを取得する。」というのが条件です。
これを満たすだけであればSEQENCE等で連番を振れば満たせます。
しかし、日付時刻＋連番であればカラム一つで時刻まで把握できます。
(日付時刻を利用することはありませんが、付加価値？として。)

というわけで、日付時刻＋連番(桁固定のサイクリック)の主キーを使いたいのですが、
以下のように同時刻で連番が先頭に戻ると順番が守れません。
時刻A９９９９
時刻A００００　← あとから挿入したのに同時刻の先頭になる

プログラムであれば同時刻なら連番を先頭から使うことも簡単ですが、
このよなことをSQL側だけでできるのでしょうか？

よろしくお願いします。

回答No.2

Siegrune · ベストアンサー

シーケンスを使う方法としては、１分以内に９９９９を超えることがないなら、
シーケンスを１分毎に０にリセットして使えばいいと思いますし、
他の方法としては、
連番 = (select nvl((select max(連番)+ 1 from テーブル
where 連番 like to_char(sysdate,'hhmiss') || 'A%')
,to_char(sysdate,'hhmiss') || 'A0001')
from dual)
で既に登録されたデータに１を加えてもいいと思いますが。

※どちらも秒単位で連番という仕様で書いています。
　単に連番で付けている番号だけでサイクリックにすると質問の現象は防げません。
　（防ぐためには、サイクリックにはできません。どこかでリセットするか十分に大きな桁数をとるか
　　いづれかが必要です。）

なお、同時に複数のコミットされていないデータが発生する
（ＤＢ側が、２ＣＰＵ以上で同時に処理されている）というのなら、不可能。
（複数のＣＰＵをまたいで連番を共有することはプログラムでもできっこないです。）
・・・２ＣＰＵ以上で同時に処理されているのでなければミリ秒まで同一のデータが出来るのは
あまり起きないと思うのですが。

お礼

回答ありがとうございます。
SQLだけでは難しそうですね。
参考にさせて頂きます。

回答No.1

bin-chan

なぜ主キーで判断しようとするのか？
レコード作成日時の列をTIMESTAMP型・デフォルト設定じゃダメなの？

TIMESTAMP型ならミリ秒までの精度がありますよ。

補足

回答ありがとうございます。

ミリ秒では重複する可能性が高いのでTIMESTAMP型そのままでは使えません。
連番を主キーにして、TIMESTAMP型のカラムも用意すればレコード作成日時はわかりますが、
質問にもあるようにレコード作成日時は使用しません。