RE: full-scan vs index for "small" tables

Quoting oracle-l-bounce_at_freelists.org:

> we are both right.
> In the real life I had kind of
>
> select where type in ('OPENED', 'ACTIVE')
>
> Anyway, this does not change anything regarding the CBO darned
> undeterministic nature.

I have to disagree that the CBO is undeterministic. Given the same inputs (i.e. statistics and parameters) it comes up with the same output, i.e. access path.

>
> Rem drop table test_cbo;
>
> create table test_cbo (id number, type varchar2(64));
>
> alter table test_cbo add constraint test_cbo_pk primary key (id);
>
> create index test_cbo_type on test_cbo (type);
>
> begin
> for m in 1..9999 loop
> insert into test_cbo values (m, 'CLOSED');
> end loop;
> commit;
> end;
> /
>
> begin dbms_stats.gather_table_stats (user, 'TEST_CBO', method_opt=>'FOR ALL
> COLUMNS SIZE AUTO', cascade=>true); end;
> /
>

This is probably your problem right there. Earlier you were claiming that you had a histogram on the type (status?) column. Your above gather_table_stats call does not create a histogram on any columns of TEST_CBO. "FOR ALL COLUMNS SIZE AUTO" creates histograms ONLY on columns that have been used in a predicate. Since this is a brand new table none of the columns have of course been used in a predicate yet, so no histograms are created.

And this may also be a source for your perception of the CBO being undeterministic: if you rerun the above gather_table_stats proc AFTER your test, it will create a histogram on the type column because now it has been used in a predicate and if you re-run your queries the optimizer will use this additional information to create a better plan.

-- 
regards

Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation
www.centrexcc.com

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