Databases Reference
In-Depth Information
SQL> select b,e from test where e > 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 703934364
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9901 | 69307 | 7 (0)|00:00:01|
|* 1 | INDEX FAST FULL SCAN| TEST_IDX1 | 9901 | 69307 | 7 (0)|00:00:01|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E">100)
SQL>
Note that unlike in the case of a full index scan, an index fast full scan uses a multiblock read
operation to read the index. Thus, this type of scan tends to be faster both due to the multiblock I/O as
well as to the fact that this type of scan can run in parallel, just as a full table scan.
Determining Whether a Query Uses an Index
DBAs and developers often wonder if the database is using a certain index. Your explain plans may
indicate the use of the index, but you may want to make sure that the database is actually using the
index. You can easily track the usage of an index by enabling the monitoring of index usage in your
database. By default, Oracle Database doesn't monitor index usage. You can make the database monitor
an index by altering the index you're interested in with the
monitoring usage
clause, as shown here:
SQL> alter index employees_idx1 monitoring usage;
Index altered.
SQL>
Once you turn index monitoring on, the database tracks the usage of the index
EMPLOYEES_IDX1
. You
can query the
V$OBJECT_USAGE
view to see if the database is using the index.
SQL> select index_name,monitoring,used from v$object_usage;
INDEX_NAME MONITORING USED
------------------- ------------------ -----------
EMPLOYEES_IDX1 YES YES
SQL>
