Databases Reference
In-Depth Information
range scan rather than an expensive full table scan. Note that while we classify a function-based index as
a “specialized" index, it is still a regular B-tree index that is created on the basis of a function (or
expression) rather than a column.
Let's say you've the following SQL statement with the
WHERE
clause specifying
UPPER(LAST_NAME):
SQL>select first_name,last_name,phone_number
from employees
where UPPER(last_name) = UPPER('alapati');
As the query's explain plan output shows, the optimizer ignores the index on the LAST_NAME
column and performs a full table scan.
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 30 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("LAST_NAME")='ALAPATI')
The column
LAST_NAME
is indexed, but the optimizer will skip the index and instead perform a full
table scan. The reason for this is that the search is really not on the
LAST_NAME
; it's on
UPPER(last_name)
.
The database treats this as a different search and since there's no index on the UPPER function, it goes
for a full table scan. In order for this query to use an index, you must create an index on the actual search
expression, a function-based index. You can create the following function to make the database use an
index on the
last_name
column:
SQL> create index emp_up_name
on employees (upper(last_name));
