SQL优化求解

Question

临时被安排来优化sql，但是功力尚浅，找不到可行的方法，希望各位给出点建议，下面是我从过程里抠出来的代表性代码，由于代码较长，分开贴declare
  V_S_TIME DATE;
  V_E_TIME DATE;
  V_ERRMESSAGE VARCHAR2(100);  I_NUMBER    INTEGER;
  
   CURSOR CURSOR_PRPCINSURED_D IS SELECT (SELECT T.VEHICLEID FROM
       T_CMCS_S_POLICY_CAR T
       WHERE G.POLICYNO=T.POLICYNO) AS VEHICLEID,
       G.INSUREDTYPE,
       G.INSUREDCODE,
       G.INSUREDNAME,
       G.INSUREDADDRESS,
       G.INSUREDNATURE,
       G.INSUREDFLAG,
       G.INSUREDIDENTITY,
       G.IDENTIFYTYPE,
       G.IDENTIFYNUMBER,
       G.ACCOUNT,
       G.DEPARTUREDATE,
       G.DEPARTUREHOUR,
       G.POLICYNO,
       G.PHONENUMBER,
       G.MOBILE,
       G.INTG_DATASTATE,
       G.SERIALNO
  FROM ODSSEND.T_CMCS_PRPCINSURED G WHERE INTG_DATASTATE = 'D';

  TYPE T_POLICYINSURED IS TABLE OF T_CMCS_S_VEHICLE_CONTACTS%ROWTYPE INDEX BY BINARY_INTEGER;
  V_POLICYINSURED T_POLICYINSURED;  TYPE T_PRPCINSURED IS TABLE OF T_PRPCINSURED_RECORD INDEX BY BINARY_INTEGER;
  V_PRPCINSURED T_PRPCINSURED;

Answer 1

begin
    V_S_TIME := SYSDATE;
 
  LOOP
    FETCH CURSOR_PRPCINSURED BULK COLLECT
      INTO V_PRPCINSURED LIMIT 1000;    FOR I_INDEX IN 1 .. V_PRPCINSURED.COUNT LOOP
      BEGIN
        V_PRPCINSUREDCODE := V_PRPCINSURED(I_INDEX);
        V_POLICYINSURED(I_INDEX).ID := SEQ_S_VEHICLE_CONTACTS.NEXTVAL;
        V_POLICYINSURED(I_INDEX).VEHICLEID := V_PRPCINSUREDCODE.V_VEHICLEID;
        V_POLICYINSURED(I_INDEX).INSUREDTYPE := V_PRPCINSUREDCODE.V_INSUREDTYPE;
        V_POLICYINSURED(I_INDEX).INSUREDCODE := V_PRPCINSUREDCODE.V_INSUREDCODE;
        V_POLICYINSURED(I_INDEX).INSUREDNAME := V_PRPCINSUREDCODE.V_INSUREDNAME;
        V_POLICYINSURED(I_INDEX).INSUREDADDRESS := V_PRPCINSUREDCODE.V_INSUREDADDRESS;
        V_POLICYINSURED(I_INDEX).INSUREDNATURE := V_PRPCINSUREDCODE.V_INSUREDNATURE;
        V_POLICYINSURED(I_INDEX).INSUREDFLAG := V_PRPCINSUREDCODE.V_INSUREDFLAG;
        V_POLICYINSURED(I_INDEX).INSUREDIDENTITY := V_PRPCINSUREDCODE.V_INSUREDIDENTITY;
        V_POLICYINSURED(I_INDEX).IDENTIFYTYPE := V_PRPCINSUREDCODE.V_IDENTIFYTYPE;
        V_POLICYINSURED(I_INDEX).IDENTIFYNUMBER := V_PRPCINSUREDCODE.V_IDENTIFYNUMBER;
        V_POLICYINSURED(I_INDEX).ACCOUNT := V_PRPCINSUREDCODE.V_ACCOUNT;
        V_POLICYINSURED(I_INDEX).DEPARTUREDATE := V_PRPCINSUREDCODE.V_DEPARTUREDATE;
        V_POLICYINSURED(I_INDEX).DEPARTUREHOUR := V_PRPCINSUREDCODE.V_DEPARTUREHOUR;
        V_POLICYINSURED(I_INDEX).INSUREDSEX := '';
        V_POLICYINSURED(I_INDEX).INSUREDBIRTHDAY := '';
        V_POLICYINSURED(I_INDEX).IDENTIFYCOM := '';
        V_POLICYINSURED(I_INDEX).BUSIPHASE := '2';
        V_POLICYINSURED(I_INDEX).BUSINO := V_PRPCINSUREDCODE.V_BUSINO;
        V_POLICYINSURED(I_INDEX).CONTACTSINDEX := '0';
        V_POLICYINSURED(I_INDEX).PHONENUMBER := V_PRPCINSUREDCODE.V_PHONENUMBER;
        V_POLICYINSURED(I_INDEX).MOBILE := V_PRPCINSUREDCODE.V_MOBILE;
        V_POLICYINSURED(I_INDEX).SERIALNO := V_PRPCINSUREDCODE.V_SERIALNO;        --下发状态为 D
        IF V_PRPCINSUREDCODE.V_DATASTATE = 'D' THEN          DELETE FROM T_CMCS_S_VEHICLE_CONTACTS G
           WHERE G.BUSINO = V_POLICYINSURED(I_INDEX).BUSINO
             AND G.SERIALNO = V_PRPCINSUREDCODE.V_SERIALNO;
          --下发状态不为 D
        ELSE
          /*SELECT COUNT(1)
            INTO I_NUMBER
            FROM T_CMCS_S_VEHICLE_CONTACTS G
           WHERE G.BUSINO = V_POLICYINSURED(I_INDEX).BUSINO
             AND G.SERIALNO = V_PRPCINSUREDCODE.V_SERIALNO;*/             SELECT CASE WHEN EXISTS (SELECT 1 FROM T_CMCS_S_VEHICLE_CONTACTS A
             WHERE A.BUSINO = V_POLICYINSURED(I_INDEX).BUSINO AND A.SERIALNO = V_PRPCINSUREDCODE.V_SERIALNO)
             THEN 1 ELSE 0 END INTO I_NUMBER FROM DUAL;
          --存储区关系人表无该数据
          IF I_NUMBER = 0 THEN            INSERT INTO T_CMCS_S_VEHICLE_CONTACTS
            VALUES V_POLICYINSURED
              (I_INDEX);          ELSE            UPDATE T_CMCS_S_VEHICLE_CONTACTS G
               SET G.INSUREDTYPE     = V_PRPCINSUREDCODE.V_INSUREDTYPE,
                   G.INSUREDCODE     = V_PRPCINSUREDCODE.V_INSUREDCODE,
                   G.INSUREDNAME     = V_PRPCINSUREDCODE.V_INSUREDNAME,
                   G.INSUREDADDRESS  = V_PRPCINSUREDCODE.V_INSUREDADDRESS,
                   G.INSUREDNATURE   = V_PRPCINSUREDCODE.V_INSUREDNATURE,
                   G.INSUREDFLAG     = V_PRPCINSUREDCODE.V_INSUREDFLAG,
                   G.INSUREDIDENTITY = V_PRPCINSUREDCODE.V_INSUREDIDENTITY,
                   G.IDENTIFYTYPE    = V_PRPCINSUREDCODE.V_IDENTIFYTYPE,
                   G.IDENTIFYNUMBER  = V_PRPCINSUREDCODE.V_IDENTIFYNUMBER,
                   G.ACCOUNT         = V_PRPCINSUREDCODE.V_ACCOUNT,
                   G.DEPARTUREDATE   = V_PRPCINSUREDCODE.V_DEPARTUREDATE,
                   G.DEPARTUREHOUR   = V_PRPCINSUREDCODE.V_DEPARTUREHOUR,
                   G.PHONENUMBER     = V_PRPCINSUREDCODE.V_PHONENUMBER,
                   G.MOBILE          = V_PRPCINSUREDCODE.V_MOBILE
             WHERE G.BUSINO = V_PRPCINSUREDCODE.V_BUSINO
               AND G.SERIALNO = V_PRPCINSUREDCODE.V_SERIALNO;          END IF;        END IF;
      EXCEPTION
        WHEN OTHERS THEN          INSERT INTO T_CMCS_PRPCINSURED_ERR
            (POLICYNO,
             RISKCODE,
             SERIALNO,
             LANGUAGE,
             INSUREDTYPE,
             INSUREDCODE,
             INSUREDNAME,
             INSUREDADDRESS,
             INSUREDNATURE,
             INSUREDFLAG,
             INSUREDIDENTITY,
             RELATESERIALNO,
             IDENTIFYTYPE,
             IDENTIFYNUMBER,
             CREDITLEVEL,
             POSSESSNATURE,
             BUSINESSSOURCE,
             BUSINESSSORT,
             OCCUPATIONCODE,
             EDUCATIONCODE,
             BANK,
             ACCOUNTNAME,
             ACCOUNT,
             LINKERNAME,
             POSTADDRESS,
             POSTCODE,
             PHONENUMBER,
             MOBILE,
             EMAIL,
             BENEFITRATE,
             BENEFITFLAG,
             FLAG,
             WORKTYPE,
             REMARK,
             APPLYQUANTITY,
             ACCOUNTNATURE,
             BANKAGENTNAME,
             BANKBRANCHESCODE,
             BANKBRANCHESNAME,
             NATIONALITY,
             ACCEPTSMFLAG,
             TRAINNUMBER,
             SEATNUMBER,
             DEPARTURESTATION,
             ARRIVALSTATION,
             DEPARTUREDATE,
             DEPARTUREHOUR,
             TICKETNUMBER,
             INSUREDVISASERIALNO,
             INSUREDVERIFYNO,
             IDENTIFYVALIDDATE,
             CUSTNO,
             MAJORFLAG,
             MAJORLEVEL,
             ISPUBLICKIND,
             DISABILITYEVALSTD,
             CLSLEVEL,
             INTG_DATASOURCE,
             INTG_DATASTATE,
             INTG_U_CHANGEDATE,
             INTG_ETL_SYSTIME,
             HANDLETIME,
             HANDLETYPE,
             HANDLESTATUS,
             ORGCODE)
            SELECT POLICYNO,
                   RISKCODE,
                   SERIALNO,
                   LANGUAGE,
                   INSUREDTYPE,
                   INSUREDCODE,
                   INSUREDNAME,
                   INSUREDADDRESS,
                   INSUREDNATURE,
                   INSUREDFLAG,
                   INSUREDIDENTITY,
                   RELATESERIALNO,
                   IDENTIFYTYPE,
                   IDENTIFYNUMBER,
                   CREDITLEVEL,
                   POSSESSNATURE,
                   BUSINESSSOURCE,
                   BUSINESSSORT,
                   OCCUPATIONCODE,
                   EDUCATIONCODE,
                   BANK,
                   ACCOUNTNAME,
                   ACCOUNT,
                   LINKERNAME,
                   POSTADDRESS,
                   POSTCODE,
                   PHONENUMBER,
                   MOBILE,
                   EMAIL,
                   BENEFITRATE,
                   BENEFITFLAG,
                   FLAG,
                   WORKTYPE,
                   REMARK,
                   APPLYQUANTITY,
                   ACCOUNTNATURE,
                   BANKAGENTNAME,
                   BANKBRANCHESCODE,
                   BANKBRANCHESNAME,
                   NATIONALITY,
                   ACCEPTSMFLAG,
                   TRAINNUMBER,
                   SEATNUMBER,
                   DEPARTURESTATION,
                   ARRIVALSTATION,
                   DEPARTUREDATE,
                   DEPARTUREHOUR,
                   TICKETNUMBER,
                   INSUREDVISASERIALNO,
                   INSUREDVERIFYNO,
                   IDENTIFYVALIDDATE,
                   CUSTNO,
                   MAJORFLAG,
                   MAJORLEVEL,
                   ISPUBLICKIND,
                   DISABILITYEVALSTD,
                   CLSLEVEL,
                   INTG_DATASOURCE,
                   INTG_DATASTATE,
                   INTG_U_CHANGEDATE,
                   INTG_ETL_SYSTIME,
                   SYSDATE,
                   '',
                   '0',
                   ORGCODE
              FROM ODSSEND.T_CMCS_PRPCINSURED T
             WHERE T.POLICYNO = V_POLICYINSURED(I_INDEX).BUSINO
               AND T.SERIALNO = V_PRPCINSUREDCODE.V_SERIALNO;          DELETE FROM ODSSEND.T_CMCS_PRPCINSURED T
           WHERE T.POLICYNO = V_POLICYINSURED(I_INDEX).BUSINO
             AND T.SERIALNO = V_PRPCINSUREDCODE.V_SERIALNO;          V_ERRMESSAGE := SUBSTR(SQLERRM, 1, 100); --返回错误信息          SP_DOLOG('P_I_POLICY-T_CMCS_S_VEHICLE_CONTACTS',
                   V_POLICYINSURED(I_INDEX)
                   .BUSINO || V_PRPCINSUREDCODE.V_SERIALNO,
                   V_ERRMESSAGE);
          COMMIT;
      END;
    END LOOP;
    COMMIT;
    EXIT WHEN CURSOR_PRPCINSURED%NOTFOUND;
  END LOOP;
  CLOSE CURSOR_PRPCINSURED;
  V_E_TIME := SYSDATE;
  SP_DOHLOG('T_CMCS_PRPCINSURED', V_S_TIME, V_E_TIME, '1');
  end;

Answer 2

这个标量子查询(SELECT T.VEHICLEID FROM
       T_CMCS_S_POLICY_CAR T
       WHERE G.POLICYNO=T.POLICYNO) AS VEHICLEID,
改成表关联查询

Answer 3

这个表量子查询我改成left join了 但是效果没多大

Answer 4

T_CMCS_S_POLICY_CAR 需要有POLICYNO字段索引

Answer 5

把所有SQL都拿出来单独执行看看哪个慢，再优化

Answer 6

我尝试过使用merge的方式来做，但是异常处理部分中要拿到错误数据，我无法实现额

Answer 7

我尝试过使用merge的方式来做

Answer 8

把所有SQL都拿出来单独执行看看哪个慢，再优化这是一整个循环，要怎么拆出来单个执行额，我不是太懂唉 

Answer 9

把所有SQL都拿出来单独执行看看哪个慢，再优化这是一整个循环，要怎么拆出来单个执行额，我不是太懂唉 这个有啥不懂的，把SQL拉出来啊，条件里的参数随便给个值看看执行效率啊

Answer 10

把所有SQL都拿出来单独执行看看哪个慢，再优化这是一整个循环，要怎么拆出来单个执行额，我不是太懂唉 这个有啥不懂的，把SQL拉出来啊，条件里的参数随便给个值看看执行效率啊
昨天测试了下各操作的效率问题：
数据量：22698条
插入：61.152s
更新：63.242s
删除：4.025s

Answer 11

优化后的代码如下：-- MERGE关系人表
declare 
    V_S_TIME DATE;
V_E_TIME DATE;
V_ERRMESSAGE VARCHAR2(100); I_NUMBER    INTEGER;
TYPE T_I_PRPCINSURED IS REF CURSOR;
CURSOR_PRPCINSURED T_I_PRPCINSURED; TYPE T_PRPCINSURED_RECORD IS RECORD(
V_BUSINO          ODSSEND.T_CMCS_PRPCINSURED.POLICYNO%TYPE,
V_SERIALNO        ODSSEND.T_CMCS_PRPCINSURED.SERIALNO%TYPE);
V_PRPCINSUREDCODE T_PRPCINSURED_RECORD; TYPE T_PRPCINSURED IS TABLE OF T_PRPCINSURED_RECORD INDEX BY BINARY_INTEGER;
V_PRPCINSURED T_PRPCINSURED;
begin
-- Test statements here
V_S_TIME := SYSDATE;    
        --下发状态为 D
    OPEN CURSOR_PRPCINSURED FOR 'SELECT POLICYNO,SERIALNO FROM ODSSEND.T_CMCS_PRPCINSURED WHERE INTG_DATASTATE = ''D''';
    LOOP
      FETCH CURSOR_PRPCINSURED BULK COLLECT INTO V_PRPCINSURED LIMIT 1000;
      FORALL I IN 1..V_PRPCINSURED.COUNT
          DELETE FROM T_CMCS_S_VEHICLE_CONTACTS G
          WHERE G.BUSINO = V_PRPCINSURED(I).V_BUSINO
            AND G.SERIALNO = V_PRPCINSURED(I).V_SERIALNO;
      EXIT WHEN  CURSOR_PRPCINSURED%NOTFOUND;
  COMMIT;
    END LOOP;
    CLOSE CURSOR_PRPCINSURED;
          --下发状态不为 D
    
     MERGE INTO T_CMCS_S_VEHICLE_CONTACTS A
     USING ( SELECT T.VEHICLEID, 
          G.INSUREDTYPE,
          G.INSUREDCODE,
          G.INSUREDNAME,
          G.INSUREDADDRESS,
          G.INSUREDNATURE,
          G.INSUREDFLAG,
          G.INSUREDIDENTITY,
          G.IDENTIFYTYPE,
          G.IDENTIFYNUMBER,
          G.ACCOUNT,
          G.DEPARTUREDATE,
          G.DEPARTUREHOUR,
          G.POLICYNO,
          G.PHONENUMBER,
          G.MOBILE,
          G.INTG_DATASTATE,
          G.SERIALNO
        FROM ODSSEND.T_CMCS_PRPCINSURED G
        LEFT JOIN T_CMCS_S_POLICY_CAR T
        ON G.POLICYNO=T.POLICYNO 
        WHERE G.INTG_DATASTATE <> 'D') B
      ON ( A.BUSINO = B.POLICYNO AND A.SERIALNO = B.SERIALNO)
     WHEN MATCHED THEN
      UPDATE SET A.INSUREDTYPE     = B.INSUREDTYPE,
                A.INSUREDCODE     = B.INSUREDCODE,
                A.INSUREDNAME     = B.INSUREDNAME,
                A.INSUREDADDRESS  = B.INSUREDADDRESS,
                A.INSUREDNATURE   = B.INSUREDNATURE,
                A.INSUREDFLAG     = B.INSUREDFLAG,
                A.INSUREDIDENTITY = B.INSUREDIDENTITY,
                A.IDENTIFYTYPE    = B.IDENTIFYTYPE,
                A.IDENTIFYNUMBER  = B.IDENTIFYNUMBER,
                A.ACCOUNT         = B.ACCOUNT,
                A.DEPARTUREDATE   = B.DEPARTUREDATE,
                A.DEPARTUREHOUR   = B.DEPARTUREHOUR,
                A.PHONENUMBER     = B.PHONENUMBER,
                A.MOBILE          = B.MOBILE
     WHEN NOT MATCHED THEN
      INSERT VALUES (
        SEQ_S_VEHICLE_CONTACTS.NEXTVAL,
        B.VEHICLEID,
        B.INSUREDTYPE,
        B.INSUREDCODE,
        B.INSUREDNAME,
        B.INSUREDADDRESS,
        B.INSUREDNATURE,
        B.INSUREDFLAG,
        B.INSUREDIDENTITY,
        B.IDENTIFYTYPE,
        B.IDENTIFYNUMBER,
        B.ACCOUNT,
        B.DEPARTUREDATE,
        B.DEPARTUREHOUR,
        '',
        '',
        '',
        '2',
        B.POLICYNO,
        '0',
        B.PHONENUMBER,
        B.MOBILE,
        B.SERIALNO);
        COMMIT;
      EXCEPTION
        WHEN OTHERS THEN
          V_ERRMESSAGE := SUBSTR(SQLERRM, 1, 100); --返回错误信息
          SP_DOLOG('P_I_POLICY-T_CMCS_S_VEHICLE_CONTACTS','',V_ERRMESSAGE);
          
  V_E_TIME := SYSDATE;
  SP_DOHLOG('T_CMCS_PRPCINSURED', V_S_TIME, V_E_TIME, '1');
end;优化后的执行效率很显著
插入：0.827s
更新：0.718s
删除：3.027s但是这样做，原过程异常部分对异常数据的操作，在优化后的过程里我不会操作，各位大大有什么好的建议？但是这样做的话，一场部分