`

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

阅读更多

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%。

分享到:
评论

相关推荐

    ORACLE9i_优化设计与系统调整

    §6.3.2 创建最少可重复测试 90 §6.3.3 测试假想 90 §6.3.4 记录和自动测试 90 §6.3.5 避免常见错误 90 第二部分 ORACLE应用系统设计优化 91 第8章ORACLE数据库系统优化安装 91 §7.1 应用系统环境规划和Oracle...

    C#编程经验技巧宝典

    数据处理 91 &lt;br&gt;5.1 数字处理技巧 92 &lt;br&gt;0140 如何对计算结果四舍五放入 92 &lt;br&gt;0141 如何将商品金额小写转换成大写 92 &lt;br&gt;0142 如何根据生日自动计算员工年龄 93 &lt;br&gt;0143 如何设置...

    程序员的SQL金典.rar

    通过对实际案例开发过程的详细分析,使读者掌握 SQL的综合应用技巧。 内容简介 本书主要介绍SQL的语法规则及在实际开发中的应用,并且对SQL在MySQL、MS SQL Server、Oracle和DB2中的差异进行了分析。本书分为3部分:...

    PHP开发实战1200例(第1卷).(清华出版.潘凯华.刘中华).part1

    实例022 在表格中插入宠物照片 38 实例023 Dreamweaver创建表单 40 实例024 Dreamweaver中创建和附加CSS样式 42 实例025 Dreamweaver控制弹出信息 45 实例026 Dreamweaver控制浏览器的窗口 46 实例027 通过...

    PHP开发实战1200例(第1卷).(清华出版.潘凯华.刘中华).part2

    实例022 在表格中插入宠物照片 38 实例023 Dreamweaver创建表单 40 实例024 Dreamweaver中创建和附加CSS样式 42 实例025 Dreamweaver控制弹出信息 45 实例026 Dreamweaver控制浏览器的窗口 46 实例027 通过...

    MySQL管理之道 性能调优、高可用与监控.part2.rar

    1.1.3 提高刷新脏页数量和合并插入数量,改善磁盘i/o处理能力 8 1.1.4 增加自适应刷新脏页功能 9 1.1.5 让innodb_buffer_pool缓冲池中的热数据存活更久 9 1.1.6 innodb的数据恢复时间加快 11 1.1.7 innodb同时...

    php网络开发完全手册

    7.3.1 字符串重复操作——str_repeat 104 7.3.2 字符串替换操作——str_replace 7.3.2 和str_ireplace 104 7.3.3 字符串分解操作——str_split 106 7.3.4 字符串单词数的计算函数—— 7.3.4 str_word_count 107 ...

    经典SQL语句大全

    19、说明:删除重复记录 1),delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) 2),select distinct * into temp from tablename delete from tablename insert into...

    数据库操作语句大全(sql)

    19、说明:删除重复记录 1),delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) 2),select distinct * into temp from tablename delete from tablename insert into...

    sql经典语句一部分

    19、说明:删除重复记录 1),delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) 2),select distinct * into temp from tablename delete from tablename insert into...

    Java开发实战1200例(第1卷).(清华出版.李钟尉.陈丹丹).part3

    实例058 用TreeSet生成不重复自动排序 随机数组 71 实例059 Map映射集合实现省市级联选择框 73 第4章 字符串处理技术 75 4.1 格式化字符串 76 实例060 把数字格式化为货币字符串 76 实例061 格式化当前日期 77 实例...

    数据库设计培训.pptx

    良好的数据库设计: 节省数据的存储空间 能够保证数据的完整性 方便进行数据库应用系统的开发 糟糕的数据库设计: 数据冗余、存储空间浪费 内存空间浪费 数据更新和插入的异常 数据库设计培训全文共37页,当前为第3...

    vc++ 应用源码包_1

    Visual.C++编程技巧精选500例源代码 内含各种例子(vc下各种控件的使用方法、标题栏与菜单栏、工具栏与状态栏、图标与光标、程序窗口、程序控制、进程与线程、字符串、文件读写操作、文件与文件夹属性操作、文件与...

Global site tag (gtag.js) - Google Analytics