SQL-Performance-Index

From: Kai Sodemann <Kai.Sodemann_at_memo.ikea.com>
Date: Mon, 27 Mar 2000 11:34:24 +0200
Message-ID: <8bna05$3r6$1@mailgate.ikea.com>

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 !

Any help is welcome.

regards
Kai

PS: Also " and column3 = param3 or param3 is null " does not use the index ! Received on Mon Mar 27 2000 - 03:34:24 CST