Re: Very High BLKS_GETS_PER_ACCESS, Clustering Factor?

"SAP BASIS Consultant" <basis_consultant_at_hotmail.com> wrote in message news:dd2036f3.0305260737.524b6331_at_posting.google.com...
> Hi,
>
> On a v8.1.5 (AIX) system, there are some indexes, about 40MB
> or 50MB in size, for which INDEX_STATS (After analysis) shows
> the following:
>
> PCT_USED: 90% (PCTFREE is 10%)
> LF_ROWS: 225000 (Or some other very large number)
> DEL_LF_ROWS: 0
> HEIGHT: 3
> BLKS_GETS_PER_ACCESS: 241 (542 in another and 5749 in another!).
>
> I rebuilt one of them, reanalyzed it, and the numbers are the same.
>
> Aside from BLKS_GETS_PER_ACCESS, the index seems in very good shape.
>
> However, BLKS_GETS_PER_ACCESS is very poor. Is this a case of
> very poor clustering (In which I cannot do much aside from
> reorganizing the table), or should I look into somehing else?
>

Adding to what others have said if this is a compound index, then it might well be a target for compression with the *least* selective column first. Yes I do mean least selective.

Its also a candidate for an upgrade to 8.1.7 but thats another tale.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************