Database Reference
In-Depth Information
SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> SELECT emp.last_name, emp.first_name, d.department_name
FROM hr.employees emp, hr.departments d
WHERE emp.department_id=d.department_id
AND d.department_name='Sales';
Execution Plan
----------------------------------------------------------
Plan hash value: 2912831499
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 340 | 4 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 180 | 1 (0)|
| 2 | NESTED LOOPS | | 10 | 340 | 4 (0)|
|* 3 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 16 | 3 (0)|
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)|
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("D"."DEPARTMENT_NAME"='Sales')
4 - access("EMP"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
The execution plan
1
indicates that the index
EMP_DEPARTMENT_IX
would be used if the query
were executed. Let's take a look at
V$OBJECT_USAGE
.
SQL> SELECT table_name, index_name, monitoring, used,
start_monitoring, end_monitoring
FROM v$object_usage
WHERE table_name IN ('EMPLOYEES', 'DEPARTMENTS');
TABLE_NAME INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
----------- ----------------- ---------- ---- ------------------- --------------
DEPARTMENTS DEPT_ID_PK YES NO 10/04/2007 17:21:54
DEPARTMENTS DEPT_LOCATION_IX YES NO 10/04/2007 17:21:55
EMPLOYEES EMP_DEPARTMENT_IX YES YES 10/04/2007 17:21:55
EMPLOYEES EMP_EMAIL_UK YES NO 10/04/2007 17:21:55
…
The index
EMP_DEPARTMENT_IX
is indeed marked as used (column
USED=YES
), even though
merely
EXPLAIN
PLAN
was executed.
Index Rebuild
It is undocumented that an index rebuild affects
V$OBJECT_USAGE
. It sets
V$OBJECT_USAGE.
USED=YES
and
V$OBJECT_USAGE.MONITORING=NO
, i.e., it terminates index monitoring.
1.
To improve legibility, the column TIME was omitted from the execution plan.
