Search My Oracle Blog

Custom Search

27 November, 2007

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 !

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016