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

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.