sql - Oracle OR 条件使查询非常慢

Question

我有一个表(50M 行)，其中在column_a 和column_b 上有索引

当我select count(*) from table where column_a in (list_a)时，我立即得到结果。

与select count(*) from table where column_b in (list_b)相同。

但是当我这样做时

从表中选择count(*)，其中column_a在(list_a)中或column_b在(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               |
----------------------------------------------------------------

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)