By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,589 Members | 2,276 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,589 IT Pros & Developers. It's quick & easy.

Indexing Question

P: n/a
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!

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/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.
Nov 12 '05 #2

P: n/a
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.


Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.