Database Reference
In-Depth Information
Execution Plan
----------------------------------------------------------
...
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 620 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| T | 999K | 4882K| 620 (1)| 00:00:01 |
---------------------------------------------------------------------------
This example shows the optimizer won't
always
use an index and, in fact, it makes the right choice in skipping
indexes. While tuning your queries, if you discover that an index isn't used when you think it
ought to be
, don't just
force it to be used—test and prove first that the index is indeed faster (via elapsed and I/O counts) before overruling
the CBO. Reason it out.
Case 6
There aren't fresh statistics for tables. The tables used to be small, but now when we look at them, they have grown
quite large. An index will now make sense, whereas it didn't originally. If we generate statistics for the table, it will use
the index.
Without correct statistics, the CBO
cannot
make the correct decisions.
Index Case Summary
In my experience, these six cases are the
main
reasons I find that indexes are not being used. It usually boils down to a
case of “They cannot be used—using them would return incorrect results,” or “They should not be used—if they were
used, performance would be terrible.”
Myth: Space Is Never Reused in an Index
This is a myth that I would like to dispel once and for all: space
is
reused in an index. The myth goes like this: you have
a table,
T
, in which there is a column,
X
. At some point, you put the value
X=5
in the table. Later you delete it. The myth
is that the space used by
X=5
will not be reused unless you put
X=5
back into the index later. The myth states that once
an index slot is used, it will be there forever and can be reused only by the same value. A corollary to this is the myth
that free space is never returned to the index structure, and a block will never be reused. Again, this is simply not true.
The first part of the myth is trivial to disprove. All we need to do is to create a table like this:
EODA@ORA12CR1> create table t ( x int, constraint t_pk primary key(x) );
Table created.
EODA@ORA12CR1> insert into t values (1);
1 row created.
EODA@ORA12CR1> insert into t values (2);
1 row created.
EODA@ORA12CR1> insert into t values (9999999999);
1 row created.
EODA@ORA12CR1> analyze index t_pk validate structure;
Index analyzed.
