ORACLE 视图合并SQL优化案例
朋友给了一条SQL说跑8个小时才出结果，结果集很小75条数据，给他安排一下。
SQL如下：
SELECT DISTINCT T.XLA_TYPE, T.XLA_CODE, T.VENDOR_CODE, T.VENDOR_NAME, T.PERIOD_NAME, T.BATCH_NAME, T.DESCRIPTION, T.INVOICE_AMOUNT, NVL(T.BASE_AMOUNT, T.INVOICE_AMOUNT) BASE_AMOUNT, T.INVOICE_NUM, T.INVOICE_DATE, T.INVOICE_TYPE, T.CURRENCY_CODE, T.LAST_NAME, T.SEGMENT1, T.CREATION_DATE, T.LEDGER_ID, T.ORG_ID, T.CREATED_BY, T.DOC_SEQUENCE_VALUE FROM (SELECT '费用发票凭证' XLA_TYPE, 'B' XLA_CODE, PV.SEGMENT1 VENDOR_CODE, PV.VENDOR_NAME, (SELECT AB.BATCH_NAME FROM AP_BATCHES_ALL AB WHERE AB.BATCH_ID = AI.BATCH_ID) BATCH_NAME, AI.DOC_SEQUENCE_VALUE, XAH.PERIOD_NAME, XAH.ACCOUNTING_DATE, AI.INVOICE_CURRENCY_CODE CURRENCY_CODE, AI.DESCRIPTION, AI.INVOICE_AMOUNT, AI.BASE_AMOUNT, AI.INVOICE_NUM, AI.INVOICE_DATE, (SELECT T.MEANING FROM AA T WHERE T.LOOKUP_TYPE = 'INVOICE TYPE' AND T.LOOKUP_CODE = AI.INVOICE_TYPE_LOOKUP_CODE) INVOICE_TYPE, PER.LAST_NAME, (SELECT GCC.SEGMENT1 FROM BB XAL, CC GCC WHERE XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND XAL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND ROWNUM = 1) SEGMENT1, AI.CREATION_DATE, XAH.LEDGER_ID, AI.ORG_ID, AI.CREATED_BY FROM DD AI, XLA.XLA_TRANSACTION_ENTITIES XTE, XLA_EVENTS XE, XLA_AE_HEADERS XAH, AP_SUPPLIERS PV, (SELECT F.USER_ID, P.LAST_NAME FROM EE F, FF P WHERE F.EMPLOYEE_ID = P.PERSON_ID) PER WHERE AI.SET_OF_BOOKS_ID = XTE.LEDGER_ID AND AI.INVOICE_ID = XTE.SOURCE_ID_INT_1 AND XTE.ENTITY_ID = XE.ENTITY_ID AND XE.EVENT_ID = XAH.EVENT_ID AND AI.VENDOR_ID = PV.VENDOR_ID AND XAH.ACCOUNTING_ENTRY_STATUS_CODE = 'F' AND XTE.ENTITY_CODE = 'AP_INVOICES' AND AI.CREATED_BY = PER.USER_ID(+) AND AI.INVOICE_TYPE_LOOKUP_CODE = 'EXPENSE REPORT' AND AI.ORG_ID = :B5 AND XAH.LEDGER_ID = :B4 AND XAH.PERIOD_NAME = :B3 AND AI.INVOICE_CURRENCY_CODE = NVL(:B2, AI.INVOICE_CURRENCY_CODE) AND :B1 = '费用发票' AND NOT EXISTS (SELECT 1 FROM GG XET WHERE XET.LANGUAGE = 'ZHS' AND XET.ENTITY_CODE LIKE 'AP_INVOICES' AND XET.EVENT_CLASS_CODE = 'PREPAYMENT APPLICATIONS' AND XET.EVENT_TYPE_CODE = XE.EVENT_TYPE_CODE AND XET.APPLICATION_ID = XE.APPLICATION_ID) UNION ALL SELECT '预付款发票凭证' XLA_TYPE, 'A' XLA_CODE, PV.SEGMENT1 VENDOR_CODE, PV.VENDOR_NAME, (SELECT AB.BATCH_NAME FROM AP_BATCHES_ALL AB WHERE AB.BATCH_ID = AI.BATCH_ID) BATCH_NAME, AI.DOC_SEQUENCE_VALUE, XAH.PERIOD_NAME, XAH.ACCOUNTING_DATE, AI.INVOICE_CURRENCY_CODE CURRENCY_CODE, AI.DESCRIPTION, AI.INVOICE_AMOUNT, AI.BASE_AMOUNT, AI.INVOICE_NUM, AI.INVOICE_DATE, (SELECT T.MEANING FROM AA T WHERE T.LOOKUP_TYPE = 'INVOICE TYPE' AND T.LOOKUP_CODE = AI.INVOICE_TYPE_LOOKUP_CODE) INVOICE_TYPE, PER.LAST_NAME, (SELECT GCC.SEGMENT1 FROM BB XAL, CC GCC WHERE XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND XAL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND ROWNUM = 1) SEGMENT1, AI.CREATION_DATE, XAH.LEDGER_ID, AI.ORG_ID, AI.CREATED_BY FROM DD AI, XLA.XLA_TRANSACTION_ENTITIES XTE, XLA_EVENTS XE, XLA_AE_HEADERS XAH, AP_SUPPLIERS PV, (SELECT F.USER_ID, P.LAST_NAME FROM EE F, FF P WHERE F.EMPLOYEE_ID = P.PERSON_ID) PER WHERE AI.SET_OF_BOOKS_ID = XTE.LEDGER_ID AND AI.INVOICE_ID = XTE.SOURCE_ID_INT_1 AND XTE.ENTITY_ID = XE.ENTITY_ID AND XE.EVENT_ID = XAH.EVENT_ID AND AI.VENDOR_ID = PV.VENDOR_ID AND XAH.ACCOUNTING_ENTRY_STATUS_CODE = 'F' AND XTE.ENTITY_CODE = 'AP_INVOICES' AND AI.CREATED_BY = PER.USER_ID(+) AND AI.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT' AND AI.ORG_ID = :B5 AND XAH.LEDGER_ID = :B4 AND XAH.PERIOD_NAME = :B3 AND AI.INVOICE_CURRENCY_CODE = NVL(:B2, AI.INVOICE_CURRENCY_CODE) AND :B1 = '预付款' AND NOT EXISTS (SELECT 1 FROM GG XET WHERE XET.LANGUAGE = 'ZHS' AND XET.ENTITY_CODE LIKE 'AP_INVOICES' AND XET.EVENT_CLASS_CODE = 'PREPAYMENT APPLICATIONS' AND XET.EVENT_TYPE_CODE = XE.EVENT_TYPE_CODE AND XET.APPLICATION_ID = XE.APPLICATION_ID) UNION ALL SELECT '标准发票凭证' XLA_TYPE, 'C' XLA_CODE, PV.SEGMENT1 VENDOR_CODE, PV.VENDOR_NAME, (SELECT AB.BATCH_NAME FROM AP_BATCHES_ALL AB WHERE AB.BATCH_ID = AI.BATCH_ID) BATCH_NAME, AI.DOC_SEQUENCE_VALUE, XAH.PERIOD_NAME, XAH.ACCOUNTING_DATE, AI.INVOICE_CURRENCY_CODE CURRENCY_CODE, AI.DESCRIPTION, AI.INVOICE_AMOUNT, AI.BASE_AMOUNT, AI.INVOICE_NUM, AI.INVOICE_DATE, (SELECT T.MEANING FROM AA T WHERE T.LOOKUP_TYPE = 'INVOICE TYPE' AND T.LOOKUP_CODE = AI.INVOICE_TYPE_LOOKUP_CODE) INVOICE_TYPE, PER.LAST_NAME, (SELECT GCC.SEGMENT1 FROM BB XAL, CC GCC WHERE XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND XAL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND ROWNUM = 1) SEGMENT1, AI.CREATION_DATE, XAH.LEDGER_ID, AI.ORG_ID, AI.CREATED_BY FROM DD AI, XLA.XLA_TRANSACTION_ENTITIES XTE, XLA_EVENTS XE, XLA_AE_HEADERS XAH, AP_SUPPLIERS PV, (SELECT F.USER_ID, P.LAST_NAME FROM EE F, FF P WHERE F.EMPLOYEE_ID = P.PERSON_ID) PER WHERE AI.SET_OF_BOOKS_ID = XTE.LEDGER_ID AND AI.INVOICE_ID = XTE.SOURCE_ID_INT_1 AND XTE.ENTITY_ID = XE.ENTITY_ID AND XE.EVENT_ID = XAH.EVENT_ID AND AI.VENDOR_ID = PV.VENDOR_ID AND XAH.ACCOUNTING_ENTRY_STATUS_CODE = 'F' AND XTE.ENTITY_CODE = 'AP_INVOICES' AND AI.CREATED_BY = PER.USER_ID(+) AND :B1 = '标准发票' AND AI.INVOICE_TYPE_LOOKUP_CODE NOT IN ('EXPENSE REPORT', 'PREPAYMENT') AND AI.ORG_ID = :B5 AND XAH.LEDGER_ID = :B4 AND XAH.PERIOD_NAME = :B3 AND AI.INVOICE_CURRENCY_CODE = NVL(:B2, AI.INVOICE_CURRENCY_CODE) AND NOT EXISTS (SELECT 1 FROM GG XET WHERE XET.LANGUAGE = 'ZHS' AND XET.ENTITY_CODE LIKE 'AP_INVOICES' AND XET.EVENT_CLASS_CODE = 'PREPAYMENT APPLICATIONS' AND XET.EVENT_TYPE_CODE = XE.EVENT_TYPE_CODE AND XET.APPLICATION_ID = XE.APPLICATION_ID)) T ORDER BY DOC_SEQUENCE_VALUE;
执行计划：
SQL_ID 0pkah9qp89a68, child number 0 ------------------------------------- SELECT DISTINCT T.XLA_TYPE, T.XLA_CODE, T.VENDOR_CODE, T.VENDOR_NAME, T.PERIOD_NAME, T.BATCH_NAME, T.DESCRIPTION, T.INVOICE_AMOUNT, NVL(T.BASE_AMOUNT, T.INVOICE_AMOUNT) BASE_AMOUNT, T.INVOICE_NUM, T.INVOICE_DATE, T.INVOICE_TYPE, T.CURRENCY_CODE, T.LAST_NAME, T.SEGMENT1, T.CREATION_DATE, T.LEDGER_ID, T.ORG_ID, T.CREATED_BY , T.DOC_SEQUENCE_VALUE FROM ( SELECT '费用发票凭证' XLA_TYPE, 'B' XLA_CODE, PV.SEGMENT1 VENDOR_CODE, PV.VENDOR_NAME, (SELECT AB.BATCH_NAME FROM AP_BATCHES_ALL AB WHERE AB.BATCH_ID = AI.BATCH_ID) BATCH_NAME, AI.DOC_SEQUENCE_VALUE , XAH.PERIOD_NAME, XAH.ACCOUNTING_DATE , AI.INVOICE_CURRENCY_CODE CURRENCY_CODE, AI.DESCRIPTION, AI.INVOICE_AMOUNT, AI.BASE_AMOUNT, AI.INVOICE_NUM, AI.INVOICE_DATE, (SELECT T.MEANING FROM AA T WHERE T.LOOKUP_TYPE = 'INVOICE TYPE' AND T.LOOKUP_CODE = AI.INVOICE_TYPE_LOOKUP_CODE) INVOICE_TYPE, PER.LAST_NAME, (SELECT GCC.SEGMENT1 FROM BB XAL, CC GCC WHERE XAL.AE_HEADER_ID = XAH.AE_HEADER_I Plan hash value: 801427799 ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2577K(100)| | | | | 1 | SORT UNIQUE | | 42 | 14028 | 2577K (1)| 08:35:34 | | | | 2 | VIEW | | 42 | 14028 | 2577K (1)| 08:35:34 | | | | 3 | UNION-ALL | | | | | | | | | 4 | TABLE ACCESS BY INDEX ROWID | AP_BATCHES_ALL | 1 | 27 | 2 (0)| 00:00:01 | | | |* 5 | INDEX UNIQUE SCAN | AP_BATCHES_U1 | 1 | | 1 (0)| 00:00:01 | | | | 6 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 1 | 52 | 4 (0)| 00:00:01 | | | |* 7 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | | 3 (0)| 00:00:01 | | | |* 8 | COUNT STOPKEY | | | | | | | | | 9 | NESTED LOOPS | | 2 | 42 | 63 (0)| 00:00:01 | | | | 10 | NESTED LOOPS | | 2 | 42 | 63 (0)| 00:00:01 | | | | 11 | PARTITION LIST ALL | | 2 | 24 | 59 (0)| 00:00:01 | 1 | 19 | | 12 | TABLE ACCESS BY LOCAL INDEX ROWID | BB | 2 | 24 | 59 (0)| 00:00:01 | 1 | 19 | |* 13 | INDEX RANGE SCAN | BB_U1 | 2 | | 58 (0)| 00:00:01 | 1 | 19 | |* 14 | INDEX UNIQUE SCAN | GL_CODE_COMBINATIONS_U1 | 1 | | 1 (0)| 00:00:01 | | | | 15 | TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS | 1 | 9 | 2 (0)| 00:00:01 | | | |* 16 | FILTER | | | | | | | | | 17 | NESTED LOOPS ANTI | | 7 | 2898 | 641K (1)| 02:08:15 | | | | 18 | NESTED LOOPS | | 7 | 2506 | 641K (1)| 02:08:15 | | | | 19 | NESTED LOOPS OUTER | | 7 | 2219 | 641K (1)| 02:08:15 | | | | 20 | NESTED LOOPS | | 7 | 1666 | 641K (1)| 02:08:15 | | | | 21 | NESTED LOOPS | | 1850 | 379K| 533K (1)| 01:46:46 | | | |* 22 | HASH JOIN | | 1707 | 300K| 434K (2)| 01:26:57 | | | |* 23 | TABLE ACCESS BY INDEX ROWID | DD | 1707 | 236K| 2854 (1)| 00:00:35 | | | |* 24 | INDEX RANGE SCAN | AP_INVOICES_N21 | 10244 | | 47 (0)| 00:00:01 | | | | 25 | PARTITION LIST ALL | | 230K| 8537K| 431K (2)| 01:26:23 | 1 | 19 | |* 26 | TABLE ACCESS FULL | XLA_TRANSACTION_ENTITIES | 230K| 8537K| 431K (2)| 01:26:23 | 1 | 19 | | 27 | PARTITION LIST ALL | | 1 | 30 | 58 (0)| 00:00:01 | 1 | 19 | | 28 | TABLE ACCESS BY LOCAL INDEX ROWID | XLA_EVENTS | 1 | 30 | 58 (0)| 00:00:01 | 1 | 19 | |* 29 | INDEX RANGE SCAN | XLA_EVENTS_U2 | 1 | | 57 (0)| 00:00:01 | 1 | 19 | | 30 | PARTITION LIST ALL | | 1 | 28 | 58 (0)| 00:00:01 | 1 | 19 | |* 31 | TABLE ACCESS BY LOCAL INDEX ROWID | XLA_AE_HEADERS | 1 | 28 | 58 (0)| 00:00:01 | 1 | 19 | |* 32 | INDEX RANGE SCAN | XLA_AE_HEADERS_N2 | 1 | | 57 (0)| 00:00:01 | 1 | 19 | | 33 | VIEW PUSHED PREDICATE | | 1 | 79 | 5 (0)| 00:00:01 | | | | 34 | NESTED LOOPS | | 1 | 40 | 5 (0)| 00:00:01 | | | |* 35 | TABLE ACCESS BY INDEX ROWID | EE | 1 | 8 | 2 (0)| 00:00:01 | | | |* 36 | INDEX UNIQUE SCAN | EE_U1 | 1 | | 1 (0)| 00:00:01 | | | |* 37 | TABLE ACCESS BY INDEX ROWID | PER_ALL_PEOPLE_F | 1 | 32 | 3 (0)| 00:00:01 | | | |* 38 | INDEX RANGE SCAN | FF_PK | 2 | | 1 (0)| 00:00:01 | | | | 39 | TABLE ACCESS BY INDEX ROWID | AP_SUPPLIERS | 1 | 41 | 1 (0)| 00:00:01 | | | |* 40 | INDEX UNIQUE SCAN | AP_SUPPLIERS_U1 | 1 | | 0 (0)| | | | |* 41 | INDEX RANGE SCAN | GG_U1 | 1 | 56 | 1 (0)| 00:00:01 | | | | 42 | TABLE ACCESS BY INDEX ROWID | AP_BATCHES_ALL | 1 | 27 | 2 (0)| 00:00:01 | | | |* 43 | INDEX UNIQUE SCAN | AP_BATCHES_U1 | 1 | | 1 (0)| 00:00:01 | | | | 44 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 1 | 52 | 4 (0)| 00:00:01 | | | |* 45 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | | 3 (0)| 00:00:01 | | | |* 46 | COUNT STOPKEY | | | | | | | | | 47 | NESTED LOOPS | | 2 | 42 | 63 (0)| 00:00:01 | | | | 48 | NESTED LOOPS | | 2 | 42 | 63 (0)| 00:00:01 | | | | 49 | PARTITION LIST ALL | | 2 | 24 | 59 (0)| 00:00:01 | 1 | 19 | | 50 | TABLE ACCESS BY LOCAL INDEX ROWID | BB | 2 | 24 | 59 (0)| 00:00:01 | 1 | 19 | |* 51 | INDEX RANGE SCAN | BB_U1 | 2 | | 58 (0)| 00:00:01 | 1 | 19 | |* 52 | INDEX UNIQUE SCAN | GL_CODE_COMBINATIONS_U1 | 1 | | 1 (0)| 00:00:01 | | | | 53 | TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS | 1 | 9 | 2 (0)| 00:00:01 | | | |* 54 | FILTER | | | | | | | | | 55 | NESTED LOOPS ANTI | | 7 | 2898 | 641K (1)| 02:08:15 | | | | 56 | NESTED LOOPS | | 7 | 2506 | 641K (1)| 02:08:15 | | | | 57 | NESTED LOOPS OUTER | | 7 | 2219 | 641K (1)| 02:08:15 | | | | 58 | NESTED LOOPS | | 7 | 1666 | 641K (1)| 02:08:15 | | | | 59 | NESTED LOOPS | | 1850 | 379K| 533K (1)| 01:46:46 | | | |* 60 | HASH JOIN | | 1707 | 300K| 434K (2)| 01:26:57 | | | |* 61 | TABLE ACCESS BY INDEX ROWID | DD | 1707 | 236K| 2854 (1)| 00:00:35 | | | |* 62 | INDEX RANGE SCAN | AP_INVOICES_N21 | 10244 | | 47 (0)| 00:00:01 | | | | 63 | PARTITION LIST ALL | | 230K| 8537K| 431K (2)| 01:26:23 | 1 | 19 | |* 64 | TABLE ACCESS FULL | XLA_TRANSACTION_ENTITIES | 230K| 8537K| 431K (2)| 01:26:23 | 1 | 19 | | 65 | PARTITION LIST ALL | | 1 | 30 | 58 (0)| 00:00:01 | 1 | 19 | | 66 | TABLE ACCESS BY LOCAL INDEX ROWID | XLA_EVENTS | 1 | 30 | 58 (0)| 00:00:01 | 1 | 19 | |* 67 | INDEX RANGE SCAN | XLA_EVENTS_U2 | 1 | | 57 (0)| 00:00:01 | 1 | 19 | | 68 | PARTITION LIST ALL | | 1 | 28 | 58 (0)| 00:00:01 | 1 | 19 | |* 69 | TABLE ACCESS BY LOCAL INDEX ROWID | XLA_AE_HEADERS | 1 | 28 | 58 (0)| 00:00:01 | 1 | 19 | |* 70 | INDEX RANGE SCAN | XLA_AE_HEADERS_N2 | 1 | | 57 (0)| 00:00:01 | 1 | 19 | | 71 | VIEW PUSHED PREDICATE | | 1 | 79 | 5 (0)| 00:00:01 | | | | 72 | NESTED LOOPS | | 1 | 40 | 5 (0)| 00:00:01 | | | |* 73 | TABLE ACCESS BY INDEX ROWID | EE | 1 | 8 | 2 (0)| 00:00:01 | | | |* 74 | INDEX UNIQUE SCAN | EE_U1 | 1 | | 1 (0)| 00:00:01 | | | |* 75 | TABLE ACCESS BY INDEX ROWID | PER_ALL_PEOPLE_F | 1 | 32 | 3 (0)| 00:00:01 | | | |* 76 | INDEX RANGE SCAN | FF_PK | 2 | | 1 (0)| 00:00:01 | | | | 77 | TABLE ACCESS BY INDEX ROWID | AP_SUPPLIERS | 1 | 41 | 1 (0)| 00:00:01 | | | |* 78 | INDEX UNIQUE SCAN | AP_SUPPLIERS_U1 | 1 | | 0 (0)| | | | |* 79 | INDEX RANGE SCAN | GG_U1 | 1 | 56 | 1 (0)| 00:00:01 | | | | 80 | TABLE ACCESS BY INDEX ROWID | AP_BATCHES_ALL | 1 | 27 | 2 (0)| 00:00:01 | | | |* 81 | INDEX UNIQUE SCAN | AP_BATCHES_U1 | 1 | | 1 (0)| 00:00:01 | | | | 82 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 1 | 52 | 4 (0)| 00:00:01 | | | |* 83 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | | 3 (0)| 00:00:01 | | | |* 84 | COUNT STOPKEY | | | | | | | | | 85 | NESTED LOOPS | | 2 | 42 | 63 (0)| 00:00:01 | | | | 86 | NESTED LOOPS | | 2 | 42 | 63 (0)| 00:00:01 | | | | 87 | PARTITION LIST ALL | | 2 | 24 | 59 (0)| 00:00:01 | 1 | 19 | | 88 | TABLE ACCESS BY LOCAL INDEX ROWID | BB | 2 | 24 | 59 (0)| 00:00:01 | 1 | 19 | |* 89 | INDEX RANGE SCAN | BB_U1 | 2 | | 58 (0)| 00:00:01 | 1 | 19 | |* 90 | INDEX UNIQUE SCAN | GL_CODE_COMBINATIONS_U1 | 1 | | 1 (0)| 00:00:01 | | | | 91 | TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS | 1 | 9 | 2 (0)| 00:00:01 | | | |* 92 | FILTER | | | | | | | | |* 93 | HASH JOIN RIGHT ANTI | | 28 | 11592 | 1295K (1)| 04:19:05 | | | |* 94 | TABLE ACCESS FULL | GG | 1 | 56 | 13 (0)| 00:00:01 | | | | 95 | NESTED LOOPS | | 28 | 10024 | 1295K (1)| 04:19:05 | | | | 96 | NESTED LOOPS | | 28 | 10024 | 1295K (1)| 04:19:05 | | | | 97 | NESTED LOOPS OUTER | | 28 | 8876 | 1295K (1)| 04:19:04 | | | | 98 | NESTED LOOPS | | 28 | 6664 | 1295K (1)| 04:19:03 | | | | 99 | NESTED LOOPS | | 7710 | 1581K| 847K (1)| 02:49:33 | | | |*100 | HASH JOIN | | 7114 | 1250K| 434K (2)| 01:26:57 | | | |*101 | TABLE ACCESS BY INDEX ROWID | DD | 7114 | 986K| 2854 (1)| 00:00:35 | | | |*102 | INDEX RANGE SCAN | AP_INVOICES_N21 | 10244 | | 47 (0)| 00:00:01 | | | | 103 | PARTITION LIST ALL | | 230K| 8537K| 431K (2)| 01:26:23 | 1 | 19 | |*104 | TABLE ACCESS FULL | XLA_TRANSACTION_ENTITIES | 230K| 8537K| 431K (2)| 01:26:23 | 1 | 19 | | 105 | PARTITION LIST ALL | | 1 | 30 | 58 (0)| 00:00:01 | 1 | 19 | | 106 | TABLE ACCESS BY LOCAL INDEX ROWID| XLA_EVENTS | 1 | 30 | 58 (0)| 00:00:01 | 1 | 19 | |*107 | INDEX RANGE SCAN | XLA_EVENTS_U2 | 1 | | 57 (0)| 00:00:01 | 1 | 19 | | 108 | PARTITION LIST ALL | | 1 | 28 | 58 (0)| 00:00:01 | 1 | 19 | |*109 | TABLE ACCESS BY LOCAL INDEX ROWID | XLA_AE_HEADERS | 1 | 28 | 58 (0)| 00:00:01 | 1 | 19 | |*110 | INDEX RANGE SCAN | XLA_AE_HEADERS_N2 | 1 | | 57 (0)| 00:00:01 | 1 | 19 | | 111 | VIEW PUSHED PREDICATE | | 1 | 79 | 5 (0)| 00:00:01 | | | | 112 | NESTED LOOPS | | 1 | 40 | 5 (0)| 00:00:01 | | | |*113 | TABLE ACCESS BY INDEX ROWID | EE | 1 | 8 | 2 (0)| 00:00:01 | | | |*114 | INDEX UNIQUE SCAN | EE_U1 | 1 | | 1 (0)| 00:00:01 | | | |*115 | TABLE ACCESS BY INDEX ROWID | PER_ALL_PEOPLE_F | 1 | 32 | 3 (0)| 00:00:01 | | | |*116 | INDEX RANGE SCAN | FF_PK | 2 | | 1 (0)| 00:00:01 | | | |*117 | INDEX UNIQUE SCAN | AP_SUPPLIERS_U1 | 1 | | 0 (0)| | | | | 118 | TABLE ACCESS BY INDEX ROWID | AP_SUPPLIERS | 1 | 41 | 1 (0)| 00:00:01 | | | ----------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("AB"."BATCH_ID"=:B1) 7 - access("B"."LOOKUP_TYPE"='INVOICE TYPE' AND "B"."LOOKUP_CODE"=:B1 AND "B"."LANGUAGE"=USERENV('LANG')) filter(("B"."LOOKUP_CODE"=:B1 AND "B"."LANGUAGE"=USERENV('LANG'))) 8 - filter(ROWNUM=1) 13 - access("XAL"."AE_HEADER_ID"=:B1) 14 - access("XAL"."CODE_COMBINATION_ID"="CODE_COMBINATION_ID") 16 - filter(:B1='费用发票') 22 - access("AI"."SET_OF_BOOKS_ID"="XTE"."LEDGER_ID" AND "AI"."INVOICE_ID"="XTE"."SOURCE_ID_INT_1") 23 - filter(("AI"."INVOICE_TYPE_LOOKUP_CODE"='EXPENSE REPORT' AND "AI"."INVOICE_CURRENCY_CODE"=NVL(:B2,"AI"."INVOICE_CURRENCY_CODE"))) 24 - access("AI"."ORG_ID"=:B5) 26 - filter("XTE"."ENTITY_CODE"='AP_INVOICES') 29 - access("XTE"."ENTITY_ID"="XE"."ENTITY_ID") 31 - filter(("XAH"."PERIOD_NAME"=:B3 AND "XAH"."ACCOUNTING_ENTRY_STATUS_CODE"='F')) 32 - access("XE"."EVENT_ID"="XAH"."EVENT_ID" AND "XAH"."LEDGER_ID"=:B4) 35 - filter("F"."EMPLOYEE_ID" IS NOT NULL) 36 - access("F"."USER_ID"="AI"."CREATED_BY") 37 - filter((DECODE("HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW_PERSON"("PAP"."PERSON_ID","PAP"."CURRENT_APPLICAN T_FLAG","PAP"."CURRENT_EMPLOYEE_FLAG","PAP"."CURRENT_NPW_FLAG","PAP"."EMPLOYEE_NUMBER","PAP"."APPLICANT_NUMBER","PAP"."NPW_NUMBER "))='TRUE' AND "PAP"."BUSINESS_GROUP_ID"=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(),'Y',"PAP"."BUSINESS_GROUP_ID","HR_GENERAL"."GET_ BUSINESS_GROUP_ID"()))) 38 - access("F"."EMPLOYEE_ID"="PERSON_ID") 40 - access("AI"."VENDOR_ID"="PV"."VENDOR_ID") 41 - access("XET"."APPLICATION_ID"="XE"."APPLICATION_ID" AND "XET"."ENTITY_CODE" LIKE 'AP_INVOICES' AND "XET"."EVENT_CLASS_CODE"='PREPAYMENT APPLICATIONS' AND "XET"."EVENT_TYPE_CODE"="XE"."EVENT_TYPE_CODE" AND "XET"."LANGUAGE"='ZHS') filter(("XET"."EVENT_CLASS_CODE"='PREPAYMENT APPLICATIONS' AND "XET"."LANGUAGE"='ZHS' AND "XET"."ENTITY_CODE" LIKE 'AP_INVOICES' AND "XET"."EVENT_TYPE_CODE"="XE"."EVENT_TYPE_CODE")) 43 - access("AB"."BATCH_ID"=:B1) 45 - access("B"."LOOKUP_TYPE"='INVOICE TYPE' AND "B"."LOOKUP_CODE"=:B1 AND "B"."LANGUAGE"=USERENV('LANG')) filter(("B"."LOOKUP_CODE"=:B1 AND "B"."LANGUAGE"=USERENV('LANG'))) 46 - filter(ROWNUM=1) 51 - access("XAL"."AE_HEADER_ID"=:B1) 52 - access("XAL"."CODE_COMBINATION_ID"="CODE_COMBINATION_ID") 54 - filter(:B1='预付款') 60 - access("AI"."SET_OF_BOOKS_ID"="XTE"."LEDGER_ID" AND "AI"."INVOICE_ID"="XTE"."SOURCE_ID_INT_1") 61 - filter(("AI"."INVOICE_TYPE_LOOKUP_CODE"='PREPAYMENT' AND "AI"."INVOICE_CURRENCY_CODE"=NVL(:B2,"AI"."INVOICE_CURRENCY_CODE" ))) 62 - access("AI"."ORG_ID"=:B5) 64 - filter("XTE"."ENTITY_CODE"='AP_INVOICES') 67 - access("XTE"."ENTITY_ID"="XE"."ENTITY_ID") 69 - filter(("XAH"."PERIOD_NAME"=:B3 AND "XAH"."ACCOUNTING_ENTRY_STATUS_CODE"='F')) 70 - access("XE"."EVENT_ID"="XAH"."EVENT_ID" AND "XAH"."LEDGER_ID"=:B4) 73 - filter("F"."EMPLOYEE_ID" IS NOT NULL) 74 - access("F"."USER_ID"="AI"."CREATED_BY") 75 - filter((DECODE("HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW_PERSON"("PAP"."PERSON_ID","PAP"."CURRENT_APPLICAN T_FLAG","PAP"."CURRENT_EMPLOYEE_FLAG","PAP"."CURRENT_NPW_FLAG","PAP"."EMPLOYEE_NUMBER","PAP"."APPLICANT_NUMBER","PAP"."NPW_NUMBER "))='TRUE' AND "PAP"."BUSINESS_GROUP_ID"=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(),'Y',"PAP"."BUSINESS_GROUP_ID","HR_GENERAL"."GET_ BUSINESS_GROUP_ID"()))) 76 - access("F"."EMPLOYEE_ID"="PERSON_ID") 78 - access("AI"."VENDOR_ID"="PV"."VENDOR_ID") 79 - access("XET"."APPLICATION_ID"="XE"."APPLICATION_ID" AND "XET"."ENTITY_CODE" LIKE 'AP_INVOICES' AND "XET"."EVENT_CLASS_CODE"='PREPAYMENT APPLICATIONS' AND "XET"."EVENT_TYPE_CODE"="XE"."EVENT_TYPE_CODE" AND "XET"."LANGUAGE"='ZHS') filter(("XET"."EVENT_CLASS_CODE"='PREPAYMENT APPLICATIONS' AND "XET"."LANGUAGE"='ZHS' AND "XET"."ENTITY_CODE" LIKE 'AP_INVOICES' AND "XET"."EVENT_TYPE_CODE"="XE"."EVENT_TYPE_CODE")) 81 - access("AB"."BATCH_ID"=:B1) 83 - access("B"."LOOKUP_TYPE"='INVOICE TYPE' AND "B"."LOOKUP_CODE"=:B1 AND "B"."LANGUAGE"=USERENV('LANG')) filter(("B"."LOOKUP_CODE"=:B1 AND "B"."LANGUAGE"=USERENV('LANG'))) 84 - filter(ROWNUM=1) 89 - access("XAL"."AE_HEADER_ID"=:B1) 90 - access("XAL"."CODE_COMBINATION_ID"="CODE_COMBINATION_ID") 92 - filter(:B1='标准发票') 93 - access("XET"."EVENT_TYPE_CODE"="XE"."EVENT_TYPE_CODE" AND "XET"."APPLICATION_ID"="XE"."APPLICATION_ID") 94 - filter(("XET"."EVENT_CLASS_CODE"='PREPAYMENT APPLICATIONS' AND "XET"."LANGUAGE"='ZHS' AND "XET"."ENTITY_CODE" LIKE 'AP_INVOICES')) 100 - access("AI"."SET_OF_BOOKS_ID"="XTE"."LEDGER_ID" AND "AI"."INVOICE_ID"="XTE"."SOURCE_ID_INT_1") 101 - filter(("AI"."INVOICE_TYPE_LOOKUP_CODE"<>'EXPENSE REPORT' AND "AI"."INVOICE_TYPE_LOOKUP_CODE"<>'PREPAYMENT' AND "AI"."INVOICE_CURRENCY_CODE"=NVL(:B2,"AI"."INVOICE_CURRENCY_CODE"))) 102 - access("AI"."ORG_ID"=:B5) 104 - filter("XTE"."ENTITY_CODE"='AP_INVOICES') 107 - access("XTE"."ENTITY_ID"="XE"."ENTITY_ID") 109 - filter(("XAH"."PERIOD_NAME"=:B3 AND "XAH"."ACCOUNTING_ENTRY_STATUS_CODE"='F')) 110 - access("XE"."EVENT_ID"="XAH"."EVENT_ID" AND "XAH"."LEDGER_ID"=:B4) 113 - filter("F"."EMPLOYEE_ID" IS NOT NULL) 114 - access("F"."USER_ID"="AI"."CREATED_BY") 115 - filter((DECODE("HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW_PERSON"("PAP"."PERSON_ID","PAP"."CURRENT_APPLICAN T_FLAG","PAP"."CURRENT_EMPLOYEE_FLAG","PAP"."CURRENT_NPW_FLAG","PAP"."EMPLOYEE_NUMBER","PAP"."APPLICANT_NUMBER","PAP"."NPW_NUMBER "))='TRUE' AND "PAP"."BUSINESS_GROUP_ID"=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(),'Y',"PAP"."BUSINESS_GROUP_ID","HR_GENERAL"."GET_ BUSINESS_GROUP_ID"()))) 116 - access("F"."EMPLOYEE_ID"="PERSON_ID") 117 - access("AI"."VENDOR_ID"="PV"."VENDOR_ID")
粗略看了一下SQL大概知道语义内容，瞄了一眼执行计划，大概知道问题所在的地方，让他加了一条hint 再跑一下这条SQL。
SELECT /*+MERGE(T)*/ DISTINCT T.XLA_TYPE, T.XLA_CODE, T.VENDOR_CODE, T.VENDOR_NAME, T.PERIOD_NAME, T.BATCH_NAME, T.DESCRIPTION, T.INVOICE_AMOUNT, NVL(T.BASE_AMOUNT, T.INVOICE_AMOUNT) BASE_AMOUNT, T.INVOICE_NUM, T.INVOICE_DATE, T.INVOICE_TYPE, T.CURRENCY_CODE, T.LAST_NAME, T.SEGMENT1, T.CREATION_DATE, T.LEDGER_ID, T.ORG_ID, T.CREATED_BY, T.DOC_SEQUENCE_VALUE FROM (SELECT '费用发票凭证' XLA_TYPE.... 后面内容省略，啥都没改。
执行计划：
Plan hash value: 3618802827 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.17 | 1 | SORT UNIQUE | | 1 | 8 | 0 |00:00:00.17 | 2 | VIEW | | 1 | 8 | 0 |00:00:00.17 | 3 | UNION-ALL | | 1 | | 0 |00:00:00.17 | 4 | TABLE ACCESS BY INDEX ROWID | AP_BATCHES_ALL | 0 | 1 | 0 |00:00:00.01 |* 5 | INDEX UNIQUE SCAN | AP_BATCHES_U1 | 0 | 1 | 0 |00:00:00.01 | 6 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 0 | 1 | 0 |00:00:00.01 |* 7 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 0 | 1 | 0 |00:00:00.01 |* 8 | COUNT STOPKEY | | 0 | | 0 |00:00:00.01 | 9 | NESTED LOOPS | | 0 | 2 | 0 |00:00:00.01 | 10 | NESTED LOOPS | | 0 | 2 | 0 |00:00:00.01 | 11 | PARTITION LIST ALL | | 0 | 2 | 0 |00:00:00.01 | 12 | TABLE ACCESS BY LOCAL INDEX ROWID | BB | 0 | 2 | 0 |00:00:00.01 |* 13 | INDEX RANGE SCAN | BB_U1 | 0 | 2 | 0 |00:00:00.01 |* 14 | INDEX UNIQUE SCAN | GL_CODE_COMBINATIONS_U1 | 0 | 1 | 0 |00:00:00.01 | 15 | TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS | 0 | 1 | 0 |00:00:00.01 | 16 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.08 | 17 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.08 | 18 | NESTED LOOPS ANTI | | 1 | 1 | 0 |00:00:00.08 | 19 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.08 | 20 | NESTED LOOPS | | 1 | 461 | 1038 |00:00:00.04 | 21 | NESTED LOOPS | | 1 | 426 | 741 |00:00:00.01 |* 22 | TABLE ACCESS BY INDEX ROWID | DD | 1 | 426 | 741 |00:00:00.01 |* 23 | INDEX SKIP SCAN | AP_INVOICES_N3 | 1 | 10291 | 1429 |00:00:00.01 |* 24 | TABLE ACCESS BY GLOBAL INDEX ROWID| XLA_TRANSACTION_ENTITIES | 741 | 1 | 741 |00:00:00.01 |* 25 | INDEX RANGE SCAN | XLA_TRANSACTION_ENTITIES_N2 | 741 | 1 | 744 |00:00:00.01 | 26 | PARTITION LIST ALL | | 741 | 1 | 1038 |00:00:00.03 | 27 | TABLE ACCESS BY LOCAL INDEX ROWID | XLA_EVENTS | 14079 | 1 | 1038 |00:00:00.03 |* 28 | INDEX RANGE SCAN | XLA_EVENTS_U2 | 14079 | 1 | 1038 |00:00:00.02 | 29 | PARTITION LIST ALL | | 1038 | 1 | 0 |00:00:00.04 |* 30 | TABLE ACCESS BY LOCAL INDEX ROWID | XLA_AE_HEADERS | 19722 | 1 | 0 |00:00:00.04 |* 31 | INDEX RANGE SCAN | XLA_AE_HEADERS_N2 | 19722 | 1 | 0 |00:00:00.03 |* 32 | INDEX RANGE SCAN | GG_U1 | 0 | 1 | 0 |00:00:00.01 | 33 | TABLE ACCESS BY INDEX ROWID | AP_SUPPLIERS | 0 | 1 | 0 |00:00:00.01 |* 34 | INDEX UNIQUE SCAN | AP_SUPPLIERS_U1 | 0 | 1 | 0 |00:00:00.01 | 35 | VIEW PUSHED PREDICATE | | 0 | 1 | 0 |00:00:00.01 | 36 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 |* 37 | TABLE ACCESS BY INDEX ROWID | EE | 0 | 1 | 0 |00:00:00.01 |* 38 | INDEX UNIQUE SCAN | EE_U1 | 0 | 1 | 0 |00:00:00.01 |* 39 | TABLE ACCESS BY INDEX ROWID | PER_ALL_PEOPLE_F | 0 | 1 | 0 |00:00:00.01 |* 40 | INDEX RANGE SCAN | FF_PK | 0 | 2 | 0 |00:00:00.01 | 41 | TABLE ACCESS BY INDEX ROWID | AP_BATCHES_ALL | 0 | 1 | 0 |00:00:00.01 |* 42 | INDEX UNIQUE SCAN | AP_BATCHES_U1 | 0 | 1 | 0 |00:00:00.01 | 43 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 0 | 1 | 0 |00:00:00.01 |* 44 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 0 | 1 | 0 |00:00:00.01 |* 45 | COUNT STOPKEY | | 0 | | 0 |00:00:00.01 | 46 | NESTED LOOPS | | 0 | 2 | 0 |00:00:00.01 | 47 | NESTED LOOPS | | 0 | 2 | 0 |00:00:00.01 | 48 | PARTITION LIST ALL | | 0 | 2 | 0 |00:00:00.01 | 49 | TABLE ACCESS BY LOCAL INDEX ROWID | BB | 0 | 2 | 0 |00:00:00.01 |* 50 | INDEX RANGE SCAN | BB_U1 | 0 | 2 | 0 |00:00:00.01 |* 51 | INDEX UNIQUE SCAN | GL_CODE_COMBINATIONS_U1 | 0 | 1 | 0 |00:00:00.01 | 52 | TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS | 0 | 1 | 0 |00:00:00.01 | 53 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.06 | 54 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.06 | 55 | NESTED LOOPS ANTI | | 1 | 1 | 0 |00:00:00.06 | 56 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.06 | 57 | NESTED LOOPS | | 1 | 461 | 470 |00:00:00.03 | 58 | NESTED LOOPS | | 1 | 426 | 454 |00:00:00.01 |* 59 | TABLE ACCESS BY INDEX ROWID | DD | 1 | 426 | 454 |00:00:00.01 |* 60 | INDEX SKIP SCAN | AP_INVOICES_N3 | 1 | 10291 | 1429 |00:00:00.01 |* 61 | TABLE ACCESS BY GLOBAL INDEX ROWID| XLA_TRANSACTION_ENTITIES | 454 | 1 | 454 |00:00:00.01 |* 62 | INDEX RANGE SCAN | XLA_TRANSACTION_ENTITIES_N2 | 454 | 1 | 454 |00:00:00.01 | 63 | PARTITION LIST ALL | | 454 | 1 | 470 |00:00:00.03 | 64 | TABLE ACCESS BY LOCAL INDEX ROWID | XLA_EVENTS | 8626 | 1 | 470 |00:00:00.02 |* 65 | INDEX RANGE SCAN | XLA_EVENTS_U2 | 8626 | 1 | 470 |00:00:00.02 | 66 | PARTITION LIST ALL | | 470 | 1 | 0 |00:00:00.02 |* 67 | TABLE ACCESS BY LOCAL INDEX ROWID | XLA_AE_HEADERS | 8930 | 1 | 0 |00:00:00.02 |* 68 | INDEX RANGE SCAN | XLA_AE_HEADERS_N2 | 8930 | 1 | 0 |00:00:00.02 |* 69 | INDEX RANGE SCAN | GG_U1 | 0 | 1 | 0 |00:00:00.01 | 70 | TABLE ACCESS BY INDEX ROWID | AP_SUPPLIERS | 0 | 1 | 0 |00:00:00.01 |* 71 | INDEX UNIQUE SCAN | AP_SUPPLIERS_U1 | 0 | 1 | 0 |00:00:00.01 | 72 | VIEW PUSHED PREDICATE | | 0 | 1 | 0 |00:00:00.01 | 73 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 |* 74 | TABLE ACCESS BY INDEX ROWID | EE | 0 | 1 | 0 |00:00:00.01 |* 75 | INDEX UNIQUE SCAN | EE_U1 | 0 | 1 | 0 |00:00:00.01 |* 76 | TABLE ACCESS BY INDEX ROWID | PER_ALL_PEOPLE_F | 0 | 1 | 0 |00:00:00.01 |* 77 | INDEX RANGE SCAN | FF_PK | 0 | 2 | 0 |00:00:00.01 | 78 | TABLE ACCESS BY INDEX ROWID | AP_BATCHES_ALL | 0 | 1 | 0 |00:00:00.01 |* 79 | INDEX UNIQUE SCAN | AP_BATCHES_U1 | 0 | 1 | 0 |00:00:00.01 | 80 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 0 | 1 | 0 |00:00:00.01 |* 81 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 0 | 1 | 0 |00:00:00.01 |* 82 | COUNT STOPKEY | | 0 | | 0 |00:00:00.01 | 83 | NESTED LOOPS | | 0 | 2 | 0 |00:00:00.01 | 84 | NESTED LOOPS | | 0 | 2 | 0 |00:00:00.01 | 85 | PARTITION LIST ALL | | 0 | 2 | 0 |00:00:00.01 | 86 | TABLE ACCESS BY LOCAL INDEX ROWID | BB | 0 | 2 | 0 |00:00:00.01 |* 87 | INDEX RANGE SCAN | BB_U1 | 0 | 2 | 0 |00:00:00.01 |* 88 | INDEX UNIQUE SCAN | GL_CODE_COMBINATIONS_U1 | 0 | 1 | 0 |00:00:00.01 | 89 | TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS | 0 | 1 | 0 |00:00:00.01 | 90 | NESTED LOOPS OUTER | | 1 | 6 | 0 |00:00:00.03 | 91 | NESTED LOOPS | | 1 | 6 | 0 |00:00:00.03 | 92 | NESTED LOOPS ANTI | | 1 | 6 | 0 |00:00:00.03 | 93 | NESTED LOOPS | | 1 | 6 | 0 |00:00:00.03 | 94 | NESTED LOOPS | | 1 | 1922 | 275 |00:00:00.02 | 95 | NESTED LOOPS | | 1 | 1774 | 233 |00:00:00.01 |* 96 | TABLE ACCESS BY INDEX ROWID | DD | 1 | 1774 | 234 |00:00:00.01 |* 97 | INDEX SKIP SCAN | AP_INVOICES_N3 | 1 | 10291 | 1429 |00:00:00.01 |* 98 | TABLE ACCESS BY GLOBAL INDEX ROWID| XLA_TRANSACTION_ENTITIES | 234 | 1 | 233 |00:00:00.01 |* 99 | INDEX RANGE SCAN | XLA_TRANSACTION_ENTITIES_N2 | 234 | 1 | 233 |00:00:00.01 | 100 | PARTITION LIST ALL | | 233 | 1 | 275 |00:00:00.01 | 101 | TABLE ACCESS BY LOCAL INDEX ROWID | XLA_EVENTS | 4427 | 1 | 275 |00:00:00.01 |*102 | INDEX RANGE SCAN | XLA_EVENTS_U2 | 4427 | 1 | 275 |00:00:00.01 | 103 | PARTITION LIST ALL | | 275 | 1 | 0 |00:00:00.01 |*104 | TABLE ACCESS BY LOCAL INDEX ROWID | XLA_AE_HEADERS | 5225 | 1 | 0 |00:00:00.01 |*105 | INDEX RANGE SCAN | XLA_AE_HEADERS_N2 | 5225 | 1 | 0 |00:00:00.01 |*106 | INDEX RANGE SCAN | GG_U1 | 0 | 1 | 0 |00:00:00.01 | 107 | TABLE ACCESS BY INDEX ROWID | AP_SUPPLIERS | 0 | 1 | 0 |00:00:00.01 |*108 | INDEX UNIQUE SCAN | AP_SUPPLIERS_U1 | 0 | 1 | 0 |00:00:00.01 | 109 | VIEW PUSHED PREDICATE | | 0 | 1 | 0 |00:00:00.01 | 110 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 |*111 | TABLE ACCESS BY INDEX ROWID | EE | 0 | 1 | 0 |00:00:00.01 |*112 | INDEX UNIQUE SCAN | EE_U1 | 0 | 1 | 0 |00:00:00.01 |*113 | TABLE ACCESS BY INDEX ROWID | PER_ALL_PEOPLE_F | 0 | 1 | 0 |00:00:00.01 |*114 | INDEX RANGE SCAN | FF_PK | 0 | 2 | 0 |00:00:00.01 ------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("AB"."BATCH_ID"=:B1) 7 - access("B"."LOOKUP_TYPE"='INVOICE TYPE' AND "B"."LOOKUP_CODE"=:B1 AND "B"."LANGUAGE"=USERENV('LANG')) filter(("B"."LOOKUP_CODE"=:B1 AND "B"."LANGUAGE"=USERENV('LANG'))) 8 - filter(ROWNUM=1) 13 - access("XAL"."AE_HEADER_ID"=:B1) 14 - access("XAL"."CODE_COMBINATION_ID"="CODE_COMBINATION_ID") 22 - filter(("AI"."INVOICE_TYPE_LOOKUP_CODE"='EXPENSE REPORT' AND "AI"."INVOICE_CURRENCY_CODE"='CNY')) 23 - access("AI"."ORG_ID"=379) filter("AI"."ORG_ID"=379) 24 - filter("XTE"."ENTITY_CODE"='AP_INVOICES') 25 - access("AI"."SET_OF_BOOKS_ID"="XTE"."LEDGER_ID" AND "AI"."INVOICE_ID"="XTE"."SOURCE_ID_INT_1") 28 - access("XTE"."ENTITY_ID"="XE"."ENTITY_ID") 30 - filter(("XAH"."PERIOD_NAME"='2022-11' AND "XAH"."ACCOUNTING_ENTRY_STATUS_CODE"='F')) 31 - access("XE"."EVENT_ID"="XAH"."EVENT_ID" AND "XAH"."LEDGER_ID"=2026) 32 - access("XET"."APPLICATION_ID"="XE"."APPLICATION_ID" AND "XET"."ENTITY_CODE" LIKE 'AP_INVOICES' AND "XET"."EVENT_CL APPLICATIONS' AND "XET"."EVENT_TYPE_CODE"="XE"."EVENT_TYPE_CODE" AND "XET"."LANGUAGE"='ZHS') filter(("XET"."EVENT_CLASS_CODE"='PREPAYMENT APPLICATIONS' AND "XET"."LANGUAGE"='ZHS' AND "XET"."ENTITY_CODE" LIKE "XET"."EVENT_TYPE_CODE"="XE"."EVENT_TYPE_CODE")) 34 - access("AI"."VENDOR_ID"="PV"."VENDOR_ID") 37 - filter("F"."EMPLOYEE_ID" IS NOT NULL) 38 - access("F"."USER_ID"="AI"."CREATED_BY") 39 - filter((DECODE("HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW_PERSON"("PAP"."PERSON_ID","PAP"."CURRENT_ EMPLOYEE_FLAG","PAP"."CURRENT_NPW_FLAG","PAP"."EMPLOYEE_NUMBER","PAP"."APPLICANT_NUMBER","PAP"."NPW_NUMBER" "PAP"."BUSINESS_GROUP_ID"=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(),'Y',"PAP"."BUSINESS_GROUP_ID","HR_GENERAL 40 - access("F"."EMPLOYEE_ID"="PERSON_ID") 42 - access("AB"."BATCH_ID"=:B1) 44 - access("B"."LOOKUP_TYPE"='INVOICE TYPE' AND "B"."LOOKUP_CODE"=:B1 AND "B"."LANGUAGE"=USERENV('LANG')) filter(("B"."LOOKUP_CODE"=:B1 AND "B"."LANGUAGE"=USERENV('LANG'))) 45 - filter(ROWNUM=1) 50 - access("XAL"."AE_HEADER_ID"=:B1) 51 - access("XAL"."CODE_COMBINATION_ID"="CODE_COMBINATION_ID") 59 - filter(("AI"."INVOICE_TYPE_LOOKUP_CODE"='PREPAYMENT' AND "AI"."INVOICE_CURRENCY_CODE"='CNY')) 60 - access("AI"."ORG_ID"=379) filter("AI"."ORG_ID"=379) 61 - filter("XTE"."ENTITY_CODE"='AP_INVOICES') 62 - access("AI"."SET_OF_BOOKS_ID"="XTE"."LEDGER_ID" AND "AI"."INVOICE_ID"="XTE"."SOURCE_ID_INT_1") 65 - access("XTE"."ENTITY_ID"="XE"."ENTITY_ID") 67 - filter(("XAH"."PERIOD_NAME"='2022-11' AND "XAH"."ACCOUNTING_ENTRY_STATUS_CODE"='F')) 68 - access("XE"."EVENT_ID"="XAH"."EVENT_ID" AND "XAH"."LEDGER_ID"=2026) 69 - access("XET"."APPLICATION_ID"="XE"."APPLICATION_ID" AND "XET"."ENTITY_CODE" LIKE 'AP_INVOICES' AND "XET"."EVENT_CL APPLICATIONS' AND "XET"."EVENT_TYPE_CODE"="XE"."EVENT_TYPE_CODE" AND "XET"."LANGUAGE"='ZHS') filter(("XET"."EVENT_CLASS_CODE"='PREPAYMENT APPLICATIONS' AND "XET"."LANGUAGE"='ZHS' AND "XET"."ENTITY_CODE" LIKE "XET"."EVENT_TYPE_CODE"="XE"."EVENT_TYPE_CODE")) 71 - access("AI"."VENDOR_ID"="PV"."VENDOR_ID") 74 - filter("F"."EMPLOYEE_ID" IS NOT NULL) 75 - access("F"."USER_ID"="AI"."CREATED_BY") 76 - filter((DECODE("HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW_PERSON"("PAP"."PERSON_ID","PAP"."CURRENT_ EMPLOYEE_FLAG","PAP"."CURRENT_NPW_FLAG","PAP"."EMPLOYEE_NUMBER","PAP"."APPLICANT_NUMBER","PAP"."NPW_NUMBER" "PAP"."BUSINESS_GROUP_ID"=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(),'Y',"PAP"."BUSINESS_GROUP_ID","HR_GENERAL 77 - access("F"."EMPLOYEE_ID"="PERSON_ID") 79 - access("AB"."BATCH_ID"=:B1) 81 - access("B"."LOOKUP_TYPE"='INVOICE TYPE' AND "B"."LOOKUP_CODE"=:B1 AND "B"."LANGUAGE"=USERENV('LANG')) filter(("B"."LOOKUP_CODE"=:B1 AND "B"."LANGUAGE"=USERENV('LANG'))) 82 - filter(ROWNUM=1) 87 - access("XAL"."AE_HEADER_ID"=:B1) 88 - access("XAL"."CODE_COMBINATION_ID"="CODE_COMBINATION_ID") 96 - filter(("AI"."INVOICE_CURRENCY_CODE"='CNY' AND "AI"."INVOICE_TYPE_LOOKUP_CODE"<>'EXPENSE REPORT' AND "AI"."INVOICE_TYPE_LOOKUP_CODE"<>'PREPAYMENT')) 97 - access("AI"."ORG_ID"=379) filter("AI"."ORG_ID"=379) 98 - filter("XTE"."ENTITY_CODE"='AP_INVOICES') 99 - access("AI"."SET_OF_BOOKS_ID"="XTE"."LEDGER_ID" AND "AI"."INVOICE_ID"="XTE"."SOURCE_ID_INT_1") 102 - access("XTE"."ENTITY_ID"="XE"."ENTITY_ID") 104 - filter(("XAH"."PERIOD_NAME"='2022-11' AND "XAH"."ACCOUNTING_ENTRY_STATUS_CODE"='F')) 105 - access("XE"."EVENT_ID"="XAH"."EVENT_ID" AND "XAH"."LEDGER_ID"=2026) 106 - access("XET"."APPLICATION_ID"="XE"."APPLICATION_ID" AND "XET"."ENTITY_CODE" LIKE 'AP_INVOICES' AND "XET"."EVENT_CL APPLICATIONS' AND "XET"."EVENT_TYPE_CODE"="XE"."EVENT_TYPE_CODE" AND "XET"."LANGUAGE"='ZHS') filter(("XET"."EVENT_CLASS_CODE"='PREPAYMENT APPLICATIONS' AND "XET"."LANGUAGE"='ZHS' AND "XET"."ENTITY_CODE" LIKE "XET"."EVENT_TYPE_CODE"="XE"."EVENT_TYPE_CODE")) 108 - access("AI"."VENDOR_ID"="PV"."VENDOR_ID") 111 - filter("F"."EMPLOYEE_ID" IS NOT NULL) 112 - access("F"."USER_ID"="AI"."CREATED_BY") 113 - filter((DECODE("HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW_PERSON"("PAP"."PERSON_ID","PAP"."CURRENT_ EMPLOYEE_FLAG","PAP"."CURRENT_NPW_FLAG","PAP"."EMPLOYEE_NUMBER","PAP"."APPLICANT_NUMBER","PAP"."NPW_NUMBER" "PAP"."BUSINESS_GROUP_ID"=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(),'Y',"PAP"."BUSINESS_GROUP_ID","HR_GENERAL 114 - access("F"."EMPLOYEE_ID"="PERSON_ID")
可以通过A-Time看到17s结果已经出来了。
为什么原来的SQL需要跑8小时才能出结果，而加一条 HINT /*+MERGE(T)*/ 17s 就能跑出来，因为这条 HINT 让 from 后面的（内联视图/视图）合并了，CBO 会尝试将内联视图/视图拆开，进行等价的改写，这个过程就叫作视图合并。
更详细的内容可以百度：ORACLE 视图合并，这里就不过多赘述。
想交流 SQL调优、PLSQL、SQL等价改写、JAVA 的朋友可以联系我 18902234014（微信同号），相互学习，共同进步。
