Database Reference
In-Depth Information
TABLE_NAME NUM_ROWS LAST_ANALYZED SCOPE
-------------------- ---------- --------------- -------
GT SHARED
Statistics for ON COMMIT DELETE ROWS
As shown earlier, when running procedures such as
GATHER_TABLE_STATS
, there is an implicit
COMMIT
that takes place.
Therefore, when generating statistics for temporary tables defined as
ON COMMIT DELETE ROWS
, the statistics gathered
reflect those of a table with zero rows in it (the statistics in this case are useless because you need the statistics to
reflect the data within the temporary table before it is removed by a
COMMIT
).
Starting with 12
c
, several of the procedures in
DBMS_STATS
(such as
GATHER_TABLE_STATS
) no longer issue an
implicit
COMMIT
after gathering statistics for temporary tables defined as
ON COMMIT DELETE ROWS
. This means it is
now possible to gather representative statistics for this type of temporary table. A simple example will demonstrate
this concept; first, create a temporary table with
ON COMMIT DELETE ROWS
:
EODA@ORA12CR1> create global temporary table gt(x number) on commit delete rows;
Table created.
Next, insert some data:
EODA@ORA12CR1> insert into gt select user_id from all_users;
51 rows created.
Now generate statistics for the schema:
EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'GT' );
PL/SQL procedure successfully completed.
A quick count will verify the rows still exist in the
GT
table:
EODA@ORA12CR1> select count(*) from gt;
COUNT(*)
----------
51
We can verify the existence of session-level statistics by querying
USER_TAB_STATISTICS
:
EODA@ORA12CR1> select table_name, num_rows, last_analyzed, scope
2 from user_tab_statistics
3 where table_name like 'GT';
TABLE_NAME NUM_ROWS LAST_ANALYZED SCOPE
------------------------- ---------- -------------- -------
GT SHARED
GT 51 18-JAN-14 SESSION
This allows you to generate useful statistics for temporary tables where you desire the rows to be deleted after
each transaction.
