多个索引可以一起工作吗？ [英] Can Multiple Indexes Work Together?

问题描述

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

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.

现在假设我执行查询，例如 SELECT * FROM sometable WHERE foo ='hello'AND bar ='world'; 我的表中有大量行，其中foo是'hello'，而少数行是bar为'world'。

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'.

因此，数据库服务器最有效的方法是使用条形索引查找bar为'world'的所有字段，然后仅返回foo为foo的那些行。 '你好'。这是 O（n）其中n是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'.

但是，我想这个过程可能会反过来，使用fo索引并搜索结果。这将是 O（m）其中m是foo为'hello'的行数。

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'.

所以Oracle足够聪明，可以在这里高效搜索吗？其他数据库怎么样？或者有什么方法可以在我的查询中告诉它以正确的顺序搜索？也许首先在 WHERE 子句中放入 bar ='world'？

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 WHERE clause?

推荐答案

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

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

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

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.

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

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.

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

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.

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

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)
/

与之对比：

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

这篇关于多个索引可以一起工作吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！