查询对象统计信息相关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');