Home » RDBMS Server » Server Administration » indexes to be rebuild (Oracle 9i/10g unix)
indexes to be rebuild [message #441295] Sat, 30 January 2010 05:12 Go to next message
suresh.wst
Messages: 48
Registered: June 2008
Location: Hyderabad
Member
Hi,

How to find indexes which are eligible to rebuild in the database environment? Is there any query to find it? Please clarify it.

Thanks,
Suresh
Re: indexes to be rebuild [message #441298 is a reply to message #441295] Sat, 30 January 2010 05:26 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Please search before posting....

It`s not only in the documents of Oracle ....
sriram Smile
Re: indexes to be rebuild [message #441299 is a reply to message #441298] Sat, 30 January 2010 05:39 Go to previous messageGo to next message
suresh.wst
Messages: 48
Registered: June 2008
Location: Hyderabad
Member
Thanks!!!
Re: indexes to be rebuild [message #441314 is a reply to message #441295] Sat, 30 January 2010 09:16 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First question is, do you need to search an index to rebuild? Why?

Regards
Michel
Re: indexes to be rebuild [message #441327 is a reply to message #441295] Sat, 30 January 2010 21:18 Go to previous messageGo to next message
entekeralam
Messages: 19
Registered: January 2010
Junior Member
I saw this online...

http://dbaregistry.blogspot.com/2010/01/dba-interview-questions.html
Re: indexes to be rebuild [message #444163 is a reply to message #441295] Fri, 19 February 2010 00:56 Go to previous messageGo to next message
pokhraj_d
Messages: 117
Registered: December 2007
Senior Member
Hi,
You can fire the below commands:-

1. First Start the Index monitoring-
SQL> Alter INDEX <INDEX_NAME> MONITORING USAGE;

2. Then Run the below sql:-

select a.INDEX_NAME, a.TABLE_NAME,a.USED,b.tablespace_name
from V$OBJECT_USAGE a, dba_indexes b,dba_objects c
where a.INDEX_NAME=b.INDEX_NAME and a.index_name=c.object_name
and a.used='NO' and b.blevel=3;

Regards-
P
Re: indexes to be rebuild [message #444167 is a reply to message #444163] Fri, 19 February 2010 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what is the rationale below your query?
Having indexes with than 3 blevel is not something that should raise a rebuild. It perfectly normal to have more than 3 levels when there billion rows in a table.
Hoping that rebuilding the index will decrease the blevel is an illusion, you must likely will have the same blevel after rebuilding.

How many times these stupid queries will still be copied and copied and copied in forums by people that do not even think about their meaning and verify them?

Regards
Michel
Re: indexes to be rebuild [message #444175 is a reply to message #444167] Fri, 19 February 2010 01:43 Go to previous message
cristi_Buc
Messages: 12
Registered: February 2010
Location: Bucharest
Junior Member
In general Oracle's B*Tree Structure is well maintained.
No COALESCE is needed in the Local Managed Tablespace.

You need to rebuild the indexes when:
- a random massive DELETE occured (the blocks are too many and a big % of their storage is empty);
- the tables are very used for select;
- no many random INSERTS will occur;
- you see a performance issue on that table/index.
Previous Topic: sys password changed by system
Next Topic: Install Oracle 10g in windows 7 home premium
Goto Forum:
  


Current Time: Sat Oct 01 02:30:57 CDT 2016

Total time taken to generate the page: 0.14588 seconds