SQL语句:
SELECT /*+parallel(t1 16)*/
T1.DATA_DATE,
T1.ACCT_NO,
T1.ACCT_ORD,
T1.ACCT_NO_PK,
T1.ACCT_BAL,
T1.D_CMP_BAL,
T1.M_CMP_BAL,
T1.Y_CMP_BAL,
T1.FLAG,
T1.ACCT_FLAG,
T1.TERM,
T1.TERM_FLAG,
T1.CUR_CODE,
NVL(T5.CUR_NAME, T1.CUR_NAME) AS CUR_NAME,
T1.SUB_CODE,
T1.CUST_NO,
T1.CUST_TYPE,
NVL(T3.CUST_NAME, T1.CUST_NAME) AS CUST_NAME,
T1.BANK_CORP_CODE,
NVL(T4.BRAN_NAME, T1.BRAN_NAME) AS BRAN_NAME,
T1.MGR_CODE,
T1.MGR_NAME,
T1.OPEN_DATE,
T1.FIX_BAL,
T1.DIV_FIX_FLAG,
T1.ADJUST_AMT,
T1.ADJUST_AMT_AF,
T1.Y_AVG_AF,
T1.Y_ADD_AF,
T1.ACCT_INTR,
T1.SIM_PROFIT,
T1.SEPA_POR,
T1.PRI,
T1.BRAN_CODE,
T1.UNIT1_CODE,
T1.UNIT2_CODE,
T1.UNIT3_CODE,
T1.UNIT4_CODE,
NVL(T6.UNIT5_CODE, T1.UNIT5_CODE) AS UNIT5_CODE,
NVL(T6.DEPT1_CODE, T1.DEPT1_CODE) AS DEPT1_CODE,
T1.INTR_RATE,
T1.DUE_DATE
FROM (SELECT /*++use_hash(T1 T2) parallel(T1 16)*/
20121223 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(20121223, -4) = '0101' THEN
NVL(T1.ADJUST_AMT_AF, 0) / 90
ELSE
(NVL(T1.ADJUST_AMT_AF, 0) + NVL(T2.Y_ADD_AF, 0)) / 90
END AS Y_AVG_AF,
CASE
WHEN SUBSTR(20121223, -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
FROM (SELECT /*+parallel(a 16)*/
*
FROM T_PM_ACCT_DTL_AF A
WHERE DATA_DATE = 20121223
AND ACCT_FLAG IN ('DEPOSIT',
'LOAN',
'OUTER',
'ETC',
'CHANGKOU',
'DYMX')
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 /*+parallel(a 16)*/
*
FROM T_PM_ACCT_DTL_AF A
WHERE DATA_DATE = 20131222
AND ACCT_FLAG IN ('DEPOSIT',
'LOAN',
'OUTER',
'ETC',
'CHANGKOU',
'DYMX')
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 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)
UNION ALL
SELECT /*+use_hash(T1 T2) parallel(T1 16)*/
20121223 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(20121223, -4) = '0101' THEN
NVL(T1.ADJUST_AMT_AF, 0) / 90
ELSE
(NVL(T1.ADJUST_AMT_AF, 0) + NVL(T2.Y_ADD_AF, 0)) / 90
END AS Y_AVG_AF,
CASE
WHEN SUBSTR(20121223, -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
FROM (SELECT /*+parallel(a 16)*/
*
FROM T_PM_ACCT_DTL_AF A
WHERE DATA_DATE = 20121223
AND ACCT_FLAG IN ('INTLBU', 'PFS', 'INCOME')
AND FLAG <> '625'
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 /*+parallel(a 16)*/
*
FROM T_PM_ACCT_DTL_AF A
WHERE DATA_DATE = 20131222
AND ACCT_FLAG IN ('INTLBU', 'PFS', 'INCOME')
AND FLAG <> '625'
AND FLAG IS NOT NULL
AND DEPT1_CODE IS NOT NULL
AND CUR_CODE != 0
AND LENGTH(TRIM(MGR_CODE)) >= 3) T2
ON NVL(T1.ACCT_NO_PK, 'XXXXX') = NVL(T2.ACCT_NO_PK, 'XXXXX')
AND NVL(T1.MGR_CODE, 'XXXXX') = NVL(T2.MGR_CODE, 'XXXXX')
AND NVL(T1.UNIT3_CODE, 'XXXXX') = NVL(T2.UNIT3_CODE, 'XXXXX')
AND NVL(T1.UNIT4_CODE, 'XXXXX') = NVL(T2.UNIT4_CODE, 'XXXXX')
AND NVL(T1.FLAG, 'X') = NVL(T2.FLAG, 'X')
UNION ALL
SELECT /*+use_hash(T1 T2) parallel(T1 16)*/
20121223 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(20121223, -4) = '0101' THEN
NVL(T1.ADJUST_AMT_AF, 0) / 90
ELSE
(NVL(T1.ADJUST_AMT_AF, 0) + NVL(T2.Y_ADD_AF, 0)) / 90
END AS Y_AVG_AF,
CASE
WHEN SUBSTR(20121223, -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
FROM (SELECT /*+parallel(a 16)*/
*
FROM T_PM_ACCT_DTL_AF A
WHERE DATA_DATE = 20121223
AND ACCT_FLAG = 'PFS'
AND FLAG = '625'
AND DEPT1_CODE IS NOT NULL
AND CUR_CODE != 0
AND LENGTH(TRIM(MGR_CODE)) >= 3
AND FLAG IS NOT NULL) T1
FULL JOIN (SELECT /*+parallel(a 16)*/
*
FROM T_PM_ACCT_DTL_AF A
WHERE DATA_DATE = 20131222
AND ACCT_FLAG = 'PFS'
AND FLAG = '625'
AND DEPT1_CODE IS NOT NULL
AND CUR_CODE != 0
AND LENGTH(TRIM(MGR_CODE)) >= 3
AND FLAG IS NOT NULL) T2
ON NVL(T1.ACCT_NO_PK, 'XXXXX') = NVL(T2.ACCT_NO_PK, 'XXXXX')
AND NVL(T1.MGR_CODE, 'XXXXX') = NVL(T2.MGR_CODE, 'XXXXX')
AND NVL(T1.UNIT3_CODE, 'XXXXX') = NVL(T2.UNIT3_CODE, 'XXXXX')
AND NVL(T1.UNIT4_CODE, 'XXXXX') = NVL(T2.UNIT4_CODE, 'XXXXX')
AND NVL(T1.FLAG, 'X') = NVL(T2.FLAG, 'X')) T1
LEFT JOIN S_PM_CUSTOMER T3
ON T1.CUST_NO = T3.CUST_NO
AND T1.BANK_CORP_CODE = T3.BANK_CORP_CODE
LEFT JOIN S_PM_BRANCH T4
ON T1.BRAN_CODE = T4.BRAN_CODE
LEFT JOIN S_PM_CURRENCY T5
ON T1.CUR_CODE = T5.CUR_CODE
LEFT JOIN (SELECT /*+parallel(a 16)*/
*
FROM S_PM_MGR_DEPT_RELA A
WHERE DEPT1_CODE <> '999999999') T6
ON T1.MGR_CODE = T6.MGR_CODE
AND T1.UNIT3_CODE = T6.UNIT3_CODE
AND T1.UNIT4_CODE = T6.UNIT4_CODE
小表未广播:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 9264K(100)| | | | | | |
| 1 | LOAD AS SELECT | | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10015 | 480 | 403K| 9264K (1)| 30:52:56 | | | Q1,15 | P->S | QC (RAND) |
|* 4 | HASH JOIN OUTER BUFFERED | | 480 | 403K| 9264K (1)| 30:52:56 | | | Q1,15 | PCWP | |
| 5 | PX RECEIVE | | 6 | 4536 | 9264K (1)| 30:52:56 | | | Q1,15 | PCWP | |
| 6 | PX SEND HASH | :TQ10013 | 6 | 4536 | 9264K (1)| 30:52:56 | | | Q1,13 | P->P | HASH |
| 7 | NESTED LOOPS OUTER | | 6 | 4536 | 9264K (1)| 30:52:56 | | | Q1,13 | PCWP | |
|* 8 | HASH JOIN OUTER | | 6 | 4200 | 9264K (1)| 30:52:56 | | | Q1,13 | PCWP | |
| 9 | PX RECEIVE | | 6 | 3966 | 9264K (1)| 30:52:56 | | | Q1,13 | PCWP | |
| 10 | PX SEND HASH | :TQ10012 | 6 | 3966 | 9264K (1)| 30:52:56 | | | Q1,12 | P->P | HASH |
|* 11 | HASH JOIN OUTER | | 6 | 3966 | 9264K (1)| 30:52:56 | | | Q1,12 | PCWP | |
| 12 | PX RECEIVE | | 6 | 3816 | 9264K (1)| 30:52:56 | | | Q1,12 | PCWP | |
| 13 | PX SEND HASH | :TQ10011 | 6 | 3816 | 9264K (1)| 30:52:56 | | | Q1,11 | P->P | HASH |
| 14 | BUFFER SORT | | 82 | 53792 | | | | | Q1,11 | PCWP | |
| 15 | VIEW | | 6 | 3816 | 9264K (1)| 30:52:56 | | | Q1,11 | PCWP | |
| 16 | UNION-ALL | | | | | | | | Q1,11 | PCWP | |
| 17 | VIEW | | 2 | 2368 | 4629K (1)| 15:25:58 | | | Q1,11 | PCWP | |
| 18 | UNION-ALL | | | | | | | | Q1,11 | PCWP | |
|* 19 | HASH JOIN OUTER | | 1 | 1132 | 2314K (1)| 07:42:59 | | | Q1,11 | PCWP | |
| 20 | PX RECEIVE | | 1 | 605 | 1157K (1)| 03:51:30 | | | Q1,11 | PCWP | |
| 21 | PX SEND HASH | :TQ10003 | 1 | 605 | 1157K (1)| 03:51:30 | | | Q1,03 | P->P | HASH |
| 22 | PX BLOCK ITERATOR | | 1 | 605 | 1157K (1)| 03:51:30 | KEY | KEY | Q1,03 | PCWC | |
|* 23 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 1 | 605 | 1157K (1)| 03:51:30 | KEY | KEY | Q1,03 | PCWP | |
| 24 | PX RECEIVE | | 1 | 527 | 1157K (1)| 03:51:30 | | | Q1,11 | PCWP | |
| 25 | PX SEND HASH | :TQ10004 | 1 | 527 | 1157K (1)| 03:51:30 | | | Q1,04 | P->P | HASH |
| 26 | PX BLOCK ITERATOR | | 1 | 527 | 1157K (1)| 03:51:30 | KEY | KEY | Q1,04 | PCWC | |
| 27 | VIEW | | 1 | 527 | 1157K (1)| 03:51:30 | | | Q1,04 | PCWP | |
|* 28 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 1 | 540 | 1157K (1)| 03:51:30 | KEY | KEY | Q1,04 | PCWP | |
|* 29 | HASH JOIN ANTI | | 1 | 696 | 2314K (1)| 07:42:59 | | | Q1,11 | PCWP | |
| 30 | PX RECEIVE | | 1 | 540 | 1157K (1)| 03:51:30 | | | Q1,11 | PCWP | |
| 31 | PX SEND HASH | :TQ10005 | 1 | 540 | 1157K (1)| 03:51:30 | | | Q1,05 | P->P | HASH |
| 32 | PX BLOCK ITERATOR | | 1 | 540 | 1157K (1)| 03:51:30 | KEY | KEY | Q1,05 | PCWC | |
|* 33 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 1 | 540 | 1157K (1)| 03:51:30 | KEY | KEY | Q1,05 | PCWP | |
| 34 | PX RECEIVE | | 1 | 156 | 1157K (1)| 03:51:30 | | | Q1,11 | PCWP | |
| 35 | PX SEND HASH | :TQ10006 | 1 | 156 | 1157K (1)| 03:51:30 | | | Q1,06 | P->P | HASH |
| 36 | PX BLOCK ITERATOR | | 1 | 156 | 1157K (1)| 03:51:30 | KEY | KEY | Q1,06 | PCWC | |
|* 37 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 1 | 156 | 1157K (1)| 03:51:30 | KEY | KEY | Q1,06 | PCWP | |
| 38 | VIEW | | 2 | 2368 | 4629K (1)| 15:25:58 | | | Q1,11 | PCWP | |
| 39 | UNION-ALL | | | | | | | | Q1,11 | PCWP | |
|* 40 | HASH JOIN OUTER | | 1 | 1132 | 2314K (1)| 07:42:59 | | | Q1,11 | PCWP | |
| 41 | PX RECEIVE | | 1 | 605 | 1157K (1)| 03:51:30 | | | Q1,11 | PCWP | |
| 42 | PX SEND HASH | :TQ10007 | 1 | 605 | 1157K (1)| 03:51:30 | | | Q1,07 | P->P | HASH |
| 43 | PX BLOCK ITERATOR | | 1 | 605 | 1157K (1)| 03:51:30 | KEY | KEY | Q1,07 | PCWC | |
|* 44 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 1 | 605 | 1157K (1)| 03:51:30 | KEY | KEY | Q1,07 | PCWP | |
| 45 | PX RECEIVE | | 1 | 527 | 1157K (1)| 03:51:30 | | | Q1,11 | PCWP | |
| 46 | PX SEND HASH | :TQ10008 | 1 | 527 | 1157K (1)| 03:51:30 | | | Q1,08 | P->P | HASH |
| 47 | PX BLOCK ITERATOR | | 1 | 527 | 1157K (1)| 03:51:30 | KEY | KEY | Q1,08 | PCWC | |
| 48 | VIEW | | 1 | 527 | 1157K (1)| 03:51:30 | | | Q1,08 | PCWP | |
|* 49 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 1 | 540 | 1157K (1)| 03:51:30 | KEY | KEY | Q1,08 | PCWP | |
|* 50 | HASH JOIN ANTI | | 1 | 663 | 2314K (1)| 07:42:59 | | | Q1,11 | PCWP | |
| 51 | PX RECEIVE | | 1 | 540 | 1157K (1)| 03:51:30 | | | Q1,11 | PCWP | |
| 52 | PX SEND HASH | :TQ10009 | 1 | 540 | 1157K (1)| 03:51:30 | | | Q1,09 | P->P | HASH |
| 53 | PX BLOCK ITERATOR | | 1 | 540 | 1157K (1)| 03:51:30 | KEY | KEY | Q1,09 | PCWC | |
|* 54 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 1 | 540 | 1157K (1)| 03:51:30 | KEY | KEY | Q1,09 | PCWP | |
| 55 | PX RECEIVE | | 1 | 123 | 1157K (1)| 03:51:30 | | | Q1,11 | PCWP | |
| 56 | PX SEND HASH | :TQ10010 | 1 | 123 | 1157K (1)| 03:51:30 | | | Q1,10 | P->P | HASH |
| 57 | PX BLOCK ITERATOR | | 1 | 123 | 1157K (1)| 03:51:30 | KEY | KEY | Q1,10 | PCWC | |
|* 58 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 1 | 123 | 1157K (1)| 03:51:30 | KEY | KEY | Q1,10 | PCWP | |
| 59 | BUFFER SORT | | | | | | | | Q1,11 | PCWC | |
| 60 | PX RECEIVE | | 2 | 2368 | 5006 (1)| 00:01:01 | | | Q1,11 | PCWP | |
| 61 | PX SEND ROUND-ROBIN | :TQ10000 | 2 | 2368 | 5006 (1)| 00:01:01 | | | | S->P | RND-ROBIN |
| 62 | VIEW | | 2 | 2368 | 5006 (1)| 00:01:01 | | | | | |
| 63 | UNION-ALL | | | | | | | | | | |
|* 64 | HASH JOIN OUTER | | 1 | 1145 | 2503 (1)| 00:00:31 | | | | | |
| 65 | PARTITION LIST SINGLE | | 1 | 605 | 2 (0)| 00:00:01 | KEY | KEY | | | |
|* 66 | TABLE ACCESS BY LOCAL INDEX ROWID| T_PM_ACCT_DTL_AF | 1 | 605 | 2 (0)| 00:00:01 | KEY | KEY | | | |
|* 67 | INDEX RANGE SCAN | T_PM_ACCT_DTL_AF_IDX1 | 8504K| | 1 (0)| 00:00:01 | KEY | KEY | | | |
| 68 | PARTITION LIST SINGLE | | 1 | 540 | 2501 (1)| 00:00:31 | KEY | KEY | | | |
|* 69 | TABLE ACCESS BY LOCAL INDEX ROWID| T_PM_ACCT_DTL_AF | 1 | 540 | 2501 (1)| 00:00:31 | KEY | KEY | | | |
|* 70 | INDEX RANGE SCAN | T_PM_ACCT_DTL_AF_IDX1 | 8504K| | 569 (0)| 00:00:07 | KEY | KEY | | | |
| 71 | NESTED LOOPS ANTI | | 1 | 663 | 2503 (1)| 00:00:31 | | | | | |
| 72 | PARTITION LIST SINGLE | | 1 | 540 | 2501 (1)| 00:00:31 | KEY | KEY | | | |
|* 73 | TABLE ACCESS BY LOCAL INDEX ROWID| T_PM_ACCT_DTL_AF | 1 | 540 | 2501 (1)| 00:00:31 | KEY | KEY | | | |
|* 74 | INDEX RANGE SCAN | T_PM_ACCT_DTL_AF_IDX1 | 8504K| | 569 (0)| 00:00:07 | KEY | KEY | | | |
| 75 | PARTITION LIST SINGLE | | 1 | 123 | 0 (0)| | KEY | KEY | | | |
|* 76 | TABLE ACCESS BY LOCAL INDEX ROWID| T_PM_ACCT_DTL_AF | 1 | 123 | 0 (0)| | KEY | KEY | | | |
|* 77 | INDEX RANGE SCAN | T_PM_ACCT_DTL_AF_IDX1 | 8504K| | 0 (0)| | KEY | KEY | | | |
| 78 | BUFFER SORT | | | | | | | | Q1,12 | PCWC | |
| 79 | PX RECEIVE | | 18 | 450 | 3 (0)| 00:00:01 | | | Q1,12 | PCWP | |
| 80 | PX SEND HASH | :TQ10001 | 18 | 450 | 3 (0)| 00:00:01 | | | | S->P | HASH |
| 81 | TABLE ACCESS FULL | S_PM_CURRENCY | 18 | 450 | 3 (0)| 00:00:01 | | | | | |
| 82 | BUFFER SORT | | | | | | | | Q1,13 | PCWC | |
| 83 | PX RECEIVE | | 98 | 3822 | 3 (0)| 00:00:01 | | | Q1,13 | PCWP | |
| 84 | PX SEND HASH | :TQ10002 | 98 | 3822 | 3 (0)| 00:00:01 | | | | S->P | HASH |
| 85 | TABLE ACCESS FULL | S_PM_BRANCH | 98 | 3822 | 3 (0)| 00:00:01 | | | | | |
| 86 | TABLE ACCESS BY INDEX ROWID | S_PM_CUSTOMER | 1 | 56 | 2 (0)| 00:00:01 | | | Q1,13 | PCWP | |
|* 87 | INDEX UNIQUE SCAN | IDX_PM_CUSTOMER | 1 | | 1 (0)| 00:00:01 | | | Q1,13 | PCWP | |
| 88 | PX RECEIVE | | 6562 | 672K| 4 (0)| 00:00:01 | | | Q1,15 | PCWP | |
| 89 | PX SEND HASH | :TQ10014 | 6562 | 672K| 4 (0)| 00:00:01 | | | Q1,14 | P->P | HASH |
| 90 | PX BLOCK ITERATOR | | 6562 | 672K| 4 (0)| 00:00:01 | | | Q1,14 | PCWC | |
|* 91 | TABLE ACCESS FULL | S_PM_MGR_DEPT_RELA | 6562 | 672K| 4 (0)| 00:00:01 | | | Q1,14 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4 - access("T1"."UNIT4_CODE"="A"."UNIT4_CODE" AND "T1"."UNIT3_CODE"="A"."UNIT3_CODE" AND "T1"."MGR_CODE"="A"."MGR_CODE")
8 - access("T1"."BRAN_CODE"="T4"."BRAN_CODE")
11 - access("T5"."CUR_CODE"=TO_NUMBER("T1"."CUR_CODE"))
19 - access(NVL("A"."TERM",0)=NVL("T2"."TERM",0) AND NVL("A"."FLAG",'X')=NVL("T2"."FLAG",'X') AND NVL("A"."SUB_CODE",'XXXXX')=NVL("T2"."SUB_CODE",'XXXXX') AND
NVL("A"."UNIT2_CODE",'XXXXX')=NVL("T2"."UNIT2_CODE",'XXXXX') AND NVL("A"."MGR_CODE",'XXXXX')=NVL("T2"."MGR_CODE",'XXXXX') AND "A"."ACCT_NO_PK"="T2"."ACCT_NO_PK")
23 - access(:Z>=:Z AND :Z<=:Z)
filter(("FLAG" IS NOT NULL AND "DEPT1_CODE" IS NOT NULL AND INTERNAL_FUNCTION("ACCT_FLAG") AND TO_NUMBER("CUR_CODE")<>0 AND LENGTH(TRIM("MGR_CODE"))>=3))
28 - access(:Z>=:Z AND :Z<=:Z)
filter(("FLAG" IS NOT NULL AND "DEPT1_CODE" IS NOT NULL AND INTERNAL_FUNCTION("ACCT_FLAG") AND TO_NUMBER("CUR_CODE")<>0 AND LENGTH(TRIM("MGR_CODE"))>=3))
29 - 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))
33 - access(:Z>=:Z AND :Z<=:Z)
filter(("FLAG" IS NOT NULL AND "DEPT1_CODE" IS NOT NULL AND INTERNAL_FUNCTION("ACCT_FLAG") AND TO_NUMBER("CUR_CODE")<>0 AND LENGTH(TRIM("MGR_CODE"))>=3))
37 - access(:Z>=:Z AND :Z<=:Z)
filter(("FLAG" IS NOT NULL AND "DEPT1_CODE" IS NOT NULL AND INTERNAL_FUNCTION("ACCT_FLAG") AND TO_NUMBER("CUR_CODE")<>0 AND LENGTH(TRIM("MGR_CODE"))>=3))
40 - access(NVL("A"."FLAG",'X')=NVL("T2"."FLAG",'X') AND NVL("A"."UNIT4_CODE",'XXXXX')=NVL("T2"."UNIT4_CODE",'XXXXX') AND
NVL("A"."UNIT3_CODE",'XXXXX')=NVL("T2"."UNIT3_CODE",'XXXXX') AND NVL("A"."MGR_CODE",'XXXXX')=NVL("T2"."MGR_CODE",'XXXXX') AND
NVL("A"."ACCT_NO_PK",'XXXXX')=NVL("T2"."ACCT_NO_PK",'XXXXX'))
44 - access(:Z>=:Z AND :Z<=:Z)
filter(("FLAG" IS NOT NULL AND "DEPT1_CODE" IS NOT NULL AND INTERNAL_FUNCTION("ACCT_FLAG") AND "FLAG"<>'625' AND TO_NUMBER("CUR_CODE")<>0 AND
LENGTH(TRIM("MGR_CODE"))>=3))
49 - access(:Z>=:Z AND :Z<=:Z)
filter(("FLAG" IS NOT NULL AND "DEPT1_CODE" IS NOT NULL AND INTERNAL_FUNCTION("ACCT_FLAG") AND "FLAG"<>'625' AND TO_NUMBER("CUR_CODE")<>0 AND
LENGTH(TRIM("MGR_CODE"))>=3))
50 - access(NVL("A"."ACCT_NO_PK",'XXXXX')=NVL("A"."ACCT_NO_PK",'XXXXX') AND NVL("A"."MGR_CODE",'XXXXX')=NVL("A"."MGR_CODE",'XXXXX') AND
NVL("A"."UNIT3_CODE",'XXXXX')=NVL("A"."UNIT3_CODE",'XXXXX') AND NVL("A"."UNIT4_CODE",'XXXXX')=NVL("A"."UNIT4_CODE",'XXXXX') AND
NVL("A"."FLAG",'X')=NVL("A"."FLAG",'X'))
54 - access(:Z>=:Z AND :Z<=:Z)
filter(("FLAG" IS NOT NULL AND "DEPT1_CODE" IS NOT NULL AND INTERNAL_FUNCTION("ACCT_FLAG") AND "FLAG"<>'625' AND TO_NUMBER("CUR_CODE")<>0 AND
LENGTH(TRIM("MGR_CODE"))>=3))
58 - access(:Z>=:Z AND :Z<=:Z)
filter(("FLAG" IS NOT NULL AND "DEPT1_CODE" IS NOT NULL AND INTERNAL_FUNCTION("ACCT_FLAG") AND "FLAG"<>'625' AND TO_NUMBER("CUR_CODE")<>0 AND
LENGTH(TRIM("MGR_CODE"))>=3))
64 - access(NVL("A"."FLAG",'X')=NVL("A"."FLAG",'X') AND NVL("A"."UNIT4_CODE",'XXXXX')=NVL("A"."UNIT4_CODE",'XXXXX') AND
NVL("A"."UNIT3_CODE",'XXXXX')=NVL("A"."UNIT3_CODE",'XXXXX') AND NVL("A"."MGR_CODE",'XXXXX')=NVL("A"."MGR_CODE",'XXXXX') AND
NVL("A"."ACCT_NO_PK",'XXXXX')=NVL("A"."ACCT_NO_PK",'XXXXX'))
66 - filter(("DEPT1_CODE" IS NOT NULL AND "FLAG" IS NOT NULL AND "ACCT_FLAG"='PFS' AND "FLAG"='625' AND TO_NUMBER("CUR_CODE")<>0 AND LENGTH(TRIM("MGR_CODE"))>=3))
67 - access("A"."SYS_NC00043$"='6')
69 - filter(("DEPT1_CODE" IS NOT NULL AND "FLAG" IS NOT NULL AND "ACCT_FLAG"='PFS' AND "FLAG"='625' AND TO_NUMBER("CUR_CODE")<>0 AND LENGTH(TRIM("MGR_CODE"))>=3))
70 - access("A"."SYS_NC00043$"='6')
73 - filter(("DEPT1_CODE" IS NOT NULL AND "FLAG" IS NOT NULL AND "ACCT_FLAG"='PFS' AND "FLAG"='625' AND TO_NUMBER("CUR_CODE")<>0 AND LENGTH(TRIM("MGR_CODE"))>=3))
74 - access("A"."SYS_NC00043$"='6')
76 - filter(("DEPT1_CODE" IS NOT NULL AND "FLAG" IS NOT NULL AND "ACCT_FLAG"='PFS' AND "FLAG"='625' AND TO_NUMBER("CUR_CODE")<>0 AND LENGTH(TRIM("MGR_CODE"))>=3 AND
NVL("A"."ACCT_NO_PK",'XXXXX')=NVL("A"."ACCT_NO_PK",'XXXXX') AND NVL("A"."MGR_CODE",'XXXXX')=NVL("A"."MGR_CODE",'XXXXX') AND
NVL("A"."UNIT3_CODE",'XXXXX')=NVL("A"."UNIT3_CODE",'XXXXX') AND NVL("A"."UNIT4_CODE",'XXXXX')=NVL("A"."UNIT4_CODE",'XXXXX') AND
NVL("A"."FLAG",'X')=NVL("A"."FLAG",'X')))
77 - access("A"."SYS_NC00043$"='6')
87 - access("T1"."BANK_CORP_CODE"="T3"."BANK_CORP_CODE" AND "T1"."CUST_NO"="T3"."CUST_NO")
91 - access(:Z>=:Z AND :Z<=:Z)
filter("DEPT1_CODE"<>'999999999')
Note
-----
- dynamic sampling used for this statement (level=2)
由于统计信息错误,导致没有小表广播,搜集统计信息后:
Plan hash value: 919621692
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 26709 (100)| | | | | | |
| 1 | LOAD AS SELECT | | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10017 | 263K| 186M| 26709 (1)| 00:05:21 | | | Q1,17 | P->S | QC (RAND) |
|* 4 | HASH JOIN RIGHT OUTER BUFFERED | | 263K| 186M| 26709 (1)| 00:05:21 | | | Q1,17 | PCWP | |
| 5 | BUFFER SORT | | | | | | | | Q1,17 | PCWC | |
| 6 | PX RECEIVE | | 525K| 17M| 2973 (1)| 00:00:36 | | | Q1,17 | PCWP | |
| 7 | PX SEND HASH | :TQ10002 | 525K| 17M| 2973 (1)| 00:00:36 | | | | S->P | HASH |
| 8 | TABLE ACCESS FULL | S_PM_CUSTOMER | 525K| 17M| 2973 (1)| 00:00:36 | | | | | |
| 9 | PX RECEIVE | | 263K| 177M| 23735 (1)| 00:04:45 | | | Q1,17 | PCWP | |
| 10 | PX SEND HASH | :TQ10016 | 263K| 177M| 23735 (1)| 00:04:45 | | | Q1,16 | P->P | HASH |
|* 11 | HASH JOIN RIGHT OUTER BUFFERED| | 263K| 177M| 23735 (1)| 00:04:45 | | | Q1,16 | PCWP | |
| 12 | PX RECEIVE | | 6082 | 231K| 4 (0)| 00:00:01 | | | Q1,16 | PCWP | |
| 13 | PX SEND BROADCAST | :TQ10003 | 6082 | 231K| 4 (0)| 00:00:01 | | | Q1,03 | P->P | BROADCAST |
| 14 | PX BLOCK ITERATOR | | 6082 | 231K| 4 (0)| 00:00:01 | | | Q1,03 | PCWC | |
|* 15 | TABLE ACCESS FULL | S_PM_MGR_DEPT_RELA | 6082 | 231K| 4 (0)| 00:00:01 | | | Q1,03 | PCWP | |
|* 16 | HASH JOIN RIGHT OUTER | | 263K| 167M| 23731 (1)| 00:04:45 | | | Q1,16 | PCWP | |
| 17 | BUFFER SORT | | | | | | | | Q1,16 | PCWC | |
| 18 | PX RECEIVE | | 98 | 1862 | 3 (0)| 00:00:01 | | | Q1,16 | PCWP | |
| 19 | PX SEND BROADCAST | :TQ10000 | 98 | 1862 | 3 (0)| 00:00:01 | | | | S->P | BROADCAST |
| 20 | TABLE ACCESS FULL | S_PM_BRANCH | 98 | 1862 | 3 (0)| 00:00:01 | | | | | |
|* 21 | HASH JOIN RIGHT OUTER | | 263K| 162M| 23727 (1)| 00:04:45 | | | Q1,16 | PCWP | |
| 22 | BUFFER SORT | | | | | | | | Q1,16 | PCWC | |
| 23 | PX RECEIVE | | 18 | 234 | 3 (0)| 00:00:01 | | | Q1,16 | PCWP | |
| 24 | PX SEND BROADCAST | :TQ10001 | 18 | 234 | 3 (0)| 00:00:01 | | | | S->P | BROADCAST |
| 25 | TABLE ACCESS FULL | S_PM_CURRENCY | 18 | 234 | 3 (0)| 00:00:01 | | | | | |
| 26 | VIEW | | 263K| 159M| 23723 (1)| 00:04:45 | | | Q1,16 | PCWP | |
| 27 | UNION-ALL | | | | | | | | Q1,16 | PCWP | |
| 28 | VIEW | | 204K| 231M| 7914 (1)| 00:01:35 | | | Q1,16 | PCWP | |
| 29 | UNION-ALL | | | | | | | | Q1,16 | PCWP | |
|* 30 | HASH JOIN OUTER | | 112K| 79M| 3957 (1)| 00:00:48 | | | Q1,16 | PCWP | |
| 31 | PX RECEIVE | | 112K| 22M| 1979 (1)| 00:00:24 | | | Q1,16 | PCWP | |
| 32 | PX SEND HASH | :TQ10004 | 112K| 22M| 1979 (1)| 00:00:24 | | | Q1,04 | P->P | HASH |
| 33 | PX BLOCK ITERATOR | | 112K| 22M| 1979 (1)| 00:00:24 | KEY | KEY | Q1,04 | PCWC | |
|* 34 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 112K| 22M| 1979 (1)| 00:00:24 | KEY | KEY | Q1,04 | PCWP | |
| 35 | PX RECEIVE | | 114K| 57M| 1978 (1)| 00:00:24 | | | Q1,16 | PCWP | |
| 36 | PX SEND HASH | :TQ10005 | 114K| 57M| 1978 (1)| 00:00:24 | | | Q1,05 | P->P | HASH |
| 37 | PX BLOCK ITERATOR | | 114K| 57M| 1978 (1)| 00:00:24 | KEY | KEY | Q1,05 | PCWC | |
| 38 | VIEW | | 114K| 57M| 1978 (1)| 00:00:24 | | | Q1,05 | PCWP | |
|* 39 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 114K| 23M| 1978 (1)| 00:00:24 | KEY | KEY | Q1,05 | PCWP | |
|* 40 | HASH JOIN RIGHT ANTI | | 92022 | 25M| 3957 (1)| 00:00:48 | | | Q1,16 | PCWP | |
| 41 | PX RECEIVE | | 112K| 8143K| 1979 (1)| 00:00:24 | | | Q1,16 | PCWP | |
| 42 | PX SEND HASH | :TQ10006 | 112K| 8143K| 1979 (1)| 00:00:24 | | | Q1,06 | P->P | HASH |
| 43 | PX BLOCK ITERATOR | | 112K| 8143K| 1979 (1)| 00:00:24 | KEY | KEY | Q1,06 | PCWC | |
|* 44 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 112K| 8143K| 1979 (1)| 00:00:24 | KEY | KEY | Q1,06 | PCWP | |
| 45 | PX RECEIVE | | 114K| 23M| 1978 (1)| 00:00:24 | | | Q1,16 | PCWP | |
| 46 | PX SEND HASH | :TQ10007 | 114K| 23M| 1978 (1)| 00:00:24 | | | Q1,07 | P->P | HASH |
| 47 | PX BLOCK ITERATOR | | 114K| 23M| 1978 (1)| 00:00:24 | KEY | KEY | Q1,07 | PCWC | |
|* 48 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 114K| 23M| 1978 (1)| 00:00:24 | KEY | KEY | Q1,07 | PCWP | |
| 49 | VIEW | | 58192 | 65M| 7910 (1)| 00:01:35 | | | Q1,16 | PCWP | |
| 50 | UNION-ALL | | | | | | | | Q1,16 | PCWP | |
|* 51 | HASH JOIN OUTER | | 55688 | 39M| 3955 (1)| 00:00:48 | | | Q1,16 | PCWP | |
| 52 | PX RECEIVE | | 55688 | 11M| 1978 (1)| 00:00:24 | | | Q1,16 | PCWP | |
| 53 | PX SEND HASH | :TQ10008 | 55688 | 11M| 1978 (1)| 00:00:24 | | | Q1,08 | P->P | HASH |
| 54 | PX BLOCK ITERATOR | | 55688 | 11M| 1978 (1)| 00:00:24 | KEY | KEY | Q1,08 | PCWC | |
|* 55 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 55688 | 11M| 1978 (1)| 00:00:24 | KEY | KEY | Q1,08 | PCWP | |
| 56 | PX RECEIVE | | 53498 | 26M| 1976 (1)| 00:00:24 | | | Q1,16 | PCWP | |
| 57 | PX SEND HASH | :TQ10009 | 53498 | 26M| 1976 (1)| 00:00:24 | | | Q1,09 | P->P | HASH |
| 58 | PX BLOCK ITERATOR | | 53498 | 26M| 1976 (1)| 00:00:24 | KEY | KEY | Q1,09 | PCWC | |
| 59 | VIEW | | 53498 | 26M| 1976 (1)| 00:00:24 | | | Q1,09 | PCWP | |
|* 60 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 53498 | 10M| 1976 (1)| 00:00:24 | KEY | KEY | Q1,09 | PCWP | |
|* 61 | HASH JOIN RIGHT ANTI | | 2504 | 709K| 3955 (1)| 00:00:48 | | | Q1,16 | PCWP | |
| 62 | PX RECEIVE | | 55688 | 4133K| 1978 (1)| 00:00:24 | | | Q1,16 | PCWP | |
| 63 | PX SEND HASH | :TQ10010 | 55688 | 4133K| 1978 (1)| 00:00:24 | | | Q1,10 | P->P | HASH |
| 64 | PX BLOCK ITERATOR | | 55688 | 4133K| 1978 (1)| 00:00:24 | KEY | KEY | Q1,10 | PCWC | |
|* 65 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 55688 | 4133K| 1978 (1)| 00:00:24 | KEY | KEY | Q1,10 | PCWP | |
| 66 | PX RECEIVE | | 53498 | 10M| 1976 (1)| 00:00:24 | | | Q1,16 | PCWP | |
| 67 | PX SEND HASH | :TQ10011 | 53498 | 10M| 1976 (1)| 00:00:24 | | | Q1,11 | P->P | HASH |
| 68 | PX BLOCK ITERATOR | | 53498 | 10M| 1976 (1)| 00:00:24 | KEY | KEY | Q1,11 | PCWC | |
|* 69 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 53498 | 10M| 1976 (1)| 00:00:24 | KEY | KEY | Q1,11 | PCWP | |
| 70 | VIEW | | 138 | 159K| 7900 (1)| 00:01:35 | | | Q1,16 | PCWP | |
| 71 | UNION-ALL | | | | | | | | Q1,16 | PCWP | |
|* 72 | HASH JOIN OUTER | | 132 | 56496 | 3950 (1)| 00:00:48 | | | Q1,16 | PCWP | |
| 73 | PX RECEIVE | | 132 | 28248 | 1975 (1)| 00:00:24 | | | Q1,16 | PCWP | |
| 74 | PX SEND HASH | :TQ10012 | 132 | 28248 | 1975 (1)| 00:00:24 | | | Q1,12 | P->P | HASH |
| 75 | PX BLOCK ITERATOR | | 132 | 28248 | 1975 (1)| 00:00:24 | KEY | KEY | Q1,12 | PCWC | |
|* 76 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 132 | 28248 | 1975 (1)| 00:00:24 | KEY | KEY | Q1,12 | PCWP | |
| 77 | PX RECEIVE | | 124 | 26536 | 1974 (1)| 00:00:24 | | | Q1,16 | PCWP | |
| 78 | PX SEND HASH | :TQ10013 | 124 | 26536 | 1974 (1)| 00:00:24 | | | Q1,13 | P->P | HASH |
| 79 | PX BLOCK ITERATOR | | 124 | 26536 | 1974 (1)| 00:00:24 | KEY | KEY | Q1,13 | PCWC | |
|* 80 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 124 | 26536 | 1974 (1)| 00:00:24 | KEY | KEY | Q1,13 | PCWP | |
|* 81 | HASH JOIN ANTI | | 6 | 1740 | 3950 (1)| 00:00:48 | | | Q1,16 | PCWP | |
| 82 | PX RECEIVE | | 124 | 26536 | 1974 (1)| 00:00:24 | | | Q1,16 | PCWP | |
| 83 | PX SEND HASH | :TQ10014 | 124 | 26536 | 1974 (1)| 00:00:24 | | | Q1,14 | P->P | HASH |
| 84 | PX BLOCK ITERATOR | | 124 | 26536 | 1974 (1)| 00:00:24 | KEY | KEY | Q1,14 | PCWC | |
|* 85 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 124 | 26536 | 1974 (1)| 00:00:24 | KEY | KEY | Q1,14 | PCWP | |
| 86 | PX RECEIVE | | 132 | 10032 | 1975 (1)| 00:00:24 | | | Q1,16 | PCWP | |
| 87 | PX SEND HASH | :TQ10015 | 132 | 10032 | 1975 (1)| 00:00:24 | | | Q1,15 | P->P | HASH |
| 88 | PX BLOCK ITERATOR | | 132 | 10032 | 1975 (1)| 00:00:24 | KEY | KEY | Q1,15 | PCWC | |
|* 89 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 132 | 10032 | 1975 (1)| 00:00:24 | KEY | KEY | Q1,15 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."BANK_CORP_CODE"="T3"."BANK_CORP_CODE" AND "T1"."CUST_NO"="T3"."CUST_NO")
11 - access("T1"."UNIT4_CODE"="A"."UNIT4_CODE" AND "T1"."UNIT3_CODE"="A"."UNIT3_CODE" AND "T1"."MGR_CODE"="A"."MGR_CODE")
15 - access(:Z>=:Z AND :Z<=:Z)
filter("DEPT1_CODE"<>'999999999')
16 - access("T1"."BRAN_CODE"="T4"."BRAN_CODE")
21 - access("T5"."CUR_CODE"=TO_NUMBER("T1"."CUR_CODE"))
30 - access(NVL("A"."TERM",0)=NVL("T2"."TERM",0) AND NVL("A"."FLAG",'X')=NVL("T2"."FLAG",'X') AND
NVL("A"."SUB_CODE",'XXXXX')=NVL("T2"."SUB_CODE",'XXXXX') AND NVL("A"."UNIT2_CODE",'XXXXX')=NVL("T2"."UNIT2_CODE",'XXXXX') AND
NVL("A"."MGR_CODE",'XXXXX')=NVL("T2"."MGR_CODE",'XXXXX') AND "A"."ACCT_NO_PK"="T2"."ACCT_NO_PK")
34 - access(:Z>=:Z AND :Z<=:Z)
filter((LENGTH(TRIM("MGR_CODE"))>=3 AND INTERNAL_FUNCTION("ACCT_FLAG") AND TO_NUMBER("CUR_CODE")<>0 AND "FLAG" IS NOT NULL AND
"DEPT1_CODE" IS NOT NULL))
39 - access(:Z>=:Z AND :Z<=:Z)
filter((LENGTH(TRIM("MGR_CODE"))>=3 AND INTERNAL_FUNCTION("ACCT_FLAG") AND TO_NUMBER("CUR_CODE")<>0 AND "FLAG" IS NOT NULL AND
"DEPT1_CODE" IS NOT NULL))
40 - 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))
44 - access(:Z>=:Z AND :Z<=:Z)
filter((LENGTH(TRIM("MGR_CODE"))>=3 AND INTERNAL_FUNCTION("ACCT_FLAG") AND TO_NUMBER("CUR_CODE")<>0 AND "FLAG" IS NOT NULL AND
"DEPT1_CODE" IS NOT NULL))
48 - access(:Z>=:Z AND :Z<=:Z)
filter((LENGTH(TRIM("MGR_CODE"))>=3 AND INTERNAL_FUNCTION("ACCT_FLAG") AND TO_NUMBER("CUR_CODE")<>0 AND "FLAG" IS NOT NULL AND
"DEPT1_CODE" IS NOT NULL))
51 - access(NVL("A"."FLAG",'X')=NVL("T2"."FLAG",'X') AND NVL("A"."UNIT4_CODE",'XXXXX')=NVL("T2"."UNIT4_CODE",'XXXXX') AND
NVL("A"."UNIT3_CODE",'XXXXX')=NVL("T2"."UNIT3_CODE",'XXXXX') AND NVL("A"."MGR_CODE",'XXXXX')=NVL("T2"."MGR_CODE",'XXXXX') AND
NVL("A"."ACCT_NO_PK",'XXXXX')=NVL("T2"."ACCT_NO_PK",'XXXXX'))
55 - access(:Z>=:Z AND :Z<=:Z)
filter((INTERNAL_FUNCTION("ACCT_FLAG") AND LENGTH(TRIM("MGR_CODE"))>=3 AND "FLAG"<>'625' AND TO_NUMBER("CUR_CODE")<>0 AND "FLAG" IS NOT
NULL AND "DEPT1_CODE" IS NOT NULL))
60 - access(:Z>=:Z AND :Z<=:Z)
filter((INTERNAL_FUNCTION("ACCT_FLAG") AND LENGTH(TRIM("MGR_CODE"))>=3 AND "FLAG"<>'625' AND TO_NUMBER("CUR_CODE")<>0 AND "FLAG" IS NOT
NULL AND "DEPT1_CODE" IS NOT NULL))
61 - access(NVL("A"."ACCT_NO_PK",'XXXXX')=NVL("A"."ACCT_NO_PK",'XXXXX') AND NVL("A"."MGR_CODE",'XXXXX')=NVL("A"."MGR_CODE",'XXXXX') AND
NVL("A"."UNIT3_CODE",'XXXXX')=NVL("A"."UNIT3_CODE",'XXXXX') AND NVL("A"."UNIT4_CODE",'XXXXX')=NVL("A"."UNIT4_CODE",'XXXXX') AND
NVL("A"."FLAG",'X')=NVL("A"."FLAG",'X'))
65 - access(:Z>=:Z AND :Z<=:Z)
filter((INTERNAL_FUNCTION("ACCT_FLAG") AND LENGTH(TRIM("MGR_CODE"))>=3 AND "FLAG"<>'625' AND TO_NUMBER("CUR_CODE")<>0 AND "FLAG" IS NOT
NULL AND "DEPT1_CODE" IS NOT NULL))
69 - access(:Z>=:Z AND :Z<=:Z)
filter((INTERNAL_FUNCTION("ACCT_FLAG") AND LENGTH(TRIM("MGR_CODE"))>=3 AND "FLAG"<>'625' AND TO_NUMBER("CUR_CODE")<>0 AND "FLAG" IS NOT
NULL AND "DEPT1_CODE" IS NOT NULL))
72 - access(NVL("A"."FLAG",'X')=NVL("A"."FLAG",'X') AND NVL("A"."UNIT4_CODE",'XXXXX')=NVL("A"."UNIT4_CODE",'XXXXX') AND
NVL("A"."UNIT3_CODE",'XXXXX')=NVL("A"."UNIT3_CODE",'XXXXX') AND NVL("A"."MGR_CODE",'XXXXX')=NVL("A"."MGR_CODE",'XXXXX') AND
NVL("A"."ACCT_NO_PK",'XXXXX')=NVL("A"."ACCT_NO_PK",'XXXXX'))
76 - access(:Z>=:Z AND :Z<=:Z)
filter(("FLAG"='625' AND "ACCT_FLAG"='PFS' AND LENGTH(TRIM("MGR_CODE"))>=3 AND TO_NUMBER("CUR_CODE")<>0 AND "DEPT1_CODE" IS NOT NULL AND
"FLAG" IS NOT NULL))
80 - access(:Z>=:Z AND :Z<=:Z)
filter(("FLAG"='625' AND "ACCT_FLAG"='PFS' AND LENGTH(TRIM("MGR_CODE"))>=3 AND TO_NUMBER("CUR_CODE")<>0 AND "DEPT1_CODE" IS NOT NULL AND
"FLAG" IS NOT NULL))
81 - access(NVL("A"."ACCT_NO_PK",'XXXXX')=NVL("A"."ACCT_NO_PK",'XXXXX') AND NVL("A"."MGR_CODE",'XXXXX')=NVL("A"."MGR_CODE",'XXXXX') AND
NVL("A"."UNIT3_CODE",'XXXXX')=NVL("A"."UNIT3_CODE",'XXXXX') AND NVL("A"."UNIT4_CODE",'XXXXX')=NVL("A"."UNIT4_CODE",'XXXXX') AND
NVL("A"."FLAG",'X')=NVL("A"."FLAG",'X'))
85 - access(:Z>=:Z AND :Z<=:Z)
filter(("FLAG"='625' AND "ACCT_FLAG"='PFS' AND LENGTH(TRIM("MGR_CODE"))>=3 AND TO_NUMBER("CUR_CODE")<>0 AND "DEPT1_CODE" IS NOT NULL AND
"FLAG" IS NOT NULL))
89 - access(:Z>=:Z AND :Z<=:Z)
filter(("FLAG"='625' AND "ACCT_FLAG"='PFS' AND LENGTH(TRIM("MGR_CODE"))>=3 AND TO_NUMBER("CUR_CODE")<>0 AND "DEPT1_CODE" IS NOT NULL AND
"FLAG" IS NOT NULL))
| 在超大表与超级小表进行HASH JOIN情况下，如果走并行,那么小表需要BROADCAST |