Summary of Oracle temporary tables (reproduced)

Reprint Address: http://www.cnblogs.com/kerrycode/p/3285936.html

Temporal table Concepts

A temporary table is a database object that is used to temporarily save temporary data (or intermediate data), which is somewhat similar to a normal table, but is quite different. It can only be stored in a temporary table space, not a user's tablespace. An Oracle temporary table is a session or transaction level that is visible only to the current session or transaction. Each session can only view and modify its own data.

Temporary table syntax

Temporary table classification

There are two types of Oracle staging tables: The session-level temporal table and the transaction-level temporary table.

1) on COMMIT DELETE ROWS

It is the default parameter of the temporary table, which means that the data in the staging table is only valid in the thing process (Transaction), and the temporary table's temporary segment is automatically truncated (TRUNCATE) after the commit (commit), but the structure of the temporary table and the metadata are also stored in the user's data dictionary. If the staging table completes its mission, it is best to delete the temporary table, otherwise the database will have many table structures and metadata for the temporary table.

2) on COMMIT PRESERVE ROWS

It means that the contents of the temporary table can exist across things, but when the session ends, the temporary segment of the temporal table is discarded as the session ends, and the data in the temporary table is naturally discarded. However, the structure of the temporal table and the metadata are also stored in the user's data dictionary. If the staging table completes its mission, it is best to delete the temporary table, otherwise the database will have many table structures and metadata for the temporary table.

1: Session-level temporal table data is related to your current session, and the data in the temporary table is still present in the event that the current sessions do not exit, and the data for the temporary table is truncated only when you exit the session (TRUNCATE table), as follows:

Session-level Temporal table creation:

Create global temporary TABLE tmp_test (ID number, NAME VARCHAR2 (+)) on COMMIT PRESERVE ROWS; Create global Tempor ARY table tmp_test on COMMIT PRESERVE rowsasselect * from TEST; operation Example:sql> CREATE GLOBAL temporary TABLE tmp_test (ID Number, NAME VARCHAR2 (+)) on COMMIT PRESERVE ROWS; Table createdsql> INSERT into tmp_test    SELECT 1, ' Kerry ' from dual;1 row insertedsql> COMMIT;  Commit completesql> SELECT * from Tmp_test;id NAME--------------------------1 kerrysql> INSERT into Tmp_test SELECT 2, ' rouce ' from dual;1 row insertedsql> ROLLBACK; Rollback completesql> SELECT * from Tmp_test;id NAME--------------------------------1 kerrysql>

2: The temporary table of the transaction level (default), this type of temporary table is related to the transaction, when the transaction commit or the transaction rollback, the data of the temporary table will be truncated itself, that is, when commit or rollback, the data will be truncate off, other features and session-level temporal table consistent.

How to create a transaction-level temporary table:

Create global temporary TABLE tmp_test (ID number, NAME VARCHAR2 (+)) on COMMIT DELETE ROWS, or create global Temporar Y TABLE tmp_test on COMMIT DELETE as SELECT * from TEST; sql> CREATE GLOBAL Temporary TABLE tmp_test (ID number, NAME VARCHAR2 (32)
) on COMMIT DELETE ROWS; 　　　　　　　　　　Table createdsql> INSERT into tmp_test Select 1, ' Kerry ' from dual;1 row insertedsql> SELECT * from Tmp_test;id NAME--------------------------------1 kerrysql> COMMIT; Commit completesql> SELECT * from Tmp_test;id  NAME----------------------------------sql>

3: About temporary tables are only visible to the current session or transaction. Each session can only view and modify its own data.

Log in to the database with DM user, open session 1, create temporary table tmp_test

Create global temporary TABLE tmp_test (ID number, NAME VARCHAR2 (+)) on COMMIT DELETE ROWS, or create global Temporar Y TABLE tmp_test on COMMIT DELETE as SELECT * from TEST; sql> CREATE GLOBAL Temporary TABLE tmp_test (ID number, NAME VARCHAR2 (+)) on COMMIT DELETE ROWS; 　　　　　　　　　　Table createdsql> INSERT into tmp_test Select 1, ' Kerry ' from dual;1 row insertedsql> SELECT * from Tmp_test;id NAME-------------------------------1 kerrysql> COMMIT; Commit completesql> SELECT * from Tmp_test;id NAME---------------------------------sql>

Log in to database with SYS user, open session 2

SELECT * from Dba_tables WHERE table_name= ' tmp_test '--can be traced to temporary table data

SELECT * from DM. Tmp_test; --no data is found, even if the tmp_test temporary table has data.

Temporary tables differ from permanent tables

Sql> Select table_name, Tablespace_name, "LOGGING",  "temporary", DURATION, "monitoring" from Dba_tables WHERE TA Ble_name in (' Tmp_test ', ' TEST '); table_name   tablespace_name LOGGING temporary DURATION    monitoring---------------------------  --------- ---------------------------TEST          tbs_eds_data    YES       N                      yestmp_test                      NO        Y     sys$session       NO

As shown above, the temporary table is stored in the temporary table space, but the above script can see that the temporary table in the data dictionary does not specify its tablespace, the temporary table is nologging,duration to Sys$session

The DML operation of a temporary table is faster, but it is also to produce Redo log, just the same DML statement, less than the Redo log generated by PERMANENT DML actually in the application, will often create a nologging permanent table (intermediate table) to save the intermediate data, Instead of temporary tables, it's hard to say what the pros and cons of these two are.

Temporary table uses

When do I use temporary tables? What is the difference between using a temporary table and using an intermediate table?

I think it is necessary to apply, the following is the David Dai on the temporary table of an application note, I think very image of the application of the temporary table scenario: For an e-commerce site, different consumers on the site shopping, is a separate SESSION, the purchase of goods into the shopping cart, Finally, the goods in the shopping cart are settled. In other words, the information in the cart must be saved throughout the session. At the same time, there are some consumers, often final checkout when the purchase of goods. If, directly to the consumer purchase information stored in the final table (PERMANENT), the final table will inevitably cause very great pressure. Therefore, for this case, you can take the method of creating a temporary table (on COMMIT PRESERVE ROWS) to resolve. The data is valid only during the session, and when the valid data for settlement success is transferred to the final table, Oracle automatically TRUNCATE the temporary data, and Oracle automatically TRUNCATE the data for the abandonment of the settlement, without coding control, and the final table processes only valid orders. Reduces the stress of frequent DML operations.

1: Temporary tables are recommended when processing a batch of temporary data that requires multiple DML operations (INSERT, UPDATE, etc.).

2: When some tables are inside the query, they need to be used multiple times to make connections. (in order to get the target data you need to associate A, B, C, and in order to get another target data, you need to associate D, B, c ....) ）

About staging tables and intermediate tables (nologging, saving intermediate data, deleting after use) that's better for storing intermediate data, I'm personally more inclined to use temporal tables instead of using intermediate tables.

Precautions

1) LOB objects are not supported, which may be based on the operational efficiency of the designer, but it is not possible to use the temporary table when the functionality is actually needed in the application. That's a lot of stuff on the internet, and I didn't trace it. LOB objects are not supported in this version, at least in Oracle 10g, where the temporary tables are supported for LOB objects.

sql> CREATE GLOBAL Temporary TABLE tmp_test

2 (

3 ID number,

4 NAME CLOB

5) on COMMIT PRESERVE ROWS;

Table created

Sql>

Sql> INSERT into Tmp_test

2 SELECT 1, ' ADF ' from DUAL;

1 row inserted

Sql> SELECT * from V$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-prod

PL/SQL Release 10.2.0.1.0-production

CORE 10.2.0.1.0 Production

TNS for 32-bit windows:version 10.2.0.1.0-production

Nlsrtl Version 10.2.0.1.0-production

2) primary foreign key relationship not supported

3) temporary tables cannot save data permanently.

4) temporary table data will not be backed up, restored, and there will be no log information for its modifications

5) temporary tables do not have DML locks

DML Locks is not acquired on the data of the temporary tables. The LOCK statement have no effect on a temporary table, and because each session have its own private data.

6) Although DML operations on temporal tables are faster, the same is true for Redo log, which is the same DML statement, less than the Redo log generated for PERMANENT DML. Please see the official documentation:

DML statements on temporary tables does not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs is generated. Data from the temporary table was automatically dropped in the case of the session termination,either when the user logs off or When the session terminates abnormally such as during a session or instance failure.

7) temporary tables can create temporary indexes, views, triggers.

8) If you want to drop the session-level staging table, and it contains data, you must first truncate the data in it. Otherwise you will get an error.

sql> DROP TABLE tmp_test PURGE;

DROP TABLE tmp_test PURGE

ORA-14452: Attempt to create, change, or delete an index in a temporary table that is in use

sql> TRUNCATE TABLE tmp_test;

Table truncated

sql> DROP TABLE tmp_test PURGE;

Table dropped

Summary of Oracle temporary tables (reproduced)