Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 159 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 471 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
How to write code efficiently

cur1 and cur2 - cursor value
p_vent - In parameter of procedure
SELECT max(generation_date)
FROM T1
WHERE c_cd=cur1.c_cd
AND d_cd=cur1.d_cd
AND l_cd=cur1.l_cd
AND T1_sub_l =cur2.sub_lat
AND T1_cln =NVL (p_vent, '%')
AND via = (select via
from T2
where l_cd=cur1.l_cd
AND sub_l=cur2.sub_lat
AND cln =NVL (p_vent, '%')
AND p_cd=NVL (p_pri_code, '%'));
How can we write this code efficiently in oracle 11g
Answers
-
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.