Creating Indexes
15
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder15Ć2
Schedule: Timing Topic
25 minutes Lecture
25 minutes Practice
50 minutes Total
Class Management Note:
Files required for this lesson are:
Demonstration: None
Practice: None
Creating Indexes 15Ć3
Objectives
If you want to improve the performance of some queries, you should consider
creating an index. You can also use indexes to enforce uniqueness on a column or
a collection of columns.
At the end of this lesson, you should be able to
D
Distinguish between the indexes that are created automatically and those that are
created manually.
D
Identify the uses for indexes.
D
Explain the index structure and why it improves query speed.
D
Create a non-unique index.
D
Remove an index from the data dictionary.
D
Evaluate guidelines for creating and using indexes.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder15Ć4

Creating Indexes 15Ć5
Overview
An Oracle7 Server index is a database object that can speed up the retrieval of rows
by using a pointer. Indexes can be created explicitly or automatically. They are
transparent to the user. If you do not have an index on the column, then a full table
scan will occur.
What Is an Index?
An index is a database object that provides direct and fast access to rows in a table.
Its purpose is to reduce the necessity of disk I/O by using a B*Tree indexed path to
locate data quickly. The index is automatically used and maintained by the Oracle7
Server. Once an index is created, no direct activity is required by the user.
Indexes are logically and physically independent of the table they index. This means
that they can be created or dropped at any time and have no effect on the base tables
or other indexes.
How Are Indexes Created?
Two types of indexes can be created. One type is a unique index. The Oracle7 Server
automatically creates this index when you define a column in a table to have a
PRIMARY KEY or a UNIQUE constraint. The name of the index is the name given
to the constraint.
The other type of index a user can create is a non-unique index. For example, you can
create a FOREIGN KEY column index for a join in a query to improve retrieval
speed.
For more information, see
Oracle7 Server Concepts Manual, Release 7.3, “Schema Objects” section, “Indexes”
topic.
Technical Note:
The decision to create indexes is a global, high-level decision. Creation and
maintenance of indexes is often a DBA task.
Reference the column that has an index in the predicate WHERE clause
without modifying the indexed column with a function or expression.

A ROWID is a hexadecimal string representation of the row address
containing block identifier, row location in the block, and the database file
identifier. The fastest way to access any particular row is by referencing its
ROWID.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder15Ć6
Creating Indexes 15Ć7
When Is the Index Used?
Once the index has been created, the Oracle7 Server will use it whenever possible to
speed up access to the data. Note that this use is automatic and usually requires no
action by the user. A brief guideline is provided below on how the Server determines
to use the index.
Optimization Techniques
When an index is used depends partly on the Oracle Optimizer being used at the time.
The Oracle7 Server uses both rule-based and cost-based optimization.
Rule-based optimization is when the Oracle7 Server decides when it is appropriate to
use an index based on its internal rules. The Server identifies the columns that are
indexed and the index types.
The cost-based optimization method uses statistics about tables along with
information about available indexes to select an execution plan for the SQL
statements.
For more information, see
Tune Oracle7 Applications course description.
Technical Note:
Some developers and DBAs create indexes for specific reporting runs, run
the reports, and then delete the indexes. Large indexes can be extremely
storage-intensive.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder15Ć8
Creating Indexes 15Ć9
Index Structure
An index is an optional structure that is independent of the table structure. Each index

is composed of column values that the index is on and pointers (or ROWIDs) to the
row containing that value. The pointer directly points to the appropriate row in the
table, therefore avoiding a full table scan.
B*Tree
The Oracle7 Server uses a balanced B*tree index structure. This is a binary,
self-balancing search structure to equalize access times to any row. It is an efficient
method of ensuring that access to any specified value will take approximately the
same time whether the row is at the beginning, middle, or end of the table.
Each index that the Oracle7 Server builds consists of a number of pages (or branches)
of storage arranged in a tree. Each page (or branch) holds a series of key values and
pointers to pages (or branches) lower in the structure until eventually the key values
indicate the location of the data itself. The location identifier at the database level is
called a ROWID.
Class Management Note:
Looking at the accompanying graphic of the S_EMP table, suppose there
were 20,000 employee rows in the table and a SELECT * FROM S_EMP
WHERE LAST_NAME = ’Menchu’ statement was executed. That would
amount to 20,000 potential I/Os even if the only Menchu in the table was
hit on the first read because the whole table is scanned. However, with an
index on the LAST_NAME column, a shorter search would occur. Starting
at the root (or top) of the index, the Server decides which side of the root
the value Menchu falls. It continues on the appropriate page (or branch)
until it identifies the block that contains Menchu. It accesses the ROWID to
point directly to the row location of the Menchu. Therefore, only four
potential I/Os occurred instead of a potential 20,000.
Technical Note:
An index does not have to be in the same tablespace as the associated table.
Furthermore, performance of queries that use an index can be improved by
storing an index and its table in different tablespaces located on different
disk drives because both the index and the table data can be retrieved in

parallel.
When an index is created, the columns to be indexed are fetched and sorted,
and the ROWID is stored along with the index value for each row.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder15Ć10
Class Management Note:
Primary keys can be a combination of up to 16 column values. It is
therefore important that an index can check across those columns for unique
combinations of values.