Oracle什么样的列必须创建索引

有人说基数高的列，有人说在where条件中的列。当一个列选择性大于20%，说明该列的数据分布就比较均衡了。因此，当一个列出现在where条件中，该列没有创建索引并且选择选择性大于20%，那么该列就必须创建索引，从而提升SQL查询性能。
具体步骤如下：
1、先执行下面的存储过程，刷新数据库监控信息

BEGIN
  DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
END;

2、再运行查询语句就可以查询出哪个表的哪个列出现在WHERE条件中

SELECT R.NAME              AS OWNER, --用户
       O.NAME              AS TABLE_NAME, --表
       C.NAME              AS COLUMN_NAME, --列
       U.EQUALITY_PREDS, --等值过滤
       U.EQUIJOIN_PREDS, --等值JOIN
       U.NONEQUIJOIN_PREDS, --不等JOIN
       U.RANGE_PREDS, --范围过滤次数 > >= < <=  BETWEEN AND
       U.LIKE_PREDS, --LIKE过滤
       U.NULL_PREDS, --NULL过滤
       TIMESTAMP
  FROM SYS.USER$ R
 INNER JOIN SYS.OBJ$ O
    ON R.USER# = O.OWNER#
 INNER JOIN SYS.COL_USAGE$ U
    ON O.OBJ# = U.OBJ#
 INNER JOIN SYS.COL$ C
    ON U.OBJ# = C.OBJ#
   AND U.INTCOL# = C.COL#
 WHERE R.NAME = 'HLHT_ARCH' /*用户*/
   and o.name = 'BASEINFO' /*表*/;

3、查询出选择性大于等于20%的列

SELECT B.OWNER,
       B.TABLE_NAME,
       A.column_name,
       ROUND(A.NUM_DISTINCT / B.NUM_ROWS*100,2) AS SELECTIVITY
  FROM DBA_TABLES B
 INNER JOIN DBA_TAB_COL_STATISTICS A
    ON B.OWNER = A.OWNER
   AND B.TABLE_NAME = A.TABLE_NAME
 WHERE B.OWNER = 'HLHT_ARCH'
   AND B.TABLE_NAME = 'BASEINFO'
   AND A.NUM_DISTINCT / B.NUM_ROWS >= 0.2 /*选择性大于20%*/

4、确保这些列没有创建索引

SELECT S.TABLE_OWNER, S.TABLE_NAME, S.COLUMN_NAME, S.INDEX_NAME
  FROM DBA_IND_COLUMNS S
 WHERE S.TABLE_OWNER = 'HLHT_ARCH'
   AND S.TABLE_NAME = 'BASEINFO'

5、把全部脚本组合起来，就可以得到全自动优化脚本了

BEGIN
  DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
END;

SELECT S.TABLE_OWNER,
       S.TABLE_NAME,
       S.COLUMN_NAME,
       S.NUM_DISTINCT,
       S.NUM_ROWS,
       S.SELECTIVITY,
       'Need Index' AS NOTICE
  FROM (SELECT B.OWNER AS TABLE_OWNER,
               B.TABLE_NAME,
               A.COLUMN_NAME,
               A.NUM_DISTINCT,
               B.NUM_ROWS,
               ROUND(A.NUM_DISTINCT / DECODE(B.NUM_ROWS, 0, 1, B.NUM_ROWS) * 100,
                     2) AS SELECTIVITY
          FROM DBA_TABLES B
         INNER JOIN DBA_TAB_COL_STATISTICS A
            ON B.OWNER = A.OWNER
           AND B.TABLE_NAME = A.TABLE_NAME) S
 WHERE 1 = 1
   AND EXISTS (SELECT 1
          FROM (SELECT R.NAME    AS TABLE_OWNER, --用户
                       O.NAME    AS TABLE_NAME, --表
                       C.NAME    AS COLUMN_NAME, --列
                       TIMESTAMP
                  FROM SYS.USER$ R
                 INNER JOIN SYS.OBJ$ O
                    ON R.USER# = O.OWNER#
                 INNER JOIN SYS.COL_USAGE$ U
                    ON O.OBJ# = U.OBJ#
                 INNER JOIN SYS.COL$ C
                    ON U.OBJ# = C.OBJ#
                   AND U.INTCOL# = C.COL#) W
         WHERE S.TABLE_OWNER = W.TABLE_OWNER
           AND S.TABLE_NAME = W.TABLE_NAME
           AND S.COLUMN_NAME = W.COLUMN_NAME) /*列在WHERE条件中*/
   AND NOT EXISTS (SELECT 1
          FROM (SELECT S.TABLE_OWNER,
                       S.TABLE_NAME,
                       S.COLUMN_NAME,
                       S.INDEX_NAME
                  FROM DBA_IND_COLUMNS S) D
         WHERE S.TABLE_OWNER = D.TABLE_OWNER
           AND S.TABLE_NAME = D.TABLE_NAME
           AND S.COLUMN_NAME = D.COLUMN_NAME) /*列没有创建索引*/
   AND S.SELECTIVITY >= 20 /*列选择性大于20*/
   AND S.TABLE_OWNER IN( USER)