| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: replace or translate?
the two functions in this thread have different purposes;
TRANSLATE replaces individual characters, and REPLACE is
meant to search and replace words. An example from my book:
SQL> select translate('beer bucket','beer','milk') as translate
2 , replace ('beer bucket','beer','milk') as replace_1
3 , replace ('beer bucket','beer') as replace_2
4 from dual;
TRANSLATE REPLACE_1 REPLACE
----------- ----------- -------
miik muckit milk bucket bucket
I think it is "safe" to assume that the two functions are optimized for
their purpose ...
and if expression evaluation is truly causing unacceptable performance
problems,
a function-based index might be the solution.
cheers,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Sami Seerangan
Sent: Friday, February 11, 2005 15:25
To: Bill Coulam
Cc: oracle-l_at_freelists.org
Subject: Re: replace or translate?
Thanks to Bill and everyone who responded.
I ran a test case on 10M rows (do have complex strings) with two nested REPLACE calls vs. a simple TRANSLATE. The result is opposite to yours.
Replace took 13 mins
Translate took 20 mins
On Thu, 10 Feb 2005 19:03:14 -0600, Bill Coulam <bcoulam_at_gmail.com> wrote:
> I ran a number of tests on 125K rows with three nested REPLACE calls
> vs. a simple TRANSLATE. On my box, the translate is not only cleaner
> to code, but edged out the REPLACE by a second every time. 'Course if
> I were replacing some complex string, I wouldn't even know where to
> being if I were forced to use TRANSLATE; I'd turn to REPLACE.
>
> - bill c.
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Feb 11 2005 - 09:53:41 CST
 |
 |