Improve the stored procedure performance

Question

I am a non db guy.. Can you please share your views in improving the following sp sample like using global temporary table or indexes or may be improving the existing queries.In the original code , i will be having lot many update queries for different tables.Thanks!!

CREATE OR REPLACE PROCEDURE SYSTEM.process_log1
IS
    cursor cur_attuid_change is
select
    sup.id as id,
    sup.name as name,
    sup.role as role,
    sup.technology as technology
from main hr, secondary sup
where sup.id=hr.id;

BEGIN
   -- update records in main tables from the cursor
   for rec_attuid_change in cur_attuid_change loop

    update main t1
    set    t1.id    = rec_attuid_change.id,
           t1.name    = rec_attuid_change.name,
           t1.ROLE=rec_attuid_change.role,
           t1.technology=rec_attuid_change.technology
    where  t1.id = rec_attuid_change.id;
    commit;

    update main1 t1
    set    t1.id    = rec_attuid_change.id,
           t1.name    = rec_attuid_change.name,
           t1.ROLE=rec_attuid_change.role,
           t1.technology=rec_attuid_change.technology
    where  t1.id = rec_attuid_change.id;
    commit;

    update main2 t1
    set    t1.id    = rec_attuid_change.id,
           t1.name    = rec_attuid_change.name,
           t1.ROLE=rec_attuid_change.role,
           t1.technology=rec_attuid_change.technology
    where  t1.id = rec_attuid_change.id;
    commit;
   end loop;


END;

/

Answer 1

Try this:

CREATE OR REPLACE PROCEDURE SYSTEM.process_log1

BEGIN

    update main t1
    set (t1.id, t1.name, t1.ROLE, t1.technology ) =
        (select
         sup.id as id,
         sup.name as name,
         sup.role as role,
         sup.technology as technology
         from
         main hr,
         secondary sup
         where
          sup.id=hr.id
          and sup.Id = t1.Id);
    commit;

    update main1 t1
    set (t1.id, t1.name, t1.ROLE, t1.technology ) =
        (select
         sup.id as id,
         sup.name as name,
         sup.role as role,
         sup.technology as technology
         from
         main hr,
         secondary sup
         where
          sup.id=hr.id
          and sup.Id = t1.Id);
    commit;

    update main2 t1
    set (t1.id, t1.name, t1.ROLE, t1.technology ) =
        (select
         sup.id as id,
         sup.name as name,
         sup.role as role,
         sup.technology as technology
         from
         main hr,
         secondary sup
         where
          sup.id=hr.id
          and sup.Id = t1.Id);
    commit;


END;

The basic idea is to get rid of the cursor and let Oracle run Set operations (Set as in e.g. Venn Diagrams rather than Set as in set something =). It will do these much faster than it will do RAT (Row At a Time).

I'm not 100% sure about the syntax, but its from here, and about a third down the page you have this:

SET (column_name, column_name, ...) = (subquery4)

Assigns the values retrieved from the database by subquery4 to the columns in the column_name list. The subquery must return exactly one row that includes all the columns listed. The column values returned by the subquery are assigned to the columns in the column list in order. The first value is assigned to the first column in the list, the second value is assigned to the second column in the list, and so on.