Re: Boolean Function in SQL

Haild wrote:
>
> plus it is much slower than the other ones that convert to a number and the
> deal wit hthe execption
>
> In article <8rk3jk$q71$1_at_nnrp1.deja.com>, huiming_at_my-deja.com wrote:
> >
> >>
> >> select
> >> decode(
> >> replace(
> >> translate(col,'0123456789','@@@@@@@@@@'),
> >> '@',null),
> >> null,'IS A NUMBER',
> >> 'IS NOT A NUMBER)
> >> from table
> >>
> >
> >Very good idea. However this will not return the correct result when
> >column contains something like '1234_at_7@'.
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Before you buy.

Have you actually done a test on your performance claim ? PL/SQL is fast but when called repeatedly from SQL you invariably get a performance hit.

A simple example of this is:

SQL> create or replace function is_number(p varchar2) return number is   2 v number;
  3 begin
  4 v := to_number(p);
  5 return 1;
  6 exception when others then return 0;   7 end;
  8 /

Function created.

SQL> set timing on
SQL> select count(*)
  2 from dba_objects
  3 where is_number(object_id) = 1;

 COUNT(*)                                             
---------                                               
     3123                                                      

 real: 491

SQL> select count(*)
  2 from dba_objects
  3 where is_number(object_name) = 1;

 COUNT(*)               
---------                  
        0                                                               

 real: 3445

So a total time of 491 + 3445 = approx 4000ms

SQL> select count(*)
  2 from dba_objects
  3 where replace(
  4 translate(object_id,'0123456789','@@@@@@@@@@'),   5 '@',null) is null
  6 /

 COUNT(*)

     3123

 real: 120

SQL> select count(*)
  2 from dba_objects
  3 where replace(
  4 translate(object_name,'0123456789','@@@@@@@@@@'),   5 '@',null) is null
  6 /

 COUNT(*)                    
---------     
        0                                                               

 real: 50

which totals to 170ms - or about 20-25 times faster...

Connor

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk
(faster/mirrored at http://www.oradba.freeserve.co.uk)

Its not the voices in my head that bother me... 
  its the voices in yours.