Home » SQL & PL/SQL » SQL & PL/SQL » cursors with same codes (oracle 10g)
cursors with same codes [message #395582] Wed, 01 April 2009 23:00 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi

below are 3 cursors all fetch similar find of data and we have 3 corresponding for loops in our application for different business processing.

Is there any way i can improve the performance by adding some logic so that i wont need all the same kind of cursors.

The queries repeat all the time in the application because it is desired to first check the contacts and then the best contact and if not found then goes for the last updated contact.

please advice if i can avoid this repetation.

  CURSOR C1(var IN VARCHAR2) IS---- finding out the contact details
      SELECT KEY,
             TITLE,
             FORENAME,
             SURNAME,
             FULL_NAME,
             IMK,
             GROUP_KEY
        FROM CONTACT
       WHERE GROUP_KEY = var
         AND NVL(FLG, 'A') = 'A'
         AND IMK IS NOT NULL;


CURSOR C2(var IN VARCHAR2) IS ---finding out the best contact
      SELECT KEY,
             TITLE,
             FORENAME,
             SURNAME,
             FULL_NAME,
             IMK,
             MI_IMK_FLG,
             DECODE(MI_IMK_FLG,
                    'Mgrr',
                    '01',
                    'Op IMK',
                    '02',
                    'Sn IMK',
                    '03',
                    'Dk Maker',
                    '04',
                    'Ir',
                    '05',
                    'Nl ',
                    '06',
                    '09') IMK_ORDER
        FROM CONTACT,IMK_BASE 
       WHERE A_KEY =VAR
         AND ANK = KEY
         AND NVL(FLG, 'A') = 'A'
         AND IMK IS NOT NULL 
       ORDER BY IMK_ORDER;

CURSOR C3(VAR IN VARCHAR2) IS ---- to find out the best contact key with last updated
      SELECT KEY,
             TITLE,
             FORENAME,
             SURNAME,
             FULL_NAME,
             IMK
        FROM CONTACT
       WHERE A_KEY =VAR
         AND NVL(FLG, 'A') = 'A'
       AND IMK IS NOT NULL 
       ORDER BY A_LAST_UPD DESC;


[Updated on: Wed, 01 April 2009 23:04]

Report message to a moderator

Re: cursors with same codes [message #395609 is a reply to message #395582] Thu, 02 April 2009 01:12 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you know you're gonna need the best contact anyway, why not fetch them in order in the first place?
Since cursor 1 has no ordering, you might as well use cursor 3 in its place.

You can also outer join with the IMK_BASE table. Order by nvl(imk_order, a_last_upd).
That way you'll only need one cursor
Previous Topic: cursor with dynamic table
Next Topic: Row count of all tab's in my DB.
Goto Forum:
  


Current Time: Sun Dec 04 18:51:25 CST 2016

Total time taken to generate the page: 0.04357 seconds