Oracle问题：一张表推荐创建多少索引合适

 

Oracle问题：一张表推荐创建多少索引合适

 

首先没有绝对说几个合适，需要结合实际业务情况评估，越来越多的情况下还要权衡DML的影响。

根据我的经验，明确索引主要影响insert、delete以及索引字段的update情况下（还会占用空间，一般不考虑这点），结合需求：

1.如果表基本静态，存储足够的情况下想建多少个都可以。

2.字段如果null值占比大，对字段等值查询或者关联查询多也可以考虑，因为null不会去维护索引，创建的索引也会很小。

3.针对某个业务特别重要需要保障涉及SQL运行快，在其他手段无法生效下只能创建索引，权衡影响DML和优先保障业务下，那么只能创建索引。

4.如果表存在大量DML，但是DML效率高低都可接受，同1。

最后，一般是否需要创建索引除了以上4点，还会考虑字段的数据倾斜度，字段在业务上的使用频率。

另外表是否为分区表也可以考虑进行(全局索引和本地索引)。

对于时间字段，需要考虑业务上对该字段的使用"between and"(或(>=…<=))的范围，跨度大有索引也不一定能用上等等等等。

 

其中，对于表DML的频率，可以参考dba_tab_modifications，只是个大概的一个趋势。

09:40:19 SYS@test(2099)> select * from dba_tab_modifications where rownum<=10;

TABLE_OWNER  TABLE_NAME             PARTITION_NAME     SUBPARTITION_NAME       INSERTS    UPDATES    DELETES TIMESTAMP           TRUNCATED DROP_SEGMENTS
------------ ---------------------- ------------------ -------------------- ---------- ---------- ---------- ------------------- --------- -------------
SYS          JAVA$MC$                                                                6          2          0 2021-07-16 00:19:04 NO                    0
SYS          JOB$                                                                  110    1288930         64 2023-07-28 04:00:00 NO                    0
SYS          KET$_AUTOTASK_STATUS                                                    0       3015          0 2023-07-28 02:15:36 NO                    0
SYS          KOTAD$                                                                  1          0          1 2020-12-16 16:21:55 NO                    0
SYS          KOTTB$                                                                  1          0          1 2020-12-16 16:21:55 NO                    0
SYS          KOTTBX$                                                                 1          0          1 2020-12-16 16:21:55 NO                    0
SYS          KOTTD$                                                                  1          0          1 2020-12-16 16:21:55 NO                    0
SYS          SOURCE$                                                             79657          0      69269 2023-07-27 08:55:12 NO                    0
SYS          SQL$                                                                 2415          0       2398 2023-07-28 00:05:24 NO                    0
SYS          SQL$TEXT                                                               39          0         22 2023-07-13 09:10:18 NO                    0

10 rows selected.

Elapsed: 00:00:00.05

 

 

查看表的字段的使用频率，跟据官方文档Column Usage in Multi Column Index (文档 ID 400214.1)可以使用sys创建视图如下和同义词，当然也只是大概的一个趋势：

create view dba_column_usage
as
select oo.name owner,
o.name,
c.name column_name,
u.equality_preds,
u.equijoin_preds,
u.nonequijoin_preds,
u.range_preds,
u.like_preds,
u.null_preds,
u.timestamp
from sys.col_usage$ u,
sys.obj$ o,
sys.user$ oo,
sys.col$ c
where o.obj# = u.obj#
and oo.user# = o.owner#
and c.obj# = u.obj#
and c.intcol# = u.intcol#;

create public synonym dba_column_usage for dba_column_usage;

 

效果：

EQUALITY_PREDS表示等值查询

EQUIJOIN_PREDS表示关联等值查询，NONEQUIJOIN_PREDS反之

RANGE_PREDS表示范围查询过滤

LIKE_PREDS表示LIKE查询过滤

NULL_PREDS表示null判断过滤