Home » RDBMS Server » Server Administration » analysis
analysis [message #289593] Mon, 24 December 2007 03:48 Go to next message
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 #289607 is a reply to message #289593] Mon, 24 December 2007 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
An index must almost never be rebuilt.
Search on AskTom or on Google there are many references on this.

Regards
Michel
Re: analysis [message #289621 is a reply to message #289593] Mon, 24 December 2007 06:36 Go to previous messageGo to next message
Upperm
Messages: 95
Registered: July 2003
Member

I'm in asktom website and I'm learning ..
Thanks michel.
Re: analysis [message #289783 is a reply to message #289621] Wed, 26 December 2007 02:27 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
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 #289785 is a reply to message #289783] Wed, 26 December 2007 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is plain wrong.
It just put your database in danger.

Regards
Michel
Re: analysis [message #289791 is a reply to message #289785] Wed, 26 December 2007 02:46 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
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 #289814 is a reply to message #289791] Wed, 26 December 2007 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This note is out of date and wrong.
Go to AskTom (http://asktom.oracle.com) and search for "rebuild index" and the like, there are several threads explaining why this is wrong and why it is wrong to rebuild an index in almost all cases.

You can go to Jonathan Lewis site: http://www.jlcomp.demon.co.uk/

Regards
Michel
Re: analysis [message #289815 is a reply to message #289791] Wed, 26 December 2007 03:37 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
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.

Michel


You can go to Jonathan Lewis site: http://www.jlcomp.demon.co.uk/



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 #289821 is a reply to message #289593] Wed, 26 December 2007 03:48 Go to previous messageGo to next message
Upperm
Messages: 95
Registered: July 2003
Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2913600659112


I've been reading an interesting thread in asktom website concerning "rebuild | not rebuild indexes" (see URL above).
But then something really still sticks on my mind and I was wondering the following:
Assume that rebuilding indexes is not good, then why and what does the following statement exists for :
ALTER INDEX IND_NAME REBUILD <OPTIIN> ?
??

Re: analysis [message #289822 is a reply to message #289821] Wed, 26 December 2007 03:54 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
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 Laughing

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

Re: analysis [message #289844 is a reply to message #289821] Wed, 26 December 2007 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Assume that rebuilding indexes is not good, then why and what does the following statement exists for :
ALTER INDEX IND_NAME REBUILD <OPTIIN> ?

Because:

1/
Customers ask for it and even if it is said it is bad they still say we want it, full stop.

2/
There are very rare cases it is useful (read AskTom links)

Regards
Michel
Re: analysis [message #289874 is a reply to message #289593] Wed, 26 December 2007 06:21 Go to previous message
Upperm
Messages: 95
Registered: July 2003
Member
@michel,
Grateful .. Thanks
Previous Topic: opatch help
Next Topic: grant select all tables/object of all schemas
Goto Forum:
  


Current Time: Fri Dec 09 07:57:42 CST 2016

Total time taken to generate the page: 0.09425 seconds