The value of METHOD_OPT specifies which column related statistics will be gathered when calling one of the get_…_stats functions.
The only allowed value for global, schema database or dictionary level objects is FOR ALL …, according to the following EBNF:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
When calling gather_table_stats, the value of the parameter accepts either the FOR ALL … option or an option that more narrowly specifies the columns to be analyzed:
FOR COLUMNS [column_clause] [size_clause]
In other words, The FOR COLUMNS … option cannot be specified when calling either of
gather_dictionary_stats
gather_database_stats
gather_schema_stats
Apparently, the value of method_opt cannot contain new lines (chr(10)).
Column clause
column_clause is defined as
column_name | extension name | extension
SIZE clause
The size clause specifies the parameters for column-histogram collection.
size_clause is defined as
SIZE { nof_hist_buckets | REPEAT | AUTO | SKEWONLY }
nof_hist_buckets specifies the number of histogram buckets to be created and must be between 1 and 2048.
With SIZE 1, no histogram is created (as a histogram with one bucket is essentially meaningless).
REPEAT indicates that histograms must only be collected on columns that already have histograms.
AUTO lets Oracle decide whether histograms should be collected.
SKEWONLY also lets Oracle decide whether histograms should be collected based on the distribution found in the column.
Default value
The default value of method_opt is for all columns size auto
Automated example
The following simple example creates a table with skewed and uniform data in its columns. Some columns are indexed.
The PL/SQL gathers statistics for this table using different values for method_opt and displays the values found in user_tab_cols.
create table tq84_method_opt (
uniform number,
uniform_indexed number,
skewed number,
skewed_indexed number
);
insert into tq84_method_opt
select
level,
level,
case when level < 9900 then 42 else level end,
case when level < 9900 then 42 else level end
from
dual connect by level <= 10000;
create index tq84_method_opt_ix_1 on tq84_method_opt (uniform_indexed);
create index tq84_method_opt_ix_2 on tq84_method_opt (skewed_indexed );
declare
procedure gather(opt varchar2) is -- {
begin
dbms_stats.delete_table_stats(
ownname => user,
tabname =>'tq84_method_opt'
);
dbms_stats.gather_table_stats(
ownname => user,
tabname =>'tq84_method_opt',
method_opt => opt
);
dbms_output.put_line('');
dbms_output.put_line(upper(opt));
dbms_output.put_line(rpad('-', length(opt), '-'));
for s in (
select
column_name,
num_distinct,
num_buckets,
histogram
from
user_tab_cols
where
table_name = 'TQ84_METHOD_OPT'
order by
column_id
) loop
dbms_output.put_line(' ' ||
rpad ( s.column_name ,20) || ' ' ||
lpad (nvl(to_char(s.num_distinct), ' '), 5) || ' ' ||
lpad (nvl(to_char(s.num_buckets ), ' '), 3) || ' ' ||
s.histogram
);
end loop;
end gather; -- }
begin
gather('for all columns size auto ');
gather('for all indexed columns size auto ');
gather('for all columns size skewonly');
gather('for all indexed columns size skewonly');
gather('for all columns size 50 ');
gather('for all indexed columns size 50 ');
end;
/
drop table tq84_method_opt;
Output:
FOR ALL COLUMNS SIZE AUTO
-------------------------------------
UNIFORM 10000 1 NONE
UNIFORM_INDEXED 10000 1 NONE
SKEWED 102 1 NONE
SKEWED_INDEXED 102 1 NONE
FOR ALL INDEXED COLUMNS SIZE AUTO
-------------------------------------
UNIFORM NONE
UNIFORM_INDEXED 10000 1 NONE
SKEWED NONE
SKEWED_INDEXED 102 1 NONE
FOR ALL COLUMNS SIZE SKEWONLY
-------------------------------------
UNIFORM 10000 1 NONE
UNIFORM_INDEXED 10000 1 NONE
SKEWED 102 102 FREQUENCY
SKEWED_INDEXED 102 102 FREQUENCY
FOR ALL INDEXED COLUMNS SIZE SKEWONLY
-------------------------------------
UNIFORM NONE
UNIFORM_INDEXED 10000 1 NONE
SKEWED NONE
SKEWED_INDEXED 102 102 FREQUENCY
FOR ALL COLUMNS SIZE 50
-------------------------------------
UNIFORM 10000 50 HYBRID
UNIFORM_INDEXED 10000 50 HYBRID
SKEWED 102 50 TOP-FREQUENCY
SKEWED_INDEXED 102 50 TOP-FREQUENCY
FOR ALL INDEXED COLUMNS SIZE 50
-------------------------------------
UNIFORM NONE
UNIFORM_INDEXED 10000 50 HYBRID
SKEWED NONE
SKEWED_INDEXED 102 50 TOP-FREQUENCY
FOR ALL COLUMNS SIZE SKEWONLY
create table tq84_data (
flg varchar2(3) not null,
val number(4,1) not null,
txt varchar2(100) not null
);
insert into tq84_data
select
case when mod(level, 1000) = 0 then 'abc'
when mod(level, 23) = 0 then 'def'
else 'xyz'
end,
round(dbms_random.value(1, 100), 1),
rpad('*', 100, '*')
from
dual connect by level <= 10000;
begin
dbms_stats.gather_table_stats(user, 'tq84_data');
end;
/
column col format a3
column endval format a6
column densty format 9.9999
select
column_name col,
num_distinct distct,
density densty,
num_buckets bucks,
histogram histogram,
sample_size siz
from
user_tab_col_statistics
where
table_name = 'TQ84_DATA';
--
-- COL DISTCT DENSTY BUCKS HISTOGRAM SIZ
-- --- ---------- ------ ---------- --------------- ----------
-- FLG 3 .3333 1 NONE 10000
-- VAL 991 .0010 1 NONE 10000
-- TXT 1 1.0000 1 NONE 10000
select
column_name col,
endpoint_actual_value endval,
endpoint_number endum
from
user_tab_histograms
where
table_name = 'TQ84_DATA'
order by
col,
endval;
--
-- COL ENDVAL ENDUM
-- --- ------ ----------
-- FLG 0
-- FLG 1
-- TXT 1
-- TXT 0
-- VAL 1
-- VAL 0
begin
dbms_stats.gather_table_stats(
user,
'tq84_data',
method_opt=>'for all columns size skewonly'
);
end;
/
select
column_name col,
num_distinct distct,
density densty,
num_buckets bucks,
histogram histogram,
sample_size siz
from
user_tab_col_statistics
where
table_name = 'TQ84_DATA';
--
-- COL DISTCT DENSTY BUCKS HISTOGRAM SIZ
-- --- ---------- ------ ---------- --------------- ----------
-- FLG 3 .0001 3 FREQUENCY 10000
-- VAL 991 .0010 1 NONE 10000
-- TXT 1 .0001 1 FREQUENCY 10000
select
column_name col,
endpoint_actual_value endval,
endpoint_number endum
from
user_tab_histograms
where
table_name = 'TQ84_DATA'
order by
col,
endval;
--
-- COL ENDVAL ENDUM
-- --- ------ ----------
-- FLG abc 10
-- FLG def 444
-- FLG xyz 10000
-- TXT 10000
-- VAL 0
-- VAL 1
drop table tq84_data;