扩展统计信息测试

create table scott.test01 as select * from dba_objects;

update scott.test01 set object_name=object_type;
commit;

exec dbms_stats.gather_table_stats(‘SCOTT’,’TEST01′);

select table_name,num_rows from dba_tables where owner = ‘SCOTT’ and table_name = ‘TEST01′;

explain plan for select * from scott.test01 where object_name=’INDEX’ and object_type=’INDEX’;

select * from table(dbms_xplan.display());

select rpad(column_name, 30, ‘ ‘) column_name,
rpad(num_distinct, 8, ‘ ‘) num_distinct,
rpad(utl_raw.cast_to_varchar2(low_value), 15, ‘ ‘) low_value,
rpad(utl_raw.cast_to_varchar2(high_value), 10, ‘ ‘) high_value,
rpad(num_nulls, 8, ‘ ‘) num_nulls,
rpad(avg_col_len, 6, ‘ ‘) avg_col_len,
rpad(density, 20, ‘ ‘) density,
histogram
from dba_tab_col_statistics
where owner = ‘SCOTT’
and table_name = ‘TEST01’
and column_name in (‘OBJECT_NAME’, ‘OBJECT_TYPE’);

exec dbms_stats.gather_table_stats(‘SCOTT’,’TEST01′,method_opt =>’for columns (object_name,object_type)’);

explain plan for select * from scott.test01 where object_name=’INDEX’ and object_type=’INDEX’;

select * from table(dbms_xplan.display());

select count(*) from scott.test01 where object_name=’INDEX’ and object_type=’INDEX’;

— 在前面收集统计信息直方图的时候也可以建立
select dbms_stats.create_extended_stats(‘scott’,’test01′,'(object_name,object_type)’)from dual ;

select * from dba_tab_cols a where table_name=’TEST01′;

begin
dbms_stats.drop_extended_stats(
ownname=>’SCOTT’,tabname=>’TEST01′,extension=>'(object_name,object_type)’
);
end;