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

Quoting oracle-l-bounce@xxxxxxxxxxxxx:

> 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

Other related posts: