RE: DB2 - Index

["Richard Jackson" <richardjackson@xxxxxxxxxxxxxxxxxx>]

Someone else said it better but there is more to indexes than the index type
or the command that creates them.  A few examples may help to make this
point.

An index can be 3-byte or 4-byte.  The 3-byte kind is called the
"4-gigabyte" type and the 4-byte kind is called a "1-terabyte" type.  The
4GB indexes are smaller and can be slightly faster than 1TB indexes.
However, if there are a lot of key updates, the 1TB index is a far better
choice because it manages contention much better.

Unlike Oracle, the AS/400 doesn't use static data about the contents of
tables to optimize a query.  The AS/400 dynamically builds an "access plan"
using the actual contents of the database table at the instant that the plan
is being constructed.  The access plan saves the results from optimizing the
query.  In most cases, the access plan is saved so that a subsequent query
can reuse the plan without rebuilding it.  If a table changes in certain
ways, the access plan will be rebuilt.  Unfortunately, since there is no
static meta-data stored about the physical data in the table, the optimizer
has to probe the table to determine how many values of this or that are
present and make optimizing decisions based on what it can find in a short
period of time.  However, you can create an Encoded Vector Index.  This is a
kind of dynamically maintained collection of static table information.

Last week or early this week, someone posted a note that, if a certain PTF
is applied, the SQL CREATE INDEX command will create indexes with a larger
page size than you can build with CRTLF.  I don't understand everything that
I know about this but the idea is that you can find your data with fewer
page faults if the index pages are larger.  That means better performance.

I do a lot of database performance tuning with the JDE OneWorld package.
JDE does a lot of index updates so it is much better to use the 1TB index
type.  It is also important to review the SMAPP setting.  Since JDE is doing
lots of index updates, there are a very large number of physical disk write
operations to the system journals for JDE tables.  This is especially true
if batch jobs create a large number of database update operations.  This may
not be important by itself but users may submit jobs using an order by
clause that they specify - the application doesn't prescribe the sequence.
If an index with the right keys for a particular order by doesn't exist, the
application may perform very poorly.  The solution is create a new index
with the right keys.  Except, this new index adds to the list of indexes
being changed during those batch jobs.  This causes more SMAPP writes, and
so forth.

There is more to database indexes and their contribution to performance than
the index type.

I wrote this note because I don't understand your question.  If we are to
answer your question fully, we need a little better idea of what you are
thinking or observing.  If you are having a problem, explain it and tell us
about what you are seeing - we will try to help.  If you don't know where to
start, explain the design and the situation and we will try to help you
understand the issues better.  Why is this question important to you?  I am
pretty sure that the others on the list don't understand your question
either.  Please let us help!

Richard Jackson
mailto:richardjackson@richardjackson.net
www.richardjacksonltd.com
Voice: 1 (303) 808-8058
Fax:   1 (303) 663-4325

-----Original Message-----
From: owner-midrange-l@midrange.com
[mailto:owner-midrange-l@midrange.com]On Behalf Of AKumar@rccl.com
Sent: Friday, July 14, 2000 12:08 PM
To: MIDRANGE-L@midrange.com
Subject: DB2 - Index



Hi,
     I am trying to use different indexes in stored procedure to maximize
the performance .
There are three choices of creating index.
CASE I) Create logical file.
CASE 2) CREATE INDEX ..........
CASE 3) CREATE ENCODED VECTOR INDEX..........

Q 1) What kind of index is created when we create Logical File?
Q 2) If there is some thumb rule (or rule) which I need to follow during
going for a index?

It is very important for us.

Regards,
Ajay Kumar
305-539-6621.


+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---