Improving the performance of SQL SQL queries

Question

I have a request that never ends (it remains to work for more than 24 hours and still continues).

Now there is not a lot of data in each table, so I can only assume that this is the efficiency of the query I wrote.

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 identifier):

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

Answer 1

You have a bunch of OR conditions. I would suggest replacing them with 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 am sure your performance problem is caused by Cartesian products. If the supplier has 100 order lines, as well as 100 req lines and 100 invoices, then the connection creates 100 * 100 * 100 = 1,000,000 lines for only one supplier. This is a large staging table.

Instead, instead of EXISTS Oracle will not create giant staging tables.

In addition, you can test performance by adding one sentence at a time.

Finally, I am not 100% sure if the logic is correct for average conditions. For example, you really want this for the 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 makes the above option. I affirm that no state is 'Closed' or 'Cancelled' , simply because it made more sense to me.

Answer 2

It looks like you are working with IFS.

In the PURCHASE_ORDER_LINE_ALL view, the objstate and status columns are defined in DDL as:

  pol.rowstate objstate PURCHASE_ORDER_LINE_API.Finite_State_Decode__(pol.rowstate) state 

I inherited some code that used state in the request and expired from time to time. When I changed the request to use objstate, it ran much faster. This trip through the Finite_State_Decode__ function added a lot of overhead to my query.

A little reference reading about why such functions exist on the system: https://yourifs.blogspot.com/2007/10/read-only-methods-pragma-methods.html