--表相关的统计信息
--包含分区、索引、索引字段
--先替换掉下面define值
define owner=STEVEN
define table_name=AWEN_OGG_TEST
--先替换掉上面define值
set linesize 160
col DATA_TYPE for a15
set pagesize 10000
col COLUMN_NAME for a30
col col for a30
select TABLE_NAME,    NUM_ROWS,    BLOCKS,    EMPTY_BLOCKS,    CHAIN_CNT,    AVG_ROW_LEN,    GLOBAL_STATS,    SAMPLE_SIZE,   to_char(t.last_analyzed,'MM-DD-YYYY') from dba_tables t where    owner = upper('&owner') and table_name = upper('&table_name');
select COLUMN_NAME, DATA_TYPE,   NUM_DISTINCT,    DENSITY,    NUM_BUCKETS,    NUM_NULLS,    SAMPLE_SIZE,    to_char(t.last_analyzed,'MM-DD-YYYY') from dba_tab_columns t where   owner = upper('&owner') and table_name = upper('&table_name');
select INDEX_NAME,    BLEVEL BLev,    LEAF_BLOCKS,    DISTINCT_KEYS,    NUM_ROWS,    AVG_LEAF_BLOCKS_PER_KEY,    AVG_DATA_BLOCKS_PER_KEY,    CLUSTERING_FACTOR,    to_char(t.last_analyzed,'MM-DD-YYYY') from    dba_indexes t where    table_name =  upper('&table_name') and table_owner = upper('&owner');
select /*+ first_rows use_nl(i,t)*/ 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.index_owner=t.owner and     i.table_name = upper('&table_name') and i.index_owner = upper('&owner') and i.table_name = t.table_name and i.column_name = t.column_name order by index_name,column_position;

--收集统计信息
exec dbms_stats.gather_table_stats('STEVEN','AWEN_OGG_TEST',degree=>10,cascade=> TRUE,no_invalidate=>false);