Indexes help us retrieve data
from tables quicker. Let's use an
example to illustrate this point:
Say we are interested in reading
about how to grow peppers in a
gardening book. Instead of reading
the book from the beginning until
we find a section on peppers, it
is much quicker for us to go to
the index section at the end of
the book, locate which pages
contain information on peppers,
and then go to these pages
directly. Going to the index first
saves us time and is by far a more
efficient method for locating the
information we need.
The same principle applies for
retrieving data from a database
table. Without an index, the
database system reads through the
entire table (this process is
called a 'table scan') to locate
the desired information. With the
proper index in place, the
database system can then first go
through the index to find out
where to retrieve the data, and
then go to these locations
directly to get the needed data.
This is much faster.
Therefore, it is often
desirable to create indexes on
tables. An index can cover one or
more columns. The general syntax
for creating an index is:
CREATE
INDEX "INDEX_NAME" ON "TABLE_NAME"
(COLUMN_NAME)
Let's assume that we have the
following table,
TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)
and we want to create an index
on the column Last_Name, we would
type in,
CREATE
INDEX IDX_CUSTOMER_LAST_NAME
on CUSTOMER (Last_Name)
If we want to create an index
on both City and Country, we would
type in,
CREATE
INDEX IDX_CUSTOMER_LOCATION
on CUSTOMER (City, Country)
There is no strict rule on how
to name an index. The generally
accepted method is to place a
prefix, such as "IDX_", before an
index name to avoid confusion with
other database objects. It is also
a good idea to provide information
on which table and column(s) the
index is used on.
Please note that the exact
syntax for CREATE
INDEX may
be different for different databases.
You should consult with your
database reference manual for
the precise syntax
