1

I have a function. When I use this function in my query it is always taking a long time like 1 to 2hrs to finish. Here's my first query ...

SELECT GET_DAYSNS_SF(CONTRACT_ID) 
FROM   CONTRACT_TEMP;

and function...

CREATE OR REPLACE FUNCTION GET_DAYSNS_SF
    (CONTRACT_ID IN NUMBER)
    RETURN DATE
AS
    CONTRACT_ID1 NUMBER(16) := CONTRACT_ID;
    DAYSNS DATE;

BEGIN
  BEGIN
    SELECT MIN(EXPECTED_DT)
    INTO DAYSNS
    FROM FLOW
    WHERE REVERSAL_STATUS = 4200
    AND FLOW_TYPE IN(1003,1006,1027)
    AND IS_CASH = 1
    AND AMOUNT > 0
    AND AMOUNT > NVL(AMT_MATCHED,0)
    AND CONTRACT_ID = CONTRACT_ID1;
 EXCEPTION 
    WHEN NO_DATA_FOUND THEN
       DAYSNS := NULL;
  END;
  RETURN DAYSNS;
END;
Mat
  • 202,337
  • 40
  • 393
  • 406
AJ Tibayan
  • 13
  • 1
  • 3
  • 2
    It depends on how many rows there are in FLOW and CONTRACT_TEMP table and what the indexes are in these tables – OracleDev Jan 03 '19 at 09:20
  • 3
    Query optimisation is all about the specific of the query and your environment, so it requires a lot of information. Please read [this post (link) which explains what we need to know](https://stackoverflow.com/a/34975420/146325) before we can help you. – APC Jan 03 '19 at 09:32
  • 2
    Is there are reason you're using a function which has to be called for every row in your table, rather than (outer) joining the tables? You're going to have some overhead just from all the context switching. (Your exception handler isn't doing anything, incidentally, as the function's query cannot get no-data-found - if there are no matches the min will be null anyway. So the sub-block isn't needed. You could also skip the local variable by giving the formal argument a different name so it doesn't clash with the table's column name. Those aren't significant for your performance issue though.) – Alex Poole Jan 03 '19 at 09:53
  • I believe you can remove this line on your query AND AMOUNT > NVL(AMT_MATCHED,0), the AMT_MATCHED does not have a specific value and you are selecting only the min value for the CONTRACT_ID that have been passed on your function. Im suspecting you also need to pass the AMT_MATCHED on the function if its coming from the table CONTRACT_TEMP. – Nick Jan 03 '19 at 10:15
  • @Nick - if `amt_matched` was from `contract_temp` rather than `flow` then the function wouldn't compile and the query wouldn't work at all - it wouldn't just be slow. I'm not sure why you think that comparison can be removed anyway though. – Alex Poole Jan 03 '19 at 11:31

2 Answers2

4

The trivial answer (very probably valid guess) is a missing index on FLOW(CONTRACT_ID).

This leads to a FULL TABLE SCAN in each function call.

Note, that in case the column CONTRACT_ID is not very selective, you may need to add some other column from the predicated used in the query in the function (e.g. REVERSAL_STATUS).

But you may do even better - you need only to left behind the PL/SQL cursor row by row logik and enter the SQL approach.

This will solve the two additional problems that remains after adding the index:

  • PL/SQL context swich is costly in case of large number of function calls

  • You implemented an OUTER JOIN with DIY matter, which is far slower that done by Oracle.

What you need id to get for each contract_id from CONTRACT_TEMP the minimum EXPECTED_DT from FLOW if exists, filtered with the WHERE predicate in the function.

This can be done in two steps:

1) Precalculate for each CONTRACT_ID the minimum EXPECTED_DT - see subquery below. Note, that using GROUP BY you calculates the result for each contract in one step.

2) LEFT OUTER JOIN the table CONTRACT_TEMP to the result of 1)

The query - that is equivalent to yours initial query:

SELECT   f.EXPECTED_DT 
FROM   CONTRACT_TEMP c
LEFT OUTER JOIN 
   (SELECT CONTRACT_ID, MIN(EXPECTED_DT) EXPECTED_DT
    FROM FLOW
    WHERE REVERSAL_STATUS = 4200
    AND FLOW_TYPE IN(1003,1006,1027)
    AND IS_CASH = 1
    AND AMOUNT > 0
    AND AMOUNT > NVL(AMT_MATCHED,0)
    GROUP BY CONTRACT_ID) f
on c.CONTRACT_ID = f.CONTRACT_ID
Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
  • I've got the answer. My condition is incomplete. I added expected_dt <= date in my left outer join. It work – AJ Tibayan Jan 04 '19 at 11:44
0

You can remove the condition AND AMOUNT > 0 as the query already has one more similar condition AMOUNT > NVL(AMT_MATCHED,0)

putnala rohith
  • 1
  • Assuming `amount` and `amt_matched` can't both be negative; or `amount` can't be zero while `amt_matched` is negative... If that assumption it correct then removing that condition is unlikely to have any significant effect on performance anyway though. – Alex Poole Jan 03 '19 at 09:50
  • I believe AMOUNT > NVL(AMT_MATCHED,0) should be the one to be remove, AMT_MATCHED is not an input value. – Nick Jan 03 '19 at 10:16
  • I can't remove that. The amount field is the amount to be paid and the amt_matched is the amount that is already paid. I use this condition before min function. I tried to remove this condition and the query run faster , but i got wrong result from that query. I think that's the problem. Maybe I need to make it like this , amount - nvl(amt_matched) > 0. – AJ Tibayan Jan 04 '19 at 03:04