Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009

    Unanswered: Fastest way to update value

    Hi all guru's,

    Kindly need your help here on any ways to do updating data without taking too much time...currently i have a proc which will update table Q with data in table P base on another data in table P are same with table are the code :

    create or replace
    PROCEDURE A (tbl_name varchar2 default ' ',
    l_name varchar2 default ' ',
    employee_id varchar2 default ' ') IS

    v_tbl varchar2(30) := tbl_name;
    v_lname varchar2(30) := l_name;
    v_empid varchar2(30) := employee_id;


    if (v_tbl = ' ') then

    dbms_output.put_line('Must put table name');


    if (v_lname <> ' ') and (v_empid <> ' ') then
    for x in 1.. v_row loop
    execute immediate '
    update '||v_tbl||' set
    '||v_lname||'=(select last_name from P
    where emplid = (select '||v_empid||' from
    (select row_number() over (order by '||v_empid||') as n, '||v_empid||'
    from '||v_tbl||')
    where n= '||x||'))

    where '||v_empid||' =(select '||v_empid||' from
    (select row_number() over (order by '||v_empid||') as n, '||v_empid||'
    from '||v_tbl||')
    where n= '||x||')' ;

    end loop;
    end if;

    end if;

    It will take value for table name, name column and employee id column. Then it will update the data in name column by selecting data from staging table P which the employee id are same with id in employee id column. But currently this proc works but its taking a bit of time...could anyone suggest me the best way to update this kind of process in very fast time?

    Thanks for help, really appreciate that

  2. #2
    Join Date
    Dec 2003
    Do you have to make the table name dynamic, and use EXECUTE IMMEDIATE? Oracle prefers that you hard code this into your PL/SQL, so that it can parse all of this ahead of time, make sure the table & fields exist, etc.

    If you're updating one record, infrequently, maybe you won't see a huge performance increase. But if you're updating several records rapidly, the system is going to need to run through all of this overhead everytime.

    Search on "Oracle Bind Variables Scaling" in Google, and read up on this some more if you're interested in the details. Anything you find on Tom Kyte's website should be exhaustingly thorough.


  3. #3
    Join Date
    Aug 2009
    Olympia, WA
    In Oracle, the easiest/fastest way to do updates based on another table is actually to use the merge statement.

    Oracle Merge SQL Insert Update Upsert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts