How to test to VARCHAR2's for equality EFFICIENTLY ?
André Hartmann
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é
:)
Eric Parker
news:3fbe...@olaf.komtel.net...
André
Have you considered NVL ?
eric
--
Remove the dross to contact me directly
Niall Litchfield
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" <andreh...@hotmail.com> wrote in message
news:3fbe...@olaf.komtel.net...
Frank
with "a = b or (a is null and b is null)".
Oracle will skip the null testing if a=b,
and this allows for other, hopefully better,
execution plans.
--
Regards, Frank van Bortel
mcstock
'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
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-litc...@audit-commission.gov.uk> wrote in message
news:3fbe177a$0$13348$ed9e...@reading.news.pipex.net...
Turkbear
Inline comment:
"mcstock" <mcstock...@spamdamenquery.com> wrote:
>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
>
Sorry but
'x' != null ( By the way that is a bad comparison operator when using NULL, it should be 'x' is not NULL) is
TRUE and should return a 1 - Nothing is equal to null, so x is not equal to null either..
1* select 1 from dual where 'x' is not Null
SQL> /
1
----------
1
1* select 1 from dual where 'x' is Null
SQL> /
no rows selected
SQL>
ctc...@hotmail.com
> Inline comment:
> "mcstock" <mcstock...@spamdamenquery.com> wrote:
>
> >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
> >
>
> Sorry but
> 'x' != null ( By the way that is a bad comparison operator when using
> NULL, it should be 'x' is not NULL) is TRUE and should return a 1 -
> Nothing is equal to null, so x is not equal to null either..
Don't you think that he knows what he needs his function to return
better than you do? If he just wanted to accept the normal behavior of
the three-valued logic operations, we wouldn't be having this thread,
would we?
Xho
--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service New Rate! $9.95/Month 50GB
mcstock
not the point
the logic of you sample pseudo code uses '!=' to compare to values, with the
assumption that either one of them could be null
if you implement it in a function, i.e.. turn
BEGIN
if (a!=b) then rv:=0 else rv:=1;
end if;
return rv;
end;
into
create or replace function compstr( a in varchar2, b in varchar2 )
return number
is
rv number;
begin
if (a!=b)
then rv:=0;
else rv:=1;
end if;
return rv;
end;
/
and test it you'll see the problem:
SQL> select
2 compstr('this', 'that')
3 , compstr('this', 'this')
4 , compstr(null, null)
5 , compstr('this', null)
6 from dual
7 /
...
COMPSTR('THIS','THAT') COMPSTR('THIS','THIS') COMPSTR(NULL,NULL)
COMPSTR('THIS',NULL)
---------------------- ---------------------- ------------------ -----------
---------
0 1 1
1
your function tells me that 'this' and null are equal -- a typical logic
error due to the fact that not only does NULL never compare equal, but it
never compares to be not-equal either.
your test implements the logic 'if you can tell me for sure that the two
values are not equal, return 0, otherwise (whether you know they are equal
or don't know they are equal), return 1'
standard classroom explanation:
did you get your bonus check yet? no.
do you know how much it is? no (i.e. null, i.e., unknown)
tell me how much it is? i can't
can you tell me if it is $100? cannot say for sure if it is -- the value is
still unknown
can you tell me if it is not $100? cannot say for sure if it isn't -- don't
know the value!
can i have 20% of your bonus? -- go away!
-- mcs
"Turkbear" <joh...@dot.spamfree.com> wrote in message
news:o2usrvsa7iv61u87v...@4ax.com...
mcstock
basic issue -- we're trying to translate 3-value (true/false/null) logic
into 2-value logic
refer to my other post for a complete example on why the proposed logic does
not work as johng expected
-- mcs
<ctc...@hotmail.com> wrote in message
news:20031121161114.889$3...@newsreader.com...
Niall Litchfield
news:_tidnXM4-8a...@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
******************************************
Jonathan Lewis
You might go one step further and check if
the (undocumented) function
sys_op_map_nonnull()
works a little faster.
e.g.
select where
sys_op_map_nonnull(colA) = sys_op_map_nonnull(colB)
the function seems to return a value that
matches the TYPE of the column, but holds
an impossible value if the column is null.
I haven't checked it very carefully yet, so use
at your own risk.
"Frank" <fbo...@nescape.net> wrote in message
news:bpl593$q4r$1...@news4.tilbu1.nb.home.nl...
Frank
But that would still be a function call.
Context switch - overhead - performance loss.
Jonathan Lewis
That depends on whether it is a built-in
function an external function - and I think
it's a built-in.
I'm also curious about the 'context switch' bit -
it gets mentioned from time to time, but there
really isn't a "context switch" in the normal
meaning of the term - it's all happening without
interruption in the oracle executable. Can
you clarify what it is that is considered to be the
context switch in that case ?
"Frank" <fbo...@nescape.net> wrote in message
news:bptt05$6bl$1...@news3.tilbu1.nb.home.nl...
> Jonathan Lewis wrote:
>
> > You might go one step further and check if
> > the (undocumented) function
> > sys_op_map_nonnull()
> > works a little faster.
> >
>
Alex Filonov
Suggested (by many) use of NVL is one of the best solutions. Another one:
WHERE a || '*' = b || '*'
Don't know which of them would be (marginally) faster. Granted, this colution
may not work if (when) Oracle changes handling of empty strings. But I think
in that case too many Oracle-based applications will not work.
Of course, you can use any character in place of '*'.
Frank
I was more referring to O/S switches,
like between subprocesses.
Tanel Poder
> it gets mentioned from time to time, but there
> really isn't a "context switch" in the normal
> meaning of the term - it's all happening without
> interruption in the oracle executable. Can
> you clarify what it is that is considered to be the
> context switch in that case ?
In Oracle docs, there is some description about context switch between plsql
engine which executes the procedural code and sql engine, which retrieves
rows.
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm#23723
So this is an Oracle context switch, not OS/hardware context switch..
Tanel.