执行计划的巨大差异
cuiht 2004-07-12 02:19:32 执行的语句如下:
INSERT /*+PARALLEL( A ,5)*/
INTO MASAKR.TB_KR_MID_MERGE_USER_MON a
SELECT /*+PARALLEL(A,7)*/ /*+PARALLEL(B,7)*/
V_DATE, NVL(B.USER_TYPE,A.USER_TYPE),NVL(B.USER_ID,A.USER_ID) ---- V_DATE 为日期变量
FROM MASAKR.TB_KR_TEMP_02_MERGE_USER_MON A
FULL OUTER JOIN MASAKR.TB_KR_TEMP_01_MERGE_USER_MON B
ON a.user_id =b.user_id ;
两种情况下的执行计划。
在SQLPLUS执行时执行计划是(1)
SQL: INSERT STATEMENT
VIEW
UNION-ALL
HASH JOIN (OUTER)
MASAKR.TB_KR_TEMP_02_MERGE_USER_MON (TABLE ACCESS FULL)
MASAKR.TB_KR_TEMP_01_MERGE_USER_MON (TABLE ACCESS FULL)
HASH JOIN (ANTI)
MASAKR.TB_KR_TEMP_01_MERGE_USER_MON (TABLE ACCESS FULL)
MASAKR.TB_KR_TEMP_02_MERGE_USER_MON (TABLE ACCESS FULL)
大约需要4分钟执行结束
但在后台调度时,其执行计划(2) 4个小时没出来结果。
SQL: INSERT STATEMENT
VIEW
UNION-ALL
HASH JOIN (OUTER)
MASAKR.TB_KR_TEMP_02_MERGE_USER_MON (TABLE ACCESS FULL)
MASAKR.TB_KR_TEMP_01_MERGE_USER_MON (TABLE ACCESS FULL)
FILTER
MASAKR.TB_KR_TEMP_01_MERGE_USER_MON (TABLE ACCESS FULL)
MASAKR.TB_KR_TEMP_02_MERGE_USER_MON (TABLE ACCESS FULL)