第一,表统计信息
--1.1批量查看表统计信息
select owner,table_name,object_type,num_rows,last_analyzed
from dba_tables
where
(owner = 'FWY' and table_name = 'T1') or
(owner = 'FWY' and table_name = 'T2') or
(owner = 'FWY' and table_name = 'T3') or
(owner = 'FWY' and table_name = 'T4');
--1.2批量查看更详细的表统计信息(10g以上)
select *
from dba_tab_statistics
where
(owner = 'FWY' and table_name = 'T1') or
(owner = 'FWY' and table_name = 'T2') or
(owner = 'FWY' and table_name = 'T3') or
(owner = 'FWY' and table_name = 'T4');
--BLOCKS 该对象的段的高水位线以下的数据块。
--EMPTY_BLOCKS 高水位线以上的。
--SAMPLE_SIZE 收集的统计信息行数
第二,索引统计信息
--2.1 批量查看索引统计信息
select owner,index_name,table_owner,table_name,uniqueness,compression,blevel,leaf_blocks,distinct_keys,status,num_rows,last_analyzed
from dba_indexes
where
where
(owner = 'FWY' and table_name = 'T1') or
(owner = 'FWY' and table_name = 'T2') or
(owner = 'FWY' and table_name = 'T3') or
(owner = 'FWY' and table_name = 'T4');
--2.2 批量查看索引统计信息(10g以上)
select *
where
(owner = 'FWY' and table_name = 'T1') or
(owner = 'FWY' and table_name = 'T2') or
(owner = 'FWY' and table_name = 'T3') or
(owner = 'FWY' and table_name = 'T4');
第二,列统计信息(包含直方图)
--查看列统计信息
select t2.owner,
t2.table_name,
t2.column_name,
t2.LAST_ANALYZED,
t1.num_distinct,
t1.low_value,
t1.high_value,
t1.DENSITY as "密度",
t1.num_nulls,
t1.avg_col_len,
--t1.histogram,--9i的没有这个
t1.num_buckets
from dba_tab_col_statistics t1 right join dba_tab_columns t2
on t1.owner=t2.owner and t1.table_name=t2.table_name and t1.column_name=t2.column_name
where
(owner = 'FWY' and table_name = 'T1') or
(owner = 'FWY' and table_name = 'T2') or
(owner = 'FWY' and table_name = 'T3') or
(owner = 'FWY' and table_name = 'T4');