Oracle的OR条件使查询变得非常缓慢。

Question

我有一张表（5000万行），其中列 column_a 和 column_b 都建有索引。

当我执行 select count(*) from table where column_a in (list_a) 时，可以立即得到结果；同样地，对于 select count(*) from table where column_b in (list_b) 也是如此。

但是当我执行 select count(*) from table where column_a in (list_a) or column_b in (list_b) 时，查询变得异常缓慢，需要半个小时才能输出正确的数字……请问我的操作是否存在问题？如何优化这个查询？

谢谢！

查询 1 的计划：

Plan hash value: 2471097773


-------------------------------------------------------------
| Id  | Operation                    | Name                 |
-------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |
|   1 |  SORT AGGREGATE              |                      |
|   2 |   NESTED LOOPS               |                      |
|   3 |    SORT UNIQUE               |                      |
|   4 |     TABLE ACCESS FULL        | LIST_A               |
|   5 |    BITMAP CONVERSION COUNT   |                      |
|   6 |     BITMAP INDEX SINGLE VALUE| MY_TABLE_IX02        |
-------------------------------------------------------------

查询2的计划

Plan hash value: 1870911518

-------------------------------------------------------------
| Id  | Operation                    | Name                 |
-------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |
|   1 |  SORT AGGREGATE              |                      |
|   2 |   NESTED LOOPS               |                      |
|   3 |    SORT UNIQUE               |                      |
|   4 |     TABLE ACCESS FULL        | LIST_B               |
|   5 |    BITMAP CONVERSION COUNT   |                      |
|   6 |     BITMAP INDEX SINGLE VALUE| MY_TABLE_IX05        |
-------------------------------------------------------------

查询3的计划：

Plan hash value: 1821967683

----------------------------------------------------------------
| Id  | Operation                       | Name                 |
----------------------------------------------------------------
|   0 | SELECT STATEMENT                |                      |
|   1 |  SORT AGGREGATE                 |                      |
|   2 |   FILTER                        |                      |
|   3 |    VIEW                         | index$_join$_001     |
|   4 |     HASH JOIN                   |                      |
|   5 |      BITMAP CONVERSION TO ROWIDS|                      |
|   6 |       BITMAP INDEX FULL SCAN    | MY_TABLE_IX02        |
|   7 |      BITMAP CONVERSION TO ROWIDS|                      |
|   8 |       BITMAP INDEX FULL SCAN    | MY_TABLE_IX05        |
|   9 |    TABLE ACCESS FULL            | LIST_A               |
|  10 |    TABLE ACCESS FULL            | LIST_B               |
----------------------------------------------------------------

- Stephane Maarek

---

你尝试过使用 EXPLAIN PLAN 吗？Oracle 是否使用了你的索引？你尝试过在 column_a、column_b 上添加一个联合索引吗？ - Frank Schmitt

尝试在 column_a 和 column_b 上创建一个复合索引。 - ibre5041

@FrankSchmitt - 我刚刚编辑了问题，并附上了每个查询的实际解释计划。 - Stephane Maarek

这看起来很可疑 - BITMAP INDEX SINGLE VALUE vs BITMAP INDEX FULL SCAN。你在两个测试中使用了相同的 IN 子句吗？ - Frank Schmitt

你可以通过查询提示在一次性的基础上完成它。我通常更喜欢先在我的会话中使用optimizer_index_cost_adj设置，因为语法更简单。这可能对某些人来说是有争议的，但是默认情况下此设置通常不合适：http://www.dba-oracle.com/oracle_tips_cost_adj.htm - Necreaux

Answer 1

根据我的经验，OR运算符通常会对查询产生负面影响（比如忽略索引并触发全表扫描）。有时这并不是太糟糕，但我曾遇到过由于它导致查询从极速变成花费数分钟的情况。

一个可能的解决方案是将OR改为UNION甚至是UNION ALL。我过去尝试过这种方法来提高查询性能，并获得了成功，但您需要将它们彼此进行比较，以确定是否适用于您的情况。

您可以尝试下面三个选项，看看它们是否在其他选项上有显着的改进。

原始查询（已编辑以返回行，因为您提到返回数据而不是进行计数）：

select * from table where column_a in (list_a) or column_b in (list_b)

避免使用OR的查询语句：

select * from table where column_a in (list_a)
UNION
select * from table where column_b in (list_b)

由于UNION会触发DISTINCT，因此这也值得一试：

select * from table where column_a in (list_a) and not column_b in (list_b)
UNION ALL
select * from table where column_b in (list_b) and not column_a in (list_a)
UNION ALL
select * from table where column_a in (list_a) and column_b in (list_b)

- Sam

---

我非常喜欢这背后的思考方式。我会进行实验，并告诉你是否有效。 - Stephane Maarek

只是为了给一些数字 - 我从完整扫描的20分钟，变成了第三个优化的union all查询，只要5分钟。非常感谢！ - Stephane Maarek

Answer 2

我猜Oracle在前两个查询中使用了索引，并在第三个查询中进行了全表扫描。如果您考虑一下，就会明白为什么Oracle无法在第三个查询中使用索引：

• 列column_a上的索引将向Oracle提供有关column_a值的信息
• 列column_b上的索引将向Oracle提供有关column_b值的信息

但是，这两个索引都不能向Oracle提供(column_a、column_b)组合的信息。因此，要加速查询，您需要一个包含column_a和column_b的索引。

- Frank Schmitt

---

一个包含两列的索引无法帮助 OR 查询。 - Necreaux

为什么不呢？至少可以使用"INDEX FAST FULL SCAN"。问题没有指定list_a/list_b的长度，但是根据执行计划，这些列表必须非常长。 - ibre5041

@Necreaux，在OR情况下，数据分布是否重要？ - jarlh

列表的元素数量不超过200个。 - Stephane Maarek

@jarlh 理论上是可以的。实际上，我不确定，但可能不行。假设查询是cola='A'和colb='Z'，并且在cola、colb上有一个索引。如果A占数据的90%，那么遍历剩余的cola值并查找colb='Z'可能是有意义的（尽管在这里进行全表扫描可能更合理）。现在想象一下，如果情况相反，A只占数据的1%。最好使用colb上的单独索引。归根结底，我认为优化器不会针对这些情况进行调整，但我不能百分之百确定。 - Necreaux