Indexes - Expert Oracle Database Architecture - page 468

■ You might be wondering why I invoked DELETE_INDEX_STATS above, it is because in Oracle 10 g and above,
a CREATE INDEX automatically does a COMPUTE STATISTICS as it creates the index. therefore, in this case, Oracle was
“tricked—it thinks it sees a table with 1,000,000 rows and a teeny tiny index on it (the table really only has 14 rows after
all). the index statistics were accurate, the table statistics were “fake.” I needed to “fake” the index statistics as well—or
I could have loaded the table up with 1,000,000 records before indexing it.

Note

And then we'll perform our queries:

EODA@ORA12CR1> set autotrace traceonly explain
EODA@ORA12CR1> select count(*)
2 from emp, dept
3 where emp.deptno = dept.deptno
4 and dept.dname = 'SALES'
5 /

Execution Plan
----------------------------------------------------------
Plan hash value: 2538954156

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | BITMAP CONVERSION COUNT | | 250K| 732K| 7 (0)| 00:00:01 |
|* 3 | BITMAP INDEX SINGLE VALUE| EMP_BM_IDX | | | | |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("EMP"."SYS_NC00009$"='SALES')

As you can see, to answer this particular question, we did not have to actually access either the EMP or DEPT
table—the entire answer came from the index itself. All the information needed to answer the question was available
in the index structure.
Further, we were able to skip accessing the DEPT table and, using the index on EMP that incorporated the data we
needed from DEPT , gain direct access to the required rows:

EODA@ORA12CR1> select emp.*
2 from emp, dept
3 where emp.deptno = dept.deptno
4 and dept.dname = 'SALES'
5 /