Question about Index (Rebuild or not ) 
Author Message
 Question about Index (Rebuild or not )

I have read somewhere (about when an index need to be drop and rebuild
).
It said that I have to run ANALYZE INDEX command to validate Structure,
then
calculate the ratio of LF_BLK_LEN/(LF_BLK_LEN+BR_BLK_LEN).  If it is not
greater that 0.7 then the ratio should be rebuild. It also said that if
the ratio of LF_BLK_LEN/(LF_BLK_LEN+BR_BLK_LEN) is nearing 0.3, the
index
definitely need to be drop and recreate or Rebuild.

Well, I have just create a new table say.. TABLE_A.  I also created an
index
in one of the column.  When the table is empty, I run the ANALYZE INDEX
command
to validate Structure and calculate the ratio of
LF_BLK_LEN/(LF_BLK_LEN+BR_BLK_LEN).
The result is 1.0.
Then, I load the data into that table using sqlload (about 1.3 million
record).
After loading the data, I ran the ANALYZE INDEX command + calculate the
ratio
of LF_BLK_LEN/(LF_BLK_LEN+BR_BLK_LEN).  The result is 0.49.

According to the article that I read, (if I am not misinterpreting it)
I have to
rebuild to index.  Therefore, I drop the index and create it again.
Afterward,
I run the ANALYZE INDEX + Calculate the ratio.  The result is 0.49
again.

Then, I try to rebuild it and do the ANALYZE INDEX + Calculating the
ratio.
I got 0.49 again.

My question:
Why I can get the ratio to be nearing 1.0 ( or at least over 0.7 ) ?
Or did I do anything wrong ? If I did something wrong, then
how to determine if an index need to be dropped and rebuild ?

Thanks for all the help

Assoy

Sent via Deja.com http://www.***.com/
Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT
 Question about Index (Rebuild or not )

The ratio you describe gives some indication (on a
new index) of the relative use of branch blocks and
leaf blocks.

The PCT_USED figure in index_stats is a measure
of how many leaf and branch rows the index could
hold (given the number of blocks actually used)
compared to the number it actually holds.  If there
are no deleted row reported, this is the figure you
want.

If there are deleted rows reported they will have been
counted twice, once because they exist, and once
because they are deleted.

In this case you need to work out:
    100 * (br_rows_len + lf_rows_len - del_lf_rows_len) / btree_space

Remember too that the figure of 0.7 is just a rule of thumb,
and need not apply in all cases.  Your data may result in
a different characteristic index use.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Quote:

>I have read somewhere (about when an index need to be drop and rebuild
>).
>It said that I have to run ANALYZE INDEX command to validate Structure,
>then
>calculate the ratio of LF_BLK_LEN/(LF_BLK_LEN+BR_BLK_LEN).  If it is not
>greater that 0.7 then the ratio should be rebuild. It also said that if
>the ratio of LF_BLK_LEN/(LF_BLK_LEN+BR_BLK_LEN) is nearing 0.3, the
>index
>definitely need to be drop and recreate or Rebuild.



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. rebuild or not rebuild index file, when ?, how?

2. Difference in ranking results from a full index rebuild verses an incremental rebuild

3. table requires rebuild - secondary index does not match ...

4. Question on Rebuilding Indexes

5. Index rebuild question

6. Question on rebuilding indexes

7. Clustered Index Rebuild Question

8. ms sql server index rebuild question

9. rebuilding an index(s) to defragment the index

10. Find Queries Not Using Indexes and Indexes Not Being Used

11. How to rebuild all indexes on database


 
Powered by phpBB® Forum Software