Database Reference
In-Depth Information
You should be accessing a very small percentage of this table. The issue to look at here is the
INDEX (RANGE SCAN)
followed by the
TABLE ACCESS BY INDEX
ROWID
. This means that Oracle will read the index and then, for the index
entries, it will perform a database block read (logical or physical I/O) to get the row data. This is not the most efficient
method if you are going to have to access a large percentage of the rows in
T
via the index (we will soon define what a
large percentage might be).
In the second case (i.e., when the index can be used
instead
of the table), you can process 100 percent (or any
percentage, in fact) of the rows via the index. You might use an index just to create a thinner version of a table. The
following query demonstrates this concept:
EODA@ORA12CR1> select count(*)
2 from t
3 where owner = user;
Execution Plan
----------------------------------------------------------
Plan hash value: 293504097
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 10 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN | T_IDX | 1716 | 5148 | 10 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"=USER@!)
Here, only the index was used to answer the query—it would not matter now what percentage of rows we were
accessing, as we would use the index only. We can see from the plan that the underlying table was never accessed; we
simply scanned the index structure itself.
It is important to understand the difference between the two concepts. When we have to do a
TABLE ACCESS BY
INDEX
ROWID
, we must ensure we are accessing only a small percentage of the total blocks in the table, which typically
equates to a small percentage of the rows, or that we
need
the first rows to be retrieved as fast as possible (the end user
is waiting for them impatiently). If we access too high a percentage of the rows (larger than somewhere between 1 and
20 percent of the rows), then it will generally take longer to access them via a B*Tree than by just full scanning the table.
With the second type of query, where the answer is found entirely in the index, we have a different story. We
read an index block and pick up many rows to process, then we go on to the next index block, and so on—we never
go to the table. There is also a
fast full scan
we can perform on indexes to make this even faster in certain cases. A fast
full scan is when the database reads the index blocks in no particular order; it just starts reading them. It is no longer
using the index as an index, but even more like a table at that point. Rows do not come out ordered by index entries
from a fast full scan.
In general, a B*Tree index would be placed on columns that we use frequently in the predicate of a query, and
we would expect some small fraction of the data from the table to be returned or the end user demands immediate
feedback. On a
thin
table (i.e., a table with few or small columns), this fraction may be very small. A query that uses
this index should expect to retrieve 2 to 3 percent or less of the rows to be accessed in the table. On a
fat
table (i.e., a
table with many columns or very wide columns), this fraction
might
go all the way up to 20 to 25 percent of the table.
This advice doesn't always seem to make sense to everyone immediately; it is not intuitive, but it is accurate. An index
is stored sorted by index key. The index will be accessed in sorted order by key. The blocks that are pointed to are
stored randomly in a heap. Therefore, as we read through an index to access the table, we will perform lots of
scattered
,
