RE: Using TRANSLATE() to detect numeric strings

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Sun, 27 Jan 2008 21:30:30 -0600
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E3604297FA4@CWYMSX04.Corp.Acxiom.net>

Jared,

Any good ideas as to why TRANSLATE would be faster than a user function? Is it just whatever magic Oracle does with its functions compared to one we create?

I ran a test using how we deal with this, using nearly the same function as Niall, except NULL is returned for non-numeric values. To do the same with TRANSLATE, I had to wrap a CASE statement around it:

SELECT CASE

          WHEN TRANSLATE(fileno, '0123456789', '||||||||||') != '||' 
             THEN NULL 
          ELSE fileno 
       END fake_number

  FROM detect_numeric;

Yet it still ran about 50% or more faster. I then ran this on a test closer to how we'd use it, around 10,000,000 rows. Normally we have just a few non-numeric values, if at all, that we'd run into during loads of client data. Again, CASE ... TRANSLATE was faster, around 40%.

I can post results from Tom's run_stats package if you'd like or are interested. I'm more interested in understanding how to alter our user function to be more efficient.

Dave

---

Dave Herring, DBA |   A c x i o m  M I C S / C S O 630-944-4762 office | 630-430-5988 wireless | 630-944-4989 fax

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared Still Sent: Friday, January 25, 2008 10:12 AM
To: Oracle-L Freelists
Subject: Using TRANSLATE() to detect numeric strings

This week I found it necessary to differentiate between strings that were composed of numeric data and those that were alphanumeric.

This was kind of fun an interesting. 

It was also too long to post here, so I put it on my blog.

http://jkstill.blogspot.com/ if you're interested.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist


*************************************************************************

The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be
legally privileged.

If the reader of this message is not the intended recipient, you are 
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.

If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.

Thank you.


*************************************************************************


--
http://www.freelists.org/webpage/oracle-l

Received on Sun Jan 27 2008 - 21:30:30 CST