Re: SQL-Performance-Index

If you use Oracle8i consider function-based indexes. It could be helpful for you.

Kai Sodemann <Kai.Sodemann_at_memo.ikea.com> wrote in message news:8bna05$3r6$1_at_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 - 00:00:00 CEST