确认执行计划中分区号对应的分区
来源：互联网 发布：网络剧输出到电视台 编辑：程序博客网 时间：2024/05/24 15:40
explain plan for select *from (SELECT 20131223 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(20131223, -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(20131223, -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 * FROM T_PM_ACCT_DTL_AF A WHERE DATA_DATE = 20131223 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 * 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 20131223 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(20131223, -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(20131223, -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 * FROM T_PM_ACCT_DTL_AF A WHERE DATA_DATE = 20131223 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 * 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 20131223 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(20131223, -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(20131223, -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 * FROM T_PM_ACCT_DTL_AF A WHERE DATA_DATE = 20131223 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 * 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 * 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_CODEPlan hash value: 3224195590 ---------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |---------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 659M| 1051G| | 2535K (56)| 08:27:06 | | ||* 1 | HASH JOIN RIGHT OUTER | | 659M| 1051G| | 2535K (56)| 08:27:06 | | || 2 | TABLE ACCESS FULL | S_PM_BRANCH | 98 | 5390 | | 3 (0)| 00:00:01 | | ||* 3 | HASH JOIN RIGHT OUTER | | 659M| 1017G| | 2532K (56)| 08:26:28 | | || 4 | TABLE ACCESS FULL | S_PM_CURRENCY | 18 | 594 | | 3 (0)| 00:00:01 | | ||* 5 | HASH JOIN RIGHT OUTER | | 659M| 997G| 5064K| 2529K (56)| 08:25:50 | | ||* 6 | TABLE ACCESS FULL | S_PM_MGR_DEPT_RELA | 6562 | 4985K| | 53 (0)| 00:00:01 | | ||* 7 | HASH JOIN RIGHT OUTER | | 8235K| 6652M| 113M| 2191K (64)| 07:18:15 | | || 8 | TABLE ACCESS FULL | S_PM_CUSTOMER | 500K| 107M| | 2973 (1)| 00:00:36 | | || 9 | VIEW | | 8235K| 4885M| | 1935K (73)| 06:27:09 | | || 10 | UNION-ALL | | | | | | | | || 11 | VIEW | | 6490K| 7328M| | 1260K (73)| 04:12:08 | | || 12 | UNION-ALL | | | | | | | | ||* 13 | HASH JOIN OUTER | | 1387K| 1498M| 816M| 420K (65)| 01:24:10 | | || 14 | PARTITION LIST SINGLE | | 1387K| 800M| | 69010 (78)| 00:13:49 | KEY | KEY ||* 15 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 1387K| 800M| | 69010 (78)| 00:13:49 | 736 | 736 || 16 | PARTITION LIST SINGLE | | 2179K| 1095M| | 255K (86)| 00:51:09 | KEY | KEY || 17 | VIEW | | 2179K| 1095M| | 255K (86)| 00:51:09 | | ||* 18 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 2179K| 1122M| | 255K (86)| 00:51:09 | 734 | 734 ||* 19 | HASH JOIN RIGHT ANTI | | 5102K| 3386M| 222M| 839K (76)| 02:47:59 | | || 20 | PARTITION LIST SINGLE | | 1387K| 206M| | 69007 (78)| 00:13:49 | KEY | KEY ||* 21 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 1387K| 206M| | 69007 (78)| 00:13:49 | 736 | 736 || 22 | PARTITION LIST SINGLE | | 5309K| 2734M| | 621K (95)| 02:04:15 | KEY | KEY ||* 23 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 5309K| 2734M| | 621K (95)| 02:04:15 | 734 | 734 || 24 | VIEW | | 1655K| 1868M| | 670K (73)| 02:14:01 | | || 25 | UNION-ALL | | | | | | | | ||* 26 | HASH JOIN OUTER | | 278K| 300M| 163M| 337K (72)| 01:07:34 | | || 27 | PARTITION LIST SINGLE | | 278K| 160M| | 38640 (61)| 00:07:44 | KEY | KEY ||* 28 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 278K| 160M| | 38640 (61)| 00:07:44 | 736 | 736 || 29 | PARTITION LIST SINGLE | | 1385K| 696M| | 255K (86)| 00:51:09 | KEY | KEY || 30 | VIEW | | 1385K| 696M| | 255K (86)| 00:51:09 | | ||* 31 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 1385K| 713M| | 255K (86)| 00:51:09 | 734 | 734 ||* 32 | HASH JOIN RIGHT ANTI | | 1376K| 870M| 35M| 332K (73)| 01:06:28 | | || 33 | PARTITION LIST SINGLE | | 278K| 32M| | 38639 (61)| 00:07:44 | KEY | KEY ||* 34 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 278K| 32M| | 38639 (61)| 00:07:44 | 736 | 736 || 35 | PARTITION LIST SINGLE | | 1385K| 713M| | 255K (86)| 00:51:09 | KEY | KEY ||* 36 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 1385K| 713M| | 255K (86)| 00:51:09 | 734 | 734 || 37 | VIEW | | 90474 | 102M| | 5005 (1)| 00:01:01 | | || 38 | UNION-ALL | | | | | | | | || 39 | NESTED LOOPS OUTER | | 1 | 1145 | | 2502 (1)| 00:00:31 | | || 40 | PARTITION LIST SINGLE | | 1 | 605 | | 2 (0)| 00:00:01 | KEY | KEY ||* 41 | TABLE ACCESS BY LOCAL INDEX ROWID| T_PM_ACCT_DTL_AF | 1 | 605 | | 2 (0)| 00:00:01 | 736 | 736 ||* 42 | INDEX RANGE SCAN | T_PM_ACCT_DTL_AF_IDX1 | 8312 | | | 1 (0)| 00:00:01 | 736 | 736 || 43 | PARTITION LIST SINGLE | | 1 | 540 | | 2500 (1)| 00:00:30 | KEY | KEY ||* 44 | TABLE ACCESS BY LOCAL INDEX ROWID| T_PM_ACCT_DTL_AF | 1 | 540 | | 2500 (1)| 00:00:30 | 734 | 734 ||* 45 | INDEX RANGE SCAN | T_PM_ACCT_DTL_AF_IDX1 | 38067 | | | 568 (0)| 00:00:07 | 734 | 734 ||* 46 | HASH JOIN RIGHT ANTI | | 90473 | 57M| | 2504 (1)| 00:00:31 | | || 47 | PARTITION LIST SINGLE | | 1 | 123 | | 2 (0)| 00:00:01 | KEY | KEY ||* 48 | TABLE ACCESS BY LOCAL INDEX ROWID| T_PM_ACCT_DTL_AF | 1 | 123 | | 2 (0)| 00:00:01 | 736 | 736 ||* 49 | INDEX RANGE SCAN | T_PM_ACCT_DTL_AF_IDX1 | 8312 | | | 1 (0)| 00:00:01 | 736 | 736 || 50 | PARTITION LIST SINGLE | | 90473 | 46M| | 2501 (1)| 00:00:31 | KEY | KEY ||* 51 | TABLE ACCESS BY LOCAL INDEX ROWID| T_PM_ACCT_DTL_AF | 90473 | 46M| | 2501 (1)| 00:00:31 | 734 | 734 ||* 52 | INDEX RANGE SCAN | T_PM_ACCT_DTL_AF_IDX1 | 38067 | | | 569 (0)| 00:00:07 | 734 | 734 |--------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("T1"."BRAN_CODE"="T4"."BRAN_CODE"(+)) 3 - access("T5"."CUR_CODE"(+)=TO_NUMBER("T1"."CUR_CODE")) 5 - access("T1"."UNIT4_CODE"="A"."UNIT4_CODE"(+) AND "T1"."UNIT3_CODE"="A"."UNIT3_CODE"(+) AND "T1"."MGR_CODE"="A"."MGR_CODE"(+)) 6 - filter("DEPT1_CODE"(+)<>'999999999') 7 - access("T1"."BANK_CORP_CODE"="T3"."BANK_CORP_CODE"(+) AND "T1"."CUST_NO"="T3"."CUST_NO"(+)) 13 - 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"(+)) 15 - filter("FLAG" IS NOT NULL AND "DEPT1_CODE" IS NOT NULL AND ("ACCT_FLAG"='CHANGKOU' OR "ACCT_FLAG"='DEPOSIT' OR "ACCT_FLAG"='DYMX' OR "ACCT_FLAG"='ETC' OR "ACCT_FLAG"='LOAN' OR "ACCT_FLAG"='OUTER') AND TO_NUMBER("CUR_CODE")<>0 AND LENGTH(TRIM("MGR_CODE"))>=3) 18 - filter("FLAG" IS NOT NULL AND "DEPT1_CODE" IS NOT NULL AND ("ACCT_FLAG"='CHANGKOU' OR "ACCT_FLAG"='DEPOSIT' OR "ACCT_FLAG"='DYMX' OR "ACCT_FLAG"='ETC' OR "ACCT_FLAG"='LOAN' OR "ACCT_FLAG"='OUTER') AND TO_NUMBER("CUR_CODE")<>0 AND LENGTH(TRIM("MGR_CODE"))>=3) 19 - 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)) 21 - filter("FLAG" IS NOT NULL AND "DEPT1_CODE" IS NOT NULL AND ("ACCT_FLAG"='CHANGKOU' OR "ACCT_FLAG"='DEPOSIT' OR "ACCT_FLAG"='DYMX' OR "ACCT_FLAG"='ETC' OR "ACCT_FLAG"='LOAN' OR "ACCT_FLAG"='OUTER') AND TO_NUMBER("CUR_CODE")<>0 AND LENGTH(TRIM("MGR_CODE"))>=3) 23 - filter("FLAG" IS NOT NULL AND "DEPT1_CODE" IS NOT NULL AND ("ACCT_FLAG"='CHANGKOU' OR "ACCT_FLAG"='DEPOSIT' OR "ACCT_FLAG"='DYMX' OR "ACCT_FLAG"='ETC' OR "ACCT_FLAG"='LOAN' OR "ACCT_FLAG"='OUTER') AND TO_NUMBER("CUR_CODE")<>0 AND LENGTH(TRIM("MGR_CODE"))>=3) 26 - 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')) 28 - filter("FLAG" IS NOT NULL AND "DEPT1_CODE" IS NOT NULL AND ("ACCT_FLAG"='INCOME' OR "ACCT_FLAG"='INTLBU' OR "ACCT_FLAG"='PFS') AND "FLAG"<>'625' AND TO_NUMBER("CUR_CODE")<>0 AND LENGTH(TRIM("MGR_CODE"))>=3) 31 - filter("FLAG" IS NOT NULL AND "DEPT1_CODE" IS NOT NULL AND ("ACCT_FLAG"='INCOME' OR "ACCT_FLAG"='INTLBU' OR "ACCT_FLAG"='PFS') AND "FLAG"<>'625' AND TO_NUMBER("CUR_CODE")<>0 AND LENGTH(TRIM("MGR_CODE"))>=3) 32 - 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')) 34 - filter("FLAG" IS NOT NULL AND "DEPT1_CODE" IS NOT NULL AND ("ACCT_FLAG"='INCOME' OR "ACCT_FLAG"='INTLBU' OR "ACCT_FLAG"='PFS') AND "FLAG"<>'625' AND TO_NUMBER("CUR_CODE")<>0 AND LENGTH(TRIM("MGR_CODE"))>=3) 36 - filter("FLAG" IS NOT NULL AND "DEPT1_CODE" IS NOT NULL AND ("ACCT_FLAG"='INCOME' OR "ACCT_FLAG"='INTLBU' OR "ACCT_FLAG"='PFS') AND "FLAG"<>'625' AND TO_NUMBER("CUR_CODE")<>0 AND LENGTH(TRIM("MGR_CODE"))>=3) 41 - 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) 42 - access(SUBSTR("FLAG",1,1)='6') 44 - 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"."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')) 45 - access(SUBSTR("FLAG"(+),1,1)='6') 46 - 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')) 48 - 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) 49 - access(SUBSTR("FLAG",1,1)='6') 51 - 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) 52 - access(SUBSTR("FLAG",1,1)='6') Note----- - dynamic sampling used for this statement (level=2)| 40 | PARTITION LIST SINGLE | | 1 | 605 | | 2 (0)| 00:00:01 | KEY | KEY ||* 41 | TABLE ACCESS BY LOCAL INDEX ROWID| T_PM_ACCT_DTL_AF | 1 | 605 | | 2 (0)| 00:00:01 | 736 | 736 ||* 42 | INDEX RANGE SCAN | T_PM_ACCT_DTL_AF_IDX1 | 8312 | | | 1 (0)| 00:00:01 | 736 | 736 | 41 - 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)42 - access(SUBSTR("FLAG",1,1)='6')这种情况下要怎么确认分区736对应的分区呢？select * from user_tab_partitions where table_name='T_PM_ACCT_DTL_AF' and partition_position=736---分区为ACCT_DTL_AF_20131223继续测试:create table T_PM_ACCT_DTL_AF_TEST ( DATA_DATE NUMBER(8), BRAN_CODE VARCHAR2(50), UNIT1_CODE VARCHAR2(10), UNIT2_CODE VARCHAR2(10), UNIT3_CODE VARCHAR2(10))partition by list (DATA_DATE) ( partition ACCT_DTL_AF_20090101 values (20090101));alter table T_PM_ACCT_DTL_AF_test add partition ACCT_DTL_AF_20090102 values(20090102) ;alter table T_PM_ACCT_DTL_AF_test add partition ACCT_DTL_AF_20090103 values(20090103) ;alter table T_PM_ACCT_DTL_AF_test add partition ACCT_DTL_AF_20090104 values(20090104) ;alter table T_PM_ACCT_DTL_AF_test add partition ACCT_DTL_AF_20090105 values(20090105) ;SQL> select table_name,partition_name,partition_position from user_tab_partitions where table_name='T_PM_ACCT_DTL_AF_TEST';TABLE_NAME PARTITION_NAME PARTITION_POSITION------------------------------ ------------------------------ ------------------T_PM_ACCT_DTL_AF_TEST ACCT_DTL_AF_20090101 1T_PM_ACCT_DTL_AF_TEST ACCT_DTL_AF_20090102 2T_PM_ACCT_DTL_AF_TEST ACCT_DTL_AF_20090103 3T_PM_ACCT_DTL_AF_TEST ACCT_DTL_AF_20090104 4T_PM_ACCT_DTL_AF_TEST ACCT_DTL_AF_20090105 5SQL> select count(*) from T_PM_ACCT_DTL_AF_TESTwhere DATA_DATE=20090104;Execution Plan----------------------------------------------------------Plan hash value: 788997387----------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 | | || 1 | SORT AGGREGATE | | 1 | 13 | | | | || 2 | PARTITION LIST SINGLE| | 1 | 13 | 2 (0)| 00:00:01 | 4 | 4 || 3 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF_TEST | 1 | 13 | 2 (0)| 00:00:01 | 4 | 4 |----------------------------------------------------------------------------------------------------------------Note----- - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size421 bytes sent via SQL*Net to client419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed访问的是4号分区SQL> select count(*) from T_PM_ACCT_DTL_AF_TESTwhere DATA_DATE=20090102;Execution Plan----------------------------------------------------------Plan hash value: 788997387----------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 | | || 1 | SORT AGGREGATE | | 1 | 13 | | | | || 2 | PARTITION LIST SINGLE| | 1 | 13 | 2 (0)| 00:00:01 | 2 | 2 || 3 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF_TEST | 1 | 13 | 2 (0)| 00:00:01 | 2 | 2 |----------------------------------------------------------------------------------------------------------------Note----- - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 24 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size421 bytes sent via SQL*Net to client419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed访问的是2号分区PARTITION_POSITION NUMBER Position of the partition within the table
0 0
- 确认执行计划中分区号对应的分区
- javascript 区号与城市的对应信息
- Oracle分区执行计划
- oracle 分区 索引 执行计划
- mysql 执行计划走分区
- 执行计划中与分区相关的OP介绍
- 分区扫描执行计划分析简介
- DB2 如何获得存储过程对应的包中的执行计划
- [RK3288][Android6.0] 调试笔记 --- eMMC分区号和名字的对应
- Android 选择国家对应区号 中英双版
- ORACLE的执行计划
- ORACLE的执行计划
- Oracle的执行计划
- 执行计划的显示
- oracle的执行计划
- mysql的执行计划
- mysql的执行计划
- MySQL的执行计划
- dom4j解析xml用输出流保存
- SQL单表复杂查询
- Android图片处理（Matrix,ColorMatrix）
- C++编译器与链接器工作原理
- 进程与线程及其区别
- 确认执行计划中分区号对应的分区
- ubuntu apache2 发布站点
- mysql查看表结构命令
- LINUX 动态链接库 - 学习总结
- 二见钟情之流程图
- Hibernate3高级特性-使用过滤器
- Log4J学习【十】Properties配置方式
- Program Files\android-sdk-windows\platform-tools\adb.exe' and can be executed.
- NHibernate一对多，多对一关系映射的一些心得 .
