explain plan for
select count(*) from (SELECT
20141001 AS data_date
,NVL(T1.ACCT_NO, T2.ACCT_NO) AS acct_no
,NVL(T1.ACCT_ORD, T2.ACCT_ORD) AS acct_ord
,NVL(T1.ACCT_NO_PK, T2.ACCT_NO_PK) AS acct_no_pk
,NVL(T1.ACCT_BAL, 0) AS acct_bal
,NVL(T1.D_CMP_BAL, T2.D_CMP_BAL) AS d_cmp_bal
,NVL(T1.M_CMP_BAL, T2.M_CMP_BAL) AS m_cmp_bal
,NVL(T1.Y_CMP_BAL, T2.Y_CMP_BAL) AS y_cmp_bal
,NVL(T1.FLAG, T2.FLAG) AS flag
,NVL(T1.ACCT_FLAG, T2.ACCT_FLAG) AS acct_flag
,NVL(T1.TERM, T2.TERM) AS term
,NVL(T1.TERM_FLAG, T2.TERM_FLAG) AS term_flag
,NVL(T1.CUR_CODE, T2.CUR_CODE) AS cur_code
,NVL(T1.CUR_NAME, T2.CUR_NAME) AS cur_name
,NVL(T1.SUB_CODE, T2.SUB_CODE) AS sub_code
,NVL(T1.CUST_NO, T2.CUST_NO) AS cust_no
,NVL(T1.CUST_TYPE, T2.CUST_TYPE) AS cust_type
,NVL(T1.CUST_NAME, T2.CUST_NAME) AS cust_name
,NVL(T1.BANK_CORP_CODE, T2.BANK_CORP_CODE) AS bank_corp_code
,NVL(T1.BRAN_NAME, T2.BRAN_NAME) AS bran_name
,NVL(T1.MGR_CODE, T2.MGR_CODE) AS mgr_code
,NVL(T1.MGR_NAME, T2.MGR_NAME) AS mgr_name
,NVL(T1.OPEN_DATE, T2.OPEN_DATE) AS open_date
,NVL(T1.FIX_BAL, 0) AS fix_bal
,NVL(T1.DIV_FIX_FLAG, T2.DIV_FIX_FLAG) AS div_fix_flag
,NVL(T1.ADJUST_AMT, 0) AS adjust_amt
,NVL(T1.ADJUST_AMT_AF, 0) AS adjust_amt_af
,CASE
WHEN SUBSTR(20141001, -4) = '0101' THEN
NVL(T1.ADJUST_AMT_AF, 0)/360
ELSE
(NVL(T1.ADJUST_AMT_AF, 0) + NVL(T2.Y_ADD_AF, 0))/360
END AS y_avg_af
,CASE
WHEN SUBSTR(20141001, -4) = '0101' THEN
NVL(T1.ADJUST_AMT_AF, 0)
ELSE
(NVL(T1.ADJUST_AMT_AF, 0) + NVL(T2.Y_ADD_AF, 0))
END AS Y_ADD_AF
,NVL(T1.ACCT_INTR, 0) AS acct_intr
,NVL(T1.SIM_PROFIT, 0) AS sim_profit
,NVL(T1.SEPA_POR, T2.SEPA_POR) AS sepa_por
,NVL(T1.PRI, T2.PRI) AS pri
,NVL(T1.BRAN_CODE, T2.BRAN_CODE) AS bran_code
,NVL(T1.UNIT1_CODE, T2.UNIT1_CODE) AS unit1_code
,NVL(T1.UNIT2_CODE, T2.UNIT2_CODE) AS unit2_code
,NVL(T1.UNIT3_CODE, T2.UNIT3_CODE) AS unit3_code
,NVL(T1.UNIT4_CODE, T2.UNIT4_CODE) AS unit4_code
,NVL(T1.UNIT5_CODE, T2.UNIT5_CODE) AS unit5_code
,NVL(T1.DEPT1_CODE, T2.DEPT1_CODE) AS dept1_code
,NVL(T1.INTR_RATE,T2.INTR_RATE) AS intr_rate
,NVL(T1.DUE_DATE,T2.DUE_DATE) AS due_date
,t1.new_cust_flag as new_cust_flag
FROM ( SELECT
/*unique*/ * FROM T_PM_ACCT_DTL_AF a
WHERE DATA_DATE = 20141001
/*AND acct_flag in ('DEPOSIT','LOAN','OUTER','ETC','CHANGKOU','DYMX','SLL','LOAN_CN')
AND flag IS NOT NULL
AND dept1_code is not NULL
AND cur_code != 0
and length(TRIM(mgr_code)) >= 3*/ ) T1
FULL JOIN (
SELECT
/*unique*/ * FROM T_PM_ACCT_DTL_AF a
WHERE DATA_DATE = 20140930
/*AND acct_flag in ('DEPOSIT','LOAN','OUTER','ETC','CHANGKOU','DYMX','SLL','LOAN_CN')
AND flag IS NOT NULL
AND dept1_code is not NULL
AND cur_code != 0
and length(TRIM(mgr_code)) >= 3*/ ) T2
ON T1.ACCT_NO_PK = T2.ACCT_NO_PK
AND nvl(T1.MGR_CODE,'XXXXX') = nvl(T2.MGR_CODE,'XXXXX')
-- AND T1.DEPT1_CODE = T2.DEPT1_CODE --ó|??è??????àí?
AND nvl(T1.UNIT2_CODE,'XXXXX') = nvl(T2.UNIT2_CODE,'XXXXX')
AND nvl(T1.SUB_CODE,'XXXXX') = nvl(T2.SUB_CODE,'XXXXX')
and nvl(t1.flag,'X') = nvl(t2.flag,'X')
and nvl(t1.term,0) = nvl(t2.term,0) );
select * from table(dbms_xplan.display());
Plan hash value: 3843934819
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 181K (1)| 00:36:17 | | |
| 1 | SORT AGGREGATE | | 1 | | | | | | |
| 2 | VIEW | | 4444K| | | 181K (1)| 00:36:17 | | |
| 3 | UNION-ALL | | | | | | | | |
|* 4 | HASH JOIN RIGHT OUTER | | 3493K| 406M| 242M| 90693 (1)| 00:18:09 | | |
| 5 | PARTITION LIST SINGLE| | 3479K| 202M| | 33214 (1)| 00:06:39 | KEY | KEY |
| 6 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 3479K| 202M| | 33214 (1)| 00:06:39 | 638 | 638 |
| 7 | PARTITION LIST SINGLE| | 3493K| 203M| | 33356 (1)| 00:06:41 | KEY | KEY |
| 8 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 3493K| 203M| | 33356 (1)| 00:06:41 | 639 | 639 |
|* 9 | HASH JOIN ANTI | | 951K| 110M| 242M| 90670 (1)| 00:18:09 | | |
| 10 | PARTITION LIST SINGLE| | 3479K| 202M| | 33214 (1)| 00:06:39 | KEY | KEY |
| 11 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 3479K| 202M| | 33214 (1)| 00:06:39 | 638 | 638 |
| 12 | PARTITION LIST SINGLE| | 3493K| 203M| | 33333 (1)| 00:06:40 | KEY | KEY |
| 13 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 3493K| 203M| | 33333 (1)| 00:06:40 | 639 | 639 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(NVL("A"."TERM",0)=NVL("A"."TERM"(+),0) AND NVL("A"."FLAG",'X')=NVL("A"."FLAG"(+),'X') AND
NVL("A"."SUB_CODE",'XXXXX')=NVL("A"."SUB_CODE"(+),'XXXXX') AND
NVL("A"."UNIT2_CODE",'XXXXX')=NVL("A"."UNIT2_CODE"(+),'XXXXX') AND
NVL("A"."MGR_CODE",'XXXXX')=NVL("A"."MGR_CODE"(+),'XXXXX') AND "A"."ACCT_NO_PK"="A"."ACCT_NO_PK"(+))
9 - access("A"."ACCT_NO_PK"="A"."ACCT_NO_PK" AND NVL("A"."MGR_CODE",'XXXXX')=NVL("A"."MGR_CODE",'XXXXX')
AND NVL("A"."UNIT2_CODE",'XXXXX')=NVL("A"."UNIT2_CODE",'XXXXX') AND
NVL("A"."SUB_CODE",'XXXXX')=NVL("A"."SUB_CODE",'XXXXX') AND NVL("A"."FLAG",'X')=NVL("A"."FLAG",'X') AND
NVL("A"."TERM",0)=NVL("A"."TERM",0))
--------------------------------------------------------------------
explain plan for
select count(*) from (SELECT /*+ NATIVE_FULL_OUTER_JOIN */
20141001 AS data_date
,NVL(T1.ACCT_NO, T2.ACCT_NO) AS acct_no
,NVL(T1.ACCT_ORD, T2.ACCT_ORD) AS acct_ord
,NVL(T1.ACCT_NO_PK, T2.ACCT_NO_PK) AS acct_no_pk
,NVL(T1.ACCT_BAL, 0) AS acct_bal
,NVL(T1.D_CMP_BAL, T2.D_CMP_BAL) AS d_cmp_bal
,NVL(T1.M_CMP_BAL, T2.M_CMP_BAL) AS m_cmp_bal
,NVL(T1.Y_CMP_BAL, T2.Y_CMP_BAL) AS y_cmp_bal
,NVL(T1.FLAG, T2.FLAG) AS flag
,NVL(T1.ACCT_FLAG, T2.ACCT_FLAG) AS acct_flag
,NVL(T1.TERM, T2.TERM) AS term
,NVL(T1.TERM_FLAG, T2.TERM_FLAG) AS term_flag
,NVL(T1.CUR_CODE, T2.CUR_CODE) AS cur_code
,NVL(T1.CUR_NAME, T2.CUR_NAME) AS cur_name
,NVL(T1.SUB_CODE, T2.SUB_CODE) AS sub_code
,NVL(T1.CUST_NO, T2.CUST_NO) AS cust_no
,NVL(T1.CUST_TYPE, T2.CUST_TYPE) AS cust_type
,NVL(T1.CUST_NAME, T2.CUST_NAME) AS cust_name
,NVL(T1.BANK_CORP_CODE, T2.BANK_CORP_CODE) AS bank_corp_code
,NVL(T1.BRAN_NAME, T2.BRAN_NAME) AS bran_name
,NVL(T1.MGR_CODE, T2.MGR_CODE) AS mgr_code
,NVL(T1.MGR_NAME, T2.MGR_NAME) AS mgr_name
,NVL(T1.OPEN_DATE, T2.OPEN_DATE) AS open_date
,NVL(T1.FIX_BAL, 0) AS fix_bal
,NVL(T1.DIV_FIX_FLAG, T2.DIV_FIX_FLAG) AS div_fix_flag
,NVL(T1.ADJUST_AMT, 0) AS adjust_amt
,NVL(T1.ADJUST_AMT_AF, 0) AS adjust_amt_af
,CASE
WHEN SUBSTR(20141001, -4) = '0101' THEN
NVL(T1.ADJUST_AMT_AF, 0)/360
ELSE
(NVL(T1.ADJUST_AMT_AF, 0) + NVL(T2.Y_ADD_AF, 0))/360
END AS y_avg_af
,CASE
WHEN SUBSTR(20141001, -4) = '0101' THEN
NVL(T1.ADJUST_AMT_AF, 0)
ELSE
(NVL(T1.ADJUST_AMT_AF, 0) + NVL(T2.Y_ADD_AF, 0))
END AS Y_ADD_AF
,NVL(T1.ACCT_INTR, 0) AS acct_intr
,NVL(T1.SIM_PROFIT, 0) AS sim_profit
,NVL(T1.SEPA_POR, T2.SEPA_POR) AS sepa_por
,NVL(T1.PRI, T2.PRI) AS pri
,NVL(T1.BRAN_CODE, T2.BRAN_CODE) AS bran_code
,NVL(T1.UNIT1_CODE, T2.UNIT1_CODE) AS unit1_code
,NVL(T1.UNIT2_CODE, T2.UNIT2_CODE) AS unit2_code
,NVL(T1.UNIT3_CODE, T2.UNIT3_CODE) AS unit3_code
,NVL(T1.UNIT4_CODE, T2.UNIT4_CODE) AS unit4_code
,NVL(T1.UNIT5_CODE, T2.UNIT5_CODE) AS unit5_code
,NVL(T1.DEPT1_CODE, T2.DEPT1_CODE) AS dept1_code
,NVL(T1.INTR_RATE,T2.INTR_RATE) AS intr_rate
,NVL(T1.DUE_DATE,T2.DUE_DATE) AS due_date
,t1.new_cust_flag as new_cust_flag
FROM ( SELECT
/*unique*/ * FROM T_PM_ACCT_DTL_AF a
WHERE DATA_DATE = 20141001
/*AND acct_flag in ('DEPOSIT','LOAN','OUTER','ETC','CHANGKOU','DYMX','SLL','LOAN_CN')
AND flag IS NOT NULL
AND dept1_code is not NULL
AND cur_code != 0
and length(TRIM(mgr_code)) >= 3*/ ) T1
FULL JOIN (
SELECT
/*unique*/ * FROM T_PM_ACCT_DTL_AF a
WHERE DATA_DATE = 20140930
/*AND acct_flag in ('DEPOSIT','LOAN','OUTER','ETC','CHANGKOU','DYMX','SLL','LOAN_CN')
AND flag IS NOT NULL
AND dept1_code is not NULL
AND cur_code != 0
and length(TRIM(mgr_code)) >= 3*/ ) T2
ON T1.ACCT_NO_PK = T2.ACCT_NO_PK
AND nvl(T1.MGR_CODE,'XXXXX') = nvl(T2.MGR_CODE,'XXXXX')
-- AND T1.DEPT1_CODE = T2.DEPT1_CODE --ó|??è??????àí?
AND nvl(T1.UNIT2_CODE,'XXXXX') = nvl(T2.UNIT2_CODE,'XXXXX')
AND nvl(T1.SUB_CODE,'XXXXX') = nvl(T2.SUB_CODE,'XXXXX')
and nvl(t1.flag,'X') = nvl(t2.flag,'X')
and nvl(t1.term,0) = nvl(t2.term,0) );
select * from table(dbms_xplan.display());
Plan hash value: 943977707
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 114K (1)| 00:22:58 | | |
| 1 | SORT AGGREGATE | | 1 | | | | | | |
| 2 | VIEW | VW_FOJ_0 | 3493K| | | 114K (1)| 00:22:58 | | |
|* 3 | HASH JOIN FULL OUTER | | 3493K| 892M| 484M| 114K (1)| 00:22:58 | | |
| 4 | PARTITION LIST SINGLE| | 3479K| 444M| | 33218 (1)| 00:06:39 | KEY | KEY |
| 5 | VIEW | | 3479K| 444M| | 33218 (1)| 00:06:39 | | |
| 6 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 3479K| 202M| | 33218 (1)| 00:06:39 | 638 | 638 |
| 7 | PARTITION LIST SINGLE| | 3493K| 446M| | 33360 (1)| 00:06:41 | KEY | KEY |
| 8 | VIEW | | 3493K| 446M| | 33360 (1)| 00:06:41 | | |
| 9 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 3493K| 203M| | 33360 (1)| 00:06:41 | 639 | 639 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."ACCT_NO_PK"="T2"."ACCT_NO_PK" AND NVL("T1"."MGR_CODE",'XXXXX')=NVL("T2"."MGR_CODE",'XXXXX
') AND NVL("T1"."UNIT2_CODE",'XXXXX')=NVL("T2"."UNIT2_CODE",'XXXXX') AND
NVL("T1"."SUB_CODE",'XXXXX')=NVL("T2"."SUB_CODE",'XXXXX') AND NVL("T1"."FLAG",'X')=NVL("T2"."FLAG",'X') AND
NVL("T1"."TERM",0)=NVL("T2"."TERM",0))