Re: Oracle not using indexes on char/varchar columns

Howard J. Rogers wrote:
> create table TEST...
> insert rows...
> exec dbms_stats.gather_table_stats('<schema_name>,'TEST')
> explain plan for select * from test where col1=1
>
> My bet is that the computation of the statistics will mean the explain plan
> will report an index scan first time.
>

Thanks for reply, but still it's not working as wanted it to.

I did 'exec dbms_stats...' and then:

explain plan for select * from test where col1=1; -- INDEX FAST FULL SCAN - COST 5 explain plan for select * from test where col1=1; -- INDEX UNIQUE SCAN - COST 1 but in another session:

explain plan for select * from dual;
-- not interesting

explain plan for select * from test where col1=1; -- INDEX UNIQUE SCAN - COST 1 (the first run for this query)

Kamil Okac Received on Wed Oct 08 2003 - 05:17:38 CDT