Home » RDBMS Server » Server Administration » Rebuilding Indexes
Rebuilding Indexes [message #331009] Wed, 02 July 2008 02:39 Go to next message
srini_thiru
Messages: 133
Registered: May 2008
Senior Member

Hi to all,

I have a doubt regarding index rebuilding.

I have analyzed one index with this

ANALYZE INDEX SR_INDEX_AK4 VALIDATE STRUCTURE.

When i queried Index_Stats Table i got the following result.

select height,name,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 "Percentage" from index_Stats;

HEIGHT NAME LF_ROWS DEL_LF_ROWS Percentage
4 SR_INDEX_AK4 61854857 5033 .00813679


After that i have rebuild the index using the command

ALTER INDEX ST_INDEX_AK4 REBUILD ONLINE;

After this command i again validated the index and used index_Stats to get the results

The result i got is,

HEIGHT NAME LF_ROWS DEL_LF_ROWS Percentage
4 SR_INDEX_AK4 61849824 0 0


Deleted rows has been removed but the height column remains 4.


What does it signifies.

I have read from the article if the height value is greater than 3 we have rebuild the indexes.


Can any one give me a clear idea about this.



Thanks in Advance,

Seenu

Report message to a moderator

Re: Rebuilding Indexes [message #331020 is a reply to message #331009] Wed, 02 July 2008 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68672
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It just means that it is useless, more dangerous, to rebuild indexes.

Quote:
I have read from the article if the height value is greater than 3 we have rebuild the indexes.

This is just wrong.

Regards
Michel

Report message to a moderator

Re: Rebuilding Indexes [message #331033 is a reply to message #331009] Wed, 02 July 2008 03:32 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Quote:
I have read from the article
You have read from what article? Can you post a link to it? Maybe you have mis-read, or read the statement out of context (but more than likely, as Michel says, the article is just plain wrong)

Report message to a moderator

Re: Rebuilding Indexes [message #331061 is a reply to message #331009] Wed, 02 July 2008 04:35 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
May be OP read from dba-oracle.com.

Report message to a moderator

Re: Rebuilding Indexes [message #331153 is a reply to message #331009] Wed, 02 July 2008 07:31 Go to previous messageGo to next message
srini_thiru
Messages: 133
Registered: May 2008
Senior Member


How can i determine that which indexes need rebuild.

Thanks in Advance,

Seenu

Report message to a moderator

Re: Rebuilding Indexes [message #331171 is a reply to message #331153] Wed, 02 July 2008 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68672
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
At junior level, no one need to be rebuilt.

Regards
Michel

Report message to a moderator

Re: Rebuilding Indexes [message #331205 is a reply to message #331009] Wed, 02 July 2008 10:15 Go to previous messageGo to next message
srini_thiru
Messages: 133
Registered: May 2008
Senior Member


I can't get you. Can you please explain this in detail.

Thanks,
Seenu

Report message to a moderator

Re: Rebuilding Indexes [message #331210 is a reply to message #331205] Wed, 02 July 2008 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68672
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no need to rebuild index (except in rare cases).
There is no indicator to automatically rebuild an index.
Why do you want to rebuild an index?

Regards
Michel

Report message to a moderator

Re: Rebuilding Indexes [message #331212 is a reply to message #331205] Wed, 02 July 2008 10:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Just search this forum / google.
This is topic is asked and answered too many times.

Report message to a moderator

Re: Rebuilding Indexes [message #331240 is a reply to message #331009] Wed, 02 July 2008 14:15 Go to previous messageGo to next message
srini_thiru
Messages: 133
Registered: May 2008
Senior Member


The performance of our database is very slow. It takes long time to get the report from the tables.

Report message to a moderator

Re: Rebuilding Indexes [message #331241 is a reply to message #331009] Wed, 02 July 2008 14:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

http://www.orafaq.com/forum/t/84315/74940/
which of the suggestions in URL above have you tried & what were the results?

[Updated on: Wed, 02 July 2008 14:19] by Moderator

Report message to a moderator

Re: Rebuilding Indexes [message #331242 is a reply to message #331240] Wed, 02 July 2008 14:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68672
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
srini_thiru wrote on Wed, 02 July 2008 21:15
The performance of our database is very slow. It takes long time to get the report from the tables.

And what is the relation with index rebuilding?

Regards
Michel

Report message to a moderator

Re: Rebuilding Indexes [message #331245 is a reply to message #331009] Wed, 02 July 2008 14:33 Go to previous messageGo to next message
srini_thiru
Messages: 133
Registered: May 2008
Senior Member


Actually i have a doubt regarding the indexes but its not related to peformance.

My other doubt is,

Our database performance is very slow. What are the steps i have to do, to check and change the database performance faster than now.

Report message to a moderator

Re: Rebuilding Indexes [message #331246 is a reply to message #331245] Wed, 02 July 2008 14:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68672
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ou can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

You can also have a look at Database Performance Tuning Guide.

Regards
Michel

Report message to a moderator

Re: Rebuilding Indexes [message #331248 is a reply to message #331245] Wed, 02 July 2008 14:35 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Read and follow anacedent's 2nd link

Report message to a moderator

Re: Rebuilding Indexes [message #331637 is a reply to message #331248] Fri, 04 July 2008 04:56 Go to previous messageGo to next message
ora110
Messages: 42
Registered: September 2007
Location: China
Member
It seems that we need not to rebuild index.

Report message to a moderator

Re: Rebuilding Indexes [message #331652 is a reply to message #331637] Fri, 04 July 2008 06:16 Go to previous message
Michel Cadot
Messages: 68672
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, it seems.

Regards
Michel

Report message to a moderator

Previous Topic: Installation
Next Topic: Query on 10 g Migration issue
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Aug 07 10:14:22 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.