Plsql_oracle Temporary tables temporary table basic concepts and usage (concept)

2014-06-08 Baoxinjian

First, the basic concept

1. My understanding of the temporal table:

Create a table in Oracle that doesn't use any of the other features, it's used for some unique features of your own software system, and it doesn't work when you're done with the data in the table. When Oracle's temporary table is created, it does not occupy the table space, and if you do not specify a temporary table (including the index of the staging table), the data you insert into the temporary table is stored in the temporary table space (temp) of the Oracle system.

2. Basic Concepts

Intermediate results for storing things or sessions can only be visible to the current session, and no other session can read, so the session parallelism is high.

3. When to use temporary tables

• When an SQL statement is associated with a table that is 2 and above, and is associated with some small table. A large table can be split and a smaller set of results is stored in a temporary table.
• Some temporary data may need to be stored during the execution of the program, which is needed throughout the session of the program and so on.

4. Deficiencies of the temporary table

• LOB objects are not supported, which may be based on the efficiency of the design, but it is not possible to use the temporary table when the functionality is actually needed in the application.
• Primary foreign key relationships are not supported

Second, type

1. Session-based

(1). Concept

Session-level temporary tables because the data in this temporary table is related to your current session, the data in the temporary table is still present when you do not exit, and when you exit the current session, the data in the temporary table is all gone. Of course, at this point, if you log in another session, you won't see the data inserted into the temporary table in the other session. That is, the data inserted by the two different sessions is irrelevant. When a session exits, the data in the temporary table is truncated (TRUNCATE TABLE, the data is emptied).

(2). syntax

CREATE GLOBAL Temporary TABLE xxgl.xxgl_test_journal (

JE_ID number,

Je_name VARCHAR (BYTE)

) on commit persever rows

2. Based on things

(1). Concept

A transaction-level temporary table is a transaction-related, when a transaction is committed or the transaction is rolled back, the data in the temporary table is truncated itself, and the other content and session-level temporary tables are consistent (including when exiting the session, the transaction-level temporary table will be automatically truncated).

(2). syntax

CREATE GLOBAL Temporary TABLE xxgl.xxgl_test_journal (

JE_ID number,

Je_name VARCHAR (BYTE)

) on commit Delete rows

Iii. the difference between things and conversations

1. Conversation refers to session, one connection

A thing is a unit of action, either success or failure, no intermediate state

2. Session-->v$seesion

Transaction-->v$transaction

3. A session can start multiple transactions

4. Sessions and things temporary table differences

Session-level temporal table with on commit preserve rows;

The transaction level uses on commit delete rows;

Usage, the session level is truncated only when the data in the temporary table is closed, and the transaction-level temporary table is truncated regardless of commit, rollback, or session end;

四、一个 a simple example

CREATE GLOBAL Temporary TABLE xxgl.xxgl_test_journal (

JE_ID number,

Je_name VARCHAR (BYTE)

) on COMMIT DELETE ROWS;

Thanks and regards

Plsql_oracle Temporary tables temporary table basic concepts and usage (concept)