When optimizing an update statement, does SQL Optimizer perform an update then a rollback? How does it simulate and optimize DML statements? Does the SQL Optimizer run SQL dynamically to give the run time / execution tuning result, e.g. does it really insert the records in the database if the SQL is an INSERT command?
For update, insert, or delete statements, SQL Optimizer will rollback all changes after executing the SQL. For getting plan during the rewrite, there is no need to do any rollback as the process will not run the statement.
From SQL Optimizer Help File:
Commit or Rollback
In order to obtain the correct result during the execution of the SQL statement, the SQL statement is executed in the database. However, any changes to the database are rolled back when UPDATE, INSERT, DELETE, or SELECT INTO statements are executed.