Oracle SQL query efficiency Improvement

Question

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

Do u have an index in any of the columns? if so please mention — Arun Palanisamy
– Arun Palanisamy, Commented Jul 29, 2015 at 14:05
@Crazy2crack Not that I am aware of. How would i check/know? — Matt
– Matt, Commented Jul 29, 2015 at 14:07
Please edit your question and add the execution plan as formatted text, please do not post screenshots — user330315
– user330315, Commented Jul 29, 2015 at 14:07
@a_horse_with_no_name How can i get the execution plan if it never completes? — Matt
– Matt, Commented Jul 29, 2015 at 14:09
Looks like at least some of these are views, with functions and context filters, which is perhaps doing a lot of work you don't need. What does the speed/plan look like if you replace the joins with where exists ... checks? — Alex Poole
– Alex Poole, Commented Jul 29, 2015 at 14:37

Gordon Linoff · Accepted Answer · 2015-07-29 14:38:52Z

4

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.

answered Jul 29, 2015 at 14:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Matt Over a year ago

Oh my god your clever! Currently out of up votes but will tomorrow!

Matt Over a year ago

This runs instantly, cross checking the results now!

user330315 Over a year ago

Hmm, I don't see any cartesian joins in the original query.

Boneist Over a year ago

I think it's the implicit cartesian joins that Gordon is talking about, rather than explicit cartesian joins. If you don't have conditions joining all the tables together in the event of multiple 1-to-many row joins on more than two tables, you're going to end up with multiple rows. E.g. sqlfiddle.com/#!4/9eecb7d/3006

Matt Over a year ago

Just to bring this full circle, results cross checked exactly. If you read this, can you suggest some reading material for using this method (exists over or conditions), i would like to further my knowledge in this (?? implicit/explicit Cartesian joins??)

|

Gene Pool · Accepted Answer · 2019-08-19 19:55:44Z

1

It looks like you are working with IFS.

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

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

I inherited some code that was using state in the query and it was occasionally timing out. When I changed the query to use objstate it ran much quicker. That trip through the Finite_State_Decode__ function was adding a lot of overhead to my query.

A little bit of background reading on why functions like this exist in the system: https://yourifs.blogspot.com/2007/10/read-only-methods-pragma-methods.html

answered Aug 19, 2019 at 19:55

Gene Pool

112 bronze badges

Collectives™ on Stack Overflow

Oracle SQL query efficiency Improvement

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related