Oracle Database Discussions

Hi, @user-r4vjd
How can we write this code efficiently in oracle 11g
Don't use cursors unless you really need to. Depending on what you're trying to do, you can do it in a single query. If you really do need to use a cursor, use only one; combine the two cursors into one. Exactly how depends on your data and your requirements. If you'd care to post some sample data (CREATE TABLE and INSERT statements) for all tables involved, the results you want from that sample data, and explanation (in general terms) of how you get those results from that data, and the code behind the other cursor, then I could try to show you exactly how to do it.

Please avoid that cursor details.
Just help in writing code efficiently

Hi,
As Frank said, don't use cursors if you don't need to, especially here as you seem to be interested only in getting a max date.
Combine both queries and retrieve the max dates directly.
By the way, are you sure about this kind of condition:
AND T1_cln =NVL (p_vent, '%')
Did you mean a LIKE operator since you're using '%', or is '%' really stored as is?

There is no purely logical reason why that code should be inefficient, given the correct constraints and indexing there would be an extremely efficient execution path to produce the required result.
It's possible, however, that in your specific case the data model means the relevant constraints are not valid.
Note that the "via = (select from T2)" means the subquery MUST logically be able to return exactly one row. (If it returns more than one then Oracle will raise an error, if it returns no rows you have effectively got a predicate "via = null" which does not evaluate to true (this is not an error, but it might not be what you were expecting).
As the previous two responses have implied - is this query inefficient, or is it simply that the surrounding pl/sql cursor approach means it executes a very large number of times, accumulating a much greater volume of work that seems reasonable.
Regards
Jonathan Lewis

Please avoid that cursor details.
Just help in writing code efficiently
But it is most likely that the cursor IS the source of any inefficiency.
I'm always amazed when people ask for help solving a problem, but try to take the most likely solutions off the table.

We don't have your data, nor have you provided details of the cur1 and cur2 cursors, nor shown the full PL code nor described the logic you are trying to achieve, so that kinda makes it hard for us to help fully.
From what I can gather, you've got cur1 as a main cursor, then cur2 is likely a cursor that opens up inside that cur1 cursor based on some related data from cur1, and then you're querying the MAX value from T1 using some related data in T2 based on both of the cursors.
As others have already said, having cursor looping is more likely to slow things down as you'll be issuing your inner query for each loop of the inner cursor. This is kind of "row by row" or otherwise known as "slow by slow" processing.
If you can, you should consider combining your cursors into a single SQL query, something along the lines of...

SELECT T1.c_cd, T1.d_cd, T1.l_cd, T1.l_sub_1, max(T1.generation_date)
FROM   (<query of cur1>) cur1
       JOIN (<query of cur2) cur2 on (<whatever join conditions related cursor 2 to cursor 1)
       JOIN T1 on (    T1.c_cd = cur1.c_cd
                   AND T1.d_cd = cur1.d_cd
                   AND T1.l_cd = cur1.l_cd
                   AND T1.T1_sub_l = cur2.sub_lat
                  )
       JOIN T2 on (    T2.via   = T1.via               -- equivalent via comparison
                   and T2.l_cd  = T1.l_cd              -- T1.l_cd = cur1.l_cd
                   and T2.sub_l = T1.T1_sub_l          -- T1.T1_sub_l = cur2.sub_lat
                   and T2.cln   = T1.T1_cln            -- T1.T1_cln = NVL(p_vent, '%')
                  )
WHERE  T1.T1_cln = NVL(p_vent, '%')
AND    T2.p_cd   = NVL(p_pri_code, '%')
GROUP BY T1.c_cd, T1.d_cd, T1.l_cd, T1.l_sub_1

Your "via = (select via from T2 where ...) was essentially using many of the same conditions from your cursors as you were using with T1, so it makes more logical sense to just treat it as a join to T2 instead using the values you've already compared with T1.
Then bring in your cursors as the main drivers of the query, starting with cur1, joining that to cur2 on whatever conditions are needed to relate them, and then join T1 to the results of those.
You'll then get results of your MAX for all the T1 records in one go, so you'll need to include the columns from T1 in the select and group by them, so you've got the MAX for each one.
Whether you stick all that in one cursor and process that one by one is up to you... depends what you're doing with those results.