"mcstock" <mcstockspamplug_at_spamdamenquery.com> wrote in message
news:_tidnXM4-8acyCOiRVn-sw_at_comcast.com...
> close....
>
> 'x' != 'x' is false, returns 0 -- ok
> 'x' != 'y' is true, return 1 -- ok
> null != null is null, returns 1 -- ok
> 'x' != null is also null, returns 1 -- not ok
doh! I didn't consider the case where a was non null and b was null. Perhaps
I need to take Dan's class. I'd still rather rewrite the base sql and not
use the function at all though, or failing that use NVL.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
>
> the most important observations in this thread are to avoid using a
> function, but to use an expression in the SQL, since using the function
will
> limit the optimizer's choices
>
> for example, with this function
>
> SQL> create or replace function vc2comp( a varchar2, b varchar2 )
> 2 return number
> 3 is
> 4 begin
> 5 if (a=b or a||b is null)
> 6 then
> 7 return 1;
> 8 else
> 9 return 0;
> 10 end if;
> 11 end vc2comp;
> 12 /
>
> Function created.
>
> SQL> select
> 2 vc2comp('a','a')
> 3 , vc2comp('a','b')
> 4 , vc2comp(null,null)
> 5 , vc2comp('a',null)
> 6 from dual;
>
> VC2COMP('A','A') VC2COMP('A','B') VC2COMP(NULL,NULL) VC2COMP('A',NULL)
> ---------------- ---------------- ------------------ -----------------
> 1 0 1 0
>
> consider the following scenario (using function based indexes with
> QUERY_REWRITE_ENABLED = TRUE)
>
> SQL> select e.ename, d.dname, e.status_code
> 2 from bigemp e, dept d
> 3 where e.deptno = :deptno
> 4 and vc2comp(e.status_code, :status) = 1
> 5 /
>
> no rows selected
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=192 Bytes=57
> 60)
>
> 1 0 MERGE JOIN (CARTESIAN) (Cost=64 Card=192 Bytes=5760)
> 2 1 TABLE ACCESS (FULL) OF 'BIGEMP' (Cost=16 Card=48 Bytes=1
> 008)
>
> 3 1 SORT (JOIN) (Cost=48 Card=4 Bytes=36)
> 4 3 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=4 Bytes=36)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 4 db block gets
> 102 consistent gets
> 0 physical reads
> 0 redo size
> 317 bytes sent via SQL*Net to client
> 314 bytes received via SQL*Net from client
> 1 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 0 rows processed
>
> SQL> select e.ename, d.dname, e.status_code
> 2 from bigemp e, dept d
> 3 where e.deptno = :deptno
> 4 and nvl(e.status_code,'-') = nvl(:status,'-')
> 5 /
>
> no rows selected
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=192 Bytes=576
> 0)
>
> 1 0 MERGE JOIN (CARTESIAN) (Cost=9 Card=192 Bytes=5760)
> 2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=4 Bytes=36)
> 3 1 SORT (JOIN) (Cost=8 Card=48 Bytes=1008)
> 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'BIGEMP' (Cost=2 Card
> =48 Bytes=1008)
>
> 5 4 INDEX (RANGE SCAN) OF 'BIGEMP$DEPT$NVLSTATUS' (NON-U
> NIQUE) (Cost=1 Card=48)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 4 db block gets
> 1 consistent gets
> 0 physical reads
> 0 redo size
> 317 bytes sent via SQL*Net to client
> 314 bytes received via SQL*Net from client
> 1 SQL*Net roundtrips to/from client
> 3 sorts (memory)
> 0 sorts (disk)
> 0 rows processed
>
> of course, your results will vary, this was performed by a professional
> driver on a closed course, etc. etc.
>
> -- mcs
>
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:3fbe177a$0$13348$ed9e5944_at_reading.news.pipex.net...
> | You might improve performance marginally by changing the logic to
> |
> | BEGIN
> | if (a!=b) then rv:=0 else rv:=1;
> | end if;
> | return rv;
> | end;
> |
> | since NULL != NULL also doesn't return true.
> |
> | I suspect you'd see most improvement by rewriting your queries to use
the
> | built in NVL function.
> |
> | --
> | Niall Litchfield
> | Oracle DBA
> | Audit Commission UK
> | "André Hartmann" <andrehartmann_at_hotmail.com> wrote in message
> | news:3fbe1046_at_olaf.komtel.net...
> | > Hi there,
> | >
> | > in my database schema there is a function that tests two VARCHAR2
> values
> | > for equality. The function goes like this:
> | >
> | > (a IN VARCHAR2, b IN VARCHAR2)
> | > RETURN INTEGER
> | > IS RV NUMBER;
> | > BEGIN
> | > IF (a IS NULL) AND (b IS NULL) THEN
> | > rv := 1;
> | > ELSE
> | > IF (a = b) THEN
> | > rv := 1;
> | > ELSE
> | > rv := 0;
> | > END IF;
> | > END IF;
> | > RETURN rv;
> | > END;
> | >
> | > Why did I do it ? Well because the usual "=" operator does not say
that
> | two
> | > NULLs are equal ! to check this, just try "select 1 from DUAL where
> | > NULL=NULL".
> | >
> | > Now I see that my function gets called VERY often, many million times
in
> a
> | > great variety of SQL statements that my application fires. Any idea
how
> i
> | > could improve the performance of the function or maybe there is an
> | entirely
> | > different approach to that ?
> | >
> | > Thanks,
> | > André
> | > :)
> | >
> | >
> |
> |
>
>
Received on Sat Nov 22 2003 - 14:03:10 CST
