| indexes to be rebuild [message #441295] |
Sat, 30 January 2010 05:12  |
suresh.wst
Messages: 53
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 #444163 is a reply to message #441295] |
Fri, 19 February 2010 00:56  |
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 #444175 is a reply to message #444167] |
Fri, 19 February 2010 01:43 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
