|
|
Prior to the advent of the relational database, database users would have to specifically know how
to get at a particular piece of data. The navigation path to that data had to be included as a part of
the request for the data. One of the biggest advances relational databases made was to eliminate
the need for this extra information, which was usually meaningless to the end user, and a source of
complexity and errors.
Oracle, like all true relational databases, does not require any navigation information in an SQL
query. Instead, Oracle maps the logical data request to the underlying physical structures. This feat
makes things easier for developers and users—but it doesn’t eliminate the need to get data
quickly and efficiently.
In response to a query, the Oracle database could simply retrieve all possible rows that could
answer a query and then eliminate the rows that don’t match the selection criteria. But this could be
inordinately inefficient. What if a user wanted to get a handful of rows out of a pool of millions?
Worse yet, what if a user wanted to get rows from a combination of tables, which would cause
Oracle to compare all the rows from all the tables in an enormous Cartesian product?
Indexes are a way to reduce the overhead of these common data operations. In true relational fashion,
the existence of indexes is transparent to the user. But an index, properly used, can dramatically
speed up retrieval operations. Even more importantly, an index can provide consistent performance
for random data access when data is fairly evenly distributed). When a query requests a row, it
doesn’t matter whether the row is located in the overall table; an index will perform the same number
of I/O operations to get the row. In the field of performance, expectation is everything, so this
consistent performance is, in some ways, at least as important as optimal performance.
This chapter covers all of the index types supported by Oracle Database 10g, including B-tree
indexes, reverse indexes, bitmap indexes, function-based indexes, and domain indexes. In addition,
this chapter looks at index-organized tables as well as clustered tables. Finally, the chapter concludes
with some broad advice on the design and use of indexes.Index Basics
The Oracle database supports a number of different types of indexes, but all or most of these index types
share a number of basic characteristics. All indexes are used to speed access to data. All indexes use
some kind of internal structure to either reduce the amount of data required to read to determine which
rows to retrieve, or deliver to those rows faster than scanning through the table that contains the rows.
And all indexes are transparent to users, which means you can add, change, or drop indexes without
changing any application code.
How an Index Works
The power of an index to speed data retrieval comes from one of the most basic facts of modern computing
architecture—the slowest function in the overall system is retrieving data from disk. When this simple
physical fact is coupled with usage patterns of users, the power of an index becomes clear.
The example at the start of the chapter pointed out how going through all the rows in a table to select a
small percentage of them for a query could require a lot of resources. An index is designed to let the
Oracle database find and retrieve specific rows with less overall I/O.
The central way an index accomplishes this is by storing a subset of the information in the complete row
in a more compact and searchable organization. These attributes mean that there is less I/O involved to
use an index to locate a particular piece of data, improving performance.
In addition, an index, which is much smaller than the table it operates on, is more likely to be cached
than the table itself. Using an index that is completely cached in memory can be an extremely fast way to
access data.
An index can be used to determine which rows should be returned for a query. An index entry includes
a pointer to the actual data row, which is the fastest way to retrieve a data row. Once the Oracle database
has used the index to identify target rows, the pointer is used to retrieve the row.
Usage patterns come into play when you are deciding what indexes to create for a particular table. Your
users may want different columns in a table at different times, but they will typically use a small number
of columns to select and sort data in queries. These columns are ideal candidates for indexes.SapereOnLine |
|
|
|
|
|
