An attempt to parameterize a query in Oracle using bind variables and OR :B1 IS NULL or NVL(:B1,col1) in a WHERE clause leads to suboptimal execution plans.
For example, I'd like to write SELECT like this:
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);
Its execution plan should depend on the values of the bind variables, bearing in mind that Oracle CBO creates optimal execution plans for each.
As shown in the following execution plan examples, using OR FROM NULL or NVL results in table level filter predicates instead of index level access predicates.
Is there any other way to parameterize SQL query in Oracle?
Test 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))
Test 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))
Test 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)