RE: Using TRANSLATE() to detect numeric strings

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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

Other related posts: