creating indexes
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (155.6 KB, 26 trang )
Creating Indexes
15
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder15Ć2
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.
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.
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.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder15Ć10
