-
My index needs rebuilding so I analyzed it and based on the information from the index_stats, the pct_used was 81% so I decided to rebuild it.
After rebuilding it, I decided to analyze it again to make sure that pct_used column of the index_stats is good. After analyzing it again, Irealized that the pct_used column has not changed.
I thought that after rebuilding the index the pct_used column of index_stats would change. Am I wrong? Or I'm doing something wrong
Below are the steps that I followed:
1.
analyze index BOB.PK_NEWSTABLE validate structure;
2.
SQL> select name,btree_space,used_space,pct_used from index_stats;
NAME BTREE_SPACE USED_SPACE PCT_USED
------------------------------ ----------- ---------- ----------
PK_NEWSTABLE 58532 47046 81
3.
ALTER INDEX BOB.PK_NEWSTABLE REBUILD storage
(initial 4M next 4M pctincrease 0)
tablespace news_ind_tbs;
4.
SQL> analyze index BOB.PK_NEWSTABLE validate structure;
Index analyzed.
5.
SQL> select name,btree_space,used_space,pct_used from index_stats;
NAME BTREE_SPACE USED_SPACE PCT_USED
------------------------------ ----------- ---------- ----------
PK_NEWSTABLE 58532 47010 81
-
Find the ratio '(del_lf_rows_len/lf_rows_length)*100'. This is the ratio you might have to observe.
-
http://otn.oracle.com/docs/products/...2292.htm#10867
I prefer to look at (del_lf_rows/lf_rows)*100 to see how much of my index is empty.
Jeff Hunter
-
OK like you guys suggested I used del_lf_rows_len and lf_rows_length and below is the result that I got.
Deleted Used % Deleted
Index Name Leaf Rows Leaf Rows Leaf Rows
------------------------------ --------- --------- ----------
PK_NEWSTABLE 0 1613 .00000
QUESTION: From the above results what is the best way to determine if the index needs to be rebuild?
-
Originally posted by marist89
[url]
I prefer to look at (del_lf_rows/lf_rows)*100 to see how much of my index is empty.
Jeff Hunter
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|