[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
--我做了一些改动加入统计信息,方便确定那些字段需要建立索引:
/* 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
--大家可以根据自己的需要修改查询范围和条件!