| analysis [message #289593] |
Mon, 24 December 2007 03:48  |
Upperm
Messages: 95
Registered: July 2003
|
Member |
|
|
Hello folks,
Oracle 9i Entreprise Edition on Win XP.
Once I launch an analyze on an index to check for its structure (analyze index index_name validate structure), the view index_stats is automatically filled.
On what data retrieved from that view should one refer to decide whether the index must be rebuilt or not ?
Thanks for any help.
|
|
|
|
|
|
|
|
| Re: analysis [message #289783 is a reply to message #289621] |
Wed, 26 December 2007 02:27  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Rebuild the index when :
- deleted entries represent 20% or more of the current entries
- the index depth is more then 4 levels.
Query data dictionary dba_indexes to collect it, get the information retrived into index_stats table.
|
|
|
|
|
|
| Re: analysis [message #289791 is a reply to message #289785] |
Wed, 26 December 2007 02:46 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Dear Michel!
Would you like to explain why were am wrong?
As my knowlegde, an B - Tree index may be good candidate for a rebuild when:
- This index becomes skewed, because parts of it are accessed more than others.
And following the Metalink - 77574.1
| Quote: |
- The BLEVEL (or branch level) is part of the B-tree index format and relates to the number of times Oracle has to narrow its search on the index while searching for a particular record. In some cases, a separate disk hit is requested for each BLEVEL. Prior to 9i, if the BLEVEL is more than 4, it is recommended to rebuild the index. As database are getting bigger and bigger, BLEVEL may not be a good indicator of time to rebuild the index. BLEVEL > 4 may suggest an evaluation of whether the partitioning option could help you.
|
[Updated on: Wed, 26 December 2007 02:47] Report message to a moderator |
|
|
|
|
|
| Re: analysis [message #289815 is a reply to message #289791] |
Wed, 26 December 2007 03:37 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Why?
The indexes is physical storage, they have private structure, they have private accessible method, they have been controlled and managed by Oracle server. They are different to others.
I know that, whenever rebuild the index, the system will be effected by
| AskTom |
o the system would generate 4.5 times the redo
o the system would run slower
o the system would consume more resources (CPU, IO, latching, etc)
o the system would not be able to handle the same user load
|
But why?
Oracle docs say
| Quote: |
Improper sizing or increased growth can produce index fragmentation. To eliminate or reduce fragmentation, you can rebuild or coalesce the index. But before you perform either task weigh the costs and benefits of each option and choose the one that works best for your situation
|
For me, I have run the script retrieving information of every indexes every month, however, I had not rebuilt them till now.
I often read the Internal B-Tree indexes written by Julian Dyke.
I am very, very wonder between Sir Tom and the others.
Yeah! This is a greate site
Thanks for advice!
[Updated on: Wed, 26 December 2007 03:47] Report message to a moderator |
|
|
|
|
|
| Re: analysis [message #289822 is a reply to message #289821] |
Wed, 26 December 2007 03:54 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
| Upperm |
Assume that rebuilding indexes is not good, then why and what does the following statement exists for :
ALTER INDEX IND_NAME REBUILD <OPTIIN> ?
??
|
Because Oracle is huge, it's different to the other DBMS, including UDB2 
Why did you asked Oracle does not use the other algorithm, but LRU?
[Updated on: Wed, 26 December 2007 03:58] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
