Index Blocks/Extent Usage - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: Index Blocks/Extent Usage

  1. #11
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    Thanks for all your thoughts and comments folks. Got this sorted now.

    I just did a rebuild during this morning, noparallel, and set extent sizes to 1M - of which there is only one extent allocated following the build!

    :-)

    T.

  2. #12
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by TomazZ
    Rebuild will use existing index, which is faster in many situations.
    Someone (I think slimdave) told me recently that if the index is marked unusable, then it has to be rebuilt by reading the table.

  3. #13
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by DaPi
    Someone (I think slimdave) told me recently that if the index is marked unusable, then it has to be rebuilt by reading the table.
    To add, even if Index is usable, & if index is built online, it will scan whole table.

    So, wass the point in Rebild with out online clause, and use Index scan, its any way going to hinder the TRANSACTIONs starting hence forth or the other way round is as well true.

    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"

  4. #14
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by slimdave
    On a side issue, a more robust process would be to make the index unusable and set skip_unusable_indexes = true, then rebuild them after the batch process completes
    Why go through the hassle of making an index unusable, having slow queries until you rebuild it, and rebuild it, and analyze it? Sounds like a lot of work to me. In this situation, I would just:
    Code:
    ALTER INDEX xyz REBUILD ... ONLINE;
    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. #15
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if we have enterprise edition we can rebuild online otherwise use the poor manīs solution

  6. #16
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    At the time the indexes are rebuilt (from 0200 hrs) we have no queries accessing the system. [Usage doesn't resume until around 9 a.m.)

    As the indexes are actually bitmap, I believe the option to rebuild 'online' is unavailable.

    We used to have the indexes present when running the batch, in the form of b-tree indexes. Unfortunately, as the user daytime queries were so ad-hoc and trying to juggle variants of concatenated indexes to suit everyone was too much of a headache and performance remained dire overall - we elected to transform to bitmaps.

    These are much smaller than the b-trees, performance gained substantially from multitudinous bitmap merges for almost everyone. The only downside unfortunately was that the overnight batch with bitmap objects in place upped the batch time from around 6 hours to 6 days or more!!!

    Hence the drop and recreate strategy.

    Of course, you may not have wanted to know this - but I felt like filling in the gaps in the scenario.

    :-)

    Right now I'm going to adopt a drop, create in parallel (to avoid long create times but with the detriment of extra space usage), followed by a rebuild noparallel (which is reasonably quick and eliminates the extra space overhead from earlier), concluding with a quick analyze!

    Unless of course I'm overlooking something?

    Is there a better stategy that I might be missing?

    - Tony.

  7. #17
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by marist89
    Why go through the hassle of making an index unusable, having slow queries until you rebuild it, and rebuild it, and analyze it? Sounds like a lot of work to me. In this situation, I would just:
    Code:
    ALTER INDEX xyz REBUILD ... ONLINE;
    Problem is that with those bitmap indexes the insert/update/delete is nightmarishly slow - it's usually better to make 'em unusable and rebuild them after the operation.

    On a different note, a rebuild on a usable index will read the index, not the table, except where you use the "compute statistics" clause - that makes it read the table not the old index, i believe.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  8. #18
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    On a different note, a rebuild on a usable index will read the index, not the table, except where you use the "compute statistics" clause - that makes it read the table not the old index, i believe.
    Try Online clause (b*tree), it also goes for table scan instead.

    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"

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