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

来源：互联网发布：网络宣传部编辑：程序博客网时间：2024/06/08 14:07

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_nameFROM   (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) aLEFT   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) bON     a.owner = b.owner       AND a.table_name = b.table_name       AND a.column_name = b.column_nameLEFT   JOIN (SELECT t.index_owner owner, t.table_name, t.column_name             FROM   all_ind_columns t, u             WHERE  t.index_owner = u.owner) cON     a.owner = c.owner       AND a.table_name = c.table_name       AND a.column_name = c.column_nameWHERE  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)

完成。

1 0