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

>from Laimutis Nedzinskas
>OK. Now I understand.

OK, now I am lost again.
My db version is 9206.

Q1: SIZE clause

As far as I guess, the <integer> of "SIZE <integer>" clause for the parameter method_opt of dbms_stats.gather_table_stats must be <number_of_distinct_column_values>+2, mustn't it?

Q2: height balanced - frequency balanced: how does deterministic Oracle choose which one to generate?

Am am asking Q1 because of the test bellow. My understanding is that until Oracle switched to frequency(?) histogram (which happened with SIZE 5 = 3(number of distinct values) + 2) the query (predicate "=" or "in") was a fullscan.

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;
/

select type, count(1) from test_cbo group by type order by 1;

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

begin sys.dbms_stats.gather_table_stats ('LTDLNE', 'TEST_CBO', method_opt=>'FOR COLUMNS SIZE 4 TYPE'); end; /

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

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

begin sys.dbms_stats.gather_table_stats ('LTDLNE', 'TEST_CBO', method_opt=>'FOR COLUMNS SIZE 5 TYPE'); 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'); explain plan for select * from TEST_CBO where type = '1';

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Laimutis Nedzinskas Sent: 29. júní 2006 18:57
To: oracle-l_at_freelists.org
Subject: 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


Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
--
http://www.freelists.org/webpage/oracle-l