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

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

现在假设我执行一个查询,例如SELECT * FROM sometable WHERE foo='hello' AND bar='world'; My table,foo为'hello'的行数很多,而bar为'world'的行数很少.

因此,数据库服务器最有效的方法是使用bar索引查找bar为'world'的所有字段,然后仅返回foo为'hello'的那些行.这是O(n)n是bar为'world'的行数.

但是,我想这个过程可能会反过来,使用fo索引并搜索结果.这就是O(m)m是foo为'hello'的行数.

那么Oracle足够聪明,可以在这里高效搜索吗？其他数据库怎么样？或者有什么方法可以在我的查询中告诉它以正确的顺序搜索？也许bar='world'在WHERE条款中排在第一位？

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

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

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