:pselect *
from tab
where ( X = :P or :P is null)
select *
from tab
where X = NVL(:P,X)
NOTE: The two queries above behave the same only if column X is NOT NULL.
Both queries take an optional parameter :p. If :p has a value, only the rows where X = :p will be returned.
If :p is left as NULL, all rows in the table will come back (because WHERE X = X).
I found this example intriguing enough to explore it myself. To make things even more interesting, I’ll also include a comparison using the COALESCE function.
QUERY USING A LOGICAL "OR"
This query looks for an employee in the EMPLOYEES table based on the :nEmpId parameter.
If :nEmpId is NULL, it will simply return all rows from the table.
select count(*), sum(salary) from employees where employee_id = :nEmpId or :nEmpId is null; -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | TABLE ACCESS FULL| EMPLOYEES | 6 | 48 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------
Looking at the execution plan, it’s clear that whether we use a specific value or NULL in the condition, the query still ends up doing a FULL TABLE SCAN — even though the EMPLOYEE_ID column has an index and is the primary key.
QUERY USING COALESCE
We can handle an optional parameter more elegantly by using the COALESCE function.
select count(*), sum(salary) from employees where employee_id = coalesce(to_number(:nEmpId), employee_id); -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | TABLE ACCESS FULL| EMPLOYEES | 1 | 8 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------
The execution plan is exactly the same as with the query using a logical OR. Here too, the query ends up fetching data via a FULL TABLE SCAN.
QUERY USING NVL
select count(*), sum(salary) from employees where employee_id = nvl(:nEmpId, employee_id); --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 4 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | VIEW | VW_ORE_B4851255 | 108 | 1404 | 4 (0)| 00:00:01 | | 3 | UNION-ALL | | | | | | |* 4 | FILTER | | | | | | | 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 8 | 1 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 | |* 7 | FILTER | | | | | | | 8 | TABLE ACCESS FULL | EMPLOYEES | 107 | 856 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter(:NEMPID IS NOT NULL) 6 - access("EMPLOYEE_ID"=:NEMPID) 7 - filter(:NEMPID IS NULL)
Even though the NVL query is functionally identical to the previous two, the execution plan suddenly changes! Instead of a single FULL TABLE SCAN, we now see two data access methods:
-
FULL TABLE SCAN – operation #8
-
INDEX UNIQUE SCAN – operation #6
Wait… two access methods? But we’re only querying one table, EMPLOYEES!
Here’s the magic: NVL is clever enough that if the column has an index and we pass a value as a parameter, the optimizer decides to use the index to fetch the data.
Statistics ----------------------------------------------------------- 3 Requests to/from client 2 consistent gets 2 consistent gets examination 2 consistent gets examination (fastpath) 2 consistent gets from cache
:nEmpId parameter empty, Oracle understands that we want to fetch all rows from the table — and in this case, it goes with a FULL TABLE SCAN.Statistics ----------------------------------------------------------- 4 Requests to/from client 6 consistent gets 6 consistent gets from cache 6 consistent gets pin 6 consistent gets pin (fastpath)
So, depending on whether we provide a value for the parameter or leave it empty, we get a completely different execution plan!
Pretty clever 🙂
But what happens if we switch to a newer database version?
Queries with NVL and COALESCE remain unchanged.
COALESCE still rigidly scans the entire table, regardless of the parameter value, while NVL continues to show off its magical trick.
But what about the query with a logical OR condition?
select count(*), sum(salary) from employees where employee_id = :nEmpId or :nEmpId is null;
--------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 4 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | VIEW | VW_ORE_B4851255 | 108 | 1404 | 4 (0)| 00:00:01 | | 3 | UNION-ALL | | 108 | 436 | 4 (0)| 00:00:01 | |* 4 | FILTER | | | | | | | 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 428 | 3 (0)| 00:00:01 | |* 6 | FILTER | | | | | | | 7 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 8 | 1 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------
In the latest 23ai version, the query with OR has finally caught up with NVL. Just like NVL, depending on the value of the :nEmpId parameter, it will either use the INDEX to fetch data (operation #8) or fall back to a FULL TABLE SCAN (operation #5).
COALESCE, however, still hasn’t learned anything — it stubbornly scans every block, full table scan style.
Komentarze
Prześlij komentarz