Performance of PL/SQL for Table Update

Dear Experts,

Referring to my question http://www.experts-exchange.com/questions/28697379/Update-Using-PL-SQL-with-Error-Condition.html, our DBA told me that this PL/SQL is unacceptable because the cascaded select statement takes too much time since the table has hundreds of millions of rows. Could you please offer me an other way?

Best Regards

DECLARE
     cnt   PLS_INTEGER := 0;
 BEGIN
     FOR c1 IN (SELECT upd_rid
                  FROM ta_upd
                 WHERE upd_rid NOT IN (SELECT ROWID
                                         FROM ta
                                        WHERE y = 'xyz'))
     LOOP
         UPDATE ta
            SET y = 'xyz'
          WHERE ROWID = c1.upd_rid;

         cnt := cnt + 1;

         IF cnt = 1000
         THEN
             cnt := 0;

             COMMIT;
         END IF;
     END LOOP;
 END;
 /
GurcanKAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
can you remove items from ta_upd as you update?
or flag them in some way?

if you can, then you don't need to do a subquery lookup

that might looks something like this...


DECLARE
    cnt PLS_INTEGER := 0;
BEGIN
    FOR c1 IN (SELECT upd_rid
                 FROM ta_upd)
    LOOP
        UPDATE ta
           SET y = 'xyz'
         WHERE ROWID = c1.upd_rid;


         ---- if delete isn't acceptable, this could be changed to an update of some flag in the table.
        DELETE ta_upd
         WHERE upd_rid = c1.upd_rid;

        cnt := cnt + 1;

        IF cnt = 1000
        THEN
            cnt := 0;

            COMMIT;
        END IF;
    END LOOP;
END;




if not, then a subquery is the only option,  it could also be written with NOT EXISTS instead of NOT IN.

Test both to see which works better for you



DECLARE
    cnt PLS_INTEGER := 0;
BEGIN
    FOR c1 IN (SELECT upd_rid
                 FROM ta_upd
                WHERE NOT EXISTS
                          (SELECT NULL
                             FROM ta
                            WHERE y = 'xyz' AND ta.ROWID = ta_upd.upd_rid))
    LOOP
        UPDATE ta
           SET y = 'xyz'
         WHERE ROWID = c1.upd_rid;

        cnt := cnt + 1;

        IF cnt = 1000
        THEN
            cnt := 0;

            COMMIT;
        END IF;
    END LOOP;
END;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GurcanKAuthor Commented:
Deletion is permitted. That's OK.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.