Databases Reference
In-Depth Information
The execution plan for this query shows that the optimizer is using the function-based index you
created.
Execution Plan
----------------------------------------------------------
Plan hash value: 1134195146
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | 5 | 425 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 5 | 425 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_LSTNAME | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("LAST_NAME") LIKE 'S%_A')
filter(UPPER("LAST_NAME") LIKE 'S%_A')
SQL>
This example showed how to create a function-based index using the Oracle function
UPPER
. You
can create a function-based index based on expressions without ever using any function, as shown in
the following example:
SQL> create index test_idx
on weather ((maxtemp-mintemp) desc, maxtemp);
The index
test_idx
materializes the values of the expressions and stores the differences between
maxtemp
and
mintemp
. If you want to find all the rows where the difference in temperatures is less than 25
and the maximum temperature is greater than 80, you can do so by issuing the following statement:
SQL> select * from weather
where ((maxtemp-mintemp) < '25' and maxtemp > '80');
You can also create an index on a
CASE
statement (or function) by using the following syntax:
SQL> create index case_tab_idx1 on case_tab (case source_tran when 'PO'
then po_id when 'VOUCHER' then voucher_id else journal_id end)
SQL> /
Index created.
This example shows how to use a function-based index for indexing only specific rows in a table.
You can issue a query such as the following to query the
case_tab
table:
select source_tran, case when source_tran like 'GL%' then journal_id
when source_tran like 'PO%' then po_id
when source_tran like 'VO%' then voucher_id
…
