Oracle SQL查询效率提升

现在每个表中没有大量数据,所以我只能假设它是我编写的查询的效率.

SELECT DISTINCT s.supplier_id 
FROM supplier_info s
INNER JOIN purchase_order_line_all po ON s.supplier_id = po.vendor_no
INNER JOIN purchase_req_line_all pr ON s.supplier_id = pr.vendor_no
INNER JOIN man_supp_invoice m ON s.supplier_id = m.IDENTITY
WHERE s.creation_date >= TRUNC(SYSDATE) - INTERVAL '6' MONTH    
OR po.state NOT IN ('Closed','Cancelled')
OR pr.state NOT IN ('PO Created','Cancelled')
OR m.invoice_date >= TRUNC(SYSDATE) - INTERVAL '18' MONTH

执行计划

Plan hash value: 2195330353

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                           |  1884 |   318K|       |   112K  (1)| 00:30:34 |
|   1 |  HASH UNIQUE                        |                           |  1884 |   318K|  1299M|   112K  (1)| 00:30:34 |
|*  2 |   HASH JOIN                         |                           |  7484K|  1234M|       |  2474   (8)| 00:00:41 |
|   3 |    INDEX FULL SCAN                  | PURCHASE_REQUISITION_PK   | 45348 |   265K|       |    18   (0)| 00:00:01 |
|*  4 |    HASH JOIN RIGHT OUTER            |                           |  7484K|  1191M|       |  2410   (6)| 00:00:40 |
|   5 |     INDEX FULL SCAN                 | PUR_ORD_LINE_EXT_PK       |     1 |    16 |       |     1   (0)| 00:00:01 |
|*  6 |     HASH JOIN                       |                           |  7484K|  1077M|  3160K|  2364   (4)| 00:00:39 |
|   7 |      VIEW                           | index$_join$_013          | 92445 |  2076K|       |   351   (2)| 00:00:06 |
|*  8 |       HASH JOIN                     |                           |       |       |       |            |          |
|*  9 |        HASH JOIN                    |                           |       |       |       |            |          |
|  10 |         INDEX FAST FULL SCAN        | PURCHASE_REQ_LINE2_IX     | 92445 |  2076K|       |    40   (0)| 00:00:01 |
|  11 |         INDEX FAST FULL SCAN        | PURCHASE_REQ_LINE1_IX     | 92445 |  2076K|       |    71   (0)| 00:00:02 |
|  12 |        INDEX FAST FULL SCAN         | PURCHASE_REQ_LINE_PK      | 92445 |  2076K|       |    57   (0)| 00:00:01 |
|* 13 |      HASH JOIN                      |                           |   387K|    47M|  2984K|  1139   (2)| 00:00:19 |
|  14 |       VIEW                          | index$_join$_015          | 92589 |  1898K|       |   184   (2)| 00:00:04 |
|* 15 |        HASH JOIN                    |                           |       |       |       |            |          |
|  16 |         INDEX FAST FULL SCAN        | PURCHASE_ORDER_LINE_PK    | 92589 |  1898K|       |    57   (0)| 00:00:01 |
|  17 |         INDEX FAST FULL SCAN        | PURCHASE_ORDER_LINE_1_IX  | 92589 |  1898K|       |    64   (2)| 00:00:02 |
|* 18 |       HASH JOIN                     |                           |   172K|    17M|  1008K|   619   (2)| 00:00:11 |
|  19 |        VIEW                         | index$_join$_016          | 41115 |   521K|       |    58   (2)| 00:00:01 |
|* 20 |         HASH JOIN                   |                           |       |       |       |            |          |
|  21 |          INDEX FAST FULL SCAN       | PURCHASE_ORDER2_IX        | 41115 |   521K|       |    17   (0)| 00:00:01 |
|  22 |          INDEX FAST FULL SCAN       | PURCHASE_ORDER_PK         | 41115 |   521K|       |    13   (0)| 00:00:01 |
|* 23 |        HASH JOIN                    |                           | 13700 |  1257K|       |   523   (1)| 00:00:09 |
|  24 |         TABLE ACCESS FULL           | SUPPLIER_INFO_TAB         |  3269 | 45766 |       |    10   (0)| 00:00:01 |
|  25 |         NESTED LOOPS                |                           |       |       |       |            |          |
|  26 |          NESTED LOOPS               |                           | 23568 |  1841K|       |   512   (1)| 00:00:09 |
|  27 |           SORT UNIQUE               |                           |     4 |    76 |       |     1   (0)| 00:00:01 |
|* 28 |            INDEX RANGE SCAN         | USER_PROFILE_ENTRY_SYS_PK |     4 |    76 |       |     1   (0)| 00:00:01 |
|* 29 |           INDEX RANGE SCAN          | INVOICE_IND9              | 15928 |       |       |     6   (0)| 00:00:01 |
|* 30 |          TABLE ACCESS BY INDEX ROWID| INVOICE_TAB               |  6246 |   372K|       |   255   (0)| 00:00:05 |
-------------------------------------------------------------------------------------------------------------------------

谓词信息(由操作ID标识)：

2 - access("A"."REQUISITION_NO"="B"."REQUISITION_NO")
   4 - access("POL"."ORDER_NO"="POLET"."ORDER_NO"(+) AND "POL"."LINE_NO"="POLET"."LINE_NO"(+) AND 
              "POL"."RELEASE_NO"="POLET"."RELEASE_NO"(+) AND "POL"."ORDER_NO"="POLET"."ORDER_NO"(+))
   6 - access("SUPPLIER_ID"="A"."VENDOR_NO")
       filter("CREATION_DATE">=TRUNC(SYSDATE@!)-INTERVAL'+00-06' YEAR(2) TO MONTH OR 
              "PURCHASE_ORDER_LINE_API"."FINITE_STATE_DECODE__"("POL"."ROWSTATE")<>'Closed' AND 
              "PURCHASE_ORDER_LINE_API"."FINITE_STATE_DECODE__"("POL"."ROWSTATE")<>'Cancelled' OR 
              "PURCHASE_REQ_LINE_API"."FINITE_STATE_DECODE__"("A"."ROWSTATE")<>'PO Created' AND 
              "PURCHASE_REQ_LINE_API"."FINITE_STATE_DECODE__"("A"."ROWSTATE")<>'Cancelled' OR 
              "I"."INVOICE_DATE">=TRUNC(SYSDATE@!)-INTERVAL'+01-06' YEAR(2) TO MONTH)
   8 - access(ROWID=ROWID)
   9 - access(ROWID=ROWID)
  13 - access("POL"."ORDER_NO"="PO"."ORDER_NO")
  15 - access(ROWID=ROWID)
  18 - access("SUPPLIER_ID"="PO"."VENDOR_NO")
  20 - access(ROWID=ROWID)
  23 - access("SUPPLIER_ID"="I"."IDENTITY")
  28 - access("USER_NAME"=NVL(RTRIM(SUBSTR(USERENV('CLIENT_INFO'),1,30)),USER@!) AND "ENTRY_CODE"='COMPANY')
  29 - access("I"."COMPANY"="ENTRY_VALUE")
  30 - filter("I"."CREATOR"='MAN_SUPP_INVOICE_API' AND "I"."PARTY_TYPE"='SUPPLIER' AND 
              "I"."ROWSTATE"<>'Cancelled')

SELECT s.supplier_id 
FROM supplier_info s
WHERE s.creation_date >= TRUNC(SYSDATE) - INTERVAL '6' MONTH OR
      NOT EXISTS (SELECT 1
                  FROM purchase_order_line_all po
                  WHERE s.supplier_id = po.vendor_no AND
                        po.state IN ('Closed','Cancelled')
                 ) AND
      NOT EXISTS (SELECT 1
                  FROM purchase_req_line_all pr 
                  WHERE s.supplier_id = pr.vendor_no AND
                        r.state IN ('PO Created','Cancelled')
                 )
      EXISTS (SELECT 1
              FROM man_supp_invoice m 
              WHERE s.supplier_id = m.IDENTITY AND
                    m.invoice_date >= TRUNC(SYSDATE) - INTERVAL '18' MONTH  
             );

我很确定你的性能问题是由笛卡尔产品引起的.如果供应商有100个订单行,100个请求行和100个发票,则该联接仅为该供应商创建100 * 100 * 100 = 1,000,000行.这是一个很大的中间表.

通过使用EXISTS,Oracle将不会生成庞大的中间表.

此外,您可以通过一次添加一个子句来测试性能.

最后,我不能100％确定中间两个条件的逻辑是否正确.例如,您可能真的想要第一个NOT EXISTS：

EXISTS (SELECT 1
                  FROM purchase_order_line_all po
                  WHERE s.supplier_id = po.vendor_no AND
                        po.state NOT IN ('Closed','Cancelled')
                 ) AND

如上所述,您的逻辑是至少有一个状态不是“已关闭”或“已取消”,这也是上述修订版所做的.我认为没有任何州是“封闭”或“取消”,只是因为这对我来说更有意义.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!