meta_with_table
emcs
42次下载
298次浏览
2019-07-24
4.5

脚本内容

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

评论

贡献排行榜