RE: function index

Johan

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

According to the execution plan uq_t1_username is a unique index. Right?

>What I cant figure out or find on the net is why it will give 25000 rows
>when it uses function based index. Anyon care to explain why this
>happens?

The queries are different. Both return the same data? If yes, what does the optimizer statistics say? e.g. what do the following queries return?

select column_name, num_distinct, density, num_nulls, num_buckets from user_tab_cols
where table_name = 'T1'
order by column_id;

select distinct_keys, leaf_blocks, blevel from user_indexes
where table_name = 'T1';

IMO you should see a difference between the two set of statistics...

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l