RE: full-scan vs index for "small" tables
- From: "Laimutis Nedzinskas" <Laimutis.Nedzinskas@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Jun 2006 18:57:21 -0000
>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
Other related posts:
