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