Indexing Question

[Kent.Brooke]

Can someone set me straight I know indexing is a "try & see" art.
However I am at a loss if it's better to use the INCLUDE switch on a
unique index and tag on the columns be used to avoid a lookup on the
disk. Or if it's better to use all of the columns as part of the index?
One more question, is it a good idea to always have the PK of the table
as part of any index created on a table? The querys don't actualy use
the PK column in their "where" clause, and the act of created a PK
automaticaly creates an index anyways. Any replys much appreciated!

[Mark A]

<Ke*********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Can someone set me straight I know indexing is a "try & see" art.
However I am at a loss if it's better to use the INCLUDE switch on a
unique index and tag on the columns be used to avoid a lookup on the
disk. Or if it's better to use all of the columns as part of the index?
One more question, is it a good idea to always have the PK of the table
as part of any index created on a table? The querys don't actualy use
the PK column in their "where" clause, and the act of created a PK
automaticaly creates an index anyways. Any replys much appreciated!

The unique index created by the PK is how DB2 guarantees that the PK is
unique. If an existing unique index exists before you create the PK, then
DB2 will use it instead of creating another one.

The decision to use the INCLUDE clause depends partly on how many extra
columns and how large they are. You could put all the table columns in an
index with the INCLUDE, and that might speed up some queries, but it will
slow down inserts, updates, deletes, and data loads.

[Pierre Saint-Jacques]

Have you tried submitting the query or a group of queries to the DB2 Advisor
tool.
It would tell you if you need which,what,where indexes and whether include
should be used.

Submit your key queries to your system. launch the DB2 Advisor Wizard from
the Control Centre and see what it recommends.
HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Mark A" <no****@nowhere.com> a écrit dans le message de news:
TN******************************@comcast.com...

<Ke*********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Can someone set me straight I know indexing is a "try & see" art.
However I am at a loss if it's better to use the INCLUDE switch on a
unique index and tag on the columns be used to avoid a lookup on the
disk. Or if it's better to use all of the columns as part of the index?
One more question, is it a good idea to always have the PK of the table
as part of any index created on a table? The querys don't actualy use
the PK column in their "where" clause, and the act of created a PK
automaticaly creates an index anyways. Any replys much appreciated!

The unique index created by the PK is how DB2 guarantees that the PK is
unique. If an existing unique index exists before you create the PK, then
DB2 will use it instead of creating another one.

The decision to use the INCLUDE clause depends partly on how many extra
columns and how large they are. You could put all the table columns in an
index with the INCLUDE, and that might speed up some queries, but it will
slow down inserts, updates, deletes, and data loads.