oracle 多个索引可以一起工作吗？

Can Multiple Indexes Work Together?

提问by Eli Courtwright

Suppose I have a database table with two fields, "foo" and "bar". Neither of them are unique, but each of them are indexed. However, rather than being indexed together, they each have a separate index.

假设我有一个包含两个字段“foo”和“bar”的数据库表。它们都不是唯一的，但每个都被索引。但是，它们不是一起编入索引，而是每个都有一个单独的索引。

Now suppose I perform a query such as SELECT * FROM sometable WHERE foo='hello' AND bar='world';My table a huge number of rows for which foo is 'hello' and a small number of rows for which bar is 'world'.

现在假设我执行了一个查询，比如SELECT * FROM sometable WHERE foo='hello' AND bar='world';我的表，其中有大量的 foo 是 'hello' 的行和少量的 bar 是 'world' 的行。

So the most efficient thing for the database server to do under the hood is use the bar index to find all fields where bar is 'world', then return only those rows for which foo is 'hello'. This is O(n)where n is the number of rows where bar is 'world'.

因此，数据库服务器在幕后所做的最有效的事情是使用 bar 索引来查找 bar 为“world”的所有字段，然后仅返回 foo 为“hello”的那些行。这是O(n)其中 n 是 bar 是“世界”的行数。

However, I imagine it's possible that the process would happen in reverse, where the fo index was used and the results searched. This would be O(m)where m is the number of rows where foo is 'hello'.

但是，我认为该过程可能会反过来发生，即使用 fo 索引并搜索结果。这将是O(m)其中 m 是 foo 是 'hello' 的行数。

So is Oracle smart enough to search efficiently here? What about other databases? Or is there some way I can tell it in my query to search in the proper order? Perhaps by putting bar='world'first in the WHEREclause?

那么 Oracle 是否足够聪明，可以在这里进行高效搜索？其他数据库呢？或者有什么方法可以在我的查询中告诉它以正确的顺序进行搜索？也许通过将bar='world'第一个放在WHERE子句中？

采纳答案by David Aldridge

Oracle will almost certainly use the most selective index to drive the query, and you can check that with the explain plan.

Oracle 几乎肯定会使用最具选择性的索引来驱动查询，您可以使用解释计划进行检查。

Furthermore, Oracle can combine the use of both indexes in a couple of ways -- it can convert btree indexes to bitmaps and perform a bitmap ANd operation on them, or it can perform a hash join on the rowid's returned by the two indexes.

此外，Oracle 可以通过多种方式组合使用这两个索引——它可以将 btree 索引转换为位图并对它们执行位图和操作，或者它可以对两个索引返回的 rowid 执行散列连接。

One important consideration here might be any correlation between the values being queried. If foo='hello' accounts for 80% of values in the table and bar='world' accounts for 10%, then Oracle is going to estimate that the query will return 0.8*0.1= 8% of the table rows. However this may not be correct - the query may actually return 10% of the rwos or even 0% of the rows depending on how correlated the values are. Now, depending on the distribution of those rows throughout the table it may not be efficient to use an index to find them. You may still need to access (say) 70% or the table blocks to retrieve the required rows (google for "clustering factor"), in which case Oracle is going to perform a ful table scan if it gets the estimation correct.

这里的一个重要考虑因素可能是被查询值之间的任何相关性。如果 foo='hello' 占表中值的 80%，bar='world' 占 10%，那么 Oracle 将估计查询将返回 0.8*0.1= 8% 的表行。但是，这可能不正确 - 查询实际上可能返回 10% 的 rwos 甚至 0% 的行，具体取决于值的相关程度。现在，根据这些行在整个表中的分布，使用索引来查找它们可能效率不高。您可能仍然需要访问（比如）70% 或表块来检索所需的行（谷歌搜索“集群因子”），在这种情况下，如果 Oracle 得到正确的估计，它将执行完整的表扫描。

In 11g you can collect multicolumn statistics to help with this situation I believe. In 9i and 10g you can use dynamic sampling to get a very good estimation of the number of rows to be retrieved.

在 11g 中，我相信您可以收集多列统计信息来帮助解决这种情况。在 9i 和 10g 中，您可以使用动态采样来很好地估计要检索的行数。

To get the execution plan do this:

要获取执行计划，请执行以下操作：

explain plan for
SELECT *
FROM   sometable
WHERE  foo='hello' AND bar='world'
/
select * from table(dbms_xplan.display)
/

Contrast that with:

对比一下：

explain plan for
SELECT /*+ dynamic_sampling(4) */
       *
FROM   sometable
WHERE  foo='hello' AND bar='world'
/
select * from table(dbms_xplan.display)
/

回答by David Aldridge

Eli,

伊莱，

In a comment you wrote:

你在评论中写道：

Unfortunately, I have a table with lots of columns each with their own index. Users can query any combination of fields, so I can't efficiently create indexes on each field combination. But if I did only have two fields needing indexes, I'd completely agree with your suggestion to use two indexes. – Eli Courtwright (Sep 29 at 15:51)

不幸的是，我有一个包含很多列的表，每个列都有自己的索引。用户可以查询任何字段组合，因此我无法有效地为每个字段组合创建索引。但是，如果我确实只有两个需要索引的字段，我会完全同意您使用两个索引的建议。– Eli Courtwright（9 月 29 日 15:51）

This is actually rather crucial information. Sometimes programmers outsmart themselves when asking questions. They try to distill the question down to the seminal points but quite often over simplify and miss getting the best answer.

这实际上是相当重要的信息。有时，程序员在提问时会比自己更聪明。他们试图将问题提炼到关键点，但往往过于简化而错过了最佳答案。

This scenario is precisely why bitmap indexes were invented -- to handle the times when unknown groups of columns would be used in a where clause.

这种情况正是发明位图索引的原因——处理在 where 子句中使用未知列组的时间。

Just in case someone says that BMIs are for low cardinality columns only and may not apply to your case. Low is probably not as small as you think. The only real issue is concurrency of DML to the table. Must be single threaded or rare for this to work.

以防万一有人说 BMI 仅适用于低基数列，可能不适用于您的情况。低可能没有你想象的那么小。唯一真正的问题是 DML 与表的并发性。必须是单线程的或罕见的才能工作。

回答by Georgi

Yes, you can give "hints" with the query to Oracle. These hints are disguised as comments ("/* HINT */") to the database and are mainly vendor specific. So one hint for one database will not work on an other database.

是的，您可以向 Oracle 提供有关查询的“提示”。这些提示被伪装成数据库的注释（“/* HINT */”）并且主要是特定于供应商的。因此，一个数据库的提示不适用于其他数据库。

I would use index hints here, the first hint for the small table. See here.

我会在这里使用索引提示，这是小表的第一个提示。见这里。

On the other hand, if you often search over these two fields, why not create an index on these two? I do not have the right syntax, but it would be something like

另一方面，如果您经常搜索这两个字段，为什么不在这两个字段上创建索引？我没有正确的语法，但它会像

CREATE INDEX IX_BAR_AND_FOO on sometable(bar,foo);

This way data retrieval should be pretty fast. And in case the concatenation is unique hten you simply create a unique index which should be lightning fast.

这样数据检索应该非常快。如果串联是唯一的，您只需创建一个唯一的索引，它应该是闪电般的快速。

回答by James Curran

So is Oracle smart enough to search efficiently here?

那么 Oracle 是否足够聪明，可以在这里进行高效搜索？

The simple answer is "probably". There are lots'o' very bright people at each of the database vendors working on optimizing the query optimizer, so it's probably doing things that you haven't even thought of. And if you update the statistics, it'll probably do even more.

简单的答案是“可能”。每个数据库供应商都有很多非常聪明的人致力于优化查询优化器，所以它可能正在做你甚至没有想到的事情。如果你更新统计数据，它可能会做得更多。

回答by Justin Cave

First off, I'll assume that you are talking about nice, normal, standard b*-tree indexes. The answer for bitmap indexes is radically different. And there are lots of options for various types of indexes in Oracle that may or may not change the answer.

首先，我假设您在谈论漂亮的、正常的、标准的 b* 树索引。位图索引的答案完全不同。对于 Oracle 中的各种类型的索引，有很多选项可能会也可能不会改变答案。

At a minimum, if the optimizer is able to determine the selectivity of a particular condition, it will use the more selective index (i.e. the index on bar). But if you have skewed data (there are N values in the column bar but the selectivity of any particular value is substantially more or less than 1/N of the data), you would need to have a histogram on the column in order to tell the optimizer which values are more or less likely. And if you are using bind variables (as all good OLTP developers should), depending on the Oracle version, you may have issues with bind variable peeking.

至少，如果优化器能够确定特定条件的选择性，它将使用更具选择性的索引（即柱状图上的索引）。但是，如果您有偏斜的数据（列栏中有 N 个值，但任何特定值的选择性远大于或小于数据的 1/N），则需要在列上有一个直方图才能告诉值或多或少的优化器。如果您正在使用绑定变量（所有优秀的 OLTP 开发人员都应该使用），根据 Oracle 版本，您可能会遇到绑定变量查看问题。

Potentially, Oracle could even do an on the fly conversion of the two b*-tree indexes to bitmaps and combine the bitmaps in order to use both indexes to find the rows it needs to retrieve. But this is a rather unusual query plan, particularly if there are only two columns where one column is highly selective.

有可能，Oracle 甚至可以将两个 b*-tree 索引即时转换为位图并组合位图，以便使用这两个索引来查找需要检索的行。但这是一个相当不寻常的查询计划，特别是如果只有两列其中一列是高度选择性的。

回答by Justin Cave

I'm sure you can also have Oracle display a query plan so you can see exactly which index is used first.

我相信您也可以让 Oracle 显示一个查询计划，以便您可以准确地看到首先使用的是哪个索引。

回答by Eran Galperin

You can provide hints as to which index to use. I'm not familiar with Oracle, but in Mysql you can use USE|IGNORE|FORCE_INDEX (see herefor more details). For best performance though you should use a combined index.

您可以提供有关使用哪个索引的提示。我不熟悉 Oracle，但在 Mysql 中，您可以使用 USE|IGNORE|FORCE_INDEX（有关更多详细信息，请参见此处）。为了获得最佳性能，您应该使用组合索引。

回答by Jeffrey L Whitledge

The best approach would be to add foo to bar's index, or add bar to foo's index (or both). If foo's index also contains an index on bar, that additional indexing level will not affect the utility of the foo index in any current uses of that index, nor will it appreciably affect the performance of maintaining that index, but it will give the database additional information to work with in optimizing queries such as in the example.

最好的方法是将 foo 添加到 bar 的索引，或将 bar 添加到 foo 的索引（或两者）。如果 foo 的索引还包含 bar 上的索引，则该附加索引级别不会影响 foo 索引在该索引的任何当前使用中的效用，也不会明显影响维护该索引的性能，但它会给数据库额外的用于优化查询的信息，例如在示例中。

回答by Michael Brown

It's better than that.

它比那更好。

Index Seeks are always quicker than full table scans. So behind the scenes Oracle (and SQL server for that matter) will first locate the range of rows on both indices. It will then look at which range is shorter (seeing that it's an inner join), and it will iterate the shorter range to find the matches with the larger of the two.

索引查找总是比全表扫描更快。所以在幕后，Oracle（和 SQL 服务器）将首先在两个索引上定位行的范围。然后它会查看哪个范围更短（看到它是一个内连接），它会迭代更短的范围以找到与两者中较大的匹配。