Index Rebuild
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Index Rebuild

  1. #1
    Join Date
    Feb 2002
    Posts
    48

    Lightbulb Index Rebuild

    I would like to find out when my indexes were last rebuilt.
    From which data dictionary table I can get that information.
    I understand last_analyzed, created and timestamp can be found in
    USER_INDEXES and USER_OBJECTS but anywhere I can
    locate date for last rebuilt?

    Thanks in advance!

  2. #2
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    last_ddl_time in user_objects

    steve
    I'm stmontgo and I approve of this message

  3. #3
    Join Date
    Oct 2003
    Location
    Sydney
    Posts
    29
    Doubt: Why would anyone want to know what time index is rebuild?

  4. #4
    Join Date
    Feb 2002
    Posts
    48
    Thanks for the info!

    I was setting up a job to rebuild indexes weekly.
    Just want to verify that the indexes are rebuilt as scheduled.

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by cchiara
    Thanks for the info!

    I was setting up a job to rebuild indexes weekly.
    Just want to verify that the indexes are rebuilt as scheduled.
    Get your asbestos underpants on my friend, 'cos you are in for a flaming.

    Here is the kind version:

    "Rebuilding indexes weekly is a waste of time, and may even be harmful to the performance of your system".
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    "When should you rebuild an index" by Jonathan Lewis:
    http://www.dbazine.com/jlewis14.shtml
    Conclusion

    There is only one sound argument for rebuilding an index:

    Will the total cost of rebuilding the index be a reasonable price to pay for the resulting benefit to the system?

    The answer to this question is frequently a resounding NO. In fact, sometimes the overall impact of rebuilding an active index will be detrimental to the system. However, there are still plenty of misconceptions about indexes that result in DBAs the world over wasting valuable time and effort rebuilding indexes unnecessarily.

    BUT, if you have a regular window when the system is not in use, have a simple batch job that runs in that window without putting pressure on other batch tasks, and that batch job can never fail — then you might as well rebuild all the “safe” indexes you want to; there is often a slight performance gain to be had, and if it costs you nothing, then you might as well take it.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Well, i see only one significant use of rebuild is some space will be freed up...but if u are gonna rebuild every now and then, i see no point..

    Rebuild once in a quarter or even half yearly will suffice and you can get some space freed up..

    We recently rebuilt our indexes (After nearly 8 months) and we got mere 10GB space freed up..so what good will it make if you rebuild every week?

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Hi abhaysk
    In some projects we do... we rebuild the fact tables every day aft every upload.
    any hint?
    well now we hv a HUGE table with 550million rows partitioned by year month. how to go from here?

    we are using MV for summary data calculation.
    Cheers!
    Cheers!
    OraKid.

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by balajiyes
    Hi abhaysk
    In some projects we do... we rebuild the fact tables every day aft every upload.
    any hint?
    U certainly mean Re-org of table than saying it rebuild the table..?

    Well Re-Org the table daily, u certainly are joking..


    Originally posted by balajiyes
    well now we hv a HUGE table with 550million rows partitioned by year month. how to go from here?
    U wana Re-Org this table daily as well?
    huh?

    Well if it one time, then Use Alter Table Move..(I think u can do it partition wise not sure coz never used partitioned tables)

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by jmodic
    "When should you rebuild an index" by Jonathan Lewis:
    http://www.dbazine.com/jlewis14.shtml
    This is an academical article having lost of assumptions (very right ones inedeed). I have the same views with him (we had an interesting talk last month) but hei: there is one non-mentioned good reason why to rebuild indexes: users very often create the indexes into the default tablespace which is a differenet mount point then the index one. At some point you have the data mount point 90% full and the index mount point only 20%.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

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