Function Based Index not working.

[kjcook]

I have created a function based index, but it keeps doing a full table scan. I used the following script.

SQL> DROP TABLE HEY;

Table dropped.

SQL> GRANT QUERY REWRITE TO MI;

Grant succeeded.

SQL> ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;

Session altered.

SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

Session altered.

SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

Session altered.

SQL> create table hey (id number, txt varchar2(20));

Table created.

SQL> BEGIN
2 FOR I IN 1..10000 LOOP
3 INSERT INTO HEY VALUES(I,'Test this');
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 FOR I IN 1..100 LOOP
3 INSERT INTO HEY VALUES(I,'Check');
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> create index hey_idx on hey(upper(txt));

Index created.

SQL> exec dbms_stats.gather_table_stats( 'mi', 'hey' -
> , method_opt => 'for all indexed columns size 1' -
> , cascade => true -
> , degree=>2 -
> , granularity => 'ALL' -
> );

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly
SQL> select count(*) from hey where upper(txt) = UPPER('CHECK');


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=5 Card=1 Bytes=1
2)

1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'HEY' (Cost=5 Card=5050 Bytes=606
00)


However, if I analyze the table with the following command I can use the index.

SQL> analyze table hey compute statistics for table for all indexed columns for all indexes;

What is the difference? I understand that should no longer use the analyze statement rather the new package dbms_stats. So what is the coorisponding dbms_stats call to make this work? Do you know of any good book the describes this package in great detail?

Thanks!!!

[gopi]

Privs:
grant query rewrite to

init.ora:
QUERY_REWRITE_INTEGRITY = TRUSTED
QUERY_REWRITE_ENABLED = TRUE