Oracle数据库中的逐行处理问题

日期： 2011-03-13 作者：Karen Morton翻译：包春霞 来源：TechTarget中国 英文

问：同事告诉我下面我写的这个脚本如果只用一条SQL来处理会更快一些，在date_ended列上用MIN会有问题(可能会存在重复记录和空的情况)。这是真的吗?为什么?为什么我的代码性能如此糟糕？ spool &&filename  — set serveroutput on format wrapped execute dbms_output.new_line; execute dbms_output.put_line(‘In UPDAT……

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论，本站保留删除与本文无关和不雅评论的权力。

问：同事告诉我下面我写的这个脚本如果只用一条SQL来处理会更快一些，在date_ended列上用MIN会有问题(可能会存在重复记录和空的情况)。这是真的吗?为什么?为什么我的代码性能如此糟糕？

spool &&filename 
-- set serveroutput on format wrapped execute dbms_output.new_line; execute dbms_output.put_line('In UPDATE_VLE_RESP.sql...'); execute dbms_output.new_line;
 DECLARE  
  -- ===============================================================   
  -- Constants   
  -- ===============================================================   
  -- VLE AUTHOR and COAUTHOR responsibilities constants   sql_c_usage_type                  
  constant varchar2(1)   := 'P';   
  sql_c_vle_auth     constant varchar2(10)  := 'VLE_AUTHOR';   
  sql_c_vle_coauth   constant varchar2(12)  := 'VLE_COAUTHOR';   
  sql_c_ps_role_object  constant varchar2(3)   := 'PPI';   
  sql_c_rv_domain       constant varchar2(15)  := 'PS_ROLE_OBJECTS';
   -- new VLE PROG_ORG responsibilities constants
 sql_c_prog_org   constant varchar2(8)   := 'PROG_ORG';
   -- ===============================================================   
   -- Type and Subtype Declarations   
   -- ===============================================================   
   -- ===============================================================   
   -- Variable Declarations   
   -- ===============================================================
   sql_commit_level           NUMBER  := 0;   
   sql_future_vle_resp_cnt    NUMBER  := 0;   
   sql_vle_upd_total          NUMBER  := 0;   
   sql_msg_text               VARCHAR2(1000);   
   sql_hdr                    VARCHAR2(1);
   sql_vle_rpt_total          NUMBER  := 0;   
   --   
   -- =====================================================   
   -- Cursors   
   -- =====================================================   
   -- get all current PPI responsibility types of VLE_AUTHOR and VLE_COAUTHOR.   
   -- This will be used to close off these responsibilities and report those   
   -- responsibilities that have been closed off in the future before they are updated.   
   CURSOR c_psr_vle (
   p_vle_usage_type IN MISREG_PROG_STRUCT_ROLES.usage_type%TYPE,                     
   p_vle_auth IN MISREG_PROG_STRUCT_ROLES.opr_type_code%TYPE,                     
   p_vle_coauth IN MISREG_PROG_STRUCT_ROLES.opr_type_code%TYPE,                     
   p_vle_ps_role_object IN MISREG_PROG_STRUCT_ROLES.rv_ps_role_object%TYPE,                     
   p_vle_rv_domain IN MISREG_PROG_STRUCT_ROLES.rv_domain%TYPE) IS
     SELECT rowid,
            psr.role_code,
            psr.hple_person_code,
            psr.opr_type_code, -- VLE_AUTHOR/VLE_COAUTHOR
            psr.rv_ps_role_object, -- 'PPI'
            psr.rv_domain, -- 'PS_ROLE_OBJECTS'
            psr.ppi_programme_code,
            psr.ppi_consecutive_version_no,
            psr.ppi_concurrent_version_no,
            psr.ppi_programme_syllabus_period,
            psr.ppi_domain_psp,
            psr.ppi_location_code,
            psr.ppi_calendar_type_code,
            psr.ppi_moa_code,
            psr.date_started,
            psr.date_ended
     FROM misreg_prog_struct_roles psr
     WHERE psr.usage_type = p_vle_usage_type
       AND psr.opr_type_code IN (p_vle_auth, p_vle_coauth)
       AND psr.rv_ps_role_object = p_vle_ps_role_object
       AND psr.rv_domain = p_vle_rv_domain
       AND SYSDATE BETWEEN psr.date_started AND NVL(psr.date_ended,SYSDATE);
   --
   -- =====================================================
   -- Procedures and Functions
   -- =====================================================

    PROCEDURE show_error(
    p_err_code   number,
    p_err_text   varchar2) 
    IS
   BEGIN
     raise_application_error(p_err_code, p_err_text);
   END show_error;
   ------------------------------------------------------------------------------
   PROCEDURE show_sys_error(
   p_err_code   number,
   p_err_text   varchar2) IS
   BEGIN
     show_error(p_err_code => p_err_code,
                p_err_text => p_err_text||' - '||
                              dbms_utility.format_error_stack);
   END show_sys_error;
   ------------------------------------------------------------------------------
   PROCEDURE show_msg(
   p_text  VARCHAR2, 
   p_new_line VARCHAR2) IS
   sql_buffer NUMBER := 1;
   BEGIN
     WHILE sql_buffer <= LENGTH(p_text) LOOP   
       dbms_output.put_line(substr(p_text,sql_buffer,200));
       sql_buffer := sql_buffer + 200;
     END LOOP;
     IF p_new_line = 'Y' THEN
       dbms_output.new_line;
     END IF;
   END show_msg;
   ------------------------------------------------------------------------------
   -- Insert PPI responsibility type of PROG_ORG based on existing current
   -- PPI responsibility type of VLE_AUTHOR/VLE_COAUTHOR. If person has both
   -- responsibilities then only 1 PROG_ORG record is created for the one with
   -- the earliest end date. NB. if no end date has been recorded then PROG_ORG
   -- record will be created with a NULL end date.
   PROCEDURE ins_prog_org_resps 
(p_vle_usage_type IN MISREG_PROG_STRUCT_ROLES.usage_type%TYPE,
 p_vle_auth IN MISREG_PROG_STRUCT_ROLES.opr_type_code%TYPE,
 p_vle_coauth IN MISREG_PROG_STRUCT_ROLES.opr_type_code%TYPE,
 p_prog_org IN MISREG_PROG_STRUCT_ROLES.opr_type_code%TYPE,
 p_vle_ps_role_object IN MISREG_PROG_STRUCT_ROLES.rv_ps_role_object%TYPE,
 p_vle_rv_domain IN MISREG_PROG_STRUCT_ROLES.rv_domain%TYPE)
 IS
     sql_role_code MISREG_PROG_STRUCT_ROLES.role_code%TYPE;
   BEGIN
     --
     sql_msg_text := 'Copying VLE responsibilities to new PROG_ORG responsibilities...';
     show_msg(p_text => sql_msg_text, p_new_line => 'N');
    --
     INSERT INTO misreg_prog_struct_roles (role_code,
                                           usage_type,
                                           created_by,
                                           created_date,
                                           hple_person_code,
                                           opr_type_code,
                                           rv_ps_role_object,
                                           rv_domain,
                                           ppi_programme_code,
                                           ppi_consecutive_version_no,
                                           ppi_concurrent_version_no,
                                           ppi_programme_syllabus_period,
                                           ppi_domain_psp,
                                           ppi_location_code,
                                           ppi_calendar_type_code,
                                           ppi_moa_code,
                                           date_started,
                                           date_ended)
     SELECT misreg_prog_struct_role_seqs.nextval, inner_view.*
     FROM (SELECT DISTINCT p_vle_usage_type in_usage_type, -- 'P'
            USER,
            SYSDATE in_cr_date,
            psr.hple_person_code,
            p_prog_org in_prog_org, -- 'PROG_ORG'
            p_vle_ps_role_object in_ps_role_object, -- 'PPI'
            p_vle_rv_domain in_vle_rv_domain, -- 'PS_ROLE_OBJECTS'
            psr.ppi_programme_code,
            psr.ppi_consecutive_version_no,
            psr.ppi_concurrent_version_no,
            psr.ppi_programme_syllabus_period,
            psr.ppi_domain_psp,
            psr.ppi_location_code,
            psr.ppi_calendar_type_code,
            psr.ppi_moa_code,
            SYSDATE in_date_started,
            psr.date_ended
     FROM misreg_prog_struct_roles psr
     WHERE psr.usage_type = p_vle_usage_type -- 'P'
       AND psr.opr_type_code IN (p_vle_auth,p_vle_coauth)
       AND psr.rv_ps_role_object = p_vle_ps_role_object
       AND psr.rv_domain = p_vle_rv_domain
       AND SYSDATE BETWEEN psr.date_started AND NVL(psr.date_ended,SYSDATE)
       AND NVL(psr.date_ended,SYSDATE) =
          (SELECT MIN(NVL(psr2.date_ended,SYSDATE)) earliest_date
           FROM misreg_prog_struct_roles psr2
           WHERE psr.usage_type = psr2.usage_type
             AND psr.hple_person_code = psr2.hple_person_code
             AND psr.rv_domain = psr2.rv_domain
             AND psr.rv_ps_role_object = psr2.rv_ps_role_object
             AND psr.ppi_programme_code = psr2.ppi_programme_code
             AND psr.ppi_consecutive_version_no = psr2.ppi_consecutive_version_no
             AND psr.ppi_concurrent_version_no = psr2.ppi_concurrent_version_no
             AND psr.ppi_programme_syllabus_period = psr2.ppi_programme_syllabus_period
             AND psr.ppi_domain_psp = psr2.ppi_domain_psp
             AND psr.ppi_location_code = psr2.ppi_location_code
             AND psr.ppi_calendar_type_code = psr2.ppi_calendar_type_code
             AND psr.ppi_moa_code = psr2.ppi_moa_code
             AND SYSDATE BETWEEN psr2.date_started AND NVL(psr2.date_ended,SYSDATE))) inner_view;
     --
     sql_msg_text := '---- There were '||SQL%rowcount||' VLE responsibilities copied to new PROG_ORG responsibilities...';
     show_msg(p_text => sql_msg_text, p_new_line => 'Y');
 --    dbms_output.put_line(chr(10));
 --    dbms_output.put_line('VLE responsibilities records copied: '||SQL%rowcount);
   EXCEPTION
     WHEN OTHERS THEN
       rollback;
       show_sys_error(-20120,
                      'Error: Inserting Prog Struct Roles for '||
                      'Usage Type: '|| p_vle_usage_type|| ' and ' ||
                      'Opr Type Code: '|| p_prog_org || ' and ' ||
                      'Prog Struct Role object: '|| p_vle_ps_role_object);
   END;
   ------------------------------------------------------------------------------
   -- Check if there are any existing future PPI responsibility types of
   -- VLE_AUTHOR/VLE_COAUTHOR. If there are then no further processing is done.
   FUNCTION future_vle_resp (p_vle_usage_type IN MISREG_PROG_STRUCT_ROLES.usage_type%TYPE,
                             p_vle_auth IN MISREG_PROG_STRUCT_ROLES.opr_type_code%TYPE,
                             p_vle_coauth IN MISREG_PROG_STRUCT_ROLES.opr_type_code%TYPE,
                             p_vle_ps_role_object IN MISREG_PROG_STRUCT_ROLES.rv_ps_role_object%TYPE,
                             p_vle_rv_domain IN MISREG_PROG_STRUCT_ROLES.rv_domain%TYPE)
     RETURN NUMBER
   IS
     v_return   NUMBER :=0;
   BEGIN
     SELECT COUNT(psr.role_code)
     INTO v_return
     FROM misreg_prog_struct_roles psr
     WHERE psr.usage_type = p_vle_usage_type -- 'P'
       AND psr.opr_type_code IN (p_vle_auth,p_vle_coauth)
       AND psr.rv_ps_role_object = p_vle_ps_role_object
       AND psr.rv_domain = p_vle_rv_domain
       AND SYSDATE < psr.date_started;
     --
     RETURN v_return;
     --
   EXCEPTION
     WHEN OTHERS THEN
       rollback;
       show_sys_error(-20120,
                      'Error: Checking future Prog Struct Roles for '||
                      'Usage Type: '|| p_vle_usage_type|| ' and ' ||
                      'Opr Type Codes: '|| p_vle_auth || ', ' || p_vle_coauth || ' and ' ||
                      'Prog Struct Role object: '|| p_vle_ps_role_object);
   END;
   --
   --=====================================================
   -- Main Processing
   --=====================================================
 BEGIN
   dbms_output.enable('1000000');
   -- =========================================================
   -- PROCESS VLE RESPONSIBILITIES UPDATE
   -- =========================================================
   --
   -- Check if there are any future responsibilities
   sql_future_vle_resp_cnt := future_vle_resp (sql_c_usage_type,
                                           sql_c_vle_auth,
                                           sql_c_vle_coauth,
                                           sql_c_ps_role_object,
                                           sql_c_rv_domain);
   --
   IF sql_future_vle_resp_cnt = 0 THEN
     -- Copy existing VLE responsibilities
     ins_prog_org_resps (sql_c_usage_type,
                         sql_c_vle_auth,
                         sql_c_vle_coauth,
                         sql_c_prog_org,
                         sql_c_ps_role_object,
                         sql_c_rv_domain);
     --
     -- Get existing VLE responsibilities to be closed off
     sql_msg_text := 'Closing off existing current VLE responsibilities...';
     show_msg(p_text => sql_msg_text, p_new_line => 'Y');
 --    dbms_output.put_line(chr(10));
 --    dbms_output.put_line('Closing off existing VLE responsibilities...');
     --
     sql_hdr := 'Y';
     FOR c_upd_vle in c_psr_vle(sql_c_usage_type,
                                sql_c_vle_auth,
                                sql_c_vle_coauth,
                                sql_c_ps_role_object,
                                sql_c_rv_domain) LOOP
       --
       sql_vle_upd_total := sql_vle_upd_total + 1;
       --
       IF c_upd_vle.date_ended IS NOT NULL THEN
         -- current VLE responsibility has been closed off in the future so report it
         IF sql_hdr = 'Y' THEN
           sql_msg_text := 'Current VLE Responsibilities closed off in the future';
           show_msg(p_text => sql_msg_text, p_new_line => 'N');
           sql_msg_text := '-----------------------------------------------------';
           show_msg(p_text => sql_msg_text, p_new_line => 'Y');
           sql_hdr := 'N';
         END IF;
         -- keep a running total of reported recs to use in output
         sql_vle_rpt_total := sql_vle_rpt_total + 1;
         sql_msg_text := 'Rec Num: '|| sql_vle_rpt_total;
         show_msg(p_text => sql_msg_text, p_new_line => 'N');
         sql_msg_text := '----------------------';
         show_msg(p_text => sql_msg_text, p_new_line => 'N');
         sql_msg_text := 'Role Code: '||c_upd_vle.role_code||'. '||
                         'Person Code: '||c_upd_vle.hple_person_code||'. '||
                         'Opr Type Code: '||c_upd_vle.opr_type_code||'. '||
                         'PS Role Obj: '||c_upd_vle.rv_ps_role_object||'. '||
                         'RV Domain: '||c_upd_vle.rv_domain||'. '||
                         'PPI Prog Code: '||c_upd_vle.ppi_programme_code||'. '||
                         'PPI Cons: '||c_upd_vle.ppi_consecutive_version_no||'. '||
                         'PPI Conc: '||c_upd_vle.ppi_concurrent_version_no||'. '||
                         'PPI Period: '||c_upd_vle.ppi_programme_syllabus_period||'. '||
                         'PPI Domain PSP: '||c_upd_vle.ppi_domain_psp||'. '||
                         'PPI Location: '||c_upd_vle.ppi_location_code||'. '||
                         'PPI Cal Type: '||c_upd_vle.ppi_calendar_type_code||'. '||
                         'PPI MoA: '||c_upd_vle.ppi_moa_code||'. '||
                         'Date Started: '||c_upd_vle.date_started||'. '||
                         'Date Ended: '||c_upd_vle.date_ended||'.';
         --
         show_msg(p_text => sql_msg_text, p_new_line => 'Y');
         -- VLE responsibilities record has been closed off in the future
       END IF;
       --
       -- Update VLE responsibilities and close off
       UPDATE misreg_prog_struct_roles
       SET date_ended = (SYSDATE - 1)
       WHERE rowid = c_upd_vle.rowid;
       --
       sql_commit_level := sql_commit_level + 1;  -- Commit after every X records
       IF sql_commit_level > 1000 THEN
         COMMIT;
         sql_commit_level := 0;
       END IF;
         --
     END LOOP;
     --
     sql_msg_text := '---- There were '||sql_vle_upd_total||' current VLE Responsibilities closed off (date_ended set to '||(sysdate-1)||')...';
     show_msg(p_text => sql_msg_text, p_new_line => 'Y');
     --
     COMMIT;
     --
   ELSE
     dbms_output.new_line;
     sql_msg_text := 'Error: Cannot process VLE responsibilities updates.'||chr(10)||
                     'There are future Prog Struct Roles for...'||chr(10)||
                     'Usage Type: '|| sql_c_usage_type|| ' and '||
                     'Opr Type Codes: '|| sql_c_vle_auth || ', ' || sql_c_vle_coauth || ' and ' ||chr(10)||
                     'Prog Struct Role object: '|| sql_c_ps_role_object;
     show_msg(p_text => sql_msg_text, p_new_line => 'Y');
     --
   END IF;
   --
   sql_msg_text := '...UPDATE_VLE_RESP.sql finished.';
   show_msg(p_text => sql_msg_text, p_new_line => 'N');
 END;
 /

 

　　答：你同事给你建议最可能的原因是你在采用逐行处理数据的方式。如Tom Kyte喜欢说的：“逐行处理数据是越来越慢”。做任何事情一次处理一行而不是一次处理多行，原理上都会更慢一些。

　　可以这样理解：如果你要到杂货店买10件商品，你来到杂货店，取第一件商品，付款，拿回家，放起来，然后检查一下列表，看看你需要的下一个商品，回到商店，拿那个商品，如此直到取完了10件商品。傻子才会这么做不是吗?你最好一趟就拿起所有的商品。对整个数据集一次处理一条记录就象去杂货店10趟拿10个商品，就是太慢了。听起来很有趣，但是与实际生活中的场景类比：就象用一条SQL语句处理一批数据和用PL/SQL循环一次处理一行的道理是一样的。

　　你的代码中的一个线索就是，可以肯定你需要在PL/SQL循环中处理的一个或两个SQL语句基本上是一样的。他们都有基本相同的解释(Where从句)。对于要更新的行，真正要做的就是设置date_ended为不同的值，除此之外没有别的。所以，为什么要一行一行地处理呢?为什么不用带Where的select语句选出需要Update的数据，然后直接更新，而不是一次只更新一行。

　　基本上你要做的就是如下几步：

　　1、执行一个查询看是否有date_started日期早于今天的的行。

　　2、给一个表插入新记录(这个逻辑让我有些晕，但如果说它能象期望的一样工作，那么OK)。Min子句的用法很可能可以用分析函数实现(我比较倾向于这样做)，那是我很愿意谈的整个其他主题，如果你想深入了解，并能一直跟踪的话。

　　3、接着，读取所有的行，就是原有的行加上新插入的行，更新每行的date_ended。

　　即便是最坏的情况，应该也是执行两个SQL语句就可以搞定：1)执行SQL插入你需要增加的行;2)用一条Update语句修改date_ended 。这种方式比一次处理一行的方式要节约时间和资源，节约时间和资源的多少视这个过程影响的行数多少有很大不同。

　　另外就是你自己多琢磨琢磨，这有助于理解逐行处理引起了额外的负担，叫做上下文交换。无论何时你从PL/SQL循环中执行SQL，Oracle都不得不在PL/SQL执行引擎和SQL执行引擎之间进行换入换出的上下文交换。在你的例子中是从Update执行语句和PL/SQL引擎间来回交换。这些交换带来了负担。即便只是1/1000秒，如果是处理几千条记录，单单是上下文交换就可以吃掉大量不必要的时间。如果使用单条SQL语句代替，就完全可以避免这些交换。当然，你还是能在PL/SQL块中执行两个或多个SQL语句，并进行例外处理等，但是真的不必用那些额外的代码来处理一次更新一行。再想想杂货店的例子，如果有100，000行记录要更新，你可以执行100，000次单独的Update语句(象你正在做的)，或者一次更新那100，000行记录。

　　最后，我要告诉你的是，试试看，就能很容易比较每种处理方式所用的时间。如果只是处理几百行记录，可能不会感觉到太明显的区别，但如果处理几千条或几万条记录就很明显。但是从性能优化的观点看，无论何时都应该总是要避免一行一行地处理数据。

作者

Karen Morton
Karen Morton

相关推荐