My code takes too much time for execution is there any efficient way to do this

Question

Here there is 3 table 1.employee(eid,ename), 2.address(aid,address), 3.employee_add(eid,aid)

employee and address has many to many relation in it.what I need to do is to clean the duplicate from address table without any data loss from employee_add table. thanks in advance! please help

   DECLARE
         a ADDRESS.AID%TYPE;
         b ADDRESS.ADDRESS%TYPE;
         c ADDRESS.AID%TYPE;
         d ADDRESS.ADDRESS%TYPE;
         CURSOR Cur1 IS
          SELECT AID,ADDRESS
          FROM ADDRESS;
          CURSOR Cur2 IS
            SELECT AID,ADDRESS
             FROM ADDRESS;
      BEGIN
        OPEN Cur1;
         LOOP
            FETCH Cur1 INTO a, b;
            EXIT WHEN Cur1%NOTFOUND;
            OPEN Cur2;
            LOOP
            FETCH Cur2 into c,d;
            IF (b=d) THEN
                IF(a!=c) THEN
                    update  employee_add set aid=a where aid=c;
                    delete from address where aid=c;
                END IF;
                END IF;
                END LOOP;
                CLOSE Cur2;
                END LOOP;
                CLOSE Cur1;

                 END;

So you would have duplicated rows in the employee_add table? E.g. if eid= 1 had 3 duplicated addresses, you'd end up with 3 rows in employee_add with the same eid and aid. Does that make sense, or should we be deleting the duplicate rows from employee_add too? — Boneist, Feb 01 '19 at 11:45
Show some sample rows from each table and tell us what exactly you are trying to achieve. As it appears, simple DML statements in pure SQL is all you would need.But, sample data would greatly help us test and give you a workable solution. — Kaushik Nayak, Feb 01 '19 at 14:16
[employee table : 1---amar 2-virat 3---aaditya] ,,,,,,,, [address table--101--banglore 102---banglore 103----banglore 104----patna 105----patna 106---patna 107--patna 108---pune 109--pune ] .......... [ employe_address table 1--101 1---102 1---103 1---107 2--101 2--102 2---105 3--102 3---108] what i want is--- address table [101---banglore 104----patna 108---pune] and employee_address[1--101 1--104 2--101 2--104 3--101 3--108] i have given the code it showing the same result even address table is refer the answer below nt working help — , Feb 01 '19 at 18:39
this is code https://stackoverflow.com/a/54485061/11000599 it is working but not showing what i want i left the duplicate part please help — , Feb 01 '19 at 18:40

score 1 · Answer 1 · answered Feb 01 '19 at 15:02

You should be able to do this using the following SQL statements (which you could put inside a PL/SQL procedure if you wanted to), like so:

-- To update the employee_add tables
MERGE INTO employee_add tgt
  USING (SELECT ea.rowid rid,
                a.aid,
                a.address,
                MIN(aid) OVER (PARTITION BY address) new_aid
         FROM   address a
                INNER JOIN employee_add ea ON ea.aid = a.aid) src
  ON (tgt.rowid = src.rid)
WHEN MATCHED THEN
  UPDATE SET tgt.aid = src.new_aid
  WHERE tgt.aid != src.new_aid;

-- Delete any rows now longer in the employee_add table
DELETE FROM address
WHERE aid NOT IN (SELECT aid FROM employee_add);

-- If you need to deduplicate the employee_add table, this should do the trick:
DELETE FROM employee_add ea1
WHERE ROWID > (SELECT MIN(ROWID)
               FROM   employee_add ea2
               WHERE  ea1.eid = ea2.eid
               AND    ea1.aid = ea2.aid;

score 0 · Answer 2 · answered Feb 01 '19 at 13:32

In general, explicit cursor is slower than implict cursor. You can try to convert the

OPEN ...
    LOOP 
        FETCH ... 
        EXIT WHEN ... 
        ...
        ...
    END LOOP;

into

FOR ... LOOP 
    ... 
 END LOOP;

else, it would help if you provide some DDLs & DMLs (together with PK, indexes and constraints).

My code takes too much time for execution is there any efficient way to do this

2 Answers2