Indices - Short Path to Data


An INDEX is a list of record addresses, kept in order on
one or more columns known as the keys.

This program shows the performance difference between Index and
no-Indexed table.  Module indx() runs the query:


       SELECT * FROM BMCHUGH.PERFDAT3
       WHERE CU_SSN   BETWEEN  '111-11-1020' AND '111-11-1140';


against table PERFDAT3 where an index has been defined on column
CU_SSN. Since CU_SSN is the only column in the WHERE clause, the
optimizer chose to do an INDEX SCAN.

Module noindex() runs the same query against PERFDAT4 where no
indices have been defined and the optimizer is forced to do
a TABLE SCAN (sequential search).


CONCEPTS:

Optimizer, access plan, binding, CREATE INDEX, key.

WHY USE INDICES

Because the rows of the base table are unordered an
index satisfies that ordering requirement, reduces disk
access by retrieveing data without lengthy sequential searching,
and reduces the number of records read.


WHEN AN INDEX IS USED

The database manager optimizer uses an index whenever the index
presents an efficient path to the data.

Consider the types of queries that are likely to be made
against a table to decide whether certain indices will help
their performance.


PERFORMANCE  ADVANTAGE

Indices always exist on a primary key of larger tables.
Creating the appropriate indices can reduce response time
considerably in some situtations.

Optimizer may or may not decide to use an index:

 example 1. on small tables a table scan may be cheaper.
 example 2. extracting a large number or rows.

It is much faster to import a table, and then create the index than
to create the index and then import the table, Due to the added overhead of
updating the index.

Creating indexes can reduce response time by 90% or more
in certain situtations.


PERFORMANCE  DISADVANTAGE

Any number of indices can be defined on a particular base table. However
the more indices there are for a table, the more indexes database manager
has to maintain during update, insert, or delete operations.

Indices do take space on the disk.


RUNSTAT

The optimizer uses the system table statistics to determine whether
an index wil be used or not.  The staitistics might get outdated
over time.  Therefore after indices are created, execute the RUNSTATS
utility to update the table statics.  Additionally, you must REBIND
any application program that accesses the table so that a new
access plan may be generated that is aware of the new index.