-
Rebuilding indexes
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.
svk
-
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)
Assistance is Futile...
-
Here's a script you can use:
Code:
select 'alter index ' || index_name || ' rebuild;'
from dba_indexes
where 1=2
Jeff Hunter
-
Originally Posted by minal_yawale
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
You need to read this before attempting a rebuild.
Rebuild
"What is past is PROLOGUE"
-
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.
-
select 'alter index ' || index_name || ' rebuild;'
from dba_indexes
where 1=2
What is this going to do exactly ? It just returns no rows
-
Originally Posted by Scorby
What is this going to do exactly ? It just returns no rows
exactly (i.e. dont rebuild)
-
Drop them all, indexes are for begginers.
I remember when this place was cool.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|