ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 1731|回复: 4

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

[复制链接]
论坛徽章:
459
探花
日期:2015-08-18 09:50:16秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
发表于 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)
以上答案都不对。

论坛徽章:
394
阿斯顿马丁
日期:2014-01-03 13:53:522014年世界杯参赛球队:喀麦隆
日期:2014-07-11 12:10:53马上有对象
日期:2014-04-09 16:19:542014年世界杯参赛球队: 洪都拉斯
日期:2014-06-25 08:25:55itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-10-01 15:27:22itpub13周年纪念徽章
日期:2014-10-09 12:04:18马上有钱
日期:2014-10-14 21:37:37马上有钱
日期:2015-01-22 00:39:13喜羊羊
日期:2015-02-20 22:26:07
发表于 2017-7-20 05:31 来自手机 | 显示全部楼层
先存历史,再更新的办法不错

使用道具 举报

回复
论坛徽章:
254
秀才
日期:2016-09-27 15:16:21技术图书徽章
日期:2016-04-29 15:04:10秀才
日期:2016-03-28 10:21:13巨蟹座
日期:2016-03-26 21:14:25水瓶座
日期:2016-03-24 22:16:36摩羯座
日期:2016-03-17 15:09:14巨蟹座
日期:2016-03-12 12:03:33双鱼座
日期:2016-02-27 21:51:59射手座
日期:2016-02-23 23:47:49双子座
日期:2016-01-27 13:01:30
发表于 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是可以的

使用道具 举报

回复
论坛徽章:
8
秀才
日期:2017-06-29 10:10:37秀才
日期:2017-06-29 10:16:48技术图书徽章
日期:2017-06-29 10:17:04秀才
日期:2017-06-29 10:17:04秀才
日期: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-7-20 15:49 | 显示全部楼层
学习了!

使用道具 举报

回复
论坛徽章:
459
探花
日期:2015-08-18 09:50:16秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
 楼主| 发表于 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中,它就无法模拟原来版本中的错误处理行为。

使用道具 举报

回复

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

本版积分规则

SACC2017购票7.8折优惠进行时

2017中国系统架构师大会(SACC2017)将于10月19-21日在北京新云南皇冠假日酒店震撼来袭。今年,大会以“云智未来”为主题,云集国内外顶级专家,围绕云计算、人工智能、大数据、移动互联网、产业应用等热点领域展开技术探讨与交流。本届大会共设置2大主会场,18个技术专场;邀请来自互联网、金融、制造业、电商等多个领域,100余位技术专家及行业领袖来分享他们的经验;并将吸引4000+人次的系统运维、架构师及IT决策人士参会,为他们提供最具价值的交流平台。
----------------------------------------
优惠时间:2017年8月30日前

活动链接>>
TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表