Re: Oracle not using indexes on char/varchar columns

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 08 Oct 2003 21:38:55 +1000
Message-ID: <3f83f7e1$0$15134$afc38c87@news.optusnet.com.au>

Kamil Okac wrote:

> 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

So, sorry Kamil... but what's the question now?

The full table scans have disappeared, and index usage is consistent. Or is it the varying cost that's bothering you? I wouldn't worry about it, largely because (from memory) costs take account of index pre-fetching and caching... so you first cost is higher than subsequent ones, because (I'd imagine) it realises the index nodes will have to be physically fetched the first time, and will be in the cache the second and subsequent times.

Not sure why you threw a select from dual in there, either.

Regards
HJR

-- 
--------------------------------------------
See my brand new website, soon to be full of 
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------

Received on Wed Oct 08 2003 - 06:38:55 CDT