主页 / dba / 问题 / 224277
Accepted
Hybris95
Asked: 2018-12-07 03:16:55 +0800 CST

Oracle 优化器拒绝使用我的索引

  • 772

我有一个相对简单的查询，它在表中选择数据。

SELECT l.columnRecovered FROM schema.TABLE l
WHERE (column1 = :a or (:a is null AND column1 is null))
AND (column2 = :b or (:b is null AND column2 is null))
AND (column3 = :c or (:c is null AND column3 is null));

这会生成以下计划：

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |   309 (100)|          |
|*  1 |  TABLE ACCESS FULL| TABLE     |     1 |    56 |   309   (1)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((("COLUMN2"=:3 OR ("COLUMN2" IS NULL AND :4 IS NULL)) AND
              ("COLUMN1"=:1 OR ("COLUMN1" IS NULL AND :2 IS NULL)) AND ((:6 IS NULL
              AND "COLUMN3" IS NULL) OR "COLUMN3"=:5)))

在这张表上，我有一个包含所有这 3 列的 UNIQUE INDEX。
但是优化器更喜欢在表上进行 FULL SCAN 而不是使用 INDEX。

事情变得奇怪的地方是，当我在没有绑定但直接替换查询中的值的情况下尝试此查询时。
当然，SQL_ID 发生了变化，但这次优化器决定使用 INDEX 并从 1104 变为 6（这当然是一个很好的优化）。

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    56 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE         |     1 |    56 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | TABLE_IDX     |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COLUMN3"='DATA3' AND "COLUMN2"='DATA2' AND "COLUMN1"='DATA1')

统计数据是最新的，此表中没有直方图。

有什么方法可以让优化器在不修改查询的情况下使用该索引？

我认为优化器认为有 3 个 NULL 的可能性可能会发生，所以这使他无法使用索引，对吗？

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    56 |   199   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLE     |     1 |    56 |   199   (2)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COLUMN2" IS NULL AND "COLUMN1" IS NULL AND "COLUMN3" IS
              NULL)
oracle index

1 个回答

  1. Best Answer

    优化器不使用你的索引，不是因为它不想，而是因为它不能。对于这样的查询，索引不会涵盖所有可能的结果。

    当:a, :b,:c都为 NULL，并且您正在搜索column1, column2,column3都为 NULL 的行时，这些行没有被(column1, column2, column3)b-tree 索引索引，因此不能用于检索匹配的行。

    添加NOT NULL约束可能会有所帮助，但如果您有 NULL 值，那当然是不可能的（我假设您有 NULL 值，否则为什么要构造这样的查询）。

    您还可以尝试创建一个包含常量值的索引，这样即使那些行也将被索引，其中索引列为 NULL。因此(column1, column2, column3)，您可以尝试创建索引而不是(column1, column2, column3, 'A')索引。

    然而，我认为最好的解决方案是在绑定变量未赋值时生成不同的查询。像上面这样的查询是尝试用一个查询处理多个案例的结果。这看起来优雅紧凑，是的，我知道，根据参数的存在生成不同的查询可能很麻烦，但我已经无数次看到这种懒惰导致严重的性能问题。

    是的，Oracle 有一些技巧，例如 NVL 优化、OR 扩展，用于处理此类谓词。这些在 Powerpoint 和培训材料中非常有效，带有简单的示例，但实际上，对于复杂的查询，它们并不总是适用。

    • 3

相关问题