sql分析常用查询

--表信息--

select owner,

       table_name,

       tablespace_name,

       num_rows,/*表数据量*/

       temporary,/*是否是临时表*/

       degree,/*并行度*/

       blocks,/*快的数量*/

       empty_blocks,/*空块的数量*/

       last_analyzed,/*统计信息更新时间*/

       PARTITIONED/*是否分区*/

  from dba_tables

 where table_name = 'DEPT'

 

 

 --表索引信息--

 select UNIQUENESS,

        TABLESPACE_NAME,

        index_type,

        BLEVEL,

        LEAF_BLOCKS,

        distinct_keys,

        CLUSTERING_FACTOR,--等于表得块数good 等于表得行数bad

        NUM_ROWS,

        LAST_ANALYZED,

        PARTITIONED,

        b.COLUMN_NAME,

        b.COLUMN_POSITION,

        STATUS

   from dba_indexes  a, dba_ind_columns b

  where a.owner = b.INDEX_OWNER

    and a.index_name = b.INDEX_NAME

    and a.table_name='DEPT'

 

--表字段信息    

select OWNER,

       TABLE_NAME,

       COLUMN_NAME,

       NUM_DISTINCT,

       NUM_NULLS,

       LAST_ANALYZED,

       HISTOGRAM

  from dba_tab_col_statistics

 where table_name = 'DEPT'

 

--表分区字段信息

select owner, name, column_name, column_position

  from dba_part_key_columns

 where name = 'DEPT'

 

--分区表分区类型

select owen, table_name, partition_type, subpartition_type

  from dba_part_tables

 

--表分区统计详细

select table_owner,

       table_name,

       partition_name,

       num_rows,

       blocks,

       empty_blocks,

       LAST_ANALYZED

  from dba_tab_partitions

 where table_name = 'DEPT';

 

 --二级分区表统计详细

 select table_owner,

       table_name,

       partition_name,

       num_rows,

       blocks,

       empty_blocks,

       LAST_ANALYZED

  from dba_tab_subpartitions

 where table_name = 'DEPT';

 

--分区索引统计信息

 select INDEX_OWNER,

        INDEX_NAME,

        PARTITION_NAME,

        BLEVEL,

        DISTINCT_KEYS,

        NUM_ROWS,

        LAST_ANALYZED,

        STATUS

   from dba_ind_partitions

  where index_name = 'xxx'

  

--分区索引分区类型

select owner, index_name, partition_type, locality,

  from dba_part_indexes

 where index_name = 'xxx'