Should I create an index when there's already a composite index?

[bobby_b_]

I have a table where fields 1 and 2 make up the primary key.
Because of this, I have a unique composite index on fields 1 and 2 (as
required by DB2). Now my question is: Fields 1 and 2 are also
separate foreign keys to two other tables. I have read that it is
always a good idea to create indexes on foreign keys. Should I create
single indexes on each of these fields? Or is that not necessary since
they are already part of a composite index?
Actually the same question applies (forgetting about the foreign key
stuff) if I decide that field 2 is very frequently going to be part of
the WHERE clause in my query. Normally I would make field 2 an index
because of this. Should I still do that, even though field 2 is
already part of the composite index?

[Rhino]

"bobby_b_" <bo******@hotmail.com> wrote in message
news:11*********************@u72g2000cwu.googlegro ups.com...

I have a table where fields 1 and 2 make up the primary key.
Because of this, I have a unique composite index on fields 1 and 2 (as
required by DB2). Now my question is: Fields 1 and 2 are also
separate foreign keys to two other tables. I have read that it is
always a good idea to create indexes on foreign keys. Should I create
single indexes on each of these fields? Or is that not necessary since
they are already part of a composite index?
Actually the same question applies (forgetting about the foreign key
stuff) if I decide that field 2 is very frequently going to be part of
the WHERE clause in my query. Normally I would make field 2 an index
because of this. Should I still do that, even though field 2 is
already part of the composite index?

Assuming that field 1 is to the left of field 2 in the index, i.e. the
definition is

Create index my_index on my_table(field1, field2)

not

Create index my_index on my_table(field2, field1)

you don't need a separate foreign key index on field1 because DB2 can use
the primary key index in its place. The performance should normally be the
same.

However, you will still want the separate foreign key index on field2.

--
Rhino

[Mark A]

"bobby_b_" <bo******@hotmail.com> wrote in message
news:11*********************@u72g2000cwu.googlegro ups.com...

I have a table where fields 1 and 2 make up the primary key.
Because of this, I have a unique composite index on fields 1 and 2 (as
required by DB2). Now my question is: Fields 1 and 2 are also
separate foreign keys to two other tables. I have read that it is
always a good idea to create indexes on foreign keys. Should I create
single indexes on each of these fields? Or is that not necessary since
they are already part of a composite index?
Actually the same question applies (forgetting about the foreign key
stuff) if I decide that field 2 is very frequently going to be part of
the WHERE clause in my query. Normally I would make field 2 an index
because of this. Should I still do that, even though field 2 is
already part of the composite index?

The term "using an index" is a loaded term, because there are two ways for
DB2 to use an index. The first is via the B-Tree, which is the fastest
access, and is what most people expect when they think of using an index.

The other way is a complete index scan, in which DB2 reads the entire index
from top to bottom, ignoring the non-leaf pages (which are the "table of
contents" of the index). A complete index scan is faster than a table scan
(usually) but usually not nearly as fast as using the B-Tree.

In order to use the B-Tree of an index, the predicate of the SQL statement
must supply at least one of the leftmost columns of the columns of the
index. The WHERE clause does not have to supply all of the columns in index
for DB2 to use the B-tree of the index. So if your WHERE clause says:

WHERE Col2 = ?

and your index is on (Col1, Col2), then DB2 cannot use the B-Tree and will
either do a complete index scan or a table scan (depending on which it
thinks is faster to complete the processing of the SQL statement).

Therefore, you would probably want a separate index just on Col2 (plus the
unique index for the primary key on Col1, Col2) if you used the above
predicate with just Col2 fairly often.

However, your assumption that all Foreign keys need indexes is incorrect,
but that is another subject. You might try the Google Groups archives for
previous posts on this subject.