求各位大神幫忙，為了比較一張表的每一列資料有沒有更新，如果有更新就更新一個標志位，目標表大概就幾萬的資料，但是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
);
uj5u.com熱心網友回復：
where覆寫了全部的欄位，并且用or連接，oracle判定使用索引的效率還不如全表掃描
uj5u.com熱心網友回復：
哦，原來是這樣，非常感謝，請問對我這種需求還有沒別的更聰明一點的辦法呢？每個欄位都比較一下感覺效率很低啊
uj5u.com熱心網友回復：
你目的是記錄資料更新的時間，可以考慮在表上建觸發器，更新的時候同時記錄更新時間uj5u.com熱心網友回復：
分析一下執行計劃uj5u.com熱心網友回復：
用minus減出你要的資料的，然后根據條件updateuj5u.com熱心網友回復：
在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 后再比較
uj5u.com熱心網友回復：
根據更新時間判斷不行嗎uj5u.com熱心網友回復：
用觸發器很方便uj5u.com熱心網友回復：
1、TAR表上的MAJOR_INCIDENT_SID的欄位上有沒有索引？2、TAR.MAJOR_INCIDENT_SID與SRC.MAJOR_INCIDENT_SID，這兩個欄位的型別是否一致？
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/65128.html
標籤：開發