The difference between 11g and 12C for Oracle transaction-based temporal tables

Oracle transaction-based temporal tables, under 11g and 12C, can be seen when statistics on temporary tables are collected, the former records are emptied, the latter is not, which is an important distinction. In the company environment with 12C, in the field with 11g, using temporary table will cause the time is slow, before I have a post http://blog.csdn.net/stevendbaguo/article/details/39964807, after using hint, is not particularly good, so the direct acquisition, the result of temporary table is emptied. The solution is to build a temporary table based on the session, and truncate after each use, otherwise there will be a problem.

Sql> select * from V$version;

BANNER con_id
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0-64bit Production 0
PL/SQL Release 12.1.0.1.0-production 0
CORE 12.1.0.1.0Production 0
TNS for linux:version 12.1.0.1.0-production 0
Nlsrtl Version 12.1.0.1.0-production 0
sql> drop table test purge;
--transaction-based temporal tables
Sql> Create global temporary table test
(
ID number
)
on commit delete rows;
sql> INSERT INTO Test select object_id from Dba_objects;
99412 rows Inserted
Sql> Select COUNT (1) from test;
COUNT (1)
----------
99412
sql> exec dbms_stats.gather_table_stats (user, ' test ');
Sql> Select COUNT (1) from test;
COUNT (1)
----------
99412



Sql> select * from V$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
PL/SQL Release 11.2.0.1.0-production
CORE 11.2.0.1.0Production
TNS for Linux:version 11.2.0.1.0-production
Nlsrtl Version 11.2.0.1.0-production
sql> drop table test purge;
--transaction-based temporal tables
Sql> Create global temporary table test
(
ID number
)
on commit delete rows;
sql> INSERT INTO Test select object_id from Dba_objects;
70379 rows Inserted
Sql> Select COUNT (1) from test;
COUNT (1)
----------
70379
sql> exec dbms_stats.gather_table_stats (user, ' test ');
Sql> Select COUNT (1) from test;
COUNT (1)
----------
0

The difference between 11g and 12C for Oracle transaction-based temporal tables