精华内容
下载资源
问答
  • 查看表的统计信息

    2021-01-22 17:41:44
    --构造测试环境 CREATE TABLE TEST_TJ(ID INT,AGE INT); BEGIN FOR I IN 1..100000 LOOP INSERT INTO TEST_TJ VALUES(MOD(I,9700),TRUNC(RAND * 120));...DBMS_STATS 包中包含过程和函数如下详细介绍: 1. COLUMN_S

    --构造测试环境
     

    CREATE TABLE TEST_TJ(ID INT,AGE INT);
    BEGIN FOR I IN 1..100000 LOOP
    INSERT INTO TEST_TJ VALUES(MOD(I,9700),TRUNC(RAND * 120));
    END LOOP;
    COMMIT;
    END;
    
    DBMS_STATS.COLUMN_STATS_SHOW(USER, 'TEST_TJ','AGE');

    DBMS_STATS 包中包含的过程和函数如下详细介绍:
    1. COLUMN_STATS_SHOW
    根据模式名,表名和列名获得该列的统计信息。返回两个结果集:一个是列的统计信息;
    另一个是直方图的统计信息。
    语法如下:
    PROCEDURE COLUMN_STATS_SHOW (
    OWNNAME IN VARCHAR(128),
    TABNAME IN VARCHAR(128),
    COLNAME IN VARCHAR(128)
    );
    列的统计信息和直方图的统计信息,格式分别如下:
    表 列统计信息
    名称  解释
    NUM_DISTINCT  不同列值的个数
    LOW_VALUE  列最小值
    HIGH_VALUE  列最大值
    NUM_NULLS  空值的个数
    NUM_BUCKETS  直方图桶的个数
    SAMPLE_SIZE  样本容量
    HISTOGRAM  直方图的类型
    表 直方图的统计信息
    名称  解释
    OWNER  模式名
    TABLE_NAME  表名
    COLUMN_NAME  列名
    HISTOGRAM  直方图类型
    ENDPOINT_VALUE  样本值
    ENDPOINT_HEIGHT
    对于频率直方图,样本值的个数;对于等高直方图,
    小于样本值大于前一个样本值的个数。
    ENDPOINT_KEYGHT
    对于频率直方图无效;对于等高直方图,样本值的
    个数。
    ENDPOINT_DISTINCT
    对于频率直方图无效;对于等高直方图,小于样本
    值大于前一个样本值之间不同样本的个数。
    参数详解
      OWNNAME 模式名,区分大小写。
      TABNAME 表名,区分大小写。
      COLNAME 列名,区分大小写。

    DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST_TJ',null,100,false, 'FOR ALL COLUMNS SIZE AUTO'); --更新所有列
    

    展开全文
  • 在前面的文章中,我们了解在Trafodion中可以使用”update statistics”语句可以在表上更新统计信息,以保证执行计划的准确性,同样的,我们也可以使用”showstats”命令来查看表的统计信息。 关于”showstats”的...

    在前面的文章中,我们了解在Trafodion中可以使用”update statistics”语句可以在表上更新统计信息,以保证执行计划的准确性,同样的,我们也可以使用”showstats”命令来查看表上的统计信息。
    关于”showstats”的语法,请参考http://trafodion.incubator.apache.org/docs/sql_reference/index.html#_showstats_statement
    showstats可以基于表上的一个字段或多个字段,也可以是字段的组合,或者是key相关的字段。具体语法在此不详细介绍,下面列举几个常用的showstats的例子,

    1 表上没有统计信息时,显示”No Histograms”

    >>showstats for table test on every column;
    
    Histogram data for Table TRAFODION.SEABASE.TEST
    Table ID: 4493039957384952556
    
       Hist ID # Ints    Rowcount         UEC Colname(s)
    ========== ====== =========== =========== ===========================
    
    No Histograms exist for the requested columns or groups
    
    
    --- SQL operation complete.

    2 显示表统计信息概括

    >>showstats for table test on every column;
    
    Histogram data for Table TRAFODION.SEABASE.TEST
    Table ID: 4493039957384952556
    
       Hist ID # Ints    Rowcount         UEC Colname(s)
    ========== ====== =========== =========== ===========================
     799435282      1           1           1 SYSKEY
     799435277      1           1           1 A
     799435272      1           1           1 B
    
    
    --- SQL operation complete.

    3 显示表统计信息详细,带后缀”detail”

    >>showstats for table test on every column detail;
    
    Detailed Histogram data for Table TRAFODION.SEABASE.TEST
    Table ID: 4493039957384952556
    
    Hist ID:    799435282
    Column(s):  SYSKEY
    Total Rows: 1
    Total UEC:  1
    Low Value:  (2259254545988670607)
    High Value: (2259254545988670607)
    Intervals:  1
    
    Number    Rowcount         UEC Boundary
    ====== =========== =========== ======================================
         0           0           0 (2259254545988670607)
         1           1           1 (2259254545988670607)
    
    
    Hist ID:    799435277
    Column(s):  A
    Total Rows: 1
    Total UEC:  1
    Low Value:  ('ab')
    High Value: ('ab')
    Intervals:  1
    
    Number    Rowcount         UEC Boundary
    ====== =========== =========== ======================================
         0           0           0 ('ab')
         1           1           1 ('ab')
    
    
    Hist ID:    799435272
    Column(s):  B
    Total Rows: 1
    Total UEC:  1
    Low Value:  ('a
    b')
    High Value: ('a
    b')
    Intervals:  1
    
    Number    Rowcount         UEC Boundary
    ====== =========== =========== ======================================
         0           0           0 ('a
    b')
         1           1           1 ('a
    b')
    
    
    --- SQL operation complete.
    展开全文
  • 此脚本可以统计表的统计信息,做成.SQL文件在SQLPLUS中执行,会让你输入SCHEMA和TABLENAME[@more@]set echo offset scan onset lines 150set pages 66set...
    此脚本可以统计表的统计信息,做成.SQL文件在SQLPLUS中执行,会让你输入SCHEMA和TABLENAME[@more@]

    set echo off
    set scan on
    set lines 150
    set pages 66
    set verify off
    set feedback off
    set termout off
    column uservar new_value Table_Owner noprint
    select user uservar from dual;
    set termout on
    column TABLE_NAME heading "Tables owned by &Table_Owner" format a30
    select table_name from dba_tables where owner=upper('&Table_Owner') order by 1
    /
    undefine table_name
    undefine owner
    prompt
    accept owner prompt 'Please enter Name of Table Owner (Null = &Table_Owner): '
    accept table_name prompt 'Please enter Table Name to show Statistics for: '
    column TABLE_NAME heading "Table|Name" format a15
    column PARTITION_NAME heading "Partition|Name" format a15
    column SUBPARTITION_NAME heading "SubPartition|Name" format a15
    column NUM_ROWS heading "Number|of Rows" format 9,999,999,990
    column BLOCKS heading "Blocks" format 999,990
    column EMPTY_BLOCKS heading "Empty|Blocks" format 999,999,990

    column AVG_SPACE heading "Average|Space" format 9,990
    column CHAIN_CNT heading "Chain|Count" format 999,990
    column AVG_ROW_LEN heading "Average|Row Len" format 990
    column COLUMN_NAME heading "Column|Name" format a25
    column NULLABLE heading Null|able format a4
    column NUM_DISTINCT heading "Distinct|Values" format 999,999,990
    column NUM_NULLS heading "Number|Nulls" format 9,999,990
    column NUM_BUCKETS heading "Number|Buckets" format 990
    column DENSITY heading "Density" format 990
    column INDEX_NAME heading "Index|Name" format a15
    column UNIQUENESS heading "Unique" format a9
    column BLEV heading "B|Tree|Level" format 90
    column LEAF_BLOCKS heading "Leaf|Blks" format 990
    column DISTINCT_KEYS heading "Distinct|Keys" format 9,999,999,990
    column AVG_LEAF_BLOCKS_PER_KEY heading "Average|Leaf Blocks|Per Key" format 99,990
    column AVG_DATA_BLOCKS_PER_KEY heading "Average|Data Blocks|Per Key" format 99,990
    column CLUSTERING_FACTOR heading "Cluster|Factor" format 999,999,990
    column COLUMN_POSITION heading "Col|Pos" format 990
    column col heading "Column|Details" format a24
    column COLUMN_LENGTH heading "Col|Len" format 9,990
    column GLOBAL_STATS heading "Global|Stats" format a6
    column USER_STATS heading "User|Stats" format a6
    column SAMPLE_SIZE heading "Sample|Size" format 9,999,999,990
    column to_char(t.last_analyzed,'MM-DD-YYYY') heading "Date|MM-DD-YYYY" format a10

    prompt
    prompt ***********
    prompt Table Level
    prompt ***********
    prompt
    select
    TABLE_NAME,
    NUM_ROWS,
    BLOCKS,
    EMPTY_BLOCKS,
    AVG_SPACE,
    CHAIN_CNT,
    AVG_ROW_LEN,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    to_char(t.last_analyzed,'MM-DD-YYYY')
    from dba_tables t
    where
    owner = upper(nvl('&&Owner',user))
    and table_name = upper('&&Table_name')
    /
    select
    COLUMN_NAME,
    decode(t.DATA_TYPE,
    'NUMBER',t.DATA_TYPE||'('||
    decode(t.DATA_PRECISION,
    null,t.DATA_LENGTH||')',
    t.DATA_PRECISION||','||t.DATA_SCALE||')'),
    'DATE',t.DATA_TYPE,
    'LONG',t.DATA_TYPE,
    'LONG RAW',t.DATA_TYPE,
    'ROWID',t.DATA_TYPE,
    'MLSLABEL',t.DATA_TYPE,
    t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
    decode(t.nullable,
    'N','NOT NULL',
    'n','NOT NULL',
    NULL) col,
    NUM_DISTINCT,
    DENSITY,
    NUM_BUCKETS,
    NUM_NULLS,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    to_char(t.last_analyzed,'MM-DD-YYYY')
    from dba_tab_columns t
    where
    table_name = upper('&Table_name')
    and owner = upper(nvl('&Owner',user))
    /

    select
    INDEX_NAME,
    UNIQUENESS,
    BLEVEL BLev,
    LEAF_BLOCKS,
    DISTINCT_KEYS,
    NUM_ROWS,
    AVG_LEAF_BLOCKS_PER_KEY,
    AVG_DATA_BLOCKS_PER_KEY,
    CLUSTERING_FACTOR,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    to_char(t.last_analyzed,'MM-DD-YYYY')
    from
    dba_indexes t
    where
    table_name = upper('&Table_name')
    and table_owner = upper(nvl('&Owner',user))
    /
    break on index_name
    select
    i.INDEX_NAME,
    i.COLUMN_NAME,
    i.COLUMN_POSITION,
    decode(t.DATA_TYPE,
    'NUMBER',t.DATA_TYPE||'('||
    decode(t.DATA_PRECISION,
    null,t.DATA_LENGTH||')',
    t.DATA_PRECISION||','||t.DATA_SCALE||')'),
    'DATE',t.DATA_TYPE,
    'LONG',t.DATA_TYPE,
    'LONG RAW',t.DATA_TYPE,
    'ROWID',t.DATA_TYPE,
    'MLSLABEL',t.DATA_TYPE,
    t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
    decode(t.nullable,
    'N','NOT NULL',
    'n','NOT NULL',
    NULL) col
    from
    dba_ind_columns i,
    dba_tab_columns t
    where
    i.table_name = upper('&Table_name')
    and owner = upper(nvl('&Owner',user))
    and i.table_name = t.table_name
    and i.column_name = t.column_name
    order by index_name,column_position
    /

    prompt
    prompt ***************
    prompt Partition Level
    prompt ***************

    select
    PARTITION_NAME,
    NUM_ROWS,
    BLOCKS,
    EMPTY_BLOCKS,
    AVG_SPACE,
    CHAIN_CNT,
    AVG_ROW_LEN,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    to_char(t.last_analyzed,'MM-DD-YYYY')
    from
    dba_tab_partitions t
    where
    table_owner = upper(nvl('&&Owner',user))
    and table_name = upper('&&Table_name')
    order by partition_position
    /


    break on partition_name
    select
    PARTITION_NAME,
    COLUMN_NAME,
    NUM_DISTINCT,
    DENSITY,
    NUM_BUCKETS,
    NUM_NULLS,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    to_char(t.last_analyzed,'MM-DD-YYYY')
    from
    dba_PART_COL_STATISTICS t
    where
    table_name = upper('&Table_name')
    and owner = upper(nvl('&Owner',user))
    /

    break on partition_name
    select
    t.INDEX_NAME,
    t.PARTITION_NAME,
    t.BLEVEL BLev,
    t.LEAF_BLOCKS,
    t.DISTINCT_KEYS,
    t.NUM_ROWS,
    t.AVG_LEAF_BLOCKS_PER_KEY,
    t.AVG_DATA_BLOCKS_PER_KEY,
    t.CLUSTERING_FACTOR,
    t.GLOBAL_STATS,
    t.USER_STATS,
    t.SAMPLE_SIZE,
    to_char(t.last_analyzed,'MM-DD-YYYY')
    from
    dba_ind_partitions t,
    dba_indexes i
    where
    i.table_name = upper('&Table_name')
    and i.table_owner = upper(nvl('&Owner',user))
    and i.owner = t.index_owner
    and i.index_name=t.index_name
    /


    prompt
    prompt ***************
    prompt SubPartition Level
    prompt ***************

    select
    PARTITION_NAME,
    SUBPARTITION_NAME,
    NUM_ROWS,
    BLOCKS,
    EMPTY_BLOCKS,
    AVG_SPACE,
    CHAIN_CNT,
    AVG_ROW_LEN,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    to_char(t.last_analyzed,'MM-DD-YYYY')
    from
    dba_tab_subpartitions t
    where
    table_owner = upper(nvl('&&Owner',user))
    and table_name = upper('&&Table_name')
    order by SUBPARTITION_POSITION
    /
    break on partition_name
    select
    p.PARTITION_NAME,
    t.SUBPARTITION_NAME,
    t.COLUMN_NAME,
    t.NUM_DISTINCT,
    t.DENSITY,
    t.NUM_BUCKETS,
    t.NUM_NULLS,
    t.GLOBAL_STATS,
    t.USER_STATS,
    t.SAMPLE_SIZE,
    to_char(t.last_analyzed,'MM-DD-YYYY')
    from
    dba_SUBPART_COL_STATISTICS t,
    dba_tab_subpartitions p
    where
    t.table_name = upper('&Table_name')
    and t.owner = upper(nvl('&Owner',user))
    and t.subpartition_name = p.subpartition_name
    and t.owner = p.table_owner
    and t.table_name=p.table_name
    /

    break on partition_name
    select
    t.INDEX_NAME,
    t.PARTITION_NAME,
    t.SUBPARTITION_NAME,
    t.BLEVEL BLev,
    t.LEAF_BLOCKS,
    t.DISTINCT_KEYS,
    t.NUM_ROWS,
    t.AVG_LEAF_BLOCKS_PER_KEY,
    t.AVG_DATA_BLOCKS_PER_KEY,
    t.CLUSTERING_FACTOR,
    t.GLOBAL_STATS,
    t.USER_STATS,
    t.SAMPLE_SIZE,
    to_char(t.last_analyzed,'MM-DD-YYYY')
    from
    dba_ind_subpartitions t,
    dba_indexes i
    where
    i.table_name = upper('&Table_name')
    and i.table_owner = upper(nvl('&Owner',user))
    and i.owner = t.index_owner
    and i.index_name=t.index_name
    /

    clear breaks
    set echo on

    来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7490392/viewspace-1023627/,如需转载,请注明出处,否则将追究法律责任。

    转载于:http://blog.itpub.net/7490392/viewspace-1023627/

    展开全文
  • sp_autostats 'table_name'; ...
    sp_autostats 'table_name';

    来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15747463/viewspace-742387/,如需转载,请注明出处,否则将追究法律责任。

    转载于:http://blog.itpub.net/15747463/viewspace-742387/

    展开全文
  • DBA_TABLES:查看表的统计信息 DBA_TAB_PARTITIONS:查看分区的统计信息 DBA_TAB_SUBPARTITIONS:查看子分区的统计信息 上述表中统计信息核心字段: NUM_ROWS:表的行数,目标表的行数是计算Cardinality的基础,而...
  • 查看表的统计信息

    2015-01-29 17:21:59
    查看表的统计信息 SQL> select table_name,NUM_ROWS, BLOCKS ,to_char(LAST_ANALYZED,'yyyymmdd hh24:mi:ss') from dba_tables where table_name='TSM_RATECOUNT'; TABLE_NAME NUM_ROWS
  • 转载 查看Oracle中表的统计信息

    千次阅读 2019-07-03 08:41:10
    oracle数据库在执行sql语句的时候,会根据统计信息计算怎样执行...1 查看当前用户下的统计信息 select * from user_tab_statistics t where t.table_name=upper('l_dm_allot_detail'); 2 查看dba用户下的统计信...
  • 如何查看表和索引的统计信息 原文:如何查看表和索引的统计信息  这几天要求做一个服务器的统计信息,主要针对表和索引。下面我就简单分享几个查询数据表和索引统计信息的方法: 1.使用T-SQL 语句...
  • 查看Oracle中表的统计信息

    千次阅读 2013-11-06 17:42:44
    oracle数据库在执行sql语句的时候,会根据统计信息计算怎样执行性能更优...1 查看当前用户下的统计信息 select * from user_tab_statistics t where t.table_name=upper('l_dm_allot_detail'); 2 查看dba用户下的统计信
  • oracle查看和更新统计表的信息

    千次阅读 2018-09-25 11:21:46
    在OEM中查看数据库会话时,发现有一个会话SQL要10多秒 在PL SQL中执行 SQL按F5查看这个SQL计划,发现别地方有 调用索引, oracle统计查看 select * from dba_tables where table_name = TABLE 检查一下...
  • 查看Oracle数据库中表的历次统计信息收集的sql
  • TBS_STAT空间统计信息 https://blog.csdn.net/u013991521/article/details/79293706 SELECT a.tablespace_name ,total / (1024 * 1024 * 1024) total,free / (1024 * 1024 * 1024) free,(total - free) / ...
  • MySQL查看表信息 InnoDB存储引擎的统计数据收集策略 ​​​​​​​ MySQL查看表信息 一、查看表信息 show table status like '表名'; ​​​​​​​ 打印出的字段描述 Name 表名; Engine 表的...
  • 在OEM中查看数据库会话时,发现有一个会话SQL要10多秒 在PL SQL中执行 SQL按F5查看这个SQL计划,发现别地方有 调用索引, oracle统计查看 select * from dba_tables where table_name = TABLE 检查一下...
  • 通过PROJECTION_COLUMNS的statistics_type查看表的的统计信息类型,和收集时间SELECT projection_name,projection_column_name, statistics_type,statistics_updated_timestamp, column_positionFROM PROJECTION_...
  • 脚本可以很方便地看到中每个字段的统计,优化器生成执行计划的时候也是看这个。 SQL> @d:/stat_sanity.sql pub_user COLUMN_NAME DISTINCT DENSITY NULLS BKTS LO HI ------------------------------ -----
  • 查看当前库中统计信息过期的表: set linesize 150 set pagesize 1000 EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; SELECT OWNER, TABLE_NAME, PARTITION_NAME, OBJECT_TYPE, STALE_STATS, LAST_...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,733
精华内容 693
关键字:

查看表的统计信息