Hirooooo’s Labo

開発メモ、ガジェット、日記、趣味など、思った事を思ったまんま書くブログ

Oracleのsequenceオブジェクトの現在値の変更の仕方

f:id:hirooooo-lab:20160625023509j:plain

前回の投稿で、Oracleシーケンスオブジェクトの基本的な使い方をまとめましたが、今回はシーケンスの現在値を変更する方法です。

前回の記事
www.hirooooo-lab.com

開発中にデータを手修正なんかしていると、シーケンス値がDBの値と不整合を起こして、キー重複エラー起きちゃうよ・・・ なんてことはよくあることだと思います。

そんな時に、シーケンスの値を再設定したかったのでやりかたを調べてみました。

実現方法は3通り

ざっと考えてみたり、調べてみた結果、大きく分けて以下の3通りのやり方があるかなって感じです。

  1. Drop,Createで作り直してどうにかする
  2. nextvalで進めてどうにかする
  3. Alterとnextvalを駆使してどうにかする

と、まぁこんな感じなんですが、
各々メリットデメリットがあるので、ケースバイケースで使い分ければいいと思います。

Drop,Createで作り直す方法

これはそのまんまですが、sequenceオブジェクト自体をいったん削除して、Create文のSTART WITHパラメータに変更したい現在値を設定して作り直しちゃえって方法です。

メリット

  • 明示的に設定値を指定できるので、わかり易い
  • 最小SQL2発で済む

デメリット

  • sequenceに関する権限とか設定しないといけない
  • シノニム等の設定も同様に再度しないといけない
  • DDL文を実行することによる影響が怖い

sample)

sequenceオブジェクトの削除方法、作成方法は前回の投稿に書いてあるので、ここでは省略。

www.hirooooo-lab.com

細かい設定がないDB環境ならばこの方法もありだと思います。
が、現実はそんなことは滅多にないと思うので、あまり効率的ではないかなと。

nextvalで進めて対応する方法

この方法はnextvalでシーケンス値を進めちゃって問題を回避する方法です。
シーケンス値が100だけど、DBの主キーが5000になってるって場合なんかは使えると思います。

メリット

  • DDL文を使わないで対応できる
  • 1つのSQL文で対応可能
  • 原始的だからわかり易い

デメリット

  • 大量に進めたい場合(100 ⇒ 10000000)にしたい場合などは時間がかかる
  • sequenceのINCREMENT BYに設定されている増減値で進めることしかできない(戻せない)

sample)

現在値10のシーケンスを5000に進める
nextvalを4990回実行すればよい

select
    {SEQオブジェクト}.nextval
from
    dual
connect by
    level <= 4990;

 ※CONNECT BY 句を使って再帰的処理にしてます

この方法は至って原始的な方法ですが、ちょっと進めれば問題解決できる場合なんかは効率的です。
ただし、デメリットにあるように、現在100を20に戻したい場合なんかは使用できません。
また、大幅に進めたい場合も時間がかかります。

Alterとnextvalで対応する方法

この方法はAlterを使ってINCREMENT BYの値を変更し、そのあとnextvalでシーケンス値を進めた後に、再度AlterでINCREMENT BYを元に戻すという方法です。
そうすることで、シーケンスの現在値を任意の値まで進め、事実上シーケンスの値を変更した結果となります。

メリット

  • ほぼ値を指定しての設定が可能
  • 1.のように権限、シノニムの再設定が必要なし
  • 2.のように大量の移動も時間がかからない
  • 値を増やすも減らすも自分次第

デメリット

  • 手順が3SQLを実行する必要がある
  • 指定したい値と現在値の差分を取ってSQLを作成する必要がある
  • AlterだけどDDL実行する
  • 最後にINCREMENT BYを戻すの忘れると惨事
  • 作業中にほかの人がnextvalを行われると・・・

sample)

現在値10のシーケンスを7777777777に進める場合

Step1

シーケンスのINCREMENT BYを設定したい値と現在値の差分に変更する

alter sequence
    {SEQオブジェクト}
increment by 7777777767;

 ※7777777767 は 7777777777-10 で算出

Step2

nextvalを1回実行する

select
    {SEQオブジェクト}.nextval
from
    dual

Step3

INCREMENT BYを元の値に戻します。

alter sequence
    {SEQオブジェクト}
increment by 1;

こんな感じで差分をINCREMENT BYに設定し、1回進めて目的の値にした後に、また元に戻すという感じです。
これだと現在値を減らすことも可能ですし、シノニムの再設定の必要もありません。

まとめ

今回3通りの方法をまとめてみましたけど、お勧めは①と②のどちらかを使うっていうのが一番ですね。
ちょっと進めるだけなら②の方法が一番だし、戻す必要があったり、大量に進めるときは②の方法みたいな使い分けすればいいと思います。