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.

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

---

Quote:

---

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

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.

Quote:

---

select 'alter index ' || index_name || ' rebuild;'
from dba_indexes
where 1=2

---

What is this going to do exactly ? It just returns no rows

Quote:

---

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.

If it's a worry about space, have a look at ALTER INDEX COALESCE:
http://download-uk.oracle.com/docs/c...8a.htm#2050689

Hey DaPi,

I concur. I've found coalescing especially helpful to free up blocks with sequential indexes where alot of deleting is going on.

I have a locking problem when coalescing a table with alot of inserts going on at the same time. When it gets to the end of the index blocking locks will bring the db to its knees.

Oracle support swears it can't happen, but I can reproduce a lock-down every time. I have to be careful to only run the coalesce for 8 or 10 hours then kill it. Archiving goes crazy after a few hours. That's when I know it is actually merging blocks.

Ken

Rebuild or Coalesce only for sequence keys.. and ofcourse where history data is purged regularly.. else no use..

Abhay.

Quote:

---

Originally Posted by DaPi

If it's a worry about space, have a look at ALTER INDEX COALESCE:
http://download-uk.oracle.com/docs/c...8a.htm#2050689

---

yeah but LMT's do that implicitly anyway

Quote:

---

Originally Posted by stmontgo

yeah but LMT's do that implicitly anyway

---

talking about indexes here, not tablespaces