Question about faroffpos - extn....

[login to unmask email]

Question about faroffpos - extn....
Neil,

As others have already said, FAROFFPOS and NEAROFFPOS relate to the degree of
correct clustering sequence in your base data - not non-clustering index
entries.

For the 'health of the index', checkout the LEAFDIST column. This give a value
indicating the sequence of the index entries for the order of the index columns.
(see Admin guide for more details).

I am using LEAFDIST to try to sort out what a good level of free space is
appropriate for my indexes, away from the defaults. The guide and various other
books give a range of 'health tests' for indexes. I have included the ones I
use below. BUT REMEMBER, even leafdist can be misleading if you have free
pages defined for your index, a zero value is not achieved (again the manual is
somewhat oblique here). The important thing is to determine your baseline
values on your monitoring columns, such as leafdist, and keep an eye on their
rate of change - more change from baseline means greater fragmentation.

My tests:
-- INDEXES : LEAFDIST TEST where................. AND
IP.LEAFDIST > 2000 -- ONE MEASURE ONLY !!
--
----------------------------------
-- INDEXES : NLEAF VS LEAFDIST TEST
where ............AND (IX.NLEAF*0.1) < (IP.LEAFDIST/100)
-- REORG INDEX ??
-- ...............................

----------------------------------
-- INDEXES : REORG TSPC - CLUSTER < 95%
where ..........................

AND IP.CARD > 0

AND IX.CLUSTERRATIO < 95 -- REORG TSPC ??

AND IX.CLUSTERING = 'Y'

----------------------------------
-- INDEXES : REORG TSPC - 10% CARD < 10% FAROFF+NEAROFF -- but
check number of pages involved, worthwhile ?
where ......................

AND (IP.CARD*0.10) < (IP.FAROFFPOS + IP.NEAROFFPOS)
-- REORG TSPC ??
AND IX.CLUSTERING = 'Y'


I can forward the full SQL if you are interested. Contact me direct.

Thanks
Allan Caldwell
__________________________________________________
Database Administrator
CSC
212 Northbourne Avenue, Braddon ACT 2612 Australia
Ph: (02) 6246 8179 Fax: (02) 6246 8077 Email: [login to unmask email]