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

Thread: Alternative Index Rebuilds Syntax

  1. #1
    Join Date
    May 2002
    Posts
    19

    Smile

    What is the difference between the following Index rebuilding syntax, as none of the books either go into enough detail or state explicitly the difference.

    1. ALTER INDEX owner.index_name REBUILD TABLESPACE tablespace_name;

    2. ALTER INDEX owner.index_name REBUILD ONLINE TABLESPACE tablespace_name;

    I have issued both statements on the test system and they both seem to work. I suspect from the literature I have read that 1. operates using a Table lock, where as 2. operates using row level locking.

    Could someone please confirm my understanding or otherwise.

    Thanks in advance
    Eddy

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    "ONLINE" allows DML operations on the table during rebuilding of the index.

    Sanjay

  3. #3
    Join Date
    May 2002
    Posts
    19
    Cheers Sanjay that confirmed my suspicions

    Eddy

  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    "ONLINE" is a new clause used while rebuilding index in 8i which will rebuild the index online with out any down time of application during index rebuild.

    but in the first one you cannot do any dml activity while rebuilding the index.

    regards
    anandkl
    anandkl

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Both SANJAY_G and anandkl are sort of right. DML is allowed but parallel DML is not (during online rebuild).
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  6. #6
    Join Date
    Mar 2002
    Posts
    301
    Hi,

    I would like to add one more question to this:

    Assuming that the DBA had given the command for rebuilding the index (any method) and some other user is
    issuing a SQL statement. While the Index is being
    rebuild will Oracle see the index for the query issued?

    Thanks.
    Vijay.
    Say No To Plastics

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Yes, sure, the old index will remain valid and usable for the duration of rebuild, so all queries will be able to use it like no index rebuild is taking place at all.
    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