FAQ
*Hi List,*
**
*Background:* 9i (9.2.0.8.0) HP-UX B.11.31 U ia64

*
*We need to shrink SSN to the last 4 digit.*
*
*update sales set ssn=to_number(substr(to_char(ssn),-4));*
**
*But the sales table is quite large and we want to commit every 1000
records.*
*
*Does anyone have a better idea to do this rather than below?*
*
*Thanks,*
*
*Roger*

declare
cursor c12 is select ssn from sales for update of ssn;
n number := 0;
begin

for eachc12 in c12 loop
if eachc12.ssn is not null then

n := n + 1;
update sales set ssn=to_number(substr(to_char(ssn),-4)) where current
of c12;
if ( n = 1000 )
then
commit;
end if;

end if;
end loop;
commit;
end;

Search Discussions

  • Steve Baldwin at Nov 13, 2009 at 8:11 pm
    Roger,

    Fetching in an array operation (FETCH .. BULK COLLECT INTO .. LIMIT
    ..) and then updating as an array operation (FORALL .. UPDATE ..)
    would be a lot more efficient than how you're proposing here.

    HTH

    Steve
    On Sat, Nov 14, 2009 at 6:50 AM, Roger Xu wrote:
    Hi List,

    Background: 9i (9.2.0.8.0)  HP-UX B.11.31 U ia64

    We need to shrink SSN to the last 4 digit.

    update sales set ssn=to_number(substr(to_char(ssn),-4));

    But the sales table is quite large and we want to commit every 1000 records.

    Does anyone have a better idea to do this rather than below?

    Thanks,

    Roger


    declare
    cursor c12 is select ssn from sales for update of ssn;
    n number := 0;
    begin
    for eachc12 in c12 loop
    if eachc12.ssn is not null then
    n := n + 1;
    update sales set ssn=to_number(substr(to_char(ssn),-4)) where current
    of c12;
    if ( n = 1000 )
    then
    commit;
    end if;
    end if;
    end loop;
    commit;
    end;
    --
    http://www.freelists.org/webpage/oracle-l
  • Alex Fatkulin at Nov 13, 2009 at 8:16 pm
    There could be a couple of answers which depend on how your table is organized.

    If you have an index on SSN then one of the things you might try is a
    simple loop like that:

    begin

    loop
    update sales set ssn=mod(ssn, 10000)
    where ssn > 9999
    and rownum <= 1000;

    commit;
    exit when sql%rowcount < 1000;
    end loop;

    end;

    though that might be slow in certain cases (if your clustering factor
    for ssn isn't good, for example).

    You might try bulk collect/update a shot and see how something like
    this will perform for you:

    declare

    cursor l_cur is select rowid from sales where ssn > 9999;
    type l_tab is table of rowid index by binary_integer;
    l_buffer l_tab;
    begin
    open l_cur;

    loop
    fetch l_cur bulk collect into l_buffer limit 1000;

    forall i in 1 .. l_buffer.count
    update sales set ssn=mod(ssn,10000) where rowid=l_buffer(i);

    commit;

    exit when l_buffer.count < 1000;

    end loop;

    close l_cur;

    end;

    that should be faster compared to row-by-row stuff in your example.

    Another thing you could consider is dong CTAS and then swapping the tables.
    On Fri, Nov 13, 2009 at 2:50 PM, Roger Xu wrote:
    Hi List,

    Background: 9i (9.2.0.8.0)  HP-UX B.11.31 U ia64

    We need to shrink SSN to the last 4 digit.

    update sales set ssn=to_number(substr(to_char(ssn),-4));

    But the sales table is quite large and we want to commit every 1000 records.

    Does anyone have a better idea to do this rather than below?

    Thanks,

    Roger


    declare
    cursor c12 is select ssn from sales for update of ssn;
    n number := 0;
    begin
    for eachc12 in c12 loop
    if eachc12.ssn is not null then
    n := n + 1;
    update sales set ssn=to_number(substr(to_char(ssn),-4)) where current
    of c12;
    if ( n = 1000 )
    then
    commit;
    end if;
    end if;
    end loop;
    commit;
    end;
    --
    Alex Fatkulin,
    http://afatkulin.blogspot.com
    http://www.linkedin.com/in/alexfatkulin
    --
    http://www.freelists.org/webpage/oracle-l
  • Alisher Yuldashev at Nov 13, 2009 at 8:17 pm
    Roger,

    I would do

    declare
    cursor c12 is select rowid rid from sales where ssn is not null;
    type v_typ is table of c12%rowtype;
    v_arr v_typ;
    begin
    open c12;
    loop
    fetch c12 bulk collect into v_arr limit 1000;
    forall i in 1..v_arr.count
    update sales set ssn=to_number(substr(to_char(ssn),-4)) where
    rowid=c12.rid(i);

    commit;
    exit when c12%notfound;
    end loop;
    close c12;
    end;

    Thanks,

    --
    Alisher Yuldashev
    Senior Oracle DBA
    The Pythian Group - Ottawa, Canada
    Web : http://www.pythian.com
    *Hi List,*
    **
    *Background:* 9i (9.2.0.8.0) HP-UX B.11.31 U ia64
    * *
    *We need to shrink SSN to the last 4 digit.*
    * *
    _update sales set ssn=to_number(substr(to_char(ssn),-4));_

    *But the sales table is quite large and we want to commit every 1000
    records.*
    * *
    *Does anyone have a better idea to do this rather than below?*
    * *
    *Thanks,*
    * *
    *Roger*


    declare
    cursor c12 is select ssn from sales for update of ssn;
    n number := 0;
    begin
    for eachc12 in c12 loop
    if eachc12.ssn is not null then
    n := n + 1;
    update sales set ssn=to_number(substr(to_char(ssn),-4)) where
    current of c12;
    if ( n = 1000 )
    then
    commit;
    end if;
    end if;
    end loop;
    commit;
    end;
    --
    http://www.freelists.org/webpage/oracle-l
  • Greg Rahn at Nov 14, 2009 at 3:25 pm
    The fastest way to do this (given the option for exclusive access to
    the table) is a parallel nologging CTAS or PDML + insert /*+ append
    */.
    On Fri, Nov 13, 2009 at 11:50 AM, Roger Xu wrote:
    We need to shrink SSN to the last 4 digit.
    update sales set ssn=to_number(substr(to_char(ssn),-4));
    --
    Regards,
    Greg Rahn
    http://structureddata.org
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 13, '09 at 7:50p
activeNov 14, '09 at 3:25p
posts5
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase