2011 年，一位 ITPUB 的网友请求优化如下 SQL。
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)) REMAINFROM ap.AP_INVOICES_ALL A, APPS.AP_UNAPPLY_PREPAYS_V BWHERE 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) ANDNVL( /*: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 ;
该 SQL 要执行 1 个多小时，AP_UNAPPLY_PREPAYS_V 是一个视图，代码如下。
CREATE OR REPLACE VIEW APPS.AP_UNAPPLY_PREPAYS_V ASSELECT 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_NUMBERFROM 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 RSLWHERE AID1.PREPAY_DISTRIBUTION_ID = AID2.INVOICE_DISTRIBUTION_IDAND AI.INVOICE_ID = AID2.INVOICE_IDAND AID1.AMOUNT < 0AND 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_IDAND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_IDAND 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(+);
执行计划如下。
-------------------------------------------------------------------------------------| 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' ANDNVL("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
从执行计划中 Id=5 看到，该 SQL 发生了连接列谓词推入，视图 AP_UNAPPLY_PREPAYS_V 被当作了嵌套循环的被驱动表。原始 SQL 中，两表的关联条件如下。
WHERE A.INVOICE_ID = B.INVOICE_ID(+)
视图中 B.INVOICE_ID 来自于 AID1.INVOICE_ID INVOICE_ID，因此，我们应该检查执行计划中 AID1.INVOICE_ID INVOICE_ID 是否走了索引。我们从执行计划中 Id=18 发现如下。
18 - filter("A"."INVOICE_ID"="AP_INVOICE_DISTRIBUTIONS_ALL"."INVOICE_ID"
这里是将连接列谓词推入到执行计划中 Id=18 进行的过滤操作，并不是将连接列谓词推入视图让表 AP_INVOICE_DISTRIBUTIONS 走 INVOICE_ID 的索引。这显然大错特错了。
因为发生了谓词推入，视图 AP_UNAPPLY_PREPAYS_V 作为嵌套循环被驱动表会被多次扫描。这里的谓词推入的时候只是起的过滤作用，并没有走谓词连接列索引。因此，我们使用 HINT：USE_HASH（A,B），让两表走 HASH 连接，从而避免视图被多次反复扫描。添加 HINT 之后，SQL 能在 1 秒返回结果。
我们也可以调整隐含参数，关闭连接列谓词推入。
ALTER SESSION SET "_push_join_predicate" = FALSE;
禁止连接列谓词推入，也能达到效果。
我们还可以检查表 AP_INVOICE_DISTRIBUTIONS 表的 INVOICE_ID 列是否存在索引，如果没有索引，可以建立一个索引，从而实现真正的连接列谓词推入。但是因为当时使用 USE_HASH 已经优化了 SQL，所以没有继续检查。
最终的 SQL 如下。
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)) REMAINFROM ap.AP_INVOICES_ALL A, APPS.AP_UNAPPLY_PREPAYS_V BWHERE 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) ANDNVL( /*: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 ;
添加 HINT 后的执行计划如下。
-------------------------------------------------------------------------------------| 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 ANDNVL("AP_INVOICE_DISTRIBUTIONS_ALL"."REVERSAL_FLAG",'N')<>'Y' AND"AP_INVOICE_DISTRIBUTIONS_ALL"."LINE_TYPE_LOOKUP_CODE"='PREPAY' ANDNVL("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")
水平高的读者或许有疑问，执行计划 Id=19 是 INDEX FULL SCAN，然后再回表过滤，这里也有性能问题，全表扫描效率应该也比 INDEX FULL SCAN 再回表效率高！是的，我们也发现了这个地方有性能问题，但是既然 SQL 都执行到 1 秒了，也就没继续优化了，千万别得了优化强迫症。
最后，我们再次强调，如果发生了连接列谓词推入，一定要检查执行计划中是否走了谓词被推入的表的连接列索引。
若有收获，就点个赞吧
0 人点赞
