Databases Reference
In-Depth Information
provide guidelines and recommendations for choosing which columns to index. If you're new to indexes
or require a refreshing, start here.
Improving Performance with Indexes
How exactly does an index improve query performance? To understand how an index works, consider
the following simple example. Suppose you create a table to hold customer information, like so:
create table cust
(cust_id number
,last_name varchar2(30)
,first_name varchar2(30));
Your business grows quickly; after a short time, millions of customers are created. You run daily
reports against this table and notice that performance has progressively decreased when issuing queries
like this:
select cust_id, last_name, first_name
from cust
where last_name = 'STARK';
When there was hardly any data in the table, this query returned in sub-seconds. Now, with over a
million rows and growing, this query is taking longer and longer. What's going on here?
When a SQL select statement executes, the Oracle query optimizer quickly calculates a step-by-step
execution plan
detailing how it will retrieve column values specified in the query. In calculating the plan,
the optimizer determines which tables and indexes will be used to retrieve data.
When no index exists, the table itself is the only access path available to satisfy the results of the
query. In this scenario, Oracle has no choice but to inspect every row within every used block in the table
(this is known as a
full table scan
) to see if there are rows with the last name of STARK. As more data is
inserted into this table, the query takes longer. The
cost
of this query (as a measure of CPU, memory, and
I/O resources consumed) is proportional to the number of table blocks. The only way to make this query
run faster is to buy better hardware...or use a performance enhancing feature such as an index.
You can peak ahead in this chapter and determine that an index on columns that appear in the
WHERE
clause of a SQL query might improve performance and decide to create an index on the
CUST
table's
LAST_NAME
column, like so:
create index cust_idx1
on cust(last_name);
This statement creates a B-tree index
(more on this later). This is the default index type in Oracle.
After creating the index, the performance of queries selecting by last name returns to sub-second timing.
Life is good.
To understand how the index improves performance, recall that an index stores two types of
information: the value of the table column(s) and the corresponding
ROWID.
The
ROWID
uniquely
identifies a row (for heap-organized tables) within a database and contains its physical location (datafile,
block, and row position within block). Once the index is created and subsequent queries execute, the
query optimizer considers whether the index will reduce the amount of resources required to return the
results of the query.
