RE: full-scan vs index for "small" tables
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: Laimutis.Nedzinskas@xxxxxxxxxxxxx, oracle-l-bounce@xxxxxxxxxxxxx
- Date: Thu, 29 Jun 2006 11:11:38 -0700
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
