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

Question

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

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

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

但是，我想这个过程可能会发生相反的情况，即使用 fo 索引并搜索结果。这将是 O(m)，其中 m 是 foo 为“hello”的行数。

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

Answer 1

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

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

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

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

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

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