| Alternative to REPLACE function? [message #460610] |
Sat, 12 June 2010 23:00  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Good evening all. I have a table with:
1 million rows
average row length 200 bytes
50 columns
and this update statement
UPDATE mytable SET varchar2_4000_column = replace(replace(replace...300 times)
It looks at every row in the table (no WHERE clause) and does these 300 replace operations on this column for each row. Each replace replaces with a null so effectively it is removing strings. Much of the time these strings are not in the column.
This update statement takes 25 minutes and it is 98% CPU and 2% USER_IO time.
Can anyone think of a clever alternative to REPLACE? I figure that is what is taking all the time since it is a CPU bound statement. Here is what I have so far.
1) if rows in this table are persistent over time then tag rows with a flag to show which ones have already been processed and skip these next time around
2) UH... there is no #2. #1 is all I got.
Anyone have ideas? Kevin
|
|
|
|
| Re: Alternative to REPLACE function? [message #460613 is a reply to message #460610] |
Sun, 13 June 2010 00:28  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
So you've got a list of 300 forbidden words - or strings - that you want to remove from the table.
How about a function-based index on DECODE(varchar2_4000_column, REPLACE(REPLACE(REPLACE(..........), varchar2_4000_column, NULL, 1)?
Then you could
UPDATE mytable
SET varchar2_4000_column = replace(replace(replace...300 times)
WHERE decode(replace(replace(replace...300 times), varchar2_4000_column, NULL, 1) = 1;
Updates and inserts would be a bit costly, but the searches would be awesome. You could probably do something similar and maybe smarter with custom index types. Barbara is very good at those. Maybe she'll see this and be inspired.
Ross Leishman.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
