performance problems on new function-based index

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. Received on Thu Aug 03 2006 - 21:01:36 CDT