Are ANALYZE and DBMS_STATS also DDLs ?

Most of us know that DDLs always run an implicit commit. That means that any transaction that was active before the DDL was issued gets COMMITted {even if the DDL itself subsequently fails}. Therefore, we ought to be [very] careful when mixing DDLs with DMLs (ie normal INSERT/UPDATE/DELETE transactions].

Common understanding is that CREATE , ALTER and DROP statements are DDLs. However, we might not always realise that implicit commits might be issued by other statements as well --- eg by ANALYZE or EXECUTE DBMS_STATS !

Here's a very short example :


SQL>
SQL> REM Demonstrate that ANALYZE and DBMS_STATS calls
SQL> rem like other DDLs also cause implicit commits
SQL>
SQL>
SQL> create table test_stats_ddl (col1 varchar2(5));
Table created.
SQL>
SQL> insert into test_stats_ddl values ('a');
1 row created.
SQL>
SQL> rollback ;
Rollback complete.
SQL>
SQL> select * from test_stats_ddl ;
no rows selected
SQL>
SQL> REM the above should show that the INSERT did get Rolledback
SQL>
SQL> rem lets try another transaction
SQL>
SQL> insert into test_stats_ddl values ('b');
1 row created.
SQL>
SQL> analyze table test_stats_ddl compute statistics;
Table analyzed.
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL> select * from test_stats_ddl;
COL1
-----
b
SQL>
SQL> REM did the INSERT of 'b' get Rolledback ? No !
SQL> rem so the analyze did an implicit commit
SQL>
SQL> REM what about dbms_stats ?
SQL>
SQL> insert into test_stats_ddl values ('c');
1 row created.
SQL>
SQL> exec dbms_stats.gather_table_stats('','test_stats_ddl');
PL/SQL procedure successfully completed.
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL> select * from test_stats_ddl;
COL1
-----
b
c
SQL>
SQL> select last_analyzed, num_rows from user_tables where table_name = 'TEST_STATS_DDL';
LAST_ANAL NUM_ROWS
--------- ----------
27-NOV-07 2
SQL>
SQL> REM so, once again, the insert did get committed
SQL>
SQL> REM let's try some "ordinary" DDL
SQL>
SQL> insert into test_stats_ddl values ('d');
1 row created.
SQL>
SQL> create table test_2 as select * from test_stats_ddl where 1=2;
Table created.
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL> select * from test_stats_ddl;
COL1
-----
b
c
d
SQL>
SQL> select * from test_2 ;
no rows selected
SQL>
SQL> REM it looks like the create table also did an implicit commit !