REORG vs REBUILD indexes

Rao A

REORG vs REBUILD indexes
I have a question on REORG vs REBUILD index. (v7.0)

To make the story short, we have a situation where one of my DBAs created a table, clustered index, followed by load. Later on, we realized that the order of columns in the clustered index is not the way we wanted. We dropped the cluster index and recreated the same index with a different column sequence.

When one of my SQLs access that index, I see lots of getpages in the DSN activity against that index in the Omegamon.

Should I go ahead with REORG of that index, or rebuild.. I thought both would do the same.

On a second thought, I am thinking we should run REORG on a tablespace rather than on a index because the tablespace data pages are not in the clustered order of the index..

Thanks
Rao.


____________________________________________________________________________________
Looking for last minute shopping deals?
Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the 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: REORG vs REBUILD indexes
(in response to Rao A)
Hello Rao,



Basically REBUILD and REORG do two different things. REBUILD will build
the index by reading the tablespace while REORG will read the index. I
prefer to do the REBUILD if possible since any potential "problems" in
the index will be eliminated.



Steen Rasmussen
CA

Principal Technical Specialist DB2 tools

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 Rao A
Sent: Thursday, February 07, 2008 11:56 AM
To: [login to unmask email]
Subject: [DB2-L] REORG vs REBUILD indexes



I have a question on REORG vs REBUILD index. (v7.0)



To make the story short, we have a situation where one of my DBAs
created a table, clustered index, followed by load. Later on, we
realized that the order of columns in the clustered index is not the way
we wanted. We dropped the cluster index and recreated the same index
with a different column sequence.



When one of my SQLs access that index, I see lots of getpages in the DSN
activity against that index in the Omegamon.



Should I go ahead with REORG of that index, or rebuild.. I thought both
would do the same.



On a second thought, I am thinking we should run REORG on a tablespace
rather than on a index because the tablespace data pages are not in the
clustered order of the index..



Thanks

Rao.







________________________________

Never miss a thing. Make Yahoo your homepage.
< http://us.rd.yahoo.com/evt=51438/*http:/www.yahoo.com/r/hs >


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.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 >

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the 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

Andy Lankester

Re: REORG vs REBUILD indexes
(in response to Steen Rasmussen)
I assume you meaning CLUSTERING INDEX, in which case if you change the column order you must, as you say, REORG the tablespace with SORTDATA YES.

Andy

_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Rao A
Sent: Thursday, February 07, 2008 5:56 PM
To: [login to unmask email]
Subject: [DB2-L] REORG vs REBUILD indexes


I have a question on REORG vs REBUILD index. (v7.0)

To make the story short, we have a situation where one of my DBAs created a table, clustered index, followed by load. Later on, we realized that the order of columns in the clustered index is not the way we wanted. We dropped the cluster index and recreated the same index with a different column sequence.

When one of my SQLs access that index, I see lots of getpages in the DSN activity against that index in the Omegamon.

Should I go ahead with REORG of that index, or rebuild.. I thought both would do the same.

On a second thought, I am thinking we should run REORG on a tablespace rather than on a index because the tablespace data pages are not in the clustered order of the index..

Thanks
Rao.



_____

Never miss a thing. HYPERLINK "http://us.rd.yahoo.com/evt=51438/*http://www.yahoo.com/r/hs"Make Yahoo your homepage.
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at HYPERLINK "http://www.idug.org/lsidug"www.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 HYPERLINK "http://www.idug.org/lsconf"IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on HYPERLINK "http://www.idug.org/lsms"Member Services


No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.21/1263 - Release Date: 06/02/2008 20:14



No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.21/1263 - Release Date: 06/02/2008 20:14


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the 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

Avram Friedman

Re: REORG vs REBUILD indexes
(in response to Andy Lankester)
Rao
Eventually you want to reorg the base table space and do runstats.
The urgency of this depends on the number of unique values in the high level
columns that were not moved around vs the number of rows in the table.
To the extent that the
Number of rows in the table / Number of unique index values that did not
relocate is less than the number of rows per page then REORG is generally
speaking not urgent as the expected base table page locations have not
changed statisticatly.

I think your comment about the number of index get pages is a red herring.
As a stand alone observation it just means you are using the index which
would normally be considered to be wonderfull.
Are you seeing
Unexpected IO?
Unexpected Sync Reads?
Sync Writes?

Of course all of the above is my view and may not apply to your own
specialized or unique requirements.

Avram Friedman

On Thu, 7 Feb 2008 09:56:03 -0800, Rao A <[login to unmask email]> wrote:

>I have a question on REORG vs REBUILD index. (v7.0)
>
>To make the story short, we have a situation where one of my DBAs created
a table, clustered index, followed by load. Later on, we realized that the order
of columns in the clustered index is not the way we wanted. We dropped the
cluster index and recreated the same index with a different column sequence.
>
>When one of my SQLs access that index, I see lots of getpages in the DSN
activity against that index in the Omegamon.
>
>Should I go ahead with REORG of that index, or rebuild.. I thought both
would do the same.
>
>On a second thought, I am thinking we should run REORG on a tablespace
rather than on a index because the tablespace data pages are not in the
clustered order of the index..
>
>Thanks
>Rao.
>
>
>
__________________________________________________________________
__________________
>Looking for last minute shopping deals?
>Find them fast with Yahoo! Search.
http://tools.search.yahoo.com/newsearch/category.php?category=shopping
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list
archives, the 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

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the 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

Nick CIANCI

Re: REORG vs REBUILD indexes
(in response to Avram Friedman)
Rao

Depends on size but I'd generally do a "CREATE INDEX ... DEFER YES"
and then REBUILD Index. It's generally faster and you get a good index out
of it.

In v7 We specified SORTKEYS and got a "turbo boost" on PI rebuilds make use
of parallelism. In v8 the key word goes and the implications are that you
now get the parallelism auto-magically.

It won't hurt to ReOrg the table ("all things being equal")

However if you are seeing a lot of activity on the index rather than the
TableSpace then ReOrging the TS may not give you anything extra. Be wary
to distinguish between I\O and GetPages as well. If you are getting a lot
more Get Pages than what you expect, you check for things like NearOffPosF,
FarOffPosF & PseudoDeletes. If the "OffPosF" values are blowing out to
frequently consider changing your FreeSpace parameters.

For on-Line maintenance you'll need to use REORG INDEX! As as far as I'm
aware there is no SHRLEVEL CHANGE option with REBUILD, just REORG.

However you increased GetPage rate may just be that the index is being made
greater use off

HTH

Regards,
   Nick CIANCI

Database Administration

IBM Certified Solutions Expert
- DB2 UDB V7.1 Database Administration for OS/390



Rao A
<[login to unmask email]
M> To
Sent by: DB2 Data [login to unmask email]
Base Discussion cc
List
<[login to unmask email] Subject
ORG> [DB2-L] REORG vs REBUILD indexes


08/02/08 04:56 AM


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-l.org>






I have a question on REORG vs REBUILD index. (v7.0)

To make the story short, we have a situation where one of my DBAs created a
table, clustered index, followed by load. Later on, we realized that the
order of columns in the clustered index is not the way we wanted. We
dropped the cluster index and recreated the same index with a different
column sequence.

When one of my SQLs access that index, I see lots of getpages in the DSN
activity against that index in the Omegamon.

Should I go ahead with REORG of that index, or rebuild.. I thought both
would do the same.

On a second thought, I am thinking we should run REORG on a tablespace
rather than on a index because the tablespace data pages are not in the
clustered order of the index..

Thanks
Rao.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the 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

Rao A

Re: REORG vs REBUILD indexes
(in response to Nick CIANCI)
I am sorry for not being clear. You are right. I meant CLUSTERING index. Also I did check the accesspath and it is index only access on that index. Hence, REORG on tablespace might not really do any good to reduce index getpages.



----- Original Message ----
From: Andy Lankester <[login to unmask email]>
To: [login to unmask email]
Sent: Thursday, February 7, 2008 1:20:51 PM
Subject: Re: [DB2-L] REORG vs REBUILD indexes


I assume you meaning CLUSTERING INDEX, in which case if you change the column order you must, as you say, REORG the tablespace with SORTDATA YES.

Andy




From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Rao A
Sent: Thursday, February 07, 2008 5:56 PM
To: [login to unmask email]
Subject: [DB2-L] REORG vs REBUILD indexes


I have a question on REORG vs REBUILD index. (v7.0)

To make the story short, we have a situation where one of my DBAs created a table, clustered index, followed by load. Later on, we realized that the order of columns in the clustered index is not the way we wanted. We dropped the cluster index and recreated the same index with a different column sequence.

When one of my SQLs access that index, I see lots of getpages in the DSN activity against that index in the Omegamon.

Should I go ahead with REORG of that index, or rebuild.. I thought both would do the same.

On a second thought, I am thinking we should run REORG on a tablespace rather than on a index because the tablespace data pages are not in the clustered order of the index..

Thanks
Rao.





Never miss a thing. Make Yahoo your homepage.
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.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

No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.21/1263 - Release Date: 06/02/2008 20:14



No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.21/1263 - Release Date: 06/02/2008 20:14


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.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


____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the 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

David Seibert

Re: REORG vs REBUILD indexes
(in response to Rao A)
Sounds to me like for this particular process, you don't need to reorg
the tablespace.

BUT, assuming you have other processes which will benefit from the order
of the data being in the new, correct clustering index order, you will
sooner or later need to reorg the tablespace.
As I understand the scene as you describe it, the data now is in
clustering order by the OLD wrong clustering index.

Dave




The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.


From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Rao A
Sent: Friday, February 08, 2008 7:10 AM
To: [login to unmask email]
Subject: Re: [DB2-L] REORG vs REBUILD indexes


I am sorry for not being clear. You are right. I meant CLUSTERING index.
Also I did check the accesspath and it is index only access on that
index. Hence, REORG on tablespace might not really do any good to reduce
index getpages.



----- Original Message ----
From: Andy Lankester <[login to unmask email]>
To: [login to unmask email]
Sent: Thursday, February 7, 2008 1:20:51 PM
Subject: Re: [DB2-L] REORG vs REBUILD indexes


I assume you meaning CLUSTERING INDEX, in which case if you change the
column order you must, as you say, REORG the tablespace with SORTDATA
YES.

Andy

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Rao A
Sent: Thursday, February 07, 2008 5:56 PM
To: [login to unmask email]
Subject: [DB2-L] REORG vs REBUILD indexes


I have a question on REORG vs REBUILD index. (v7.0)

To make the story short, we have a situation where one of my DBAs
created a table, clustered index, followed by load. Later on, we
realized that the order of columns in the clustered index is not the way
we wanted. We dropped the cluster index and recreated the same index
with a different column sequence.

When one of my SQLs access that index, I see lots of getpages in the DSN
activity against that index in the Omegamon.

Should I go ahead with REORG of that index, or rebuild.. I thought both
would do the same.

On a second thought, I am thinking we should run REORG on a tablespace
rather than on a index because the tablespace data pages are not in the
clustered order of the index..

Thanks
Rao.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the 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