RE: full-scan vs index for "small" tables
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Jun 2006 11:07:46 -0000
>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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Laimutis Nedzinskas
Sent: 29. júní 2006 18:57
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: full-scan vs index for "small" tables
>From: Wolfgang Breitling [mailto:breitliw@xxxxxxxxxxxxx] 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
--
//www.freelists.org/webpage/oracle-l
Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
--
//www.freelists.org/webpage/oracle-l
Other related posts: