Re: How to test to VARCHAR2's for equality EFFICIENTLY ?

From: mcstock <mcstockspamplug_at_spamdamenquery.com>
Date: Fri, 21 Nov 2003 13:09:50 -0500
Message-ID: <_tidnXM4-8acyCOiRVn-sw@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

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 Fri Nov 21 2003 - 12:09:50 CST