Database Reference
In-Depth Information
When Should You Use a B*Tree Index?
Not being a big believer in “rules of thumb” (there are exceptions to every rule), I don't have any rules of thumb for
when to use (or not to use) a B*Tree index. To demonstrate why I don't have any rules of thumb for this case, I'll
present two equally valid ones:
•
Only use B*Tree to index columns if you are going to access a very small percentage of the
rows in the table via the index.
•
Use a B*Tree index if you are going to process many rows of a table and the index can be used
instead of
the table.
These rules seem to offer conflicting advice, but in reality, they do not—they just cover two extremely different
cases. There are two ways to use an index given the preceding advice:
•
As the means to access rows in a table
: You will read the index to get to a row in the table.
Here you want to access a very small percentage of the rows in the table.
•
As the means to answer a query
: The index contains enough information to answer the entire
query—we will not have to go to the table at all. The index will be used as a
thinne
r version of
the table.
There are other ways as well—for example, we could be using an index to retrieve
all
of the rows in a table,
including columns that are not in the index itself. That seemingly goes counter to both rules just presented. The case
in which that would be true would be an interactive application where you are getting some of the rows and displaying
them, then some more, and so on. You want to have the query optimized for initial response time, not overall
throughput.
The first case (i.e., use the index if you are going to access a small percentage of the table) says if you have a
table
T
(using the same table
T
from earlier) and you have a query plan that looks like this:
EODA@ORA12CR1> set autotrace traceonly explain
EODA@ORA12CR1> select owner, status
2 from t
3 where owner = USER;
Execution Plan
----------------------------------------------------------
Plan hash value: 1695850079
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ...
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1716 | 17160 | 13 (0)| ...
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | T | 1716 | 17160 | 13 (0)| ...
|* 2 | INDEX RANGE SCAN | DESC_T_IDX | 288 | | 2 (0)| ...
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(SYS_OP_DESCEND("OWNER")=SYS_OP_DESCEND(USER@!))
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))=USER@!)
