求各位大神帮忙,为了比较一张表的每一列数据有没有更新,如果有更新就更新一个标志位,目标表大概就几万的数据,但是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
);
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
);
解决方案 »
- 急!求一条统计结果的sql
- 如何实现SQL的轮流显示
- sql语句如何写
- oracle 链接 Mysql 乱码问题请教
- 在请教一个PLSQL问题
- 请教高手,帮忙转一个SQLSERVER的函数(100分)
- 急,通过Oracle 的数据文件能否象 MSSQL2000那样附加吗?
- 大哥们,来帮忙,关于sqlldr导文本文件..
- WINDOWS下新装oracle,为什么我进不去呀
- 调用了一个过程或函数,如果很慢,怎么通过session表追踪???
- jdbc 调用oracle 的procedure ,查询的结果以游标返回,抛出no more data from read socket。
- ORACLE 游标丢数的问题
oracle判定使用索引的效率还不如全表扫描
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 后再比较
2、TAR.MAJOR_INCIDENT_SID与SRC.MAJOR_INCIDENT_SID,这两个字段的类型是否一致?