Re: performance problems on new function-based index

On 3 Aug 2006 19:01:36 -0700, skcar14_at_yahoo.com wrote:

>Hi guys,
>
>I am having performance problems with the addition of new
>function-based indexes.
>
>alter session set nls_comp='ANSI';
>alter session set nls_sort='BINARY_CI';
>* have to run this because the of case-insensitivity requirements
>
>I have a view. for ex:
>
>create or replace view view1
>as
>select * from emp1,user
>where emp1.empno=user.empno
>union
>select * from emp2,user
>where emp2.empno=user.empno
>union
>select * from emp3,user
>where emp3.empno=user.empno and so on
>
>When I run this it works with a full table scan. Then when i created a
>function-based index:
>
>create index user_ix on
>user(nlssort(empno,'NLS_SORT=BINARY_CI'));
>
>analyze index user_ix compute statistics;
>analyze table user compute statistics;
>
>the view hangs. but when i run the individual select statements it
>works.
>Do you guys have any idea on what's going on? Any advise is greatly
>appreciated.
>
>Thanks.

get explain plan for the individual selects get explain plan for the view.
My question is, assuming empno is indeed a number, why do you set up a function based index on a number. Doesn't make sense to me. My guess is the user table will be the driving table in the individual select, so the index won't be used anyway. Did you
- index emp<x>.empno
- made sure emp<x>.empno is in identical case in the user table or
are we talking about a real mess?

--
Sybrand Bakker, Senior Oracle DBA