Re: Trimming blanks from VARCHAR2 columns

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 21 Nov 1998 01:27:15 GMT
Message-ID: <365615e1.2673374@192.86.155.100>

A copy of this was sent to "Mac Lazarus" <mac.lazarus_at_eds.com> (if that email address didn't require changing) On 20 Nov 1998 21:53:33 GMT, you wrote:

>The data in the varchar2 columns of a specific table has leading and
>trailing spaces. I have thought about running the following update
>statement:
>
>update table1
>set col1 = ltrim(rtrim(col1);
>
>If table1 is large, this would require a lot of rollback space. The key to
>the table is system generated(even though the key is varchar2 also), so I
>do not know how to programmatically divide several update statements evenly
>, i.e. have several update statements of the form
>
>update table1
>set col1 = ltrim(rtrim(col1)
>where key between 'xxx'and 'yyy'
>
>Does anyone have any ideas on how to code this?
>

Here are 2 different ways to do this, one is slower but will run without error, the other is faster but stands a chance of hitting a 1555 "snapshot too old" error.... Both examples demo against the following table:

SQL> create table demo ( x varchar2(255) ); Table created.

SQL> begin

  2          for i in 1 .. 300 loop
  3                  insert into demo values ( '     ' || i || '     ' );
  4          end loop;

5 end;
6 /
PL/SQL procedure successfully completed.

SQL> commit;
Commit complete.

SQL> select '"' || x || '"' from demo where rownum < 5;

'"'||X||'"'

"     1     "
"     2     "
"     3     "
"     4     "

The first block will iteratively scan the table updating rows that have not been updated yet. Each iteration through the loop will run slower and slower as each has more data to scan before it finds rows not already updated:

SQL> begin

  2          loop
  3                  update demo set x = ltrim(rtrim(x))
  4               where x <> ltrim(rtrim(x))
  5                     and rownum < 25;
  6  
  6                  dbms_output.put_line( 'Updated ' || sql%rowcount  ||
                                           ' rows....' );
  7                  exit when sql%rowcount = 0;
  8                  commit;
  9          end loop;
 10          commit;

11 end;
12 /

The second will run much faster but stands a chance of a snapshot too old since we are committing inside of a loop that is reading the table we are updating. Since this should full scan the table, the chances of a 1555 are actually quite small, its typically scattered reads that result in 1555's in examples like this:

SQL> begin

  2          for x in ( select rowid rid, rownum rnum from demo ) loop
  3                  update demo set x = ltrim(rtrim(x)) where rowid = x.rid;
  4  
  4                  if ( mod(x.rnum,25) = 0 ) then
  5                          dbms_output.put_line( 'Updated ' || x.rnum || 
                                                   ' rows so far....' );
  6                          commit;
  7                  end if;
  8          end loop;
  9          commit;

10 end;
11 /

>TIA,
>
>Mac
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Nov 20 1998 - 19:27:15 CST