Oracle VIEW PUSHED PREDICATE案例分析

最新推荐文章于 2024-05-16 19:07:13 发布

DBhanG

最新推荐文章于 2024-05-16 19:07:13 发布

阅读量879

点赞数

分类专栏： Oracle SQL优化案例与测试文章标签： oracle

本文链接：https://blog.csdn.net/baoyuhang0/article/details/120021166

版权

Oracle SQL优化案例与测试专栏收录该内容

3 篇文章 0 订阅

订阅专栏

SELECT *
  FROM (SELECTA.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 VIEWAPPS.AP_UNAPPLY_PREPAYS_V AS
SELECT AID1.ROWIDROW_ID,
       AID1.INVOICE_ID INVOICE_ID,
       AID1.INVOICE_DISTRIBUTION_IDINVOICE_DISTRIBUTION_ID,
       AID1.PREPAY_DISTRIBUTION_IDPREPAY_DISTRIBUTION_ID,
       AID1.DISTRIBUTION_LINE_NUMBERPREPAY_DIST_NUMBER,
       (-1) * AID1.AMOUNTPREPAY_AMOUNT_APPLIED,
       nvl(AID2.PREPAY_AMOUNT_REMAINING,AID2.AMOUNT) PREPAY_AMOUNT_REMAINING,
       AID1.DIST_CODE_COMBINATION_IDDIST_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_IDPO_DISTRIBUTION_ID,
       AID1.RCV_TRANSACTION_IDRCV_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
WHEREAID1.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

个人分析思路:
1.该语句包含视图，要从执行计划中看是否发生视图合并以及谓词推入。
2.从执行计划id 5可以看出发生了谓词推入，没有发生视图合并。
3.从原SQL语句中找推入的谓词，看是否为连接列推入。
原语句中ap.AP_INVOICES_ALL A, APPS.AP_UNAPPLY_PREPAYS_V B表和视图的连接列为 A.INVOICE_ID = B.INVOICE_ID(+)
4.执行计划谓词过滤信息检索可以看到18行 filter(“A”.“INVOICE_ID”=“AP_INVOICE_DISTRIBUTIONS_ALL”.“INVOICE_ID”
AP_INVOICE_DISTRIBUTIONS_ALL表为视图中的表，也就是说18行发生了连接列谓词推入.
如果没有发生谓词推入那么谓词过滤信息应为 filter(“A”.“INVOICE_ID”=APPS.AP_UNAPPLY_PREPAYS_V.“INVOICE_ID”
在这种情况下问题就非常明显了
作为被驱动表的不能合并的视图，内部多个表关联，这种情况下不可以推，因为本身该视图整体返回结果rows少，但是视图中单表rows很多，这样谓词推入，就会导致视图中的该表被干好多次(驱动表返回行数)
在这里插入图片描述

解决方法：
加入hint走HASH连接

DBhanG

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle VIEW PUSHED PREDICATE案例分析

SELECT * FROM (SELECTA.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
复制链接

扫一扫