drop/truncate and rollback segment

Question

I know that drop removes the data as well as the table structure whereas truncate retains table structure.

Does drop/truncate write to roll back segment ?

Answer 1

In Oracle the answer is No, since the rollback segments are only used for DML.

TRUNCATE is DDL.

Answer 2

Each single DDL statement is a discrete transaction. This is because databases need manage their metadata (the data dictionary in Oracle). Basically it has to correct and valid all the time, so changes to the metadata cannot be rolled back. Consequently an implicit commit is issued before and after each DDL statement. This is applies to most, probably all, RDBMS products.

Both TRUNCATE TABLE and DROP TABLE are DDL statements are DDL, so there is no rollback. If we have an Enterprise Edition license for a recent version of Oracle we can use FLASHBACK TABLE to recover the table to a previous state, including BEFORE DROP.

edit

Here are the differences between DELETE and TRUNCATE TABLE. The example date is this big table:

SQL> exec dbms_stats.gather_table_stats(user,'BIG_TABLE')

PL/SQL procedure successfully completed.

SQL> select blocks, num_rows
  2  from user_tables
  3  where table_name = 'BIG_TABLE'
  4  /

    BLOCKS   NUM_ROWS
---------- ----------
     15449    2340320

SQL> 

Deletion first....

SQL> delete from big_table
  2  /

2340320 rows deleted.

Elapsed: 00:01:20.37
SQL>
SQL> exec dbms_stats.gather_table_stats(user,'BIG_TABLE')

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.20
SQL>
SQL> select blocks, num_rows
  2  from user_tables
  3  where table_name = 'BIG_TABLE'
  4  /

    BLOCKS   NUM_ROWS
---------- ----------
     15449          0

Elapsed: 00:00:00.11
SQL>

And now the truncate ...

SQL> truncate table big_table reuse storage
  2  /

Table truncated.

Elapsed: 00:00:08.31
SQL> exec dbms_stats.gather_table_stats(user,'BIG_TABLE')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26
SQL>
SQL> select blocks, num_rows
  2  from user_tables
  3  where table_name = 'BIG_TABLE'
  4  /

    BLOCKS   NUM_ROWS
---------- ----------
         0          0

Elapsed: 00:00:00.00
SQL>

The differences between are apparent. The TRUNCATE is much, much faster. Also it has zeroised the number of blocks in the table. Notice that even the gathering of statistics is faster after the truncation. This is because the TRUNCATE statement resets the high water mark, (i.e. the zero blocks), so the job knows all the assigned blocks are unused.

Answer 3

Both drop and truncate write to rollback segment.
You just cannot do rollback on your own because Oracle executes each DDL statement in separate transaction that is automatically started and commited.

It works like this:

begin
    COMMIT; -- any outstanding work
    begin
       DDL statement;
       COMMIT; -- the DDL statement
    exception
       when others then
            ROLLBACK;  -- any work done by the DDL
            RAISE;     -- reraise the exception back to the client
    end;
end;

In case of system crash in middle of DDL statement Oracle will be able to rollback interrupted operation.

Source of information: article on Ask Tom.