Databases Reference
In-Depth Information
A B-tree Index contains the data for the fields included in the index and the ROWIDs of the
rows that contain the same values as for the indexed fields. These values are stored in index
entries, packed together in the leaf nodes of the tree representing the index.
When the indexed fields of a row are (all) NULL, the
corresponding index entry is not stored in the index.
A peculiarity to this structure is in the way the leaf nodes are connected. They are linked
to both the left and right adjacent leaf nodes, to allow the operation of range scan, which
is useful when we have a predicate similar to
WHERE FIELD BETWEEN A AND B
. In this
situation, we visit the tree structure to reach the leaf node related to the value
A
, and then
we jump to the adjacent leaf node that contains the value
B
, without having to visit the
entire tree structure again.
We have seen in this recipe how to build an index, a function-based index, and a descending
index. The last type is useful when we want to use queries in which we want the data sorted in
a mixed ascending/descending way on the fields indexed. If we use a regular index to access
the data, Oracle is unable to do the sort in a mixed way, in a query like this:
SELECT FIELD1, FIELD2
FROM TABLE
WHERE FIELD1 BETWEEN A AND B
ORDER BY FIELD1 DESC, FIELD2
We have a regular (ascending) index in place on
FIELD1
,
FIELD2
.
If we create an index like this:
CREATE MIXED_INDEX ON TABLE (FIELD1 DESC, FIELD2)
Then, we can use the index to access the data and retrieve the rows sorted, that is, we don't
have to do a
SORT
step after retrieving the rows.
In the last two queries, we have seen how the Oracle database uses an
INDEX FAST FULL
SCAN
operation to retrieve the rows when there is a predicate, which involves the second field
of the index (
CUST_FIRST_NAME
) but not the first (
CUST_LAST_NAME
).
This operation consists of visiting all the database blocks of the index, without any order or
access path, to retrieve the data we are looking for. Why doesn't the fifth query of our
TEST
CASE
use an index?
Let's try to answer this question. The following is the query from the
TEST CASE
:
SELECT CUST_FIRST_NAME, CUST_LAST_NAME,
CUST_YEAR_OF_BIRTH, CUST_EMAIL
FROM CUSTOMERS WHERE CUST_FIRST_NAME = 'Darby';
