Hi,

On sunday i have done offline reorg of table.

Monday morning when i took database health snapshot it was showing me msg "REORG TABLE INDEX"

Indicator Name = db.tb_reorg_req
Value = 1
Evaluation timestamp = 10/04/2011 10:29:41.058715
Alert state = Attention

Collection:

Name = "SAMPLE"."TEST"
Detail = REORG TABLE INDEX
State = Attention
Evaluation timestamp = 10/03/2011 08:53:37.000000


Do i need to reorg index also after reorging table??? Is it mandatory??

reorg table will reorg data and index
check status with reorgchk and see which indicator is on
might be clusterratio.. and this index is not the clustering index..

Ok... What should i do now??

Table statistics:

F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80

SCHEMA.NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
----------------------------------------------------------------------------------------
Table: SAMPLE.TEST
4320110 0 290743 290743 - 1.13e+09 0 96 100 ---
----------------------------------------------------------------------------------------

Index statistics:

F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (Space used on leaf pages / Space available on non-empty leaf pages) > MIN(50, (100 - PCTFREE))
F6: (100 - PCTFREE) * (Amount of space available in an index with one less level / Amount of space required for all keys) < 100
F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20
F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20

SCHEMA.NAME INDCARD LEAF ELEAF LVLS NDEL KEYS LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEAD F4 F5 F6 F7 F8 REORG
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Table: SAMPLE.TEST
Index: SAMPLE.PK_T_EMAIL
4320110 53073 0 4 0 4320110 32 32 628 628 28 91 9 0 0 *----
--------------------------------------------------------------------------------------------------------------------------------------------------------------

CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
for indexes that are not in the same sequence as the base table. When multiple
indexes are defined on a table, one or more indexes may be flagged as needing
REORG. Specify the most important index for REORG sequencing.

Tables defined using the ORGANIZE BY clause and the corresponding dimension
indexes have a '*' suffix to their names. The cardinality of a dimension index
is equal to the Active blocks statistic of the table.

F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80

Currently CLUSTERRATIO is 28.

Reorganizing the index SAMPLE.PK_T_EMAIL will help me resolve this???

SAMPLE.PK_T_EMAIL is a regular index. How can i increase its CLUSTERRATIO ??

this is a duplicate of
How can we increase CLUSTERRATIO of a regular index??