当前位置: 代码迷 >> 综合 >> VIEW PUSHED PREDICATE(谓词推入)引发的惨剧
  详细解决方案

VIEW PUSHED PREDICATE(谓词推入)引发的惨剧

热度:97   发布时间:2023-12-14 21:21:14.0

帮网友调SQL

http://www.itpub.net/forum.php?mod=viewthread&tid=1492997&extra=pageD1%3D&page=1

原SQL如下(要跑1个多小时):

SELECT *
FROM (SELECT  A.INVOICE_ID,
A.VENDOR_ID,
A.INVOICE_NUM,
A.INVOICE_AMOUNT,
A.GL_DATE,
A.INVOICE_CURRENCY_CODE,
SUM(NVL(B.PREPAY_AMOUNT_APPLIED, 0)) PAID_AMOUNT,
A.INVOICE_AMOUNT - SUM(NVL(B.PREPAY_AMOUNT_APPLIED, 0)) REMAIN
FROM ap.AP_INVOICES_ALL A, APPS.AP_UNAPPLY_PREPAYS_V B
WHERE A.INVOICE_ID = B.INVOICE_ID(+)
AND A.ORG_ID = 126 /*:B4*/
AND A.SOURCE = 'OSM IMPORTED' /*:B3*/
AND A.INVOICE_NUM BETWEEN NVL( /*:B2*/ null, A.INVOICE_NUM) AND
NVL( /*:B1*/ null, A.INVOICE_NUM)
GROUP BY A.INVOICE_ID,
A.INVOICE_NUM,
A.INVOICE_AMOUNT,
A.VENDOR_ID,
A.GL_DATE,
A.INVOICE_CURRENCY_CODE)
WHERE REMAIN > 0 ;

B是一个视图,定义如下:

CREATE OR REPLACE VIEW APPS.AP_UNAPPLY_PREPAYS_V AS
SELECT AID1.ROWID ROW_ID,
AID1.INVOICE_ID INVOICE_ID,
AID1.INVOICE_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID,
AID1.PREPAY_DISTRIBUTION_ID PREPAY_DISTRIBUTION_ID,
AID1.DISTRIBUTION_LINE_NUMBER PREPAY_DIST_NUMBER,
(-1) * AID1.AMOUNT PREPAY_AMOUNT_APPLIED,
nvl(AID2.PREPAY_AMOUNT_REMAINING, AID2.AMOUNT) PREPAY_AMOUNT_REMAINING,
AID1.DIST_CODE_COMBINATION_ID DIST_CODE_COMBINATION_ID,
AID1.ACCOUNTING_DATE ACCOUNTING_DATE,
AID1.PERIOD_NAME PERIOD_NAME,
AID1.SET_OF_BOOKS_ID SET_OF_BOOKS_ID,
AID1.DESCRIPTION DESCRIPTION,
AID1.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID,
AID1.RCV_TRANSACTION_ID RCV_TRANSACTION_ID,
AID1.ORG_ID ORG_ID,
AI.INVOICE_NUM PREPAY_NUMBER,
AI.VENDOR_ID VENDOR_ID,
AI.VENDOR_SITE_ID VENDOR_SITE_ID,
ATC.TAX_ID TAX_ID,
ATC.NAME TAX_CODE,
PH.SEGMENT1 PO_NUMBER,
PV.VENDOR_NAME VENDOR_NAME,
PV.SEGMENT1 VENDOR_NUMBER,
PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE,
RSH.RECEIPT_NUM RECEIPT_NUMBER
FROM AP_INVOICES              AI,
AP_INVOICE_DISTRIBUTIONS AID1,
AP_INVOICE_DISTRIBUTIONS AID2,
AP_TAX_CODES             ATC,
PO_VENDORS               PV,
PO_VENDOR_SITES          PVS,
PO_DISTRIBUTIONS         PD,
PO_HEADERS               PH,
PO_LINES                 PL,
PO_LINE_LOCATIONS        PLL,
RCV_TRANSACTIONS         RTXNS,
RCV_SHIPMENT_HEADERS     RSH,
RCV_SHIPMENT_LINES       RSL
WHERE AID1.PREPAY_DISTRIBUTION_ID = AID2.INVOICE_DISTRIBUTION_ID
AND AI.INVOICE_ID = AID2.INVOICE_ID
AND AID1.AMOUNT < 0
AND nvl(AID1.REVERSAL_FLAG, 'N') != 'Y'
AND AID1.TAX_CODE_ID = ATC.TAX_ID(+)
AND AID1.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
AND AI.VENDOR_ID = PV.VENDOR_ID
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND AID1.PO_DISTRIBUTION_ID = PD.PO_DISTRIBUTION_ID(+)
AND PD.PO_HEADER_ID = PH.PO_HEADER_ID(+)
AND PD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID(+)
AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)
AND AID1.RCV_TRANSACTION_ID = RTXNS.TRANSACTION_ID(+)
AND RTXNS.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID(+)
AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID(+);


执行计划如下:

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   |  Name                         | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                               |     1 |    69 |   722 |
|*  1 |  FILTER                                     |                               |       |       |       |
|   2 |   SORT GROUP BY                             |                               |     1 |    69 |   722 |
|   3 |    NESTED LOOPS OUTER                       |                               |     3 |   207 |   697 |
|*  4 |     TABLE ACCESS FULL                       | AP_INVOICES_ALL               |     3 |   153 |   694 |
|   5 |     VIEW PUSHED PREDICATE                   | AP_UNAPPLY_PREPAYS_V          |     1 |    18 |     1 |
|   6 |      NESTED LOOPS                           |                               |     1 |   372 |     3 |
|   7 |       NESTED LOOPS                          |                               |     1 |   368 |     3 |
|   8 |        NESTED LOOPS                         |                               |     1 |   361 |     2 |
|   9 |         NESTED LOOPS                        |                               |     1 |   347 |     1 |
|  10 |          NESTED LOOPS OUTER                 |                               |     1 |   334 |     1 |
|  11 |           NESTED LOOPS OUTER                |                               |     1 |   321 |     1 |
|  12 |            NESTED LOOPS OUTER               |                               |     1 |   295 |     1 |
|  13 |             NESTED LOOPS OUTER              |                               |     1 |   269 |     1 |
|  14 |              NESTED LOOPS OUTER             |                               |     1 |   243 |     1 |
|  15 |               NESTED LOOPS OUTER            |                               |     1 |   197 |     1 |
|  16 |                NESTED LOOPS OUTER           |                               |     1 |   157 |     1 |
|  17 |                 NESTED LOOPS OUTER          |                               |     1 |    98 |     1 |
|* 18 |                  TABLE ACCESS BY INDEX ROWID| AP_INVOICE_DISTRIBUTIONS_ALL  |     1 |    72 |     1 |
|* 19 |                   INDEX FULL SCAN           | AP_INVOICE_DISTRIBUTIONS_N20  |     1 |       |       |
|* 20 |                  TABLE ACCESS BY INDEX ROWID| AP_TAX_CODES_ALL              |     1 |    26 |       |
|* 21 |                   INDEX UNIQUE SCAN         | AP_TAX_CODES_U1               |     1 |       |       |
|* 22 |                 TABLE ACCESS BY INDEX ROWID | PO_DISTRIBUTIONS_ALL          |     1 |    59 |       |
|* 23 |                  INDEX UNIQUE SCAN          | PO_DISTRIBUTIONS_U1           |     1 |       |       |
|* 24 |                TABLE ACCESS BY INDEX ROWID  | PO_HEADERS_ALL                |     1 |    40 |       |
|* 25 |                 INDEX UNIQUE SCAN           | PO_HEADERS_U1                 |     1 |       |       |
|* 26 |               TABLE ACCESS BY INDEX ROWID   | PO_LINE_LOCATIONS_ALL         |     1 |    46 |       |
|* 27 |                INDEX UNIQUE SCAN            | PO_LINE_LOCATIONS_U1          |     1 |       |       |
|* 28 |              TABLE ACCESS BY INDEX ROWID    | PO_LINES_ALL                  |     1 |    26 |       |
|* 29 |               INDEX UNIQUE SCAN             | PO_LINES_U1                   |     1 |       |       |
|  30 |             TABLE ACCESS BY INDEX ROWID     | RCV_TRANSACTIONS              |     1 |    26 |       |
|* 31 |              INDEX UNIQUE SCAN              | RCV_TRANSACTIONS_U1           |     1 |       |       |
|  32 |            TABLE ACCESS BY INDEX ROWID      | RCV_SHIPMENT_LINES            |     1 |    26 |       |
|* 33 |             INDEX UNIQUE SCAN               | RCV_SHIPMENT_LINES_U1         |     1 |       |       |
|* 34 |           INDEX UNIQUE SCAN                 | RCV_SHIPMENT_HEADERS_U1       |     1 |    13 |       |
|* 35 |          TABLE ACCESS BY INDEX ROWID        | AP_INVOICE_DISTRIBUTIONS_ALL  |     1 |    13 |       |
|* 36 |           INDEX UNIQUE SCAN                 | AP_INVOICE_DISTRIBUTIONS_U2   |     1 |       |       |
|* 37 |         TABLE ACCESS BY INDEX ROWID         | AP_INVOICES_ALL               |     1 |    14 |     1 |
|* 38 |          INDEX UNIQUE SCAN                  | AP_INVOICES_U1                |     1 |       |       |
|* 39 |        TABLE ACCESS BY INDEX ROWID          | PO_VENDOR_SITES_ALL           |     1 |     7 |     1 |
|* 40 |         INDEX UNIQUE SCAN                   | PO_VENDOR_SITES_U1            |     1 |       |       |
|* 41 |       INDEX UNIQUE SCAN                     | PO_VENDORS_U1                 |     1 |     4 |       |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."INVOICE_AMOUNT"-SUM(NVL("B"."PREPAY_AMOUNT_APPLIED",0))>0)
4 - filter("A"."ORG_ID"=126 AND "A"."SOURCE"='OSM IMPORTED' AND
"A"."INVOICE_NUM">=NVL(NULL,"A"."INVOICE_NUM") AND "A"."INVOICE_NUM"<=NVL(NULL,"A"."INVOICE_NUM"))
18 - filter("A"."INVOICE_ID"="AP_INVOICE_DISTRIBUTIONS_ALL"."INVOICE_ID" AND
"AP_INVOICE_DISTRIBUTIONS_ALL"."AMOUNT"<0 AND NVL("AP_INVOICE_DISTRIBUTIONS_ALL"."REVERSAL_FLAG",'N')<>'Y'
AND "AP_INVOICE_DISTRIBUTIONS_ALL"."LINE_TYPE_LOOKUP_CODE"='PREPAY' AND
NVL("AP_INVOICE_DISTRIBUTIONS_ALL"."ORG_ID",NVL(TO_NUMBER(DECODE(SUBSTRB(:B1,1,1),'
',NULL,SUBSTRB(:B2,1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(:B3,1,1),' ',NULL,SUBSTRB(:B4,1,10))),(-99)))
19 - filter("AP_INVOICE_DISTRIBUTIONS_ALL"."PREPAY_DISTRIBUTION_ID" IS NOT NULL)
20 - filter(NVL("AP_TAX_CODES_ALL"."ORG_ID"(+),NVL(TO_NUMBER(DECODE(SUBSTRB(:B1,1,1),'
',NULL,SUBSTRB(:B2,1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(:B3,1,1),' ',NULL,SUBSTRB(:B4,1,10))),(-99)))
21 - access("AP_INVOICE_DISTRIBUTIONS_ALL"."TAX_CODE_ID"="AP_TAX_CODES_ALL"."TAX_ID"(+))
22 - filter(NVL("PO_DISTRIBUTIONS_ALL"."ORG_ID"(+),NVL(TO_NUMBER(DECODE(SUBSTRB(:B1,1,1),'
',NULL,SUBSTRB(:B2,1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(:B3,1,1),' ',NULL,SUBSTRB(:B4,1,10))),(-99)))
23 - access("AP_INVOICE_DISTRIBUTIONS_ALL"."PO_DISTRIBUTION_ID"="PO_DISTRIBUTIONS_ALL"."PO_DISTRIBUTION_ID"
(+))
24 - filter(NVL("PO_HEADERS_ALL"."ORG_ID"(+),NVL(TO_NUMBER(DECODE(SUBSTRB(:B1,1,1),'
',NULL,SUBSTRB(:B2,1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(:B3,1,1),' ',NULL,SUBSTRB(:B4,1,10))),(-99)))
25 - access("PO_DISTRIBUTIONS_ALL"."PO_HEADER_ID"="PO_HEADERS_ALL"."PO_HEADER_ID"(+))
26 - filter(NVL("PO_LINE_LOCATIONS_ALL"."ORG_ID"(+),NVL(TO_NUMBER(DECODE(SUBSTRB(:B1,1,1),'
',NULL,SUBSTRB(:B2,1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(:B3,1,1),' ',NULL,SUBSTRB(:B4,1,10))),(-99)))
27 - access("PO_DISTRIBUTIONS_ALL"."LINE_LOCATION_ID"="PO_LINE_LOCATIONS_ALL"."LINE_LOCATION_ID"(+))
28 - filter(NVL("PO_LINES_ALL"."ORG_ID"(+),NVL(TO_NUMBER(DECODE(SUBSTRB(:B1,1,1),'
',NULL,SUBSTRB(:B2,1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(:B3,1,1),' ',NULL,SUBSTRB(:B4,1,10))),(-99)))
29 - access("PO_LINE_LOCATIONS_ALL"."PO_LINE_ID"="PO_LINES_ALL"."PO_LINE_ID"(+))
31 - access("AP_INVOICE_DISTRIBUTIONS_ALL"."RCV_TRANSACTION_ID"="RTXNS"."TRANSACTION_ID"(+))
33 - access("RTXNS"."SHIPMENT_LINE_ID"="RSL"."SHIPMENT_LINE_ID"(+))
34 - access("RSL"."SHIPMENT_HEADER_ID"="RSH"."SHIPMENT_HEADER_ID"(+))
35 - filter(NVL("AP_INVOICE_DISTRIBUTIONS_ALL"."ORG_ID",NVL(TO_NUMBER(DECODE(SUBSTRB(:B1,1,1),'
',NULL,SUBSTRB(:B2,1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(:B3,1,1),' ',NULL,SUBSTRB(:B4,1,10))),(-99)))
36 - access("AP_INVOICE_DISTRIBUTIONS_ALL"."PREPAY_DISTRIBUTION_ID"="AP_INVOICE_DISTRIBUTIONS_ALL"."INVOICE
_DISTRIBUTION_ID")
37 - filter(NVL("AP_INVOICES_ALL"."ORG_ID",NVL(TO_NUMBER(DECODE(SUBSTRB(:B1,1,1),'
',NULL,SUBSTRB(:B2,1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(:B3,1,1),' ',NULL,SUBSTRB(:B4,1,10))),(-99)))
38 - access("AP_INVOICES_ALL"."INVOICE_ID"="AP_INVOICE_DISTRIBUTIONS_ALL"."INVOICE_ID")
39 - filter(NVL("PO_VENDOR_SITES_ALL"."ORG_ID",NVL(TO_NUMBER(DECODE(SUBSTRB(:B1,1,1),'
',NULL,SUBSTRB(:B2,1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(:B3,1,1),' ',NULL,SUBSTRB(:B4,1,10))),(-99)))
40 - access("AP_INVOICES_ALL"."VENDOR_SITE_ID"="PO_VENDOR_SITES_ALL"."VENDOR_SITE_ID")
41 - access("AP_INVOICES_ALL"."VENDOR_ID"="PV"."VENDOR_ID")
Note: cpu costing is off
92 rows selected.


看到 Note: cpu costing is off 我就知道DB 是9i

这个SQL语句要优化很简单,SQL调优做得太多了,看到index unique scan我就忽略它,因为它不会发生性能问题(相信我,遇到index unique scan你不要看它)

执行计划中引起我注意的地方有3个

1.VIEW PUSHED PREDICATE  表明它进行了谓词推入

18行,19行

从哪里看到有谓词推入呢? 请注意观察18行 A"."INVOICE_ID"="AP_INVOICE_DISTRIBUTIONS_ALL 这个就把A的过滤条件给推入了

谓词推入是双刃剑,但是我看到的更多的案例是谓词推入反而引发性能问题,问题的原因在于谓词推入后CBO计算基数就 大大减小了。

可以用hint use_hash 强制CBO走hash join,这样CBO就不能进行谓词推入了

SELECT *
FROM (SELECT /*+ use_hash(a,b) */ A.INVOICE_ID,
A.VENDOR_ID,
A.INVOICE_NUM,
A.INVOICE_AMOUNT,
A.GL_DATE,
A.INVOICE_CURRENCY_CODE,
SUM(NVL(B.PREPAY_AMOUNT_APPLIED, 0)) PAID_AMOUNT,
A.INVOICE_AMOUNT - SUM(NVL(B.PREPAY_AMOUNT_APPLIED, 0)) REMAIN
FROM ap.AP_INVOICES_ALL A, APPS.AP_UNAPPLY_PREPAYS_V B
WHERE A.INVOICE_ID = B.INVOICE_ID(+)
AND A.ORG_ID = 126 /*:B4*/
AND A.SOURCE = 'OSM IMPORTED' /*:B3*/
AND A.INVOICE_NUM BETWEEN NVL( /*:B2*/ null, A.INVOICE_NUM) AND
NVL( /*:B1*/ null, A.INVOICE_NUM)
GROUP BY A.INVOICE_ID,
A.INVOICE_NUM,
A.INVOICE_AMOUNT,
A.VENDOR_ID,
A.GL_DATE,
A.INVOICE_CURRENCY_CODE)
WHERE REMAIN > 0 ;


也可以 ALTER SESSION SET "_push_join_predicate" = FALSE;  效果一样,最终的执行计划如下:

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   |  Name                         | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                               |     1 |    69 |   723 |
|*  1 |  FILTER                                     |                               |       |       |       |
|   2 |   SORT GROUP BY                             |                               |     1 |    69 |   723 |
|*  3 |    HASH JOIN OUTER                          |                               |     3 |   207 |   698 |
|*  4 |     TABLE ACCESS FULL                       | AP_INVOICES_ALL               |     3 |   153 |   694 |
|   5 |     VIEW                                    | AP_UNAPPLY_PREPAYS_V          |     1 |    18 |     3 |
|   6 |      NESTED LOOPS                           |                               |     1 |   372 |     3 |
|   7 |       NESTED LOOPS                          |                               |     1 |   368 |     3 |
|   8 |        NESTED LOOPS                         |                               |     1 |   361 |     2 |
|   9 |         NESTED LOOPS                        |                               |     1 |   347 |     1 |
|  10 |          NESTED LOOPS OUTER                 |                               |     1 |   334 |     1 |
|  11 |           NESTED LOOPS OUTER                |                               |     1 |   321 |     1 |
|  12 |            NESTED LOOPS OUTER               |                               |     1 |   295 |     1 |
|  13 |             NESTED LOOPS OUTER              |                               |     1 |   269 |     1 |
|  14 |              NESTED LOOPS OUTER             |                               |     1 |   243 |     1 |
|  15 |               NESTED LOOPS OUTER            |                               |     1 |   197 |     1 |
|  16 |                NESTED LOOPS OUTER           |                               |     1 |   157 |     1 |
|  17 |                 NESTED LOOPS OUTER          |                               |     1 |    98 |     1 |
|* 18 |                  TABLE ACCESS BY INDEX ROWID| AP_INVOICE_DISTRIBUTIONS_ALL  |     1 |    72 |     1 |
|* 19 |                   INDEX FULL SCAN           | AP_INVOICE_DISTRIBUTIONS_N20  |     1 |       |       |
|* 20 |                  TABLE ACCESS BY INDEX ROWID| AP_TAX_CODES_ALL              |     1 |    26 |       |
|* 21 |                   INDEX UNIQUE SCAN         | AP_TAX_CODES_U1               |     1 |       |       |
|* 22 |                 TABLE ACCESS BY INDEX ROWID | PO_DISTRIBUTIONS_ALL          |     1 |    59 |       |
|* 23 |                  INDEX UNIQUE SCAN          | PO_DISTRIBUTIONS_U1           |     1 |       |       |
|* 24 |                TABLE ACCESS BY INDEX ROWID  | PO_HEADERS_ALL                |     1 |    40 |       |
|* 25 |                 INDEX UNIQUE SCAN           | PO_HEADERS_U1                 |     1 |       |       |
|* 26 |               TABLE ACCESS BY INDEX ROWID   | PO_LINE_LOCATIONS_ALL         |     1 |    46 |       |
|* 27 |                INDEX UNIQUE SCAN            | PO_LINE_LOCATIONS_U1          |     1 |       |       |
|* 28 |              TABLE ACCESS BY INDEX ROWID    | PO_LINES_ALL                  |     1 |    26 |       |
|* 29 |               INDEX UNIQUE SCAN             | PO_LINES_U1                   |     1 |       |       |
|  30 |             TABLE ACCESS BY INDEX ROWID     | RCV_TRANSACTIONS              |     1 |    26 |       |
|* 31 |              INDEX UNIQUE SCAN              | RCV_TRANSACTIONS_U1           |     1 |       |       |
|  32 |            TABLE ACCESS BY INDEX ROWID      | RCV_SHIPMENT_LINES            |     1 |    26 |       |
|* 33 |             INDEX UNIQUE SCAN               | RCV_SHIPMENT_LINES_U1         |     1 |       |       |
|* 34 |           INDEX UNIQUE SCAN                 | RCV_SHIPMENT_HEADERS_U1       |     1 |    13 |       |
|* 35 |          TABLE ACCESS BY INDEX ROWID        | AP_INVOICE_DISTRIBUTIONS_ALL  |     1 |    13 |       |
|* 36 |           INDEX UNIQUE SCAN                 | AP_INVOICE_DISTRIBUTIONS_U2   |     1 |       |       |
|* 37 |         TABLE ACCESS BY INDEX ROWID         | AP_INVOICES_ALL               |     1 |    14 |     1 |
|* 38 |          INDEX UNIQUE SCAN                  | AP_INVOICES_U1                |     1 |       |       |
|* 39 |        TABLE ACCESS BY INDEX ROWID          | PO_VENDOR_SITES_ALL           |     1 |     7 |     1 |
|* 40 |         INDEX UNIQUE SCAN                   | PO_VENDOR_SITES_U1            |     1 |       |       |
|* 41 |       INDEX UNIQUE SCAN                     | PO_VENDORS_U1                 |     1 |     4 |       |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."INVOICE_AMOUNT"-SUM(NVL("B"."PREPAY_AMOUNT_APPLIED",0))>0)
3 - access("A"."INVOICE_ID"="B"."INVOICE_ID"(+))
4 - filter("A"."ORG_ID"=126 AND "A"."SOURCE"='OSM IMPORTED' AND
"A"."INVOICE_NUM">=NVL(NULL,"A"."INVOICE_NUM") AND "A"."INVOICE_NUM"<=NVL(NULL,"A"."INVOICE_NUM"))
18 - filter("AP_INVOICE_DISTRIBUTIONS_ALL"."AMOUNT"<0 AND
NVL("AP_INVOICE_DISTRIBUTIONS_ALL"."REVERSAL_FLAG",'N')<>'Y' AND
"AP_INVOICE_DISTRIBUTIONS_ALL"."LINE_TYPE_LOOKUP_CODE"='PREPAY' AND
NVL("AP_INVOICE_DISTRIBUTIONS_ALL"."ORG_ID",NVL(TO_NUMBER(DECODE(SUBSTRB(:B1,1,1),'
',NULL,SUBSTRB(:B2,1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(:B3,1,1),' ',NULL,SUBSTRB(:B4,1,10))),(-99)))
19 - filter("AP_INVOICE_DISTRIBUTIONS_ALL"."PREPAY_DISTRIBUTION_ID" IS NOT NULL)
20 - filter(NVL("AP_TAX_CODES_ALL"."ORG_ID"(+),NVL(TO_NUMBER(DECODE(SUBSTRB(:B1,1,1),'
',NULL,SUBSTRB(:B2,1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(:B3,1,1),' ',NULL,SUBSTRB(:B4,1,10))),(-99)))
21 - access("AP_INVOICE_DISTRIBUTIONS_ALL"."TAX_CODE_ID"="AP_TAX_CODES_ALL"."TAX_ID"(+))
22 - filter(NVL("PO_DISTRIBUTIONS_ALL"."ORG_ID"(+),NVL(TO_NUMBER(DECODE(SUBSTRB(:B1,1,1),'
',NULL,SUBSTRB(:B2,1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(:B3,1,1),' ',NULL,SUBSTRB(:B4,1,10))),(-99)))
23 - access("AP_INVOICE_DISTRIBUTIONS_ALL"."PO_DISTRIBUTION_ID"="PO_DISTRIBUTIONS_ALL"."PO_DISTRIBUTION_ID"
(+))
24 - filter(NVL("PO_HEADERS_ALL"."ORG_ID"(+),NVL(TO_NUMBER(DECODE(SUBSTRB(:B1,1,1),'
',NULL,SUBSTRB(:B2,1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(:B3,1,1),' ',NULL,SUBSTRB(:B4,1,10))),(-99)))
25 - access("PO_DISTRIBUTIONS_ALL"."PO_HEADER_ID"="PO_HEADERS_ALL"."PO_HEADER_ID"(+))
26 - filter(NVL("PO_LINE_LOCATIONS_ALL"."ORG_ID"(+),NVL(TO_NUMBER(DECODE(SUBSTRB(:B1,1,1),'
',NULL,SUBSTRB(:B2,1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(:B3,1,1),' ',NULL,SUBSTRB(:B4,1,10))),(-99)))
27 - access("PO_DISTRIBUTIONS_ALL"."LINE_LOCATION_ID"="PO_LINE_LOCATIONS_ALL"."LINE_LOCATION_ID"(+))
28 - filter(NVL("PO_LINES_ALL"."ORG_ID"(+),NVL(TO_NUMBER(DECODE(SUBSTRB(:B1,1,1),'
',NULL,SUBSTRB(:B2,1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(:B3,1,1),' ',NULL,SUBSTRB(:B4,1,10))),(-99)))
29 - access("PO_LINE_LOCATIONS_ALL"."PO_LINE_ID"="PO_LINES_ALL"."PO_LINE_ID"(+))
31 - access("AP_INVOICE_DISTRIBUTIONS_ALL"."RCV_TRANSACTION_ID"="RTXNS"."TRANSACTION_ID"(+))
33 - access("RTXNS"."SHIPMENT_LINE_ID"="RSL"."SHIPMENT_LINE_ID"(+))
34 - access("RSL"."SHIPMENT_HEADER_ID"="RSH"."SHIPMENT_HEADER_ID"(+))
35 - filter(NVL("AP_INVOICE_DISTRIBUTIONS_ALL"."ORG_ID",NVL(TO_NUMBER(DECODE(SUBSTRB(:B1,1,1),'
',NULL,SUBSTRB(:B2,1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(:B3,1,1),' ',NULL,SUBSTRB(:B4,1,10))),(-99)))
36 - access("AP_INVOICE_DISTRIBUTIONS_ALL"."PREPAY_DISTRIBUTION_ID"="AP_INVOICE_DISTRIBUTIONS_ALL"."INVOICE
_DISTRIBUTION_ID")
37 - filter(NVL("AP_INVOICES_ALL"."ORG_ID",NVL(TO_NUMBER(DECODE(SUBSTRB(:B1,1,1),'
',NULL,SUBSTRB(:B2,1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(:B3,1,1),' ',NULL,SUBSTRB(:B4,1,10))),(-99)))
38 - access("AP_INVOICES_ALL"."INVOICE_ID"="AP_INVOICE_DISTRIBUTIONS_ALL"."INVOICE_ID")
39 - filter(NVL("PO_VENDOR_SITES_ALL"."ORG_ID",NVL(TO_NUMBER(DECODE(SUBSTRB(:B1,1,1),'
',NULL,SUBSTRB(:B2,1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(:B3,1,1),' ',NULL,SUBSTRB(:B4,1,10))),(-99)))
40 - access("AP_INVOICES_ALL"."VENDOR_SITE_ID"="PO_VENDOR_SITES_ALL"."VENDOR_SITE_ID")
41 - access("AP_INVOICES_ALL"."VENDOR_ID"="PV"."VENDOR_ID")
Note: cpu costing is off
93 rows selected.


这个SQL之所以跑很慢,是因为18行谓词推入,而它又是外部nested loop的驱动行源,这样导致过滤AP_INVOICE_DISTRIBUTIONS_ALL表灰常多次(由于无法连接他DB,无法计算过滤多少次)

  相关解决方案