DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: alter index ... rebuild question

  1. #1
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204

    alter index ... rebuild question

    What happens to TEMP when I execute the following statement:

    ALTER INDEX MY_INDEX REBUILD PARALLEL ONLINE NOLOGGING

    Does TEMP need to be big enough to hold all the keys?
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    will be used for sorting, wont store all of it, but your tablespace will need to be big enough to hold the old and current index

  3. #3
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    So if my index is 30gigs and 50% sparse, I should have 30gigs of temp ready?
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  4. #4
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Originally posted by KenEwald
    So if my index is 30gigs and 50% sparse, I should have 30gigs of temp ready?
    A 30 gig index???!!! Dude that's insane! Ever heard of partitioning?
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by davey23uk
    will be used for sorting, wont store all of it, but your tablespace will need to be big enough to hold the old and current index
    I'd guess that it would depend on whether the index is already usable or unusable -- I don't think that temp space is involved in rebuilding a usable index.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by davey23uk
    will be used for sorting, wont store all of it
    Then how do you expect your index to be perfectly structured?

    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"

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    I'd guess that it would depend on whether the index is already usable or unusable -- I don't think that temp space is involved in rebuilding a usable index.
    Well, still oracle uses temp ts for sorting.

    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
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by OracleDoc
    A 30 gig index???!!! Dude that's insane! Ever heard of partitioning?
    What if a unique index ( global nonpartitioned index ) is neccesaary for that partitioned table and that key/s is/are not a part of column/s on which table is partitioned?

    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"

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Hi Abhay,

    Slim's point is that if the index is usable, the rebuild is based on the index, NOT the table. The required data can be read in order by reading the chain of leaf blocks, so no sorting should be required.

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by DaPi
    Hi Abhay,

    Slim's point is that if the index is usable, the rebuild is based on the index, NOT the table. The required data can be read in order by reading the chain of leaf blocks, so no sorting should be required.
    Well i do understand that point and was of that impression too, but you can check the usage of Temp TS while even rebuilding usable index.

    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