How does include column in index help.

Priya Kalra

How does include column in index help.
If we add a column as an include part of the index, how does it help in
performance.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Wayne Driscoll

Re: How does include column in index help.
(in response to Priya Kalra)
Priya,
When an index contains include columns, it may help performance, but it
may also hurt performance. If the include column is involved in a
SELECT that only selects columns from the index, and the index is used
to access the rows, by having the column INCLUDED in the index, you can
avoid accessing the table data. However, if the column is frequently
updated, then there will be some performance drawbacks do the need to
update the index key's as well as the table. Also, include columns will
increase the size of the index, which could impact the number of levels,
which could reduce the amount of queries that utilize this index.
Wayne Driscoll
Sr. Software Developer
Quest Software
[login to unmask email]
NOTE: All opinions are strictly my own. EMail Address in sig must be
modified.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Priya Kalra
Sent: Thursday, December 04, 2003 10:00 PM
To: [login to unmask email]
Subject: [DB2-L] How does include column in index help.


If we add a column as an include part of the index, how does it help in
performance.

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". If you will be out of the office,
send the SET DB2-L NO MAIL command to [login to unmask email] The
IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Zhong Shi

Re: How does include column in index help.
(in response to Wayne Driscoll)
My understand is then you have an indexes for that column.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Priya Kalra
Sent: Thursday, December 04, 2003 11:00 PM
To: [login to unmask email]
Subject: How does include column in index help.


If we add a column as an include part of the index, how does it help in
performance.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Steve Westfall

Re: How does include column in index help.
(in response to Zhong Shi)
Zhong,

Priya was referring to the INCLUDE optional keyword on the CREATE INDEX
statement. (I believe that in Version 7 this option exists only for
non-mainframe versions. I'm not sure if it's being added for Version 8 on Z/OS.)
When an index is created, the optional INCLUDE clause can be used to append
additional columns to the set of index key columns. Such additional columns are
NOT "indexed" (at least, not in the index being created; it's possible that they
are indexed in some other index).

In V7.2 this option was only available when creating a UNIQUE index. I'm not
sure if that restriction was later removed. Anyone?

See the DB2 SQL Reference manual and Wayne Driscoll's reply to Priya for further
discussion. As Wayne points out, in some situations the appended columns can
improve performance, but in others they can degrade it, so you need to analyze
your applications to determine whether its use is appropriate for you.

Steve Westfall
Equifax, Inc.
Lombard, Illinois




"Shi, Zhong"
<[login to unmask email] To: [login to unmask email]
TURE.COM> cc:
Sent by: DB2 Subject: Re: How does include column in index help.
Data Base
Discussion List
<[login to unmask email]
.ORG>


12/05/2003 12:38
PM
Please respond
to DB2 Database
Discussion list
at IDUG





My understand is then you have an indexes for that column.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Priya Kalra
Sent: Thursday, December 04, 2003 11:00 PM
To: [login to unmask email]
Subject: How does include column in index help.


If we add a column as an include part of the index, how does it help in
performance.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page
at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or
Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL
command to [login to unmask email] The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page
at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or
Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL
command to [login to unmask email] The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Jim Brown

Re: How does include column in index help.
(in response to Steve Westfall)
You could potentially have index only access in your queries.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Priya Kalra
Sent: Thursday, December 04, 2003 11:00 PM
To: [login to unmask email]
Subject: How does include column in index help.

If we add a column as an include part of the index, how does it help in
performance.

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". If you will be out of the office,
send the SET DB2-L NO MAIL command to [login to unmask email] The
IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm


*************************************************************************************
The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you have received this email in error please contact the sender and delete the material from any computer.
*************************************************************************************

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Alexandros Papadopoulos

Re: How does include column in index help.
(in response to Jim Brown)
To my understanding, the purpose of the INCLUDE statement in the LUW version
7.2 of DB2 is quite simple:
You can have a unique index as required by the logic of your data and you
can include some extra columns in order to avoid data access (to get the
equivalent of INDEXOLNY access path).

hth
Alekos
-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Friday, December 05, 2003 9:23 PM
To: [login to unmask email]
Subject: Re: How does include column in index help.

Zhong,

Priya was referring to the INCLUDE optional keyword on the CREATE INDEX
statement. (I believe that in Version 7 this option exists only for
non-mainframe versions. I'm not sure if it's being added for Version 8 on
Z/OS.)
When an index is created, the optional INCLUDE clause can be used to append
additional columns to the set of index key columns. Such additional columns
are
NOT "indexed" (at least, not in the index being created; it's possible that
they
are indexed in some other index).

In V7.2 this option was only available when creating a UNIQUE index. I'm
not
sure if that restriction was later removed. Anyone?

See the DB2 SQL Reference manual and Wayne Driscoll's reply to Priya for
further
discussion. As Wayne points out, in some situations the appended columns
can
improve performance, but in others they can degrade it, so you need to
analyze
your applications to determine whether its use is appropriate for you.

Steve Westfall
Equifax, Inc.
Lombard, Illinois




"Shi, Zhong"
<[login to unmask email] To:
[login to unmask email]
TURE.COM> cc:
Sent by: DB2 Subject: Re: How does include
column in index help.
Data Base
Discussion List
<[login to unmask email]
.ORG>


12/05/2003 12:38
PM
Please respond
to DB2 Database
Discussion list
at IDUG





My understand is then you have an indexes for that column.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Priya Kalra
Sent: Thursday, December 04, 2003 11:00 PM
To: [login to unmask email]
Subject: How does include column in index help.


If we add a column as an include part of the index, how does it help in
performance.

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page
at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join
or
Leave the list". If you will be out of the office, send the SET DB2-L NO
MAIL
command to [login to unmask email] The IDUG List Admins can be reached
at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page
at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join
or
Leave the list". If you will be out of the office, send the SET DB2-L NO
MAIL
command to [login to unmask email] The IDUG List Admins can be reached
at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
can be reached at [login to unmask email] Find out the latest on
IDUG conferences at http://conferences.idug.org/index.cfm


****************************************************************************
**********
National Bank of Greece Group email disclaimer
This message is intended only for the addressee(s). If you are not included
in the intended recipient list you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
information is strictly prohibited.

Employees of National Bank of Greece Group are expressly required not to
make defamatory statements and not to infringe or authorize any infringement
of copyright or any other legal right by e-mail communications. Any such
communication is contrary to company policy and outside the scope of the
employment of the individual concerned.
The company will not accept any liability in respect of such communication,
and the employee responsible will be personally liable for any damages or
other liability arising. Employees who receive such an e-mail must notify
their supervisor immediately.
****************************************************************************
**********


***************************************************************************************
Äéåõêñßíéóç çëåêôñïíéêïý ôá÷õäñïìåßïõ
Ïé ðëçñïöïñßåò ðïõ óõìðåñéëáìâÜíïíôáé óå áõôü ôï ìÞíõìá åßíáé åìðéóôåõôéêÝò êáé ç ÷ñÞóç ôïõò åðéôñÝðåôáé ìüíïí áðü ôïí áíáöåñüìåíï ðáñáëÞðôç. ÅÜí Ý÷åôå ëÜâåé ôï ðáñüí ìÞíõìá áðü ëÜèïò êáé äåí åßóôå ï ðñïïñéæüìåíïò ðáñáëÞðôçò, óáò åíçìåñþíïõìå üôé áðïêÜëõøç, áíáðáñáãùãÞ, äéáíïìÞ Þ ïðïéáóäÞðïôå Üëëçò ìïñöÞò ÷ñÞóç ôùí ðåñéå÷ïìÝíùí ôïõ ðáñüíôïò ìçíýìáôïò áðáãïñåýåôáé. Åðßóçò ðáñáêáëåßóèå íá áðïóôåßëåôå ôï áñ÷éêü ìÞíõìá óôç äéåýèõíóç [login to unmask email], êáèþò êáé óôç óõíÝ÷åéá íá äéáãñÜøåôå ôï ìÞíõìá áðü ôï óýóôçìÜ óáò.
Ïé åðéêïéíùíßåò ìÝóù ôïõ Äéáäéêôýïõ äåí åßíáé áóöáëåßò êáé ãéá ôïí ëüãï áõôü ï ¼ìéëïò Åôáéñéþí ôçò ÅèíéêÞò ÔñÜðåæáò ôçò ÅëëÜäïò äåí áðïäÝ÷åôáé íïìéêÞ åõèýíç ãéá ôá ðåñéå÷üìåíá ôïõ ðáñüíôïò ìçíýìáôïò êáé ãéá ïðïéáäÞðïôå æçìéÜ ðñïêëçèåß áðü éïýò ðïõ åßíáé äõíáôüí íá åéóáãÜãåé. Ïé áðüøåéò ðïõ äéáôõðþíïíôáé áíÞêïõí áðïêëåéóôéêÜ óôïí áðïóôïëÝá ôïõ ìçíýìáôïò êáé äåí áíôéðñïóùðåýïõí áðáñáßôçôá ôéò áðüøåéò ôïõ Ïìßëïõ Åôáéñéþí ôçò ÅèíéêÞò ÔñÜðåæáò ôçò ÅëëÜäïò.
Óáò Åõ÷áñéóôïýìå,
¼ìéëïò Åôáéñéþí ÅèíéêÞò ÔñÜðåæáò ôçò ÅëëÜäïò

Email Disclaimer
The information in this email is confidential and is intended solely for the addressee(s). If you have received this transmission in error, and you are not an intended recipient, be aware that any disclosure, copying, distribution or use of this transmission or its contents is prohibited. Furthermore, you are kindly requested to send us back the original message at the address [login to unmask email], and delete the message from your system immediately.
Internet communications are not secure and therefore the National Bank of Greece Group does not accept legal responsibility for the contents of this message and for any damage whatsoever that is caused by viruses being passed. Any views or opinions presented are solely those of the author and do not necessarily represent those of National Bank of Greece Group.
Thank You,
National Bank of Greece Group
***************************************************************************************

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mark Ruhe

Re: How does include column in index help.
(in response to Alexandros Papadopoulos)
As of version 8 this is still just for unique indexes.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of [login to unmask email]
Sent: Monday, December 08, 2003 1:12 AM
To: [login to unmask email]
Subject: Re: How does include column in index help.


To my understanding, the purpose of the INCLUDE statement in the LUW version
7.2 of DB2 is quite simple:
You can have a unique index as required by the logic of your data and you
can include some extra columns in order to avoid data access (to get the
equivalent of INDEXOLNY access path).

hth
Alekos
-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Friday, December 05, 2003 9:23 PM
To: [login to unmask email]
Subject: Re: How does include column in index help.

Zhong,

Priya was referring to the INCLUDE optional keyword on the CREATE INDEX
statement. (I believe that in Version 7 this option exists only for
non-mainframe versions. I'm not sure if it's being added for Version 8 on
Z/OS.)
When an index is created, the optional INCLUDE clause can be used to append
additional columns to the set of index key columns. Such additional columns
are
NOT "indexed" (at least, not in the index being created; it's possible that
they
are indexed in some other index).

In V7.2 this option was only available when creating a UNIQUE index. I'm
not
sure if that restriction was later removed. Anyone?

See the DB2 SQL Reference manual and Wayne Driscoll's reply to Priya for
further
discussion. As Wayne points out, in some situations the appended columns
can
improve performance, but in others they can degrade it, so you need to
analyze
your applications to determine whether its use is appropriate for you.

Steve Westfall
Equifax, Inc.
Lombard, Illinois




"Shi, Zhong"
<[login to unmask email] To:
[login to unmask email]
TURE.COM> cc:
Sent by: DB2 Subject: Re: How does include
column in index help.
Data Base
Discussion List
<[login to unmask email]
.ORG>


12/05/2003 12:38
PM
Please respond
to DB2 Database
Discussion list
at IDUG





My understand is then you have an indexes for that column.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Priya Kalra
Sent: Thursday, December 04, 2003 11:00 PM
To: [login to unmask email]
Subject: How does include column in index help.


If we add a column as an include part of the index, how does it help in
performance.

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page
at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join
or
Leave the list". If you will be out of the office, send the SET DB2-L NO
MAIL
command to [login to unmask email] The IDUG List Admins can be reached
at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page
at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join
or
Leave the list". If you will be out of the office, send the SET DB2-L NO
MAIL
command to [login to unmask email] The IDUG List Admins can be reached
at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
can be reached at [login to unmask email] Find out the latest on
IDUG conferences at http://conferences.idug.org/index.cfm


****************************************************************************
**********
National Bank of Greece Group email disclaimer
This message is intended only for the addressee(s). If you are not included
in the intended recipient list you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
information is strictly prohibited.

Employees of National Bank of Greece Group are expressly required not to
make defamatory statements and not to infringe or authorize any infringement
of copyright or any other legal right by e-mail communications. Any such
communication is contrary to company policy and outside the scope of the
employment of the individual concerned.
The company will not accept any liability in respect of such communication,
and the employee responsible will be personally liable for any damages or
other liability arising. Employees who receive such an e-mail must notify
their supervisor immediately.
****************************************************************************
**********


***************************************************************************************
??e??????s? ??e?t??????? ta??d???e???
?? p????f???e? p?? s??pe???a??????ta? se a?t? t? ?????a e??a? e?p?ste?t???? ?a? ? ???s? t??? ep?t??peta? ????? ap? t?? a?afe???e?? pa?a??pt?. ??? ??ete ???e? t? pa??? ?????a ap? ????? ?a? de? e?ste ? p????????e??? pa?a??pt??, sa? e???e??????e ?t? ap???????, a?apa?a????, d?a???? ? ?p??asd?p?te ????? ???f?? ???s? t?? pe??e??????? t?? pa???t?? ?????at?? apa???e?eta?. ?p?s?? pa?a?a?e?s?e ?a ap?ste??ete t? a????? ?????a st? d?e????s? [login to unmask email], ?a??? ?a? st? s????e?a ?a d?a????ete t? ?????a ap? t? s?st??? sa?.
?? ep????????e? ??s? t?? ??ad??t??? de? e??a? asfa?e?? ?a? ??a t?? ???? a?t? ? ?????? ?ta????? t?? ??????? ???pe?a? t?? ????d?? de? ap?d??eta? ?????? e????? ??a ta pe??e???e?a t?? pa???t?? ?????at?? ?a? ??a ?p??ad?p?te ????? p??????e? ap? ???? p?? e??a? d??at?? ?a e?sa???e?. ?? ap??e?? p?? d?at?p????ta? a?????? ap???e?st??? st?? ap?st???a t?? ?????at?? ?a? de? a?t?p??s?pe???? apa?a?t?ta t?? ap??e?? t?? ?????? ?ta????? t?? ??????? ???pe?a? t?? ????d??.
Sa? ???a??st???e,
?????? ?ta????? ??????? ???pe?a? t?? ????d??

Email Disclaimer
The information in this email is confidential and is intended solely for the addressee(s). If you have received this transmission in error, and you are not an intended recipient, be aware that any disclosure, copying, distribution or use of this transmission or its contents is prohibited. Furthermore, you are kindly requested to send us back the original message at the address [login to unmask email], and delete the message from your system immediately.
Internet communications are not secure and therefore the National Bank of Greece Group does not accept legal responsibility for the contents of this message and for any damage whatsoever that is caused by viruses being passed. Any views or opinions presented are solely those of the author and do not necessarily represent those of National Bank of Greece Group.
Thank You,
National Bank of Greece Group
***************************************************************************************

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Raquel Rodriguez

INCLUDE Column
(in response to Mark Ruhe)
UDB V 8.0 for LUW. Have a table T1 with a uniqe index
defined on columns COL1, COL2 with INCLUDE of COL3.
So, what will the access path be for the following
query:

SELECT COL1, COL2 FROM T1 WHERE COL3 = 'ABC'

Will it be an 'index only' scan or will DB2 have to go
to the datapages to fetch records. Assuming the table
has 20k records and runstats has been run.

Note that COL3 (which appears in the WHERE predicate)
is only an INCLULDEd column.

TIA
Raquel.


__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm