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

Thread: Rebuilding Indexes

  1. #1
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi Friends..

    Quick Question..

    Does anybody know why it is recommended that users aren't logged on at the time when indexes are being rebuilt ?

    Is it a performance issue, data consistency issue, something else ?

    Cheers

    Suresh

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Its primarily a performance issue. The oracle would resolute to table scans which is v.expensive. That is the primary reason.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    While you rebuild index it doesn't matter how many users are connected to the database at all. What matters is that there there should be as few transactions changing the contents of the index as possible while index rebuild is taking place. The reason for this is that Oracle has to take thoss extra changes into account and has to apply them as a final stage of index rebuild. So this obviously takes some extra resources and some extra time for rebuild, that is all.

    Table scans are not an isue here, Oracle never performs table scans for index rebuilds. The source object during index rebuild is allways an existing index, never the table itself.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Thanks Folks,

    I was unsure what was happening to new records/transactions while the index was being rebuilt, i.e whether they would be included in the index or not, obviously they are.

    Regarding what Sam was saying...
    Whilst an index is being rebuilt, If a query selects a table who's index is being rebuilt, will this not result in a full table scan whilst the index is being rebuilt - hence degrading performance further ?

    Cheers

    Suresh

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by Sureshy
    Regarding what Sam was saying...
    Whilst an index is being rebuilt, If a query selects a table who's index is being rebuilt, will this not result in a full table scan whilst the index is being rebuilt - hence degrading performance further ?
    Oh, now I see what Sam ment! He was talking about table being scaned by *other* session while index is being rebuild. Sorry abou the misunderstanding.

    However other users will still be able to read indexes while they are being rebuild, so there will still be no need for full table scans with their queries.

    [Edited by jmodic on 06-12-2001 at 11:11 AM]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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