GLOBAL TEMPORARY TABLEs and GATHER_TABLE_STATS

UPDATE NOTE :  These tests, published in January 2011 are for version 11.2.0.1   There are differences in 12.1 and above, so do not rely on these for 12.1 behaviour.

If you use a Global Temporary Table in your application code, watch where and how you run GATHER_TABLE_STATS on it.
(I prefer to NOT have statistics on a GTT and to delete statistics if present ; so that Oracle can do Dynamic Sampling against the table when using it in a query).

If your GTT has been defined as ON COMMIT DELETE ROWS, the GATHER_TABLE_STATS call will result in rows being deleted. This is because the GATHER_TABLE_STATS issues an implicit commit.
If your GTT has been defined as ON COMMIT PRESERVE ROWS, the GATHER_TABLE_STATS will not delete rows in the table.

Here is a demonstration with comments :

SQL> REM Demo impact of Gather Stats on GTTs
SQL>
SQL> drop table MY_GTT_DELETE ;
drop table MY_GTT_DELETE
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create global temporary table MY_GTT_DELETE (object_id number, object_name varchar2(30)) on commit DELETE rows;

Table created.

SQL> drop table MY_GTT_PRESERVE;
drop table MY_GTT_PRESERVE
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create global temporary table MY_GTT_PRESERVE (object_id number, object_name varchar2(30)) on commit PRESERVE rows;

Table created.

SQL> drop table MY_GTT_PARALLEL;
drop table MY_GTT_PARALLEL
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create global temporary table MY_GTT_PARALLEL (object_id number, object_name varchar2(30)) on commit PRESERVE rows;

Table created.

SQL>
SQL>
SQL> REM ###############################################################################################
SQL> REM -- first : a table that is defined as ON COMMIT DELETE ROWS
SQL> insert into MY_GTT_DELETE select object_id, object_name from dba_objects where object_id is not null;

76701 rows created.

SQL> -- verify that rows are present
SQL> select count(*) from MY_GTT_DELETE;

COUNT(*)
----------
76701

SQL> -- now gather stats
SQL> exec dbms_stats.gather_table_stats('','MY_GTT_DELETE');

PL/SQL procedure successfully completed.

SQL> -- look for the rows now
SQL> select count(*) from MY_GTT_DELETE;

COUNT(*)
----------
0

SQL> select num_rows from user_tables where table_name = 'MY_GTT_DELETE';

NUM_ROWS
----------
0

SQL> --- The table has no rows. Apparently the gather_table_stats issued a COMMIT
SQL> ---- This also means that any other DML that I had executed in my current session also got COMMITted !!
SQL>
SQL>
SQL> REM ###############################################################################################
SQL> REM -- second : a table that is defined as ON COMMIT PRESERVE ROWS
SQL> insert into MY_GTT_PRESERVE select object_id, object_name from dba_objects where object_id is not null;

76701 rows created.

SQL> -- verify that rows are present
SQL> select count(*) from MY_GTT_PRESERVE;

COUNT(*)
----------
76701

SQL> -- now gather stats
SQL> exec dbms_stats.gather_table_stats('','MY_GTT_PRESERVE');

PL/SQL procedure successfully completed.

SQL> -- look for the rows now
SQL> select count(*) from MY_GTT_PRESERVE;

COUNT(*)
----------
76701

SQL> select num_rows from user_tables where table_name = 'MY_GTT_PRESERVE';

NUM_ROWS
----------
76701

SQL> --- The table still has rows
SQL>
SQL> REM ###############################################################################################
SQL> REM Check to see if the rows persist across sessions
SQL> REM ---- they shouldn't !
SQL> disconnect
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> connect hemant/hemant
Connected.
SQL> select count(*) from MY_GTT_PRESERVE;

COUNT(*)
----------
0

SQL> select num_rows from user_tables where table_name = 'MY_GTT_PRESERVE';

NUM_ROWS
----------
76701

SQL> --- the table has no rows but the statistics reflect the earlier row count
SQL> exec dbms_stats.gather_table_stats('','MY_GTT_PRESERVE');

PL/SQL procedure successfully completed.

SQL> select num_rows from user_tables where table_name = 'MY_GTT_PRESERVE';

NUM_ROWS
----------
0

SQL>
SQL>
SQL> REM ###############################################################################################
SQL> REM -- last : a table where I use DEGREE in the GATHER_STATS
SQL> insert into MY_GTT_PARALLEL select object_id, object_name from dba_objects where object_id is not null;

76701 rows created.

SQL> -- verify that rows are present
SQL> select count(*) from MY_GTT_PARALLEL;

COUNT(*)
----------
76701

SQL> -- now gather stats
SQL> exec dbms_stats.gather_table_stats('','MY_GTT_PARALLEL',degree=>4,estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> -- look for the rows now
SQL> select count(*) from MY_GTT_PARALLEL;

COUNT(*)
----------
76701

SQL> --- The table still has rows
SQL> select num_rows from user_tables where table_name = 'MY_GTT_PARALLEL';

NUM_ROWS
----------
76701

SQL>
SQL> REM ###############################################################################################
SQL> REM Can anyone tell my why the 3rd DROP TABLE fails here ?
SQL> REM -- note that I have not disconnected and reconnected
SQL> DROP TABLE MY_GTT_DELETE;

Table dropped.

SQL> DROP TABLE MY_GTT_PRESERVE;

Table dropped.

SQL> DROP TABLE MY_GTT_PARALLEL;
DROP TABLE MY_GTT_PARALLEL
*
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use


SQL>

Thus, the GATHER_TABLE_STATS call on MY_GTT_DELETE actually deleted rows.
.
.
.