ok, here's the explain plan output; the index referenced is a non-unique key added to the names column: note the index referenced as: EMP_NAME_IX which is a non-unique index placed on the employee name.
David, the results surprised me as well since the index is not defined as
the way it was referenced in the where clause. I frequently assumed that any
functional transformations (upper, instr, etc.) cancel out the effect of a
non-function based index.
After some additional research, "INDEX FAST FULL SCAN" means that the
database is not using the table itself, but instead the index for finding
the results of the query.
Again, I am using the standard HR.EMPLOYEES table available to most versions
of Oracle. For my example below, I am using the Oracle 11g XE (express)
edition.
SQL> @test_script
EMPLOYEE_ID UPPER_LAST_NAME
----------- -------------------------
130 ATKINSON
156 KING
100 KING
191 PERKINS
Execution Plan
----------------------------------------------------------
Plan hash value: 2832838249
--------------------------------------------------------------------------------
-----------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)|
Time |
--------------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | 5 | 60 | 3
(34)|
00:00:01 |
| 1 | VIEW | index$_join$_001 | 5 | 60 | 3
(34)|
00:00:01 |
|* 2 | HASH JOIN | | | |
|
|
| 3 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK | 5 | 60 | 1
(0)|
00:00:01 |
|* 4 | INDEX FAST FULL SCAN| EMP_NAME_IX | 5 | 60 | 1
(0)|
00:00:01 |
--------------------------------------------------------------------------------
-----------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(ROWID=ROWID)
4 - filter(INSTR(UPPER("LAST_NAME"),'KIN',1,1)>0)
Rich Pascual
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
