这是无处不在的数据加载的祸害。完成了99%的过程,只是因为它在最后几行失败:
1 2 3 4 5 6 | insert into target_table select * from massive_table where last_row_is_a_duplicate = 'Y';
ORA-00001: unique constraint (...) violated |
这通常会中止整个过程。回滚一切。
这意味着您需要清除数据。然后再次运行整个负载。
啊!
如果负载需要数小时才能完成,则这尤其成问题。
幸运的是,Oracle数据库有几种方法可用于跳过重复的行,以阻止这种情况的发生。在这篇文章中,您将看到如何通过以下方法做到这一点:
但是首先,快速回顾一下什么是主键和唯一约束以及为什么要使用它们。
能够在表中定位特定行是SQL的一项基本功能。实际上,这是第一个范式的要求。
在您的应用程序中获取帐户的用户名。为了确保有人输入了正确的密码并在登录时提取其详细信息,您需要找到该用户名的行。并且确保每个用户名最多在帐户表中出现一次。
声明用户unique名将强制第二个属性。如果有人尝试插入重复的名称,则会引发错误:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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 |
这样可以保证每个用户名最多显示一次。
主键是唯一约束的特例。它具有以下额外属性:
- 其所有列均为必填(not null)
- 每个表最多可以有一个主键。
那么,如何在使用主键还是唯一约束之间进行选择?
一些表可以具有许多唯一的标识符。常见的原因是代理键–序列分配的值或GUID值在应用程序之外没有任何意义。例如,您可以account_id在accounts表中添加一列:
1 2 3 4 5 6 7 8 9 10 11 | 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 ); |
该表只能有一个主键。因此,account_id且username必须至少是唯一约束。但是哪个应该是主键?
该accounts表可能有许多子表引用其中的特定行。例如订单,发票和付款。为确保这些子行指向有效的帐户,请创建一个从子表到父表的外键。
外键指向的列应该是不可变的。不变。这避免了必须从父级到子级级键值更新的问题。例如,如果有人想更改其用户名。
主键和唯一约束都可以成为外键的目标。但是因为您只能有一个主键,所以按照惯例,外键指向此约束。
因此,通过创建account_id主键,您可以告诉其他开发人员:
“这是不可变的值。将其用作外键的目标。”
创建唯一约束可以提高数据质量。通常,当有人尝试插入现有值(例如使用现有用户名创建新帐户)时,您想停止这种情况。
但是有时您可能想跳过此错误。在加载已存在的密钥时说。
如果源数据可能包含表中已存在的键值,则最好避免完全加载这些键值。
使用not exists子查询执行此操作:
1 2 3 4 5 6 7 | 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提示无提示地忽略了重复的值:
1 2 3 | insert /*+ ignore_row_on_dupkey_index ( acct ( username ) ) */ into accounts acct ( username, given_name ) select username, given_name from accounts_stage; |
因此,如果accounts_stage用户名重复,则会添加其中一行,而绕过另一行。
当使用插入值的版本时,这也适用:
1 2 3 | insert /*+ ignore_row_on_dupkey_index ( acct ( username ) ) */ into accounts acct ( username, given_name ) values ( 'chrissaxon', 'Chris' ); |
要使用提示,请在表名称或别名后加上以下任一名称:
- 唯一索引的名称
- 以逗号分隔的唯一索引中的列列表
注意,您只能忽略表中的一个约束。如果您要跳过多余的用户名并具有自动生成的主键,这不是问题。但是,如果表上有许多唯一键,则存在限制。
虽然添加提示很容易,但它还有其他一些主要缺点:
- 您不知道哪一行会被忽略
- 没有什么可以告诉你,有重复的值
因此,如果您想知道是否存在重复项,则需要对数据进行后处理以进行检查。
如果源包含相同的行(每一列具有相同的值),则这可能不会打扰您。当然,如果是这种情况,最好添加distinct子查询:
1 2 3 | insert into accounts ( username, given_name ) select distinct username, given_name from accounts_stage; |
但是,如果只有用户名发生冲突,那么您可能想知道。并找出您跳过了哪些行。为此,您需要保留哪些行失败。
DML错误记录启用insert,update和delete语句将异常存储在另一个表中。允许语句完成而没有错误。康纳在这段视频中对此进行了讨论:
要使用它,首先必须创建错误日志表。默认情况下,这将创建一个名为的表err$_<tablename>。
然后在您的SQL中使用log errors子句:
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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; |
该reject limit默认为零。这意味着如果有任何重复项,您仍然会得到例外。将此设置为unlimited可以忽略所有ORA-00001错误,但是插入仍然成功!
当然,如果负载中有很多错误,则可能是更大问题的征兆。并且您想停止处理。因此,如果要中止负载(如果有多于100条失败行),请设置reject limit 100。
加载完成后,您将需要查询错误记录表以查看是否存在任何问题。
这可能是一个问题,同时有很多负载在运行。您如何知道错误属于哪个进程?
要解决此问题,请在负载中添加标签。然后在以下情况下使用它来找出违规数据:
1 2 3 4 5 6 7 | 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'; |