MERGE INTO效率很低

求各位大神帮忙，为了比较一张表的每一列数据有没有更新，如果有更新就更新一个标志位，目标表大概就几万的数据，但是sql根本跑不出来..建了索引也没用到...MERGE INTO TMM_D_OTG_MAJOR_INCIDENT TAR 
USING TMM_MV_S_OTG_MAJOR_INCIDENT  SRC 
ON (TAR.MAJOR_INCIDENT_SID = SRC.MAJOR_INCIDENT_SID)
WHEN MATCHED THEN
  UPDATE
  SET TAR.END_DATE                       = SYSDATE
  WHERE 1                                =0
  OR TAR.MAJOR_INCIDENT_NUMBER          <> SRC.MAJOR_INCIDENT_NUMBER
  OR TAR.PRIMARY_APPLICATION_NAME       <> SRC.PRIMARY_APPLICATION_NAME
  OR TAR.PRIMARY_APPLICATION_SID        <> SRC.PRIMARY_APPLICATION_SID
  OR TAR.MAJOR_INCIDENT_STATE_NAME      <> SRC.MAJOR_INCIDENT_STATE_NAME
  OR TAR.OPENED_AT                      <> SRC.OPENED_AT
  OR TAR.PRIMARY_IT_SERVICE_NAME        <> SRC.PRIMARY_IT_SERVICE_NAME
  OR TAR.PRIORITY                       <> SRC.PRIORITY
  OR TAR.SHORT_DESCRIPTION              <> SRC.SHORT_DESCRIPTION
  OR TAR.U_BUSINESS_IMPACT_DESCRIPTION  <> SRC.U_BUSINESS_IMPACT_DESCRIPTION
  OR TAR.U_BUS_START                    <> SRC.U_BUS_START
  OR TAR.U_BUS_END                      <> SRC.U_BUS_END
  OR TAR.U_CAUSED_BY_CHANGE             <> SRC.U_CAUSED_BY_CHANGE
  OR TAR.U_COB_INVOKED                  <> SRC.U_COB_INVOKED
  OR TAR.U_COB_REASON                   <> SRC.U_COB_REASON
  OR TAR.U_COUNTRIES_IMPACTED           <> SRC.U_COUNTRIES_IMPACTED
  OR TAR.U_EXT_END                      <> SRC.U_EXT_END
  OR TAR.U_EXT_START                    <> SRC.U_EXT_START
  OR TAR.U_FINANCIAL_IMPACT             <> SRC.U_FINANCIAL_IMPACT
  OR TAR.U_FIRM_FINANCIAL_IMPACT        <> SRC.U_FIRM_FINANCIAL_IMPACT
  OR TAR.U_HIGH_TIDE_SEVERITY           <> SRC.U_HIGH_TIDE_SEVERITY
  OR TAR.U_INCIDENT_DURATION_NAME       <> SRC.U_INCIDENT_DURATION_NAME
  OR TAR.U_MISSED_SLA                   <> SRC.U_MISSED_SLA
  OR TAR.U_MI_ACC_EXEC_NAME_CC          <> SRC.U_MI_ACC_EXEC_NAME_CC
  OR TAR.U_REGULATORY                   <> SRC.U_REGULATORY
  OR TAR.U_REGULATORY_ORG_NAME_CC       <> SRC.U_REGULATORY_ORG_NAME_CC
  OR TAR.U_REPUTATIONAL                 <> SRC.U_REPUTATIONAL
  OR TAR.U_SERVICE_OWNER_NAME_CC        <> SRC.U_SERVICE_OWNER_NAME_CC
  OR TAR.U_TOTAL_EXTERNAL_CUST_IMPACTED <> SRC.U_TOTAL_EXTERNAL_CUST_IMPACTED
  OR TAR.U_TOTAL_IMPACT_DURATION_NAME   <> SRC.U_TOTAL_IMPACT_DURATION_NAME
  OR TAR.U_TOTAL_USER_IMPACTED          <> SRC.U_TOTAL_USER_IMPACTED
  OR TAR.U_FAILED_APPLICATION_INST_NAME <> SRC.U_FAILED_APPLICATION_INST_NAME
  OR TAR.U_FAILED_APPLICATION_NAME      <> SRC.U_FAILED_APPLICATION_NAME
  OR TAR.U_FAILED_APPLICATION_INST_SID  <> SRC.U_FAILED_APPLICATION_INST_SID
  OR TAR.U_FAILED_APPLICATION_SID       <> SRC.U_FAILED_APPLICATION_SID
  OR TAR.U_TECH_START                   <> SRC.U_TECH_START
  OR TAR.U_TECH_END                     <> SRC.U_TECH_END
  OR TAR.U_TRADING_RISK                 <> SRC.U_TRADING_RISK
  OR TAR.DIRECT_CAUSE_NAME              <> SRC.DIRECT_CAUSE_NAME
  OR TAR.PARENT_INCIDENT_SID            <> SRC.PARENT_INCIDENT_SID
  OR TAR.TASK_CMDB_CI_NAME              <> SRC.TASK_CMDB_CI_NAME
  OR TAR.TASK_OPENED_AT                 <> SRC.TASK_OPENED_AT
  OR TAR.TASK_PRIORITY                  <> SRC.TASK_PRIORITY
  OR TAR.TASK_SEVERITY                  <> SRC.TASK_SEVERITY
  OR TAR.TASK_SHORT_DESCRIPTION         <> SRC.TASK_SHORT_DESCRIPTION
  OR TAR.TASK_NUMBER                    <> SRC.TASK_NUMBER
  OR TAR.T_IMPACTED_BUSINESSES_NAME_CC  <> SRC.T_IMPACTED_BUSINESSES_NAME_CC
  OR TAR.T_IMPACTED_COUNTRIES_NAME_CC   <> SRC.T_IMPACTED_COUNTRIES_NAME_CC
  OR TAR.T_IMPACTED_LOBS_NAME_CC        <> SRC.T_IMPACTED_LOBS_NAME_CC
  OR TAR.T_IMPACTED_REGIONS_NAME_CC     <> SRC.T_IMPACTED_REGIONS_NAME_CC
  OR TAR.T_IMPACTED_SECTORS_NAME_CC     <> SRC.T_IMPACTED_SECTORS_NAME_CC
  OR TAR.T_RESOLUTION_DESCRIPTION       <> SRC.T_RESOLUTION_DESCRIPTION
  OR TAR.T_IMPACTED_DSMT_COUNTRIES_NAME <> SRC.T_IMPACTED_DSMT_COUNTRIES_NAME
  OR TAR.T_IMPACTED_DSMT_SECTORS_NAME   <> SRC.T_IMPACTED_DSMT_SECTORS_NAME
  OR TAR.CAUSED_BY_NUM                  <> SRC.CAUSED_BY_NUM
  OR TAR.T_IMPACTED_DSMT_REGIONS_NAME   <> SRC.T_IMPACTED_DSMT_REGIONS_NAME WHEN NOT MATCHED THEN
  INSERT VALUES
    (
      SEQ_TMM_D_OTG_MAJOR_INCI_PK.NEXTVAL,
      SRC.MAJOR_INCIDENT_NUMBER,
      SRC.PRIMARY_APPLICATION_NAME,
      SRC.PRIMARY_APPLICATION_SID,
      SRC.MAJOR_INCIDENT_SID,
      SRC.MAJOR_INCIDENT_STATE_NAME,
      SRC.OPENED_AT,
      SRC.PRIMARY_IT_SERVICE_NAME,
      SRC.PRIORITY,
      SRC.SHORT_DESCRIPTION,
      SRC.U_BUSINESS_IMPACT_DESCRIPTION,
      SRC.U_BUS_START,
      SRC.U_BUS_END,
      SRC.U_CAUSED_BY_CHANGE,
      SRC.U_COB_INVOKED,
      SRC.U_COB_REASON,
      SRC.U_COUNTRIES_IMPACTED,
      SRC.U_EXT_END,
      SRC.U_EXT_START,
      SRC.U_FINANCIAL_IMPACT,
      SRC.U_FIRM_FINANCIAL_IMPACT,
      SRC.U_HIGH_TIDE_SEVERITY,
      SRC.U_INCIDENT_DURATION_NAME,
      SRC.U_MISSED_SLA,
      SRC.U_MI_ACC_EXEC_NAME_CC,
      SRC.U_REGULATORY,
      SRC.U_REGULATORY_ORG_NAME_CC,
      SRC.U_REPUTATIONAL,
      SRC.U_SERVICE_OWNER_NAME_CC,
      SRC.U_TOTAL_EXTERNAL_CUST_IMPACTED,
      SRC.U_TOTAL_IMPACT_DURATION_NAME,
      SRC.U_TOTAL_USER_IMPACTED,
      SRC.U_FAILED_APPLICATION_INST_NAME,
      SRC.U_FAILED_APPLICATION_NAME,
      SRC.U_FAILED_APPLICATION_INST_SID,
      SRC.U_FAILED_APPLICATION_SID,
      SRC.U_TECH_START,
      SRC.U_TECH_END,
      SRC.U_TRADING_RISK,
      SRC.DIRECT_CAUSE_NAME,
      SRC.PARENT_INCIDENT_SID,
      SRC.TASK_CMDB_CI_NAME,
      SRC.TASK_OPENED_AT,
      SRC.TASK_PRIORITY,
      SRC.TASK_SEVERITY,
      SRC.TASK_SHORT_DESCRIPTION,
      SRC.TASK_SID,
      SRC.TASK_NUMBER,
      SRC.T_IMPACTED_BUSINESSES_NAME_CC,
      SRC.T_IMPACTED_COUNTRIES_NAME_CC,
      SRC.T_IMPACTED_LOBS_NAME_CC,
      SRC.T_IMPACTED_REGIONS_NAME_CC,
      SRC.T_IMPACTED_SECTORS_NAME_CC,
      SRC.T_RESOLUTION_DESCRIPTION,
      SRC.EXTERN_REF_ID,
      SRC.EXTERN_REF_SYSTEM,
      SRC.T_IMPACTED_DSMT_COUNTRIES_NAME,
      SRC.T_IMPACTED_DSMT_SECTORS_NAME,
      SRC.CAUSED_BY_NUM,
      SRC.T_IMPACTED_DSMT_REGIONS_NAME,
      SYSDATE,
      SYSDATE
    );

解决方案 »

  1.   

    where覆盖了全部的字段，并且用or连接，
    oracle判定使用索引的效率还不如全表扫描
      

  2.   

    用minus减出你要的数据的，然后根据条件update
      

  3.   

    在WHEN  MATCHED 的update 语句目的就是，只要有其中一个字段不同就更新TAR.END_DATE= SYSDATE, 何不写成这样 － 
    UPDATE
      SET TAR.END_DATE                       = SYSDATE
      WHERE  
    （TAR.MAJOR_INCIDENT_NUMBER || concatenate other TAR fields)
     <> (SRC.MAJOR_INCIDENT_NUMBER || concatenate other SRC fields) 以上语句可以在条件两边做hash - dbms_utility.get_hash_value 后再比较
      

  4.   

    1、TAR表上的MAJOR_INCIDENT_SID的字段上有没有索引？
    2、TAR.MAJOR_INCIDENT_SID与SRC.MAJOR_INCIDENT_SID，这两个字段的类型是否一致？