Rebuilding indexes
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Rebuilding indexes

  1. #1
    Join Date
    Jun 2006
    Posts
    40

    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

  2. #2
    Join Date
    Jul 2000
    Posts
    521
    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

  3. #3
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    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...

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Here's a script you can use:
    Code:
    select 'alter index ' || index_name || ' rebuild;'
    from dba_indexes
    where 1=2
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    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
    "What is past is PROLOGUE"

  6. #6
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Rebuild the indexes on your TEST system and then you'll know how long it takes!

    Just my 2 cents worth
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  7. #7
    Join Date
    Apr 2006
    Posts
    377
    If the index isn't a bitmap or cluster index, why not just rebuild the index online and let it fly.

  8. #8
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    select 'alter index ' || index_name || ' rebuild;'
    from dba_indexes
    where 1=2
    What is this going to do exactly ? It just returns no rows

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Quote Originally Posted by Scorby
    What is this going to do exactly ? It just returns no rows

    exactly (i.e. dont rebuild)

  10. #10
    Join Date
    Jan 2001
    Posts
    3,131
    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
  •  


Click Here to Expand Forum to Full Width