执行的语句如下:
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)
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)
如果记录多的时候,想得到结果就很慢了
肯定快些,但为什么后台程序的执行计划不一样呢?
上述过程单进程没有任何问题,1000万 连接 900万的记录 4分钟就可结束。
若用子进程,就出现BUG,就是 HASH JOIN 在子进程中存在BUG