Databases Reference
In-Depth Information
Tip
The
ROWID
uniquely identifies a row for heap-organized tables. However, with table clusters, it is possible
to have rows in different tables that are physically located in the same block and have identical
ROWIDs
.
In the prior example, suppose there are millions of records in the
CUST
table but only one record in
the table with the last name of STARK. The query optimizer can inspect the index and within a few disk
reads locate the exact location (via the
ROWID
) of the one block within the table that contains the record
of interest. This results in very fast performance. In this case, it wouldn't matter if there were millions
and millions more records in the table; as long as the value contained in the index is fairly unique, Oracle
will be able to return the required rows with a minimal amount of disk reads.
Conversely, consider if the value in the
LAST_NAME
column wasn't very unique. Suppose millions of
records in the
CUST
table had the value of LEE. If the query optimizer did use the index, it would have to
read from the index millions of times, retrieve the
ROWID
s, and then also read from the table millions of
times. In this situation, it's faster to bypass the index and instead scan every block in the table. For this
reason, sometimes the optimizer calculates that the index isn't beneficial to performance and ignores it.
Tip
The higher the degree of uniqueness, the more efficient a B-tree index becomes. In database jargon, a very
selective (unique) column value compared to the total number of rows in a table is said to have
high cardinality
.
Conversely,
low cardinality
refers to few unique values compared to the total rows for the table.
There's another interesting scenario we should point out. Suppose instead of selecting all column
values out of the
CUST
table, you only select the
LAST_NAME
column.
select last_name
from cust
where last_name = 'STARK';
In this scenario, since the index contains all of the values in the
SELECT
clause, Oracle is able to
satisfy the results of the query by only accessing the index. Oracle doesn't have to read the table
structure itself. When the
SELECT
clause columns are all contained with an index, this is known as a
covering index. These indexes are particularly efficient because only the index blocks need to be read.
Before reading on, let's review the concepts introduced up to this point in the chapter.
•
Indexes are optional objects defined on a table and one or more columns.
•
Indexes consume resources.
•
A B-tree index is the default index type in Oracle.
•
A fairly unique column value compared to all other rows in a table results in a
more efficient B-tree index.
•
When appropriately created, indexes improve performance.
