Hi, Pierre,
Thanks a lot. I got some new opioions after reading your email.
But there is one thing I want to verify. As my understanding, a *primary index* is the same meaning as *cluster index*. Which implies the physical sequence of records should be the same as the sequence of the index.When you declare a index with*cluster* clause, the index will be set as the cluster index; Otherwise, the first index you create for the table will be the cluster index.A primary key is usually the primay index, but not invariably. Since when you create a primary key, an unique index will be created for the table inexplicitly. And this index usually the first index you create for the table.
And could you please verify my new options?
1. The reorg is used to: a) Rearrange physica sequence of the records in tables; b) Free space
1.1 If the physical rearrangement happens, it always rearrange according the cluster index sequence.
1.2 If no index in the table is declared, then physical rearrangement of the records will never happen.
1.3 When a delete happens in a table, the records are not removed physically, but the space they occupied as available. Reorg will physically remove those records to empty the space.
1.4 The "overflow" stands for a pointer to a pointer. That is, if a records is updated to a longer value, and it doesn't have space available, it has to be moved to a new place, and there will be a pointer from the orginal space point to the new place.
1.5 Reorg also deal with the overflow records
2. I can't understand what you mentioned about indexscan. How can a table "rebuild"?
As my understanding, re-order will happen in spite of "indexscan" specified. But by "indexscan", it will scan the index first and then re-order the records; Without "indexscan", the records will be read directly. So I think "indexscan" is faster.
3. Why inplace reorg can do only one of the re-order and place free? From the Command syntax, it seems online reorg can allow both read/write access, but offline reorg can allow only read access; and online reorg can be pause/stop/resume
4. If reorgchk does not give any '*' to indicate the need of a reorg, reorg won't do lot help since little statistic information will be updated so the access plan won't be changed a lot. But, it also won't do any *harm*, in this case the reorg can be finished very fast.
5. Even do reorgchk firstly, the reorg won't use the result of reorgchk. It re-order records of free space according the information get by itself.
Best wishes,
Qin
**********************
*A climber to DBA*
**********************
----- Original Message -----
From: "Pierre Saint-Jacques " <db2udbdba-ezmlmshield-x70509233.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Monday, August 28, 2006 1:58 AM
Subject: Re: Is reorgchk necessary
> This is not quite right.
> A DB2 *offline* reorg always reclaim empty space (deleted rows/pages) and
> always rebuilds all indexes of the table. It is ***very irrelevant*** if
> there is a primary key or not. The reorgchk does not even consider if the
> index is underlying a primary key or not. The reorgchk will just indicate
> if the index needs to be reorg'ed.
>
> If there is an index with the cluster clause defined then that index is used
> to resequence the table.
> If there is no index with a cluster clause then an index can be specified
> with the INDEX clause.
> If neither of those are specified, no resequencing of the table happens.
> This is also true for *online* or INPLACE reorgs.
>
> The physical sort of the table will happen if you do not specify INDEXSCAN.
> In the case of INDEXSCAN then the table is not physically sorted but rebuilt
> in the sequence of the keys of the index specified even if the row location
> sequence does not match the key sequence.
>
> The reorgchk will indicate 3 thresholds that deals with space management and
> reclaiming of space. If any of these is reached, it is recommended to reorg.
> This means that reorg/runstats/rebind *could* give you a better performing
> access plan.
>
> For an *online* INPLACE reorg the table is either resequenced because of
> cluster index or index clause, or, the space in the table is reclaimed.
> Never both. it ia always either/or. It is the presence of a cluster clause
> or, if none, the specification of the INDEX clause that defines what
> happens.
>
> If reorgchk does not give you any '*' to indicate the need of a reorg then
> it is highly doubtful that doing the reorg/runstats/rebind process would
> improve performance. Remeber that reorgchk uses UPDATE STATISITICS as a
> default so if it does not indicate aneed for reorg then runstats/rebind
> would probably not help.
>
> The reorgxchk command uses 5 thesholds for the index. On (F4) is for
> sequencing, thee CLUSTERRATIO. The other 4 are for space management. Again,
> it is completely irrelevant as to whether the index is the primary key.
> What is important is if that index is the **clustering** index. For this
> one and the others, the other formulae indicate to reorg the table (OFFLINE)
> to reclaim space, levels and leaf page management issues.
>
> Hope this helps, Pierre.
>
>
>
> Pierre Saint-Jacques
> SES Consultants Inc.
> 514-737-4515
> ----- Original Message -----
> From: "Kylin" <db2udbdba-ezmlmshield-x63566171.[Email address protected]
> To: "LazyDBA Discussion" <[Email address protected]
> Sent: Sunday, August 27, 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
>>
>
>
>
>
> ---------------------------------------------------------------------
> 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