当前位置：文江博客话题详情

多个相同列索引的 Oracle 性能

发布于 2024-07-25 05:13:26 字数 127 浏览 9 评论 0原文

我正在使用一个新的 Oracle 数据库，其中一个表具有以下索引：

索引 1：ColA、ColB
索引 2：ColA

第二个索引是否冗余，这会对性能产生负面影响吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

℡Ms空城旧梦 2024-08-01 05:13:26

Google 是我最好的朋友：

http://www.orafaq.com/node/926

主要这篇文章的要点是：

If 2 indexes ( I1 and I2 ) exist for a table and
   the number of columns in Index I1 is less or equal to the number of column in index I2 and
   index I1 has the same columns in the same order as leading columns of index I2 
Then
   If index I1 is UNIQUE then
      If index I2 is used to support Foregh Key or for Index Overload then
         Do Nothing
      Else
         Index I2 can be DROPPED
      End If
   Else
      Index I1 can be DROPPED
   End If
End If

我同意这一点！
事实上，在谷歌中搜索“重复索引”会有不同的答案。

Google is my best friend :

http://www.orafaq.com/node/926

The main point of this article is :

If 2 indexes ( I1 and I2 ) exist for a table and
   the number of columns in Index I1 is less or equal to the number of column in index I2 and
   index I1 has the same columns in the same order as leading columns of index I2 
Then
   If index I1 is UNIQUE then
      If index I2 is used to support Foregh Key or for Index Overload then
         Do Nothing
      Else
         Index I2 can be DROPPED
      End If
   Else
      Index I1 can be DROPPED
   End If
End If

And I'm agree with that !
In fact, search "duplicate indexes" in Google to have different kind of answer.

回复收藏 0 原文

青柠芒果 2024-08-01 05:13:26

第二个索引是不同的，并且本身并不冗余。

这个查询怎么样：

SELECT DISTINCT ColA FROM TABLE WHERE ColA IS NOT NULL;

Oracle 可以完全从索引 2 回答这个问题。现在，索引 2 预计会比索引 1 小（更少的块）。这意味着，对于上述查询来说，它是一个更好的索引。

如果您的应用程序从未执行过比 Index1 更适合 Index2 的查询，那么它对于您的应用程序来说是多余的。

索引始终是性能权衡。当执行插入、更新或删除时，需要做额外的工作来维护每个附加索引。

索引提供的性能提高是否足以弥补这一点？取决于您的应用程序和数据使用情况。

The second index is different and is not redundant per se.

How about this query:

SELECT DISTINCT ColA FROM TABLE WHERE ColA IS NOT NULL;

Oracle can answer this question entirely from Index 2. Now, index 2 would be expected to be small (less blocks) than index 1. This means, it is a better index for the above query.

If your application never does a query that suits Index2 better than Index1, then it is redundant for your application.

Indexes are always a performance tradeoff. When an insert, update or delete is performed there is extra work to do in order to maintain each additional index.

Is this more than compensated for by the increased performance provided by the index? Depends on your application and data usage.

回复收藏 0 原文