I have a query which is never finishing (left running for over 24 hours and was still going).
Now there isn’t a huge amount of data in each table so I can only assume it is the efficiency of the query I have written.
x
SELECT DISTINCT s.supplier_id FROM supplier_info sINNER JOIN purchase_order_line_all po ON s.supplier_id = po.vendor_noINNER JOIN purchase_req_line_all pr ON s.supplier_id = pr.vendor_noINNER JOIN man_supp_invoice m ON s.supplier_id = m.IDENTITYWHERE 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 Execution Plan
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 |-------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation 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')Advertisement
Answer
You have a bunch of OR conditions. I would suggest replacing these by not exists:
SELECT s.supplier_id FROM supplier_info sWHERE 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 );I’m pretty sure your performance problem is caused by cartesian products. If a supplier has 100 order lines, and 100 req lines, and 100 invoices, then the join is creating 100*100*100 = 1,000,000 rows just for that one supplier. This is a big intermediate table.
By using EXISTS instead, Oracle will not be producing gargantuan intermediate tables.
Also, you can test the performance by adding one clause at a time.
Finally, I’m not 100% sure if the logic is correct for the middle two conditions. For instance, you might really want this for first 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') ) ANDAs written, your logic is that at least one state is not 'Closed' or 'Cancelled', which is what the above revision also does. I put in that no states are 'Closed' or 'Cancelled', just because that made more sense to me.