本帖最后由 dicksamfeng 于 2013-4-16 10:48 编辑

MERGE INTO TEST_EXTENTAE

USING (SELECT A.RECORD_ID,

A.DIR,

A.COM4,

A.COM5,

A.COM6,

A.HAR4,

A.HDESC,

A.HAR_DESC,

A.N_NAME,

A.N_CITY,

A.N_ST,

A.SL_DESC,

A.SC_DESC,

A.YRA,

A.MONTH,

A.QA,

A.UI_CITY,

A.UI_ST,

A.FB_CITY,

A.FB_CTRY,

A.R_CODE,

A.R_CITY,

A.R_CYCODE,

A.R_CTY,

A.H_FLAG,

A.UDATE,

A.XZ,

A.XT,

SUBSTR(JF.FTP_FILE_NAME, 1, length(JF.FTP_FILE_NAME) - 4) IMPORT_FILE

FROM TESTA, FILE_PROCESSJF

WHERE JF.DATA_TYPE = 'TEST'

AND STATUS = 'LOADING') AJ

ON (TRIM(AE.RECORD_ID) = TRIM(AJ.RECORD_ID))

WHEN MATCHED THEN

UPDATE

SET AE.DIR           = TRIM(AJ.DIR),

AE.COM4          = TRIM(AJ.COM4),

AE.COM5     = TRIM(AJ.COM5),

AE.COM6     = TRIM(AJ.COM6),

AE.HAR4         = TRIM(AJ.HAR4),

AE.HDESC       = TRIM(AJ.HDESC),

AE.HAR_DESC     = TRIM(AJ.HAR_DESC),

AE.N_NAME      = TRIM(AJ.N_NAME),

AE.N_CITY      = TRIM(AJ.N_CITY),

AE.N_ST        = TRIM(AJ.N_ST),

AE.SL_DESC    = TRIM(AJ.SL_DESC),

AE.SC_DESC     = TRIM(AJ.SC_DESC),

AE.YRA           = TRIM(AJ.YRA),

AE.MONTH         = TRIM(AJ.MONTH),

AE.QA          = TRIM(AJ.QA),

AE.UI_CITY     = TRIM(AJ.UI_CITY),

AE.UI_ST       = TRIM(AJ.UI_ST),

AE.FB_CITY    = TRIM(AJ.FB_CITY),

AE.FB_CTRY    = TRIM(AJ.FB_CTRY),

AE.R_CODE     = TRIM(AJ.R_CODE),

AE.R_CITY     = TRIM(AJ.R_CITY),

AE.R_CYCODE = TRIM(AJ.R_CYCODE),

AE.R_CTY  = TRIM(AJ.R_CTY),

AE.H_FLAG   = TRIM(AJ.H_FLAG),

AE.UDATE         = TRIM(AJ.UDATE),

AE.XZ      = TRIM(AJ.XZ),

AE.XT      = TRIM(AJ.XT),

AE.IMPORT_FILE   = TRIM(AJ.IMPORT_FILE),

AE.LAST_UPDATE_DATE = sysdate

WHEN NOT MATCHED THEN

INSERT

VALUES

(

TRIM(AJ.RECORD_ID),

TRIM(AJ.DIR),

TRIM(AJ.COM4),

TRIM(AJ.COM5),

TRIM(AJ.COM6),

TRIM(AJ.HAR4),

TRIM(AJ.HDESC),

TRIM(AJ.HAR_DESC),

TRIM(AJ.N_NAME),

TRIM(AJ.N_CITY),

TRIM(AJ.N_ST),

TRIM(AJ.SL_DESC),

TRIM(AJ.SC_DESC),

TRIM(AJ.YRA),

TRIM(AJ.MONTH),

TRIM(AJ.QA),

TRIM(AJ.UI_CITY),

TRIM(AJ.UI_ST),

TRIM(AJ.FB_CITY),

TRIM(AJ.FB_CTRY),

TRIM(AJ.R_CODE),

TRIM(AJ.R_CITY),

TRIM(AJ.R_CYCODE),

TRIM(AJ.R_CTY),

TRIM(AJ.H_FLAG),

TRIM(AJ.UDATE),

TRIM(AJ.XZ),

TRIM(AJ.XT),

TRIM(AJ.IMPORT_FILE),

sysdate);

SQL> select count(1) from test;  COUNT(1)----------    535683

SQL> select count(1) from test_extent;  COUNT(1)----------   1731857

执行计划

MERGE STATEMENT, GOAL = ALL_ROWS                        237550        9645746532        12973529085540

MERGE        DBA_OWNER        TEST_EXTENT

VIEW        DBA_OWNER

HASH JOIN OUTER                        237550        9645746532        7649076999876

MERGE JOIN CARTESIAN                        14908        557548        268180588

TABLE ACCESS FULL        DBA_OWNER        FILE_PROCESS        3        1        30

BUFFER SORT                        14905        535683        241593033

TABLE ACCESS FULL        DBA_OWNER        TEST        14905        535683        241593033

TABLE ACCESS FULL        DBA_OWNER        TEST_EXTENT        17307        1730031        539769672

整个过程跑了1204秒，db是在我机器上的，硬件不是很好，但应该不会跑那么慢吧？

test_extent与test都建立了主键：record_id，为什么没有走索引呢？ 该怎么优化好呢？