Hi DevBob, yes - an API will cause repeated database access so can slow down the execution.You where clause has “where contract =’W’ or contract <> ‘W’. That suggests any value for W is acceptable for the query (this will cause full table reads: everything where contract= “W” + everything where contract is anything else) .Which contract do you want to be selecting for? Only ‘W’?I am going into a meeting for an hour and will have a look at it N
two things:run the whole query without the NVL statements and see if you get a performance improvement. Then add one NVL statement back in and see what happens The where clause in the NVL statements may be causing you some issues. where ( l1p.contract = 'W' and l1f.ms_set = cic.cf$_ms_set or l1p.contract <> 'W' ) In the where clause you are saying where contract =’W’ or contract <> ‘W’? Is that correct?
Try running this segment (from the NVL) by itself and see how long it takes. At a glance it appears that it will do multiple full table traverses. select sum(invoiced_qty) from rega1app.customer_info_cfv cic left join rega1app.cust_ord_invo_stat col on l1p.contract = col.contract and l1p.part_no = col.part_no and cic.customer_id = col.customer_no and ( extract( YEAR from to_date(col.invoice_date)) = extract( YEAR from to_date(l1f.ms_date)) ) and ( extract( MONTH from to_date(col.invoice_date)) = extract( MONTH from to_date(l1f.ms_date)) ) where ( l1p.contract = 'W' and l1f.ms_set = cic.cf$_ms_set or l1p.contract <> 'W' )
Susith - brilliant! Now why didn't I think of that :-)?I will try it and let you know. Thanks a million.
Already have an account? Login
No account yet? Create an account
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.
Sorry, our virus scanner detected that this file isn't safe to download.