begin dbms_stats.flush_database_monitoring_info; end;
查询指定用户指定表出现在where条件中的列信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
select r.name as owner, o.name as table_name, c.name as column_name, u.equality_preds, -- 等值过滤 u.equijoin_preds, -- 等值join,ex: where a.id=b.id u.nonequijoin_preds, -- 不等于join u.range_preds, -- 返回过滤次数 u.like_preds, -- like过滤 u.null_preds, -- null过滤 u.timestamp from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r where u.obj# = o.obj# and u.obj# = c.obj# and u.intcol# = c.col# and r.name = 'RETAIL' and o.name = 'CON_CONTRACT'
查询指定用户指定表的索引信息
1 2 3 4
select c.table_owner, c.table_name, c.column_name, c.index_name from dba_ind_columns c where c.table_owner = 'RETAIL' and c.table_name = 'CON_CONTRACT';
指定用户指定表的选择性大于20%的列
1 2 3 4 5 6 7 8
select a.owner, a.table_name, a.column_name, round(a.num_distinct / b.num_rows * 100, 2) selectivity from dba_tab_col_statistics a, dba_tables b where a.owner = b.owner and a.table_name = b.table_name and a.owner = 'RETAIL' and a.table_name = 'CON_CONTRACT' and a.num_distinct / b.num_rows >= 0.2;
select t1.owner, t1.table_name, t1.column_name, t1.num_rows, t1.Cardinality, t1.selectivity, 'Need index'asnotice from (select b.owner, a.table_name, a.column_name, b.num_rows, a.num_distinct Cardinality, round(a.num_distinct / b.num_rows * 100, 2) selectivity from dba_tab_col_statistics a, dba_tables b where a.owner = b.owner and a.table_name = b.table_name and a.owner = 'RETAIL' and a.table_name = 'CON_CONTRACT') t1 where t1.selectivity >= 20 andnotexists(select1 from dba_ind_columns t2 where t2.table_owner = t1.owner and t2.table_name = t1.table_name and t2.column_name = t1.column_name) andexists(select1 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r where o.obj# = u.obj# and c.obj# = u.obj# and c.col# = u.intcol# and r.name = t1.owner and o.name = t1.table_name and c.name = t1.column_name);
select a.owner, a.table_name, a.column_name, b.num_rows, a.num_distinct, trunc(a.num_distinct / b.num_rows * 100, 2) selectivity, 'Need Gather Histogram'notice from dba_tab_col_statistics a, dba_tables b where a.owner = 'RETAIL' and a.table_name = 'CON_CONTRACT' and a.owner = b.owner and a.table_name = b.table_name and a.num_distinct / b.num_rows < 0.01 and (a.owner, a.table_name, a.column_name) in (select r.name owner, o.name table_name, c.name column_name from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r where o.obj# = u.obj# and c.obj# = u.obj# and c.col# = u.intcol# and r.name = a.owner and o.name = a.table_name) and a.histogram = 'NONE';