--输入表名，获取该SQL相关的表及索引、栏位等信息
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
from dba_tab_columns tc, table_list p
where tc.owner = p.table_owner  and tc.table_name = p.table_name
union all
select table_owner,table_name,column_name,null,null
from ic
where column_name like 'SYS_NC%'
) ,
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)*/
owner           ,
table_name      ,
blocks          ,
num_rows        ,
index_name      ,
leaf_blocks     LEAF_B,
distinct_keys   INXNDV,
column_position pos,
column_name     ,
num_distinct    COLNDV,
num_nulls       NULLS,
last_analyzed   
from
(
select  /*+ordered use_hash(t tc)*/ 
t.owner,
t.table_name ,
t.num_rows,
t.blocks,
i.index_name ,
i.distinct_keys ,
i.leaf_blocks,
ic.column_position,
tc.column_name,
tc.num_distinct ,
tc.num_nulls ,
t.last_analyzed
 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
)
/