Hi All,
Is there any way to calculate the time required for rebuilding the indexes? Also Please let me know whether rebuilding the will solve the disk space related issue?
Thanks in advance.
Minal
Printable View
Hi All,
Is there any way to calculate the time required for rebuilding the indexes? Also Please let me know whether rebuilding the will solve the disk space related issue?
Thanks in advance.
Minal
No, there is no way to calculate time required for rebuilding indexes.
If the table has gone through a lot of DELETEs, rebuilding indexes will create some free space. But, its only temporary. If the table keeps on undergoing similar DML activity, the index will grow again.
Well, you could rebuild indexes and coalese free space, but what happens if that index you have rebuilt would have expanded into the whitespace that you have just "shrunk"? Lets say you have an index like this with leaf blocks a bit like this:
ABBOTT||space||BROWN||CURTIS||space||WAITE
It is rebuilt to look like this:
ABBOTT||BROWN||CURTIS||WAITE
Then you enter NOTTAGE and ANDREWS into the table that the index references, not only you have just expanded your index back to where it was (using the same amount of space), but you have introduced the un-nessessary overhead of widening the index again.
In other words, unless you are sure you need to rebuild an index (each index has been analyzed on its own merits), don't worry about it just:
BUY MORE DISK (and allocate it to the tablespace where you hold your indexes)
Here's a script you can use:
Code:select 'alter index ' || index_name || ' rebuild;'
from dba_indexes
where 1=2
You need to read this before attempting a rebuild. :)Quote:
Originally Posted by minal_yawale
Rebuild
Rebuild the indexes on your TEST system and then you'll know how long it takes!
Just my 2 cents worth
If the index isn't a bitmap or cluster index, why not just rebuild the index online and let it fly.
What is this going to do exactly ? It just returns no rowsQuote:
select 'alter index ' || index_name || ' rebuild;'
from dba_indexes
where 1=2
Quote:
Originally Posted by Scorby
exactly (i.e. dont rebuild)
Drop them all, indexes are for begginers.