--输入表名，获取该SQL相关的表及索引、栏位等信息
  set lines 200 pages 200
  break on t_name
  col t_name for a32
  col i_name for a24
  col c_name for a24     
  col c_type for a8
  col last_ana for a10  
  col t_blk  for a7
  col t_row  for a7
  col C_NDV  for a7
  col C_NUL  for a7
  col I_BLK  for a7
  col I_NDV  for a7
  col pos    for 999
  with table_list as
  (select  /*+ materialize */ OWNER table_owner, table_name
   from dba_tables
  where  table_name = upper(trim('&tname'))
  and nvl('&owner',owner) = owner
  ),t as (select /*+ materialize */ owner,t.table_name,num_rows,last_analyzed ,blocks
  from dba_tables t, table_list p
  where t.owner = p.table_owner  and t.table_name = p.table_name
  ) ,
  ic as (select /*+ materialize */ ic.table_owner,ic.table_name,column_name,index_owner,index_name,column_position  
  from dba_ind_columns ic, table_list p
  where ic.table_owner = p.table_owner  and ic.table_name = p.table_name
  ) ,
  tc as (select  /*+ materialize */table_owner,tc.table_name,column_name,num_distinct,num_nulls,substr(DATA_TYPE,1,8) data_type,tc.density
  from dba_tab_columns tc, table_list p
  where tc.owner = p.table_owner  and tc.table_name = p.table_name
  union all
  select ic.table_owner,ic.table_name,ic.column_name,ic2.num_distinct,ic2.num_nulls,'FUNCTION' ,ic2.density
  from ic,dba_tab_col_statistics ic2
  where ic.column_name like 'SYS_NC%'
  and ic.table_name = ic2.table_name(+)
  and ic.table_owner = ic2.owner(+)
  and ic.column_name = ic2.column_name(+)
  ) ,
  i as (select  /*+ materialize */i.table_owner,i.table_name,index_name,owner,distinct_keys,leaf_blocks
  from dba_indexes i, table_list p
  where i.table_owner = p.table_owner  and i.table_name = p.table_name
  )
  select  /*+ordered use_hash(t tc)*/ 
  t.owner||'.'||t.table_name      t_name,
  trim(to_char(t.blocks,case when t.blocks<1e4 then '9,999' else '9.9EEEE' end))  t_blk,
  trim(to_char(t.num_rows,case when t.num_rows<1e4 then '9,999' else '9.9EEEE' end))  t_row,
  i.index_name i_name      ,
  trim(to_char(i.leaf_blocks,case when i.leaf_blocks<1e4 then '9,999' else '9.9EEEE' end))  I_BLK,
  trim(to_char(i.distinct_keys,case when i.distinct_keys<1e4 then '9,999' else '9.9EEEE' end))  I_NDV,
  ic.column_position pos,
  tc.column_name c_name     ,
  tc.data_type c_type,
  trim(to_char(tc.num_distinct,case when tc.num_distinct<1e4 then '9,999' else '9.9EEEE' end))  C_NDV,
  trim(to_char(tc.num_NULLS,case when tc.num_NULLS<1e4 then '9,999' else '9.9EEEE' end))  C_NUL,
  trim(to_char(tc.density,case when tc.density>1e-3 then '0.999' else '9.9EEEE' end))  C_SEL,
  trim(to_char(t.last_analyzed,'YYYYMMDD')) as last_ANA   
   from   t ,  tc ,  ic,  i
   where tc.table_owner = t.owner and tc.table_name = t.table_name
   and ic.table_owner(+) = tc.table_owner and ic.table_name(+) = tc.table_name and ic.column_name(+)=tc.column_name
   and i.table_owner(+) = ic.table_owner and i.table_name(+) = ic.table_name and i.index_name(+)=ic.index_name
   order by t.owner,t.table_name,i.index_name,column_position,num_distinct desc
  /