RE: replace or translate?
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <dba.orcl@xxxxxxxxx>, "'Bill Coulam'" <bcoulam@xxxxxxxxx>
- Date: Fri, 11 Feb 2005 15:50:05 +0100
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Sami Seerangan
Sent: Friday, February 11, 2005 15:25
To: Bill Coulam
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx> 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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
