Oracle OR条件使查询非常慢

lnlaulya 于 2022-11-22 发布在 Oracle

关注(0)|答案(2)|浏览(410)

我有一个表（50 M行），它在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               |
----------------------------------------------------------------

oracle

来源：https://stackoverflow.com/questions/28742891/oracle-or-conditions-makes-query-very-slow

2条答案

按热度按时间

rxztt3cl1#

根据我的经验，OR往往会对查询产生负面影响（比如忽略索引和触发全表扫描）。有时候这并不是很糟糕，但我遇到过这样的查询，它从 lightning 般的速度变成了几分钟。
一个可能的解决方案是将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)

赞(0）回复(0）举报 2022-11-22

yqyhoc1h2#

我猜 Oracle 在前两个查询中使用索引，而在第三个查询中进行全表扫描。如果您仔细考虑一下， Oracle 不能在第三个查询中使用索引的原因是合乎逻辑的：