Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: replace or translate?

RE: replace or translate?

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Fri, 11 Feb 2005 15:50:05 +0100
Message-Id: <20050211145011.A6B934A099B@ha-smtp3.tiscali.nl>


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-l
Received on Fri Feb 11 2005 - 09:53:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US