Oracle Databaseの重複キーエラー(ORA-00001)を回避する方法 (2020/03/03)

https://blogs.oracle.com/sql/post/how-to-skip-duplicate-key-errors-ora-00001-in-oracle-database

投稿者： Chris Saxon | Developer Advocate

これは、あらゆるデータロードの悩みの種です。プロセスの99%が完了しても、最後の数行で失敗してしまうのです。

insert into target_table
  select *
  from   massive_table
  where  last_row_is_a_duplicate = 'Y';

ORA-00001: unique constraint (...) violated

これは通常、プロセス全体を中止します。すべてをロールバックします。

つまり、データをクリーンアップする必要があります。そして、もう一度ロードを実行します。

これは、ロードが完了するまでに何時間もかかる場合に特に問題となります。

幸いなことに、Oracle Databaseには重複行をスキップする方法がいくつかあり、これを利用することでこのような事態を防ぐことができます。この記事では、その方法を紹介します。

サブクエリを使用して既存のキーの追加を停止
挿入時に ignore_row_on_dupkey_index ヒントを追加
DMLエラーロギングを使用して、エラーを捕捉
制約を遅延可能化

しかし、その前に、主キーと一意性制約とは何か、なぜそれを持つのかについて、簡単に復習しておきましょう。

主キー制約と一意性制約とは？

テーブルの中の特定の1行を見つけることができるのは、SQLの基本的な機能です。実際、これは第一正規形の必要条件です。

例えば、あなたのアプリケーションのアカウントのユーザー名を考えてみましょう。誰かが正しいパスワードを入力したことを確認し、ログイン時にその詳細を表示するためには、そのユーザー名の行を見つける必要があります。そして、それぞれのユーザ名が accounts テーブルに一度しか現れないことを確認します。

ユーザ名を一意であると宣言することで、この二番目の性質が守られます。そして、誰かが重複した名前を挿入しようとすると、エラーを発生させます。

create table accounts (
  username varchar2(10)
    not null
    unique,
  given_name varchar2(512)
    not null
);

insert into accounts ( username, given_name )
  values ( 'chrissaxon', 'Chris' );
insert into accounts ( username, given_name )
  values ( 'chrissaxon', 'Christopher'  );

ORA-00001: unique constraint (CHRIS.SYS_C0018134) violated

これは、各ユーザー名が一度しか登場しないことを保証しています。

主キーは一意制約の特殊なケースです。これには次のような特別な性質があります。

すべてのカラムが必須である (NULLではない)
各テーブルは最大1つのプライマリキーを持つことができる。

では、主キー制約と一意性制約のどちらを使えばいいのでしょうか？

テーブルによっては、多くの一意識別子を持つことができます。一般的な理由はサロゲートキー、つまりアプリケーションの外では意味を持たないシーケンスに割り当てられた値やGUIDの値です。たとえば、accountsテーブルにaccount_idカラムを追加することができます。

create table accounts (
  account_id integer
    generated as identity
    not null
    primary key,
  username varchar2(10)
    not null
    unique,
  given_name varchar2(512)
    not null
);

テーブルには主キーを1つだけ持つことができます。ですから、account_idとusernameの少なくとも一方は一意制約でなければなりません。しかし、どちらが主キーになるべきでしょうか？

accountsテーブルには、その中の特定の行を参照する子テーブルがたくさんあるはずです。たとえば、注文、請求書、支払いなどです。これらの子テーブルの行が有効なアカウントを指すようにするには、子テーブルから親テーブルへの外部キーを作成します。

外部キーが指すカラムは、不変である必要があります。変更できないようにします。これにより、親から子へキー値をカスケード更新しなければならない問題を回避することができます。例えば、誰かが自分のユーザー名を変更したい場合などです。

主キーも一意制約も、外部キーのターゲットにすることができます。しかし、主キーは1つしか持てないので、慣習的に外部キーはこの制約を指します。

つまり、account_idを主キーにすることで、他の開発者に伝えることができるのです。

「これは不変の値です。これは不変の値なので、外部キーのターゲットとして使用してください。」

一意制約を作成することで、データ品質を向上させることができます。通常、誰かが既存の値を挿入しようとしたとき、たとえば、既存のユーザー名で新しいアカウントを作成しようとしたとき、これを阻止したいと思います。

しかし、時にはこのエラーをスキップしたい場合もあります。たとえば、すでに存在するキーを読み込むときなどです。

サブクエリによる重複行のスキップ

Image by Pexels from Pixabay

ソースデータに既にテーブルに存在するキー値が含まれている可能性がある場合、これらの値を完全に読み込むことは避けたほうがよいでしょう。そのためには、not exists サブクエリを使用します。

insert into accounts ( username, given_name )
  select username, given_name
  from   accounts_stage acst
  where  not exists (
    select * from accounts acct
    where  acct.username = acst.username
  );

これは、accounts_stageからユーザー名を読み込むだけで、accountsからユーザー名を読み込むことはありません。

これはinsert ... selectのときだけ動作します。insert ... valuesを使ったクエリはありません。ですから、この方法はこれらでは動作しません。

また、ソースに重複がある場合はどうでしょうか？その場合、どのように余分な行をスキップするのでしょうか?

ignore_row_on_dupkey_indexヒントを使用して重複行を無視

最も簡単な方法は、クエリにヒントを追加することです。11.2 で追加された ignore_row_on_dupkey_index ヒントは、重複する値を黙って無視します。

insert /*+ ignore_row_on_dupkey_index ( acct ( username ) ) */
  into accounts acct ( username, given_name )
  select username, given_name from accounts_stage;

つまり、accounts_stageに重複したユーザ名がある場合、片方の行を追加し、もう片方をスキップします。これは、insertのvaluesバージョンを使用しているときにも動作します。

insert /*+ ignore_row_on_dupkey_index ( acct ( username ) ) */
  into accounts acct ( username, given_name )
  values ( 'chrissaxon', 'Chris' );

ヒントを使用するには、テーブル名またはエイリアスの後にどちらかを付けます。

一意なインデックスの名前
一意なインデックスに含まれるカラムのカンマ区切りリスト

テーブルの制約を1つだけ無視できることに注意してください。余分なユーザー名をスキップして自動生成されるプライマリーキーを持ちたい場合は問題ではありません。しかし、1つのテーブルに多くのユニークキーを持つ場合は制約となります。

ヒントを追加するのは簡単ですが、他にもいくつかの大きな欠点があります。

どの行が無視されるのかがわからない
値が重複していることを知らせるものがない

したがって、重複があるかどうかを知りたい場合は、データを後処理して確認する必要があります。

ソースに同一の行、つまりすべての列が同じ値を持つ行が含まれている場合、これは気にならないかもしれません。もちろん、この場合はサブクエリでdistinctを追加したほうがよいでしょう。

insert into accounts ( username, given_name )
  select distinct username, given_name
  from   accounts_stage;

しかし、もし衝突するのがユーザー名だけであれば、それは知りたいことでしょう。そして、どの行をスキップしたかを知ることができます。そのためには、どの行が失敗したかを記録しておく必要があります。

DMLエラーログで重複した値を保存

DMLエラーログは、挿入、更新、削除ステートメントが別のテーブルに例外を保存することを可能にします。これにより、エラーなしでステートメントを完了させることができます。このビデオでは、Connorがそれについて説明しています。

これを使用するには、まず、エラーログ・テーブルを作成する必要があります。デフォルトでは、これはerr$_<tablename>という名前のテーブルを作成します。

そして、SQLの中でlog errors節を使います。

exec dbms_errlog.create_error_log ( 'accounts' );

insert into accounts ( username, given_name )
  values ( 'chrissaxon', 'Chris' )
  log errors into err$_accounts reject limit unlimited;

insert into accounts ( username, given_name )
  values ( 'chrissaxon', 'Christopher' )
  log errors into err$_accounts reject limit unlimited;

insert into accounts ( username, given_name )
  select username, given_name from accounts_stage
  log errors into err$_accounts reject limit unlimited;

拒否の上限はデフォルトで0です。つまり、重複がある場合は例外が発生します。これを無制限に設定すると、すべてのORA-00001エラーを無視することができますが、挿入はまだ成功します!

もちろん、ロード中に多くのエラーが発生した場合は、より大きな問題の兆候である可能性があります。そして、あなたは処理を停止したいでしょう。そこで、例えば100行以上の失敗があった場合、ロードを中断したいのであれば、リジェクト・リミットを100に設定します。

ロードが完了したら、エラーログテーブルに問い合わせて問題がなかったかどうかを確認する必要があります。

これは、同時に多くの負荷が実行されている場合に問題となる可能性があります。どのプロセスでエラーが発生したのかを知るにはどうしたらいいのでしょうか？

この問題に対処するために、負荷にタグを追加します。そして、このタグを使用して、問題のあるデータを後から探し出します。

insert into accounts ( username, given_name )
  values ( 'chrissaxon', 'Christopher' )
  log errors into err$_accounts ('load name') 
  reject limit unlimited;
  
select * from err$_accounts
where  ora_err_tag$ = 'load name';

これまでの手法はすべて、重複を安全に無視できることを前提としています。少なくともロード・トランザクションを実行している間は。

しかし、時にはすべてのコピーをロードする必要があるかもしれません。そして、「正しい」ものを残すためにデータを後処理します。

最後の方法は、これを可能にします。同じトランザクション内で検証を行うことができるのであれば、です。

遅延可能な制約による制約の適用を遅らせる

Photo by Mat Brown from Pexels

制約をdeferrableと宣言することで、データベースが制約を検証するタイミングをコミットまで遅らせることができます。

create table accounts (
  username varchar2(10)
    not null
    unique deferrable initially deferred,
  given_name varchar2(512)
    not null
);

insert into accounts ( username, given_name )
  values ( 'chrissaxon', 'Chris' );
insert into accounts ( username, given_name )
  values ( 'chrissaxon', 'Christopher' );

これまでの方法との主な違いは、トランザクション中にターゲット・テーブルが重複を含む可能性があることです。データベースはコミット時にのみORA-00001を発生させます。その時点で、すべての行がロールバックされます!

commit;

ORA-02091: transaction rolled back
ORA-00001: unique constraint (CHRIS.SYS_C0018128) violated

select count (*) from accounts;

COUNT(*)   
          0

そのため、トランザクションを終了する前に、重複する行を検出して削除する必要があります。

これは、多くのinsert文を実行するマルチパス・ロードの場合に便利です。そして、どの行を残すかは、最後に決めることができます。

遅延可能なキーは、制約を守るために一意でないインデックスを作成することに注意してください。これにより、主キーの検索は効率的ではなくなります。また、ignore_row_on_dupkey_indexヒントは、ユニークインデックスを必要とするため、このヒントは壊れます。

insert /*+ ignore_row_on_dupkey_index ( acct ( username ) ) */
  into accounts acct ( username, given_name )
  values ( 'chrissaxon', 'Chris' );
  
ORA-38913: Index specified in the index hint is invalid

ヒントの制限を指摘してくれた、deferrable constraints を提案する Richard Foote に HT。

どのアプローチがベストか？

いつものように、「ベスト」には主観的な要素が含まれます。そして、それはあなたの目標に依存します。以下の質問に答えてみてください。

なぜ重複した行が発生するのか?

重複行を挿入しようとしたときに、どのような動作をさせたいのか？

ソースデータがクリーンで、既存の値を再挿入する可能性がある場合、SQLを変更するのがよいでしょう。最も簡単なのは、not exists節を追加することです。また、非キーカラムを最新のデータで上書きしたい場合は、マージを使用します。

しかし、乱雑なデータをロードしている場合、ORA-00001エラー以外にも問題が発生する可能性があります。例えば、NULLでないカラムにNULLを入れようとしているような場合です。このような場合、DMLエラーロギングを行うことで、すべての問題を捕捉することができます。

ほとんどの場合、ignore_row_on_dupkey_indexヒントは最後の手段であるべきです。素早く修正する必要がある場合は便利です。しかし、間違いなく厄介な回避策です!

同様に、遅延可能な主キー制約やユニーク制約は、おそらくプロセスを再設計する必要があることを示すサインです。また、既存の制約を遅延可能にすることはできません。これを行うには、制約を削除して再作成する必要があり、ほとんどの場合、そのインデックスも再作成する必要があります。このため、ほとんどの既存のテーブルにこの方法を追加することは現実的ではありません。しかし、コピーをロードし、その後クリーニングする必要がある場合は、この方法が唯一の実行可能な方法かもしれません。

あなたはどの方法が一番好きですか？また、他にこの問題に対処する方法はありますか？コメント欄で教えてください。

SQLを学びたいですか？Databases for Developersに参加してください。無料のオンラインコース、Databases for Developers: Foundationsに参加してください。