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.
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
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 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
);
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')
) AND
As 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.