oracle处理插入重复记录的技巧

5738861587

　　http://book.csdn.net/bookfiles/669/10066921093.shtml
精明地使用异常（Exceptions）
Discerning Use of Exceptions
勇敢与鲁莽的界线很模糊，我建议进攻式编程，但并不是要你模仿轻步兵旅在Balaclava的自杀性冲锋（注7）。针对异常编程，最终可能落得虚张声势的愚蠢结果，但自负的开发者还是对它“推崇备至（go for it）”，并坚信检查和处理异常能使他们完成任务。
正如其名字所暗示的，异常应该是那些例外情况。对数据库编程的具体情况而言，不是所有异常都要求同样的处理方式——这是理解异常的使用是否明智的关键点。有些是“好”异常，应预先抛出；有些是“坏”异常，仅当真正的灾害发生时才抛出。
例如，以主键为条件进行查询时，如果没有结果返回则开销极少，因为只需检查索引即可判断。然而，如果查询无法使用索引，就必须搜索整个表——当此表数据量很大，所在机器又正在接近满负荷工作时，可能造成灾难。
有些异常的处理代价高昂，即使是在最佳情况下也不例外，例如重复键（duplicate key）的探测。“唯一性（uniqueness）”如何保证呢？我们几乎总是建立一个唯一性索引，每次向该索引增加一个键时，都要检查是否违反了该唯一性索引的约束。然而，建立索引项需要记录物理地址，于是就要求先将记录插入表，后将索引项插入索引。如果违反此约束，数据库会取消不完全的插入，并返回违反约束的错误信息。上述这些操作开销巨大。但最大的问题是，整个处理必须围绕个别异常展开，于是我们必须“从个别记录的角度进行思考”，而不是“从数据集出发进行思考”，这与关系数据库理论完全背道而驰。多次违反此约束会导致性能严重下降。
来看一个 Oracle 的例子。假设在两家公司合并后，电子邮件地址定为<Initial><Name>的标准格式，最多 12 个字符，所有空格或引号以下划线代替。
如果新的employee表已经建好，并包含3 000 条从employee_old表中提取并进行标准化处理的电子邮件地址。我们希望每个员工的电子邮件地址具有唯一性，于是Fernando Lopez的地址为flopez，而Francisco Lopez的地址为flopez2。实际上，我们实际测试的数据中有33 个潜在的重复项，所以我们需要做如下测试：
SQL> insert into employees(emp_num, emp_name,
emp_firstname, emp_email)
2 select emp_num,
3 emp_name,
4 emp_firstname,
5 substr(substr(EMP_FIRSTNAME, 1, 1)
6 ||translate(EMP_NAME, ' ''', '_ _'), 1, 12)
7 from employees_old;
insert into employees(emp_num, emp_name, emp_firstname, emp_email)
*
ERROR at line 1:
ORA-00001: unique constraint (EMP_EMAIL_UQ) violated
Elapsed: 00:00:00.85
3 000 条数据中重复 33 条，比率大约是 1%，所以，或许可以心安理得地处理符合标准的 99%，并用异常来处理其余部分。毕竟，1% 的不符标准数据带来的异常处理开销应该不大。以下是采用该“乐观方法”的代码：
SQL> declare
2 v_counter varchar2(12);
3 b_ok boolean;
4 n_counter number;
5 cursor c is select emp_num,
6 emp_name,
7 emp_firstname
8 from employees_old;
9 begin
10 for rec in c
11 loop
12 begin
13 insert into employees(emp_num, emp_name,
14 emp_firstname, emp_email)
15 values (rec.emp_num,
16 rec.emp_name,
17 rec.emp_firstname,
18 substr(substr(rec.emp_firstname, 1, 1)
19 ||translate(rec.emp_name, ' ''', '_ _'), 1, 12));
20 exception
21 when dup_val_on_index then
22 b_ok := FALSE;
23 n_counter := 1;
24 begin
25 v_counter := ltrim(to_char(n_counter));
26 insert into employees(emp_num, emp_name,
27 emp_firstname, emp_email)
28 values (rec.emp_num,
29 rec.emp_name,
30 rec.emp_firstname,
31 substr(substr(rec.emp_firstname, 1, 1)
32 ||translate(rec.emp_name, ' ''', '_ _'), 1,
33 12 - length(v_counter)) || v_counter);
34 b_ok := TRUE;
35 exception
36 when dup_val_on_index then
37 n_counter := n_counter + 1;
38 end;
39 end;
40 end loop;
41 end;
40 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:18.41
但这个异常处理的开销到底在哪里呢？让我们先从测试数据中剔除“问题记录”，然后再执行相同的测试，比较发现：这次测试的总运行时间，与上次几乎相同，都是18 秒。然而，从测试数据中剔除“问题记录”之后再执行前面第一段 insert...select 语句时，速度明显比循环快：最终发现采用“一次处理一行”的方式导致耗时增加了近 50%。那么，在此例中可以不用“一次处理一行”的方式吗？可以，但要首先避免使用异常。正是这个通过异常处理解决“问题记录”问题决定，迫使我们采用循序方式的。
另外，由于发生冲突的电子邮件地址可能不止一个，可以为它们指定某个数字获得唯一性。
很容易判断有多少个数据记录发生了冲突，增加一个group by子句就可以了。但在分配数字时，如果不使用主数据库系统提供的分析功能，恐怕比较困难。（Oracle 称为分析功能（analytical function），DB2 则称在线分析处理（online analytical processing，OLAP），SQL Server 称之为排名功能（ranking function）。）纯粹从SQL角度来看，探索此问题的解决方案很有意义。
重复的电子邮件地址都可以被赋予一个具唯一性的数字：1赋给年纪最大的员工，2 赋给年纪次之的的员工……依次类推。为此，可以编写一个子查询，如果是group中的第一个电子邮件地址就不作操作，而该group中的后续电子邮件地址则加上序号。代码如下：
SQL> insert into employees(emp_num, emp_firstname,
2 emp_name, emp_email)
3 select emp_num,
4 emp_firstname,
5 emp_name,
6 decode(rn, 1, emp_email,
7 substr(emp_email,
8 1, 12 - length(ltrim(to_char(rn))))
9 || ltrim(to_char(rn)))
10 from (select emp_num,
11 emp_firstname,
12 emp_name,
13 substr(substr(emp_firstname, 1, 1)
14 ||translate(emp_name, ' ''', '_ _'), 1, 12)
15 emp_email,
16 row_number()
17 over (partition by
18 substr(substr(emp_firstname, 1, 1)
19 ||translate(emp_name,' ''','_ _'),1,12)
20 order by emp_num) rn
21 from employees_old)
22 /
3000 rows created.
Elapsed: 00:00:11.68
上面的代码避免了一次一行的处理，而且该解决方案的执行时间仅是先前方案的 60%。