[20140714]关于在那些字段建立索引的问题

[20140714]关于在那些字段建立索引的问题.txt

前几天有人问我,如何确定在那些字段建立索引的问题,我的方法很简单,通过awr报表可以来确定一部分索引的建立,应用程序绑定做的很
好,方法很简单,可以使用toad的SGA trace,组合一些查询条件很容易确定,这个方法的缺陷就是,如果你应用绑定没做好,shared pool设置
很大的情况下,每次扫描真的是一种灾难!(不是很快,多次操作心里很烦!)

与别人交谈,提到利用col_usage$,可以知道谓词的使用情况,从而决定在那些字段做了索引.我google相关信息发现如下链接:

http://www.dba-scripts.com/scripts/diagnostic-and-tuning/troubleshooting/find-missing-index/

SELECT *
  FROM (  SELECT    'the column '
                 || c.name
                 || ' of the table '
                 || us.name
                 || '.'
                 || o.name
                 || ' was used '
                 || u.equality_preds
                 || ' times in an equality predicate and '
                 || u.equijoin_preds
                 || ' times in an equijoin predicate and is not indexed'
                    AS colum_to_index
            FROM sys.col_usage$ u,
                 sys.obj$ o,
                 sys.col$ c,
                 sys.user$ us
           WHERE     u.obj# = o.obj#
                 AND u.obj# = c.obj#
                 AND us.user# = o.owner#
                 AND u.intcol# = c.col#
                 AND us.name = '&SCHEMA_NAME'
                 AND c.name NOT IN (SELECT column_name
                                      FROM dba_ind_columns
                                     WHERE index_owner = '&SCHEMA_NAME')
                 AND (u.equality_preds > 100 OR u.equijoin_preds > 100)
        ORDER BY u.equality_preds + u.equijoin_preds DESC)
WHERE ROWNUM < 11;

--我做了一些改动加入统计信息,方便确定那些字段需要建立索引:

/* Formatted on 2014/7/14 10:25:10 (QP5 v5.252.13127.32867) */
SELECT *
  FROM (  SELECT    'the column '
                 || c.name
                 || ' of the table '
                 || us.name
                 || '.'
                 || o.name
                 || ' was used '
                 || u.equality_preds
                 || ' times in an equality predicate and '
                 || u.equijoin_preds
                 || ' times in an equijoin predicate and is not indexed'
                    AS colum_to_index,
                 h.DISTCNT,
                 h.ROW_CNT,
                 h.NULL_CNT,
                 h.DENSITY,
                 u.EQUALITY_PREDS,
                 u.EQUIJOIN_PREDS,
                 u.NONEQUIJOIN_PREDS,
                 u.RANGE_PREDS,
                 u.LIKE_PREDS,
                 u.NULL_PREDS
            FROM sys.col_usage$ u,
                 sys.obj$ o,
                 sys.col$ c,
                 sys.hist_head$ h,
                 sys.user$ us
           WHERE     u.obj# = o.obj#
                 AND u.obj# = c.obj#
                 AND us.user# = o.owner#
                 AND u.intcol# = c.col#
                 AND us.name = '&SCHEMA_NAME'
                 AND h.obj# = o.obj#
                 AND h.col# = c.col#
                 AND c.name NOT IN (SELECT column_name
                                      FROM dba_ind_columns
                                     WHERE index_owner = '&SCHEMA_NAME')
                 AND (   u.equality_preds > 100
                      OR u.equijoin_preds > 100
                      OR u.RANGE_PREDS > 100)
        ORDER BY u.equality_preds + u.equijoin_preds DESC)
WHERE ROWNUM < 11;

--大家可以根据自己的需要修改查询范围和条件!

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1218195/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1218195/