RE: Using TRANSLATE() to detect numeric strings

Parsing a string into an actual numeric value is quite a bit more work than just checking whether a string is exclusively digits.

If valid input in your case, as in Jared's, is just digits with no decimal points or plus or minus signs or scientific notation, then translating to a fixed string or to null and comparing to null to see if it is the "just digits" version of "is numeric" should be a lot faster.

In fact in an error rich environment preempting the number conversion exception with a little more elaborate character list than just digits (adding in the symbols allowed in the number formats you allow) would probably speed up Niall's function. For screening low error rates I don't see a way to get better than Niall's function at handling all legal numeric formats.

That is if you are apples and apples in terms of the function being stored in the database and avoiding client to dbserver context switches, I would expect Jared's "is digits" to beat "is numeric" every time.

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Herring Dave - dherri
Sent: Sunday, January 27, 2008 10:31 PM
To: jkstill_at_gmail.com; Oracle-L Freelists Subject: 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

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

<snip>

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