rebuilding skewed indexes

[CENSUSDBA]

What is the best way to rebuild a skewed index. Here are the parameters:

INDEX_NAME UNIQUENESS BLEV LEAF_BLOCKS
DISTINCT_KEYS NUM_ROWS AVG_LEAF_BLOCKS_PER_KEY
AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR SAMPLE_SIZE

JB_ACT_CODE NONUNIQUE 5 8

9 115 1

12 115 115


********************
Thanx

[davey23uk]

there is only one way to rebuild an index

alter index xxx rebuild

But what is this 'skewed' you talk about - what so you see as wrong with it

[dbasan]

It should be

ALTER INDEX [schema.]index REBUILD ;

A skewed index has records clumped close together due to
their similar indexed values, as a result may cause disk contention.
BLEVEL > 4 needs a rebuild.

[davey23uk]

from ask tom

for example says
" I like to use the rule of thumb that if the blevel column in DBA_INDEXES is
greater 4, it's time to rebuild the index."

Is this a rule to say "should be rebuild"

Thanks Tom


Followup:

if the blevel is 5
and you rebuild
and the blevel stays 5
hmmm, what then?

or it goes to 4 but a week later it is 5 (and it worked really hard to get there
again).

did the have perhaps an incomplete article? are they missing the part where you
check to see if you did more good than harm to your system? that what you did
had any sort of positive effect anywhere?

[dbasan]

" I like to use the rule of thumb that if the blevel column in DBA_INDEXES is 
greater 4, it's time to rebuild the index."

Is this a rule to say "should be rebuild"

Analyze index and Index_stats show whether the index actually needs a rebuild.

Further details/scripts in metalink doc 77574.1

[CENSUSDBA]

Is this method also helpful? I assume its just another method to gather statistics.

begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 7
);
end;

[davey23uk]

whats your actual problem?