如何说服Oracle在使用绑定变量的SQL查询中创建最佳执行计划？

Question

试图使用bind变量参数化Oracle中的查询，而在WHERE子句中OR :B1为NULL或NVL(:B1，col1)将导致执行计划的次优。

例如，我想这样写SELECT：

SELECT * FROM MY_TABLE
WHERE (COL1 = :B1 OR :B1 IS NULL) AND (COL2 = :B2 OR :B2 IS NULL) AND (COL3 = :B3 OR :B3 IS NULL);

它的执行计划应该依赖于绑定变量的值，记住Oracle CBO为每个变量创建了最佳的执行计划。

如以下执行计划示例所示，使用OR FROM NULL或NVL将生成表级筛选器谓词，而不是索引级访问谓词。

在Oracle中还有其他方法来参数化SQL查询吗？

测试1

select * from employees where employee_id = :B1;

Plan hash value: 1833546154
 
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    69 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EMPLOYEE_ID"=TO_NUMBER(:B1))

测试2

select * from employees where (employee_id = :B1 OR :B1 IS NULL);

Plan hash value: 1445457117
 
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     6 |   414 |    35   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     6 |   414 |    35   (0)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(:B1 IS NULL OR "EMPLOYEE_ID"=TO_NUMBER(:B1))

测试3

select * from employees where employee_id = NVL(:B1,employee_id);   

Plan hash value: 71496665
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |   108 | 14364 |    36   (0)| 00:00:01 |
|   1 |  VIEW                          | VW_ORE_B4851255 |   108 | 14364 |    36   (0)| 00:00:01 |
|   2 |   UNION-ALL                    |                 |       |       |            |          |
|*  3 |    FILTER                      |                 |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES       |     1 |    69 |     1   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN         | EMP_EMP_ID_PK   |     1 |       |     0   (0)| 00:00:01 |
|*  6 |    FILTER                      |                 |       |       |            |          |
|   7 |     TABLE ACCESS FULL          | EMPLOYEES       |   107 |  7383 |    35   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(:B1 IS NOT NULL)
   5 - access("EMPLOYEE_ID"=:B1)
   6 - filter(:B1 IS NULL)

Answer 1

对于这个用例，测试1在逻辑上是不正确的。如果将NULL作为:B1的值提供，它将返回0行。

测试2在逻辑上是正确的，但是它的执行计划不是最优的，性能也是如此。在这种情况下，优化器无法利用索引，因为默认情况下不对NULL值进行索引。

测试3在逻辑上是正确的，它的执行计划是最优的，这应该用于这样的简单情况。但是小心点。

将employee_id = NVL(:B1,employee_id)转化为基于employee_id = :B1和:B1 is null的两个分支的联合收集结果。这称为NVL优化，并显示为执行计划中的OR-展开(ORE) .这是优化器的一个不错的小技巧。

问题是，只有在有这样一个谓词时，这才能正常工作。如果有更多这样的谓词，优化器将不会对其余谓词执行此操作，也不会为UNION创建其他分支。

这样的查询：

SELECT * FROM MY_TABLE
WHERE COL1 = NVL(:B1, COL1) AND COL2 = NVL(:B2, COL2) AND COL3 = NVL(:B3, COL3);

不会为所有路径生成分支。优化器将对一个谓词执行NVL优化，然后将剩余的谓词放置在第一个谓词转换所创建的两个分支中。这意味着从这样的查询中生成的计划永远不会是最优的。这是导致我遇到的大量SQL性能问题的原因，到目前为止，还没有一个开发人员知道这一点。如果您希望具有最佳的执行计划和性能，则应该动态生成最简单的SQL语句，而不是试图用单个SQL语句覆盖所有可能的场景。例如，如果没有为:B1提供任何值，那么只需在SQL语句中不包含该谓词即可。