Re: Oracle not using indexes on char/varchar columns

Kamil Okac wrote:

> Hello,
>
> We've migrated to Oracle 9.2.0.1.0 (Solaris, 64bit) and are encountering
> problem with indexes. Oracle won't use indexes on char/varchar columns,
> until any (even unrelated to the query or the table referenced in query)
> 'explain plan' is run in that session. The first 'explain plan' of the
> query says that 'FULL SCAN' would be used, the same 'explain plan' ran
> for the second time gives correct information (INDEX UNIQUE SCAN).
>
>
> What to do? Workaround would be to call 'EXPLAIN PLAN FOR SELECT * FROM
> DUAL' at the beginning of every session, but i don't like that :)
>
>
> Thanks,
>
> Kamil
>
> ----------------------------------------------
>
> Example:
>
> CREATE TABLE TEST
> (
> COL1 varchar2(4) primary key
> )
>
>
> ...
> insert data
> ...
>
>
> select * from TEST where COL1=1;
>
> -- wait several minutes, select is not using index
>
>
> explain plan for select * from TEST where COL1=1
>
> -- says 'FULL SCAN'
>
>
> explain plan for select * from TEST where COL1=1
>
> -- says 'INDEX UNIQUE SCAN'
>
>
> select * from TEST where COL1=1;
>
> -- done immediately
>
> ----------------------------------------------

Well, at least in your test run, you aren't computing any statistics on your table, so naturally the optimizer is left completely in the dark at the best way of getting at the data... and plumps for the full table scan in the first instance.

The changing plan you're getting as you do explain plan, I would guess to be the result of Oracle 9i's new dynamic sampling feature. The running of explain plan causes dynamic statistics to be collected, so the second running of explain plan takes advantage of those statistics to work out a better way of doing it.

Dynamic sampling is a nice feature, but you shouldn't be relying on it like this. It's your job to collect statistics properly on objects that have been subject to a lot of DML, so get acquainted with dbms_stats.

Re-run your test like so:

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.

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!!
--------------------------------------------