Database Reference
In-Depth Information
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1303508680
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | T_PK | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"='5')
we get the expected
INDEX UNIQUE
SCAN
, and we can see the function is not being applied. You should
always
avoid
implicit conversions anyway. Always compare apples to apples and oranges to oranges. Another case where this
comes up frequently is with dates. We try to query
-- find all records for today
select * from t where trunc(date_col) = trunc(sysdate);
and discover that the index on
DATE_COL
will not be used. We can either index the
TRUNC(DATE_COL)
or, perhaps more
easily, query using range comparison operators. The following demonstrates the use of greater than and less than on a
date. Once we realize that the condition
TRUNC(DATE_COL) = TRUNC(SYSDATE)
is the same as the condition
select *
from t
where date_col >=trunc(sysdate)
and date_col < trunc(sysdate+1)
this moves all of the functions to the right-hand side of the equation, allowing us to use the index on
DATE_COL
(and
provides the same result as
WHERE TRUNC(DATE_COL) = TRUNC(SYSDATE)
).
If possible, you should always remove the functions from database columns when they are in the predicate.
Not
only will doing so allow for more indexes to be considered for use, but it will also reduce the amount of processing the
database needs to do. In the preceding case, when we used
where date_col >=trunc(sysdate)
and date_col < trunc(sysdate+1)
the
TRUNC
values are computed once for the query, and then an index could be used to find just the qualifying values.
When we used
TRUNC(DATE_COL) = TRUNC(SYSDATE)
, the
TRUNC(DATE_COL)
had to be evaluated once
per row
for
every row in the entire table (no indexes).
