I have question about
DROP COLUMN CHECKPOINT xxx ;
Oracle says, specifying checkpoint will minimize the use of rollback space.
Please clarify this, because as I understand checkpoint is a database event which synchronizes the modified data blocks in memory with the datafiles on disk. It offers Oracle the means for ensuring the consistency of data modified by transactions.
checkpoint does not have any relation to rollback, it has a link with redo log.
But I have tested this with a big table, it works as written.
May be I have wrongly understood, please explain.
I will try to explain with my perception
1. Oracle create Rollback/Undo with DDL statement.
2. Drop columns is single Oracle command which has to work complete at statement level.Meaning whenever this command will be completed successfully then only changes shud perist with Data Dictionary.
3. When u mention Checkpoint say for 1000 rows with every 1000 rows Oracle commit the transaction.
4.So lesser Rollback/Undo.
5. That is one of reason why they suggest marked column unused before dropping it.
Experts Correct me I am deviating.
but point no. 5 is not right, because it will generate the same amount of redo even if marked unused.
Only checkpoint keyword makes difference on rollback.