抓出where使用且未建索引且指定选择性的列

/*

1. 抓出列选择性>=20 的列

2. 这个列没有建立索引

3. 这个列出现在where条件中

*/

这里面的owner值自己改改。(师从落落，请勿喷我 )

WITH u AS
 (SELECT 'NORMPHONE' owner FROM dual)
SELECT a.owner,
       a.table_name,
       a.column_name,
       a.selectivity,
       b.owner,
       b.table_name,
       b.column_name,
       c.owner,
       c.table_name,
       c.column_name
FROM   (SELECT a.owner,
               a.table_name,
               a.column_name,
               b.num_rows,
               a.num_distinct cardinality,
               decode(b.num_rows,
                      NULL,
                      0,
                      0,
                      0,
                      round(a.num_distinct / b.num_rows * 100, 2)) selectivity,
               a.histogram,
               a.num_buckets
        FROM   dba_tab_col_statistics a, dba_tables b, u
        WHERE  a.owner = b.owner
               AND a.owner = u.owner
               AND a.table_name = b.table_name) a
LEFT   JOIN (SELECT r.name            owner,
                    o.name            table_name,
                    c.name            column_name,
                    equality_preds, ---等值过滤
                    equijoin_preds, ---等值JOIN过滤 比如where a.id=b.id
                    nonequijoin_preds, ----不等JOIN过滤
                    range_preds, ----范围过滤 &get; &get;= < <= between and
                    like_preds, ----LIKE过滤
                    null_preds, ----NULL 过滤
                    TIMESTAMP
             FROM   sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r, u
             WHERE  o.obj# = u.obj#
                    AND c.obj# = u.obj#
                    AND c.col# = u.intcol#
                    AND o.owner# = r.user#
                    AND r.name = u.owner) b
ON     a.owner = b.owner
       AND a.table_name = b.table_name
       AND a.column_name = b.column_name
LEFT   JOIN (SELECT t.index_owner owner, t.table_name, t.column_name
             FROM   all_ind_columns t, u
             WHERE  t.index_owner = u.owner) c
ON     a.owner = c.owner
       AND a.table_name = c.table_name
       AND a.column_name = c.column_name
WHERE  a.selectivity >= 20
       AND (b.owner IS NOT NULL AND b.table_name IS NOT NULL AND
       b.column_name IS NOT NULL)
       AND
       (c.owner IS NULL AND c.table_name IS NULL AND c.column_name IS NULL)

完成。