Re: SQL-Performance-Index

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 27 Mar 2000 10:45:34 -0500
Message-ID: <d5vudsgi8sp87ed4k8qg75l41p0ipjil7r@4ax.com>

A copy of this was sent to "Kai Sodemann" <Kai.Sodemann_at_memo.ikea.com> (if that email address didn't require changing) On Mon, 27 Mar 2000 11:34:24 +0200, you wrote:

>Hi,
>
>I have the following problem:
>
>I have a DB-function with several IN parameters. The parameters are to
>restrict a SELECT statement.
>
>procedure proc1 ( param1 IN varchar2, param2 IN varchar2, param3 IN
>varchar2) return varchar2 is
> result varchar2(10);
>Begin
> select column4 into result
> from tab1
> where column1 = param1
> and column2 = param2
> and column3 = nvl(param3, column3);
> return result;
>End proc1;
>
>I have an unique index on column3, but this index is not used because of
>the NVL function, that is used there. As TAB1 is a very big table I need the
>select statement to use the index !
>

with the NVL it cannot use the index -- you are saying in effect:

for each row:

   if parma3 is null

        compare column3 to column3
   else

        compare column3 to parm3
   end if

An index can only be used when the thing you are comparing to is deterministic -- the NVL() function returns a different function for each and every row in the table, hence an index cannot be used for that.

>Any help is welcome.
>
>regards
>Kai
>
>PS: Also " and column3 = param3 or param3 is null " does not use the index !
>

a couple of solutions:

....
is

   return varchar2(10);
begin

   if ( parm3 is null ) then

      /* this cannot of course use the index on column3 since there is no
         predicate on it */
      select column4 into result 
        from tab1 
       where column1 = parm1 
         and column2 = parm2;
   else
      select column4 into result 
        from tab1 
       where column1 = parm1 
         and column2 = parm2
         and column3 = parm3;

   end if;
end;

That is -- use a query that is appropriate for the input. If there are many combinations -- dynamic sql may be very helpful (just be sure to use bind variables).

You could try a union as well:

scott_at_dev8i> create table t as select object_id, object_name from all_objects; Table created.

scott_at_dev8i> create unique index t_idx on t(object_id); Index created.

scott_at_dev8i> set autotrace on

scott_at_dev8i> variable data varchar2(255)
scott_at_dev8i> variable parm1 number
scott_at_dev8i> variable parm2 varchar2(255)

scott_at_dev8i> exec :parm1 := 9088;
scott_at_dev8i> exec :parm2 := null;

scott_at_dev8i> select object_name into :data   2 from t
  3 where object_id = :parm1
  4 union all
  5 select object_name
  6 from t
  7 where object_name = :parm2 and :parm1 is NULL   8 /

OBJECT_NAME

---

/10076b23_OraCustomDatumClosur

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 UNION-ALL

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T'
   3    2       INDEX (UNIQUE SCAN) OF 'T_IDX' (UNIQUE)
   4    1     FILTER
   5    4       TABLE ACCESS (FULL) OF 'T'

Statistics

---

          0  recursive calls
          0  db block gets
          3  consistent gets
          1  physical reads
          0  redo size
       1125  bytes sent via SQL*Net to client
        890  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

So, that shows me when :parm1 is NOT null, 3 blocks (consistent gets) are processed. It only did the index read and table access by rowid. It blew off the full scan (the second part of the union all). OTOH:

scott_at_dev8i> exec :parm1 := null;
scott_at_dev8i> exec :parm2 := 'x';

scott_at_dev8i> select object_name into :data   2 from t
  3 where object_id = :parm1
  4 union all
  5 select object_name
  6 from t
  7 where object_name = :parm2 and :parm1 is NULL   8 /

no rows selected

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 UNION-ALL

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T'
   3    2       INDEX (UNIQUE SCAN) OF 'T_IDX' (UNIQUE)
   4    1     FILTER
   5    4       TABLE ACCESS (FULL) OF 'T'

Statistics

---

          0  recursive calls
         16  db block gets
         91  consistent gets
          0  physical reads
          0  redo size
        948  bytes sent via SQL*Net to client
        777  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

Here I see many more block reads indidcating it did the full scan since parm1 was null this time around.

This is similar to the 2 query code but done with 1 query...

--
http://osi.oracle.com/~tkyte/  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Mar 27 2000 - 09:45:34 CST