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

Thread: indexes become unuseable after certain amount of data going in

  1. #1
    Join Date
    Mar 2006
    Posts
    176

    indexes become unuseable after certain amount of data going in

    We have one job to rebuild indexes every weekend and I asked my colleague why and he told me b/c certain amount of data change, indexes become unusable. I am new with this app so don't know 100% what's going on but as far as Oracle concern, I think indexes only become UNUSABLE when we rebuild the tables so rowid change which made indexes unuable. I am not sure about amount of data change which made indexes unusable.

    Please give me some feedback.
    thanks,

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Maybe the collegue meant unusable in an informal sense, rather than unusable in the strict Oracle sense.

    There also is a SQL*Loader option to skip index maintenance that would render the indexes actually unusable.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #3
    Join Date
    Jan 2001
    Posts
    3,134
    Or, as is my case, the application can use sqlloader direct and load duplicates into a table via a flatfile. Then when sqlloader goes to build the (unique) index, it is in a unusable state.
    I remember when this place was cool.

  4. #4
    Join Date
    Mar 2006
    Posts
    176
    there were no SQL loader involved on this app, so I guess he means unuseable in informal sense which I completely agreed. So, you guys think rebuild indexes once a week should be ok?

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Not without some reason for doing so. What does unusable really mean in the sense that it's being used here?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  6. #6
    Join Date
    Jan 2001
    Posts
    3,134
    Quote Originally Posted by matthew00
    there were no SQL loader involved on this app, so I guess he means unuseable in informal sense which I completely agreed. So, you guys think rebuild indexes once a week should be ok?

    Well, that is a question that can open a can of worms, I'll give you the standard dba answer.....it depends.

    How much space is reclaimed after the rebuild?
    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