提升oracle sql效率,Oracle SQL查询效率提升

我有一个永远不会完成的查询(剩下运行超过24小时但仍在继续).

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

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')