| ALTER INDEX . REBUILD - Why it (DDL) generates REDO? [message #64229] |
Wed, 05 January 2005 03:07  |
patrick
Messages: 83
Registered: December 2000
|
Member |
|
|
Hi,
I'm using Oracle 8i on AIX 4.3 (UNIX).
DDL takes effect immediately and permanently. There is no rollback option! And ALTER INDEX ... REBUILD is a DDL!
Plus, we can always re-build an index after recovery because we havn't lost index-data: we can always re-create the index!
So my question is: why this ALTER INDEX ... REBUILD ddl statement is logged or generates REDO??
If a system crash during the rebuild operation, we can always re-create the index: so we don't need to log this REBUILD DDL operation?? Where am I wrong?
It's an important conceptual issue!
Thank you in advance for your help!
Best regards,
Patrick.
|
|
|
|
| Re: ALTER INDEX . REBUILD - Why it (DDL) generates REDO? [message #64231 is a reply to message #64229] |
Wed, 05 January 2005 03:55  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
First i would never rebuild the index.
It does generate a lot of redo records.
USE NOlogging option to avoid it.
If you are rebuilding ONE or FEW known indexes, its good.
You very well know which index is required to be rebuilt (after recovcery in case of a crash).
Unfortunately , the great myth of SCHEDULED INDEX REBUILDING was very effective in our minds sometime back.
If i am doing a scheduled rebuild of thousands of indexes , I DONT Want the pain of keeping track of indexes to be rebuilt and their DDL.
Generally speaking,
Any DDL will have some RECURSIVE DML with Data Dictionary.
By rebuilding the index, something like this will happen in dictionary.
1. Drop Index Myindex.
2. IN DataDictionary,
dba_indexes ( it is a view, only the base TABLES are updated. Is it ind$??..i am not sure) is changed to reflect hat the table now has no index named Myindex.
similiar changes are made in Obj$ and other base tables.
3. recreate the index.
again the bases table are now updated.
So you DDL is actually an DML, for which the REDO records be vital in case of recovery.
Only If the indexes (DDL of the indexes) are known to you can recreate the indexes.
ELSE
You have to dependent on the redo information.
May be,someone can give a better explanation....
|
|
|
|
| Re: ALTER INDEX . REBUILD - Why it (DDL) generates REDO? [message #64238 is a reply to message #64231] |
Wed, 05 January 2005 09:23 |
patrick
Messages: 83
Registered: December 2000
|
Member |
|
|
Hi,
Thank you for your explanations!
I knew that it generates some RECURSIVE DML in Data Dictionary but not so much!! I was very surprised that it generates such an amount of redo with just RECURSIVE DML!
My archiver filled my disk full with 8 INDEX REBUILD statements! :(
Well, there is a couple of million rows in these underlying tables...
Best regards,
Patrick.
|
|
|
|
|
|
| Re: ALTER INDEX . REBUILD - Why it (DDL) generates REDO? [message #64244 is a reply to message #64239] |
Wed, 05 January 2005 10:24 |
patrick
Messages: 83
Registered: December 2000
|
Member |
|
|
Hi,
Yes, it make sense!
But, conceputally: REDO is made for recovery! To avoid data loss. if we rebuild and index and the server crashes: the index could -hypotetically- be invalid, and it would still be possible to re-create the index after the database is mounted. We don't loose any data.
If it logs in REDO all the changes during REBUILD and the database crashes: then when we mount the database, it will recover the index at the last state just before the REBUILD started!?
Is it worth all the REDO log generated by the REBUILD when we know that we can anyway re-create the index or re-execute a REBUILD statement after a crash?
Thank you for your help in advance! Quite conceptual questions...
Best regards,
Patrick.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
