RE: Using TRANSLATE() to detect numeric strings
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <Dave.Herring@xxxxxxxxxx>, <jkstill@xxxxxxxxx>, "'Oracle-L Freelists'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 28 Jan 2008 08:20:44 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Herring Dave - dherri
Sent: Sunday, January 27, 2008 10:31 PM
To: jkstill@xxxxxxxxx; 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
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
<snip>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
