查询对象统计信息相关SQL

第一，表统计信息 --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');