查看: 5758|回复: 4

[每日一题] PL/SQL Challenge 每日一题:2017-7-17 用FORALL 和 BULK COLLECT 改善性能

[复制链接]
论坛徽章:
528
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2017-7-20 05:17 | 显示全部楼层 |阅读模式

(原发表于 2011-7-21)

最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引:
http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处:
http://www.plsqlchallenge.com/

作者:Steven Feuerstein

运行环境:SQLPLUS, SERVEROUTPUT已打开
注:本题给出答案时候要求给予简要说明才能得到奖品

我们在人力资源的schema下有如下三张表(员工employee属于部门department,而部门属于区域division):

CREATE TABLE plch_departments
(
   department_id   INTEGER
, division_id     INTEGER
, department_name VARCHAR2(100)
)
/

CREATE TABLE plch_employees
(
   employee_id     INTEGER
, department_id   INTEGER
, last_name       VARCHAR2 (100)
, salary          NUMBER
, hire_date       DATE
)
/

CREATE TABLE plch_history
(
   department_id  INTEGER
, total_salary   NUMBER
, changed_on     DATE
)
/

我们用这个过程来给一个指定区域的所有员工修改工资:

CREATE OR REPLACE PROCEDURE plch_update (
   division_in   IN plch_departments.division_id%TYPE
, newsal_in     IN plch_employees.salary%TYPE)
IS
   CURSOR dept_cur
   IS
      SELECT department_id
           , (SELECT SUM (e.salary)
                FROM plch_employees e
               WHERE e.department_id = d.department_id) total_salary
        FROM plch_departments d
       WHERE division_id = division_in;
BEGIN
   FOR rec IN dept_cur
   LOOP
      BEGIN
         INSERT INTO plch_history (department_id, total_salary, changed_on)
              VALUES (rec.department_id, rec.total_salary, SYSDATE);

         rec.total_salary := newsal_in;

         plch_adjust_compensation (rec.department_id, rec.total_salary);

         UPDATE plch_employees
            SET salary = rec.total_salary
          WHERE department_id = rec.department_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            plch_log_error;
      END;
   END LOOP;
END plch_update;
/

这个程序已经工作了好几年了,但是最近我们完成了一系列收购,现在每个部门平均有15,000名员工,每个区域有8,000个部门。

哪些选项描述了一个完整的或者部分的改善程序性能的步骤,而保持所有现有的功能?

注意:你可以有如下假设:

plch_log_errors 和 plch_adjust_compensation 过程已经被定义,它们的逻辑和问题无关;
plch_adjust_compensation过程的逻辑不能够从一个SQL语句中执行(也即,哪怕它被重写成一个函数,它也必须在一个PL/SQL块中被调用);
每次执行这个过程,INSERT和UPDATE都会对这个指定区域的某些(或许全部)部门抛出异常;
执行这个过程的会话有足够的PGA内存。

(A)
保留原来的FOR游标循环(ORACLE会自动把它们优化成BULK COLLECT级别的性能)。只需将那个FOR游标循环中的两个DML语句替换成FORALL语句。

(B)
将两个DML语句(INSERT和UPDATE)替换成一个FORALL,而这个FORALL执行同样的这两个语句,利用的是BULK COLLECT填充的集合中的数据。


(C)
假设部门和工资已经“装载”到名为l_departments 和 l_salaries的集合中,利用如下的两个FORALL语句执行:

BEGIN
   FORALL indx IN 1 .. l_departments.COUNT SAVE EXCEPTIONS
      INSERT INTO plch_history (department_id, total_salary, changed_on)
           VALUES (l_departments (indx), l_salaries (indx), SYSDATE);

   FORALL indx IN 1 .. l_departments.COUNT SAVE EXCEPTIONS
      UPDATE plch_employees
         SET salary = l_salaries (indx)
       WHERE department_id = l_departments (indx);
EXCEPTION
   WHEN OTHERS
   THEN
      ...记录 SQL%BULK_EXCEPTIONS 中找到的错误...
END;

(D)
在plch_update过程的异常处理器中将所有异常作为一个整体来处理,如下:

PROCEDURE plch_update...
IS
BEGIN
   ... 代码的主体 ...
EXCEPTION
   WHEN OTHERS THEN
      ... 此处处理所有错误 ...
END;

(E)
将两个DML语句加入LOG ERRORS, 并且把它们放在FORALL语句之中。

(F)
将INSERT到历史表的语句转移到触发器中,将整个游标循环及其嵌套的代码块取代成一个UPDATE语句,这个语句修改在指定区域的部门工作的所有员工工资。

(G)
以上答案都不对。

论坛徽章:
401
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2017-7-20 05:31 来自手机 | 显示全部楼层
先存历史,再更新的办法不错

使用道具 举报

回复
论坛徽章:
323
生肖徽章2007版:猴
日期:2008-05-16 11:28:59生肖徽章2007版:马
日期:2008-10-08 17:01:01SQL大赛参与纪念
日期:2011-04-13 12:08:17授权会员
日期:2011-06-17 16:14:53ITPUB元老
日期:2011-06-21 11:47:01ITPUB官方微博粉丝徽章
日期:2011-07-01 09:45:27ITPUB十周年纪念徽章
日期:2011-09-27 16:30:472012新春纪念徽章
日期:2012-01-04 11:51:22海蓝宝石
日期:2012-02-20 19:24:27铁扇公主
日期:2012-02-21 15:03:13
发表于 2017-7-20 09:14 | 显示全部楼层
C

A: 保留FOR 循环,在里面替换DML为FORALL,应该是FORALL替换FOR循环,不然还是单行操作,不会提升效率
B: 将两个DML语句(INSERT和UPDATE)替换成一个FORALL,这个不能这样,一个FORALL 只能接一个DML
C:  FORALL替换FOR循环,OK
D: 放外层,一旦报错,就退出了,保持所有现有的功能,这个就做不到了
E:  LOG ERRORS 或 FORALL ,选一个方法就可以,而不是同时
F:  要求说了,plch_adjust_compensation过程的逻辑不能够从一个SQL语句中执行,所以一个UPDATE处理不了
G: c是可以的

使用道具 举报

回复
论坛徽章:
10
秀才
日期:2017-06-29 10:10:37秀才
日期:2017-08-23 14:11:07秀才
日期:2017-08-11 15:37:01秀才
日期:2017-08-11 15:37:01秀才
日期:2017-08-11 15:37:01秀才
日期:2017-08-11 15:37:01秀才
日期:2017-06-29 10:17:04技术图书徽章
日期:2017-06-29 10:17:04秀才
日期:2017-06-29 10:16:48秀才
日期:2018-01-02 10:32:00
发表于 2017-7-20 15:49 | 显示全部楼层
学习了!

使用道具 举报

回复
论坛徽章:
528
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2017-7-21 03:53 | 显示全部楼层
答案G,本期无人得奖。

A: FOR游标循环能被PL/SQL编译器自动优化,这确实是真的。不幸的是我们仍然需要把它变成BULK COLLECT。为了利用FORALL, 我们需要把查询的数据转移到集合中,然后传递给FORALL语句。
B: 你在一个FORALL中只能用一个DML语句,所以这里需要“两个”不同的FORALL语句。
C: INSERT先执行,并且它是针对游标识别出来的每一行执行的。所以FORALL的数值型区间格式完全没问题。
然而需要注意的是,如果有一个INSERT由于任何原因失败了,UPDATE就不能被执行。所以这个INSERT FORALL必须用SAVE EXCEPTION捕获错误。然后它必须和UPDATE FORALL沟通使得这些特定的行不被修改。

虽然有可能写出这种代码,使得你仍然可以使用数值型区间格式,但是更合理(也更简单)的方法是用INDICES OF(虽然你也可以用VALUES OF)

除了转换为INDICES OF之外,你至少还需要把第一个FORALL语句放在它自己的嵌套块中,带有它自己的异常部分,使得错误能够被捕获并处理,然后才能执行FORALL...UPDATE。

D: 这个方法绝对不能和原有功能匹配。原功能用了一个嵌套块和异常部分来捕获并处理任何发生的错误,记录下来,然后继续处理数据。

为了和这个功能匹配,SAVE EXCEPTIONS必须和所有FORALL语句一起使用,即使一个单独的DML语句出错了,处理过程还能够继续。

E: LOG ERRORS在行的级别移除了SQL错误。但是你必须匹配原有的功能,它是在语句级移除异常的。而且,假设中已经说明UPDATE语句确实会出错,它会影响多行。因此,使用LOG ERRORS 可能导致不同的结果,所以是错的。

F: 寻找各种途径来简化你的代码并且近可能多地依赖“纯”SQL,这永远是一个好的思路。你当然能够写一个UPDATE SQL来修改在指定区域的部门工作的所有员工工资。

然而,这样的代码转换不能满足所有的需求及假设。具体地说:

这个单独的UPDATE语句违背了调整算法必须在SQL之外执行的假设。
如果你把INSERT语句和调整算法中的一种或两种放在触发器中,你就失去了FORALL的性能收益。
当代码被放到触发器中和一个DML中,它就无法模拟原来版本中的错误处理行为。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

【有奖讨论】解决存储挑战了解一下
奖品:米家车载空气净化器 、米家声波电动牙刷 、小米运动蓝牙耳机

在数字经济时代,井喷式增长的数据,在释放大量商业价值的同时,也随之对企业的IT基础设施带来了不容忽视的挑战!如何存储、管理、使用这些数据呢?这是一条比以往更艰难的路~

活动时间:9月20日-10月11日

参与讨论>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表