-
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
-
"ONLINE" allows DML operations on the table during rebuilding of the index.
Sanjay
-
Cheers Sanjay that confirmed my suspicions
Eddy
-
"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
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|