meta_with_table
罗海雄
142次下载
898次浏览
2019-07-29
5

脚本内容

--输入表名,获取该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 /

评论

贡献排行榜