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

>From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com]
>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.  

OK. Now I understand. This is really so. Oracle says no more no less in pl/sql package specs:

• AUTO : Oracle determines the columns to collect histograms based on data distribution and the *workload* of the columns.

I produced another test. This time a table with 3 columns. I'am not sure if it matters.

It took me to explain_plan-gather statistics *2* times before I got histograms!

This is exactly what I noticed before: plan may change just like that w/o no reason. It's enough just to issue explain plan several times. Is 2 a magic number? May be.

Here is the test:

drop table test_cbo;
create table test_cbo (id number, type varchar2(64), category 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', '1');     end loop;
    for m in 10000..19999 loop

        insert into test_cbo values (m, 'BAD', '2');     end loop;
    for m in 20000..29999 loop

        insert into test_cbo values (m, 'WORSE', '3');     end loop;
    commit;
end;
/

explain plan for select * from TEST_CBO where type in ( '1', 'Z');

begin sys.dbms_stats.gather_table_stats ('LTDLNE', 'TEST_CBO', method_opt=>'FOR ALL COLUMNS SIZE AUTO', cascade=>true); end; /

select * from user_histograms where table_name='TEST_CBO' order by table_name, column_name;

explain plan for select * from TEST_CBO where type in ( '1', 'Z');

begin sys.dbms_stats.gather_table_stats ('LTDLNE', 'TEST_CBO', method_opt=>'FOR ALL COLUMNS SIZE AUTO', cascade=>true); end; /

select * from user_histograms where table_name='TEST_CBO' order by table_name, column_name;

Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer

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