performance problems on new function-based index

Giganews Newsgroups
Subject: performance problems on new function-based index
Posted by:  skcar…@yahoo.com
Date: 3 Aug 2006

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.

Replies