 |
|
Oracle PL/SQL Indexing
Oracle Tips by
Burleson
|
Think back to the last time you were in a library and needed to
find a particular book. While it’s certainly feasible to start at the
beginning of the shelves and read through each book title to look for
that book, it would take hours (or, more likely, days) to find the
right book.
Instead of looking through every book, you probably went to a card
catalog (or to a computer terminal) and, based on a few keywords, were
able to find your book fairly quickly. That card catalog (or the
database that you accessed through the terminal) is a perfect example
of an index.
When an index is created on a table, Oracle creates a data
structure very similar to a card catalog. This structure is updated
whenever an UPDATE, DELETE, or INSERT is
performed on the table. Indexes are created in two ways.
-
A primary key for a table is associated
with a unique index; this index is a composite set of all the
columns that have the primary key constraint.
-
A CREATE INDEX statement is run. This is
the case with all indexes except for the table’s primary key index.
An index provides Oracle with a way to quickly locate and retrieve
data from its tables. When tables start to contain thousands of rows,
it becomes crucial that indexes be used. In order to achieve high
performance, SQL statements then have to be carefully tuned to use
(and, in some cases, not to use) one or more indexes for a table.
As an application developer, you should keep an eye out for
situations in which none of the indexes on a table is appropriate for
the task that your code must accomplish. If this is the case, you
should consult your DBA. It might be necessary for the DBA to add an
index to a table if your code is to perform as well as possible.
TIP: Finding The Indexes For A Table
Before writing code against a table, it’s a good
idea to get a listing of all the indexes on the table. You can
execute this query at the SQL prompt to retrieve a listing of the
indexes for a specified table, as shown in Listing 2.3.
Listing 2.3 Finding the indexes for a
table.
SELECT index_name, column_name
FROM ALL_IND_COLUMNS
WHERE table_name = '&1'
ORDER BY index_name, column_name;
When prompted for a value for &1, type in
the name of the table for which you want to find the indexes. If
your database has the same table in multiple schemas, modify this
query to include the owner of the table.
This is an
excerpt from the book "High Performance Oracle Database
Automation" by Jonathan Ingram and Donald K. Burleson, Series
Editor. |
