If a large table is ordered (clusterratio of the cluster index is 90%+) then
the sort of the table may be shoreter (better yet use INDEXSCAN) which means
no sort will happen and this shortens the reorg.
However, the space will still need to be reclaimed and this implys a large
movement of rows within pages and may still take a lot of time.
Regards, Pierre.
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
----- Original Message -----
From: "Kylin" <db2udbdba-ezmlmshield-x7356844.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Monday, August 28, 2006 11:02 AM
Subject: Re: Is reorgchk necessary


> Hi,
>
> Thanks for reply.
>
> But I still think, if a large table is ordered, even I run reorg, it won't
> take a lot of time, right?
>
> Maybe I should search how reorg works in fact. :)
>
> Best wishes,
> Qin
>
> **********************
> *A climber to DBA*
> **********************
> ----- Original Message -----
> From: "hsteiner" <db2udbdba-ezmlmshield-x45578342.[Email address
> protected]
> To: "LazyDBA Discussion" <[Email address protected]
> Sent: Monday, August 28, 2006 10:55 PM
> Subject: Re: Is reorgchk necessary
>
>
>> REORG can be a time and resource consuming job on a large table, so doing
>> it selectively (based on REORGCHK results) makes sense. It will
>> reorganize based on the "first" index created (typically the PK index)
>> unless you specify a different index. I strongly recommend looking into
>> REORGing by an index other than the PK - pick the index you do the most
>> "set" processing on. For example, REORGing an employee table by deptcode
>> will make deptcode set queries run much faster. When you are retrieving
>> individual employee rows by a unique index, the index clustering doesn't
>> really matter.
>>
>> RUNSTATS should *ALWAYS* follow a REORG, so your statistics accurately
>> reflect the reorganized result.
>>
>> REBIND is a good idea so that your plans take advantage of the optimal
>> strategy based on the REORG. For example, before the REORG the
>> clusterratio of a certain index might have been poor, discouraging its
>> use, but after the REORG your clusterratio might be 100% again, and the
>> rebind would optimize accordingly.
>>
>> My 2 cents.
>>
>> Best, Hal Steiner
>>
>>
>>
>> -----Original Message-----
>> From: db2udbdba-ezmlmshield-x63566171.[Email address protected]
>> To: [Email address protected]
>> Sent: Sun, 27 Aug 2006 9:45 AM
>> Subject: Is reorgchk necessary
>>
>>
>>
>> Hi,
>>
>> As my understanding, reorg will physically move records in a table
>> according the
>> primary index sequence. So that durning the query, less IO operations are
>> needed.
>>
>> reorgchk will generate a report accoding some statistic informations. It
>> seems
>> there will be a threshold. When this threshold reaches, it will mark the
>> table
>> or index with a * to tell reorg is necessary.
>>
>> But from my point of view, doing reorg as frequently as possible is
>> better. When
>> disordered records accumulate to a certain degree, it will cost longer
>> time to
>> run reorg. It just likes if you sort your book once a week or once a
>> month.
>>
>> So I think reorg should be perfrom in spite of the result of reorgchk. If
>> a
>> table is ordered, "reorg" won't move any records. It works only when a
>> table is
>> disordered. Besides, reorgchk itself costs time to generate the report.
>>
>> Following is my preferred Database Maintenance Process:
>>
>> reorg --> runstats -> rebind
>>
>> I'm also not clear about the reorg on index. Since there can be only one
>> primary
>> index for one table, so the records can sort according only one sequence.
>> What
>> will the reorg of index do? Sort the index itself but not records in the
>> table?
>>
>> Please feel free to correct me if you find any issues. And any comments
>> will be
>> greatly appreciated.
>>
>> Best wishes,
>> Qin
>>
>> **********************
>> *A climber to DBA*
>> **********************
>>
>>
>> ---------------------------------------------------------------------
>> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>> To post a dba job: http://jobs.lazydba.com
>> To Subscribe : http://www.LazyDBA.com
>> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>> ________________________________________________________________________
>> Check out AOL.com today. Breaking news, video search, pictures, email and
>> IM. All on demand. Always Free.
>>
>>
>> ---------------------------------------------------------------------
>> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>> To post a dba job: http://jobs.lazydba.com
>> To Subscribe : http://www.LazyDBA.com
>> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>>
>>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>



DB2 & UDB email list listserv db2-l LazyDBA home page