Development

Oracleのシーケンス(sequence)オブジェクトを変更する3つのやりかた

  • Oracleシーケンスの値を変更したい
  • Alterでやろうと思ったら出来ないじゃん!

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

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

そこで、シーケンスの値を変更(更新)したかったのでやり方を調べてみました。  

ざっと考えたり調べた結果、シーケンス(sequence)を変更するには3通りのやり方ができそうなので、その方法と個人的なオススメの方法を紹介します。  

この記事でわかること
  • Oracleシーケンスオブジェクトの変更方法

racleのシーケンス(sequence)オブジェクトを変更する3つのやりかた

Oracleのシーケンス(sequence)の値を変更するには以下の3つの方法があります。  

  1. Drop,Createで作り直して変更する  
  2. nextvalで進めて変更する  
  3. Alterとnextvalを駆使して変更する  

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

それでは1つずつ説明していきます。

シーケンスをDrop,Createで作り直して変更

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

作り直して変更するメリット

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

 作り直して変更するデメリット

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

 シーケンスのDrop,Create[sample]

Oracleのシーケンス(sequence)オブジェクトの削除方法、作成方法はこちらの記事に書きましたのでここでは省略。  

Oracleのシーケンス(sequence)オブジェクトの基本的な使い方Oracleのシーケンス(sequence)オブジェクトの基本的な使い方です。 作成・変更・削除・値の取得の方法について書いてます。...

細かい設定がないDB環境ならばこの方法もありかな?

でも現実はそんなことは滅多にないと思うので、あまり効率的ではないかな・・・。

シーケンスをnextvalで進めて変更

この方法はnextvalでシーケンス値を進めちゃって値を変更させる方法です。  

シーケンス値が100だけど、DBの主キーが5000になってるって場合なんかは使えると思います。  

進めて変更するメリット

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

進めて変更するデメリット

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

シーケンスのnextval[sample]  

ex:現在値10のシーケンスを5000に進める

nextvalを4990回実行すれば良いね!

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

このSQLではCONNECT BY 句を使って再帰的処理にしてます。

この方法は至って原始的な方法ですが、ちょっと進めれば問題解決できる場合なんかは効率的です。

ただし、デメリットにあるように、現在100を20に戻したい場合なんかは使用できません。  

また、大幅に進めたい場合も時間がかかります。  

シーケンスをAlterとnextvalで変更する方法

この方法はAlterを使ってシーケンス(sequence)オブジェクトのINCREMENT BYの値を変更し、そのあとnextvalでシーケンス値を進めた後に、再度AlterでINCREMENT BYを元に戻すという方法です。  

そうすることで、シーケンスの現在値を任意の値まで進め、事実上シーケンスの値を変更した結果となります。  

Alterで変更するメリット

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

Alterで変更するデメリット

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

シーケンスのAlter/nextval[sample]

ex)現在値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回進めて目的の値にした後に、また元に戻すという感じです。  

これだと現在値を減らすことも可能ですし、シノニムの再設定の必要もありません。  

デメリット多いけれど、いちばん何でもできる方法だね!

Oracleのシーケンス変更のまとめ

Oracleのシーケンス(sequence)オブジェクトを変更する3つの方法をまとめてみました。

個人的なオススメは①と②が良いな

ちょっと進めるだけなら②の方法が一番だし、戻す必要があったり、大量に進めるときは①の方法みたいな使い分けすればいいと思います。  

誰かの参考になれば幸いです。 

COMMENT

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です