Query on Index

Vanitha Subramaniam

Query on Index
Hi all,

We have two indexes on a table indx1(col1,col2) and indx2(col1,col2,col3,col4,col5).


1. If we delete indx1, will there be significant performance improvements while inserting.

2. Is indx1 is more advantageous while accessing with col1,col2 than indx2.

Thanks,
Vanitha

________________________________
DISCLAIMER:
This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Steen Rasmussen

Re: Query on Index
(in response to Vanitha Subramaniam)
As often in the DB2 world, there are many answers to a simple question.



You will need less GETP activity using the first index in a query,
meaning less cpu etc.

Having both indexes will hurt performance a bit when insert, update,
delete processing is happening since DB2 needs to manipulate both
indexes.

Your utilities will require more time to maintain two indexes instead of
one.



Dropping the small index will help across the board - except for these
queries which only have predicates on those two columns.



Steen Rasmussen
CA

Sr Engineering Services Architect

IBM Certified Database Associate - DB2 9 Fundamentals

IBM Certified Database Administrator - DB2 9 DBA for z/OS



From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Vanitha_Subramaniam
Sent: Wednesday, December 03, 2008 6:03 AM
To: [login to unmask email]
Subject: [DB2-L] Query on Index



Hi all,



We have two indexes on a table indx1(col1,col2) and
indx2(col1,col2,col3,col4,col5).



1. If we delete indx1, will there be significant performance
improvements while inserting.

2. Is indx1 is more advantageous while accessing with col1,col2
than indx2.



Thanks,

Vanitha



________________________________

DISCLAIMER:
This email (including any attachments) is intended for the sole use of
the intended recipient/s and may contain material that is CONFIDENTIAL
AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or
copying or distribution or forwarding of any or all of the contents in
this message is STRICTLY PROHIBITED. If you are not the intended
recipient, please contact the sender by email and delete all copies;
your cooperation in this regard is appreciated.



________________________________

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA
< http://idug.org/lsNA >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more. If you have not yet signed
up for Basic Membership in IDUG, available at no cost, click on Member
Services < http://www.idug.org/lsms >


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mark Vickers

Re: Query on Index
(in response to Steen Rasmussen)
It depends...if the bulk of work on this table is random access, then the
savings may be significant.
A unique index will produce faster access than one with duplicates.
If the first index is unique, you may not be able to drop it as it could
transgress the business rules for the model.
Analyze the impact - query usage of the indexes by package, drop index1,
rebind affected packages and compare the explains to what you had before.
Mark.



"Rasmussen, Steen" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/03/2008 07:30 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] Query on Index






As often in the DB2 world, there are many answers to a simple question.

You will need less GETP activity using the first index in a query, meaning
less cpu etc.
Having both indexes will hurt performance a bit when insert, update,
delete processing is happening since DB2 needs to manipulate both indexes.
Your utilities will require more time to maintain two indexes instead of
one.

Dropping the small index will help across the board – except for these
queries which only have predicates on those two columns.

Steen Rasmussen
CA
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified Database Administrator - DB2 9 DBA for z/OS

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Vanitha_Subramaniam
Sent: Wednesday, December 03, 2008 6:03 AM
To: [login to unmask email]
Subject: [DB2-L] Query on Index

Hi all,

We have two indexes on a table indx1(col1,col2) and
indx2(col1,col2,col3,col4,col5).

1. If we delete indx1, will there be significant performance
improvements while inserting.
2. Is indx1 is more advantageous while accessing with col1,col2 than
indx2.

Thanks,
Vanitha


DISCLAIMER:
This email (including any attachments) is intended for the sole use of the
intended recipient/s and may contain material that is CONFIDENTIAL AND
PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying
or distribution or forwarding of any or all of the contents in this
message is STRICTLY PROHIBITED. If you are not the intended recipient,
please contact the sender by email and delete all copies; your cooperation
in this regard is appreciated.


IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA
The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at IDUG.ORG under the
Listserv tab. While at the site, you can also access the IDUG Online
Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more. If you have not yet signed up for
Basic Membership in IDUG, available at no cost, click on Member Services


IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA
The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at IDUG.ORG under the
Listserv tab. While at the site, you can also access the IDUG Online
Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more. If you have not yet signed up for
Basic Membership in IDUG, available at no cost, click on Member Services




This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete all copies
of this message.



Richard Fazio

Re: Query on Index
(in response to Mark Vickers)
What's your ratio of Inserts to Selects?



Do you need index 1 to maintain uniqueness?



What's the performance impact to removing index 1? Have you run tests
to document the degree of degradation?



How many statements during a given time period execute using index
1....index 2?



You need to create a CRUD matrix documenting each process, access path,
and number of times executed to answer such a question.



After you do some of this research I think you will be able to see the
answer yourself. There is no default answer, sorry, this one "depends"
upon your environment.



faz



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Vanitha_Subramaniam
Sent: Wednesday, December 03, 2008 6:03 AM
To: [login to unmask email]
Subject: [DB2-L] Query on Index



Hi all,



We have two indexes on a table indx1(col1,col2) and
indx2(col1,col2,col3,col4,col5).



1. If we delete indx1, will there be significant performance
improvements while inserting.

2. Is indx1 is more advantageous while accessing with col1,col2
than indx2.



Thanks,

Vanitha



________________________________

DISCLAIMER:
This email (including any attachments) is intended for the sole use of
the intended recipient/s and may contain material that is CONFIDENTIAL
AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or
copying or distribution or forwarding of any or all of the contents in
this message is STRICTLY PROHIBITED. If you are not the intended
recipient, please contact the sender by email and delete all copies;
your cooperation in this regard is appreciated.


________________________________

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA
< http://idug.org/lsNA >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more. If you have not yet signed
up for Basic Membership in IDUG, available at no cost, click on Member
Services < http://www.idug.org/lsms >


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms