Re: function index

Did you analyze the table after building the FB index? The FB index creates an invisible column in the table and unless the table is re-analyzed that column will have no statistics and the CBO will use column statistics defaults to calculate selectivity and estimated cardinality.

johan Eriksson wrote:

Hi

The database is 10.2 on RHEL 3.

I have a table on which I have  2 indexes, one normal and one
function-based.

The different queries I run are

select count(*) from t1 where username='user1';
and
select count(*) from t1 where lower(username)='user1';

the index are created with

create index f_t1_username on t1(lower(username));
create index uq_t1_username on t1(username);


-- Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l


Other related posts: