ORACLE temporary table Summary

Temporary table concept

A temporary table is a database object used to temporarily save temporary data (or intermediate data). It is similar to a common table, but it is quite different. It can only be stored in temporary tablespaces, not user tablespaces. ORACLE temporary tables are session-or transaction-level and are only visible to the current session or transaction. Each session can only view and modify its own data.

 

Temporary table syntax

 

 

 

Temporary Table Category

 

There are two types of ORACLE temporary tables: Session-level temporary tables and transaction-level temporary tables.

1) ON COMMIT DELETE ROWS

It is the default parameter of the temporary table, indicating that the data in the temporary table is only valid in the Transaction process. After the Transaction is committed (COMMIT, the temporary segment of the temporary table will be automatically truncated (TRUNCATE), but the structure and metadata of the temporary table are also stored in the user's data dictionary. If a temporary table completes its mission, it is recommended to delete the temporary table. Otherwise, the database will leave many table structures and metadata of the temporary table.

2) ON COMMIT PRESERVE ROWS

It indicates that the content of the temporary table can exist across things. However, when the session ends, the temporary segment of the temporary table will be discarded as the session ends, the data in the temporary table is naturally discarded. However, the structure and metadata of the temporary table are stored in the user's data dictionary. If a temporary table completes its mission, it is recommended to delete the temporary table. Otherwise, the database will leave many table structures and metadata of the temporary table.

1: The data in the SESSION-level temporary table is related to your current SESSION. If the current SESSION does not exit, the data in the temporary table will still exist, the temporary TABLE data is truncated (truncate table) only when you exit the current SESSION, as shown below:

Session-level temporary table creation:

Create global temporary table TMP_TEST (id number, NAME VARCHAR2 (32) on commit preserve rows; or create global temporary table TMP_TEST on commit preserve rowsasselect * from test; Operation example: SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST (ID NUMBER, NAME VARCHAR2 (32) 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: Transaction-level temporary tables (default). This type of temporary tables is related to transactions. When a transaction is committed or the transaction is rolled back, the data in the temporary table will be truncated by itself, that is, when COMMIT or ROLLBACK is used, data is TRUNCATE. Other features are consistent with Session-level temporary tables.

How to create a temporary transaction table:

Create global temporary table TMP_TEST (id number, NAME VARCHAR2 (32) on commit delete rows; or create global temporary 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: the temporary table is only visible to the current session or transaction. Each session can only view and modify its own data.

Log on to the database with the DM user, Open SESSION 1, and create a temporary table TMP_TEST

 

Create global temporary table TMP_TEST (id number, NAME VARCHAR2 (32) on commit delete rows; or create global temporary 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>

 

 

Log on to the database with the sys user and Open SESSION 2.

SELECT * FROM DBA_TABLES WHERE TABLE_NAME = 'tmp _ test' -- temporary table data can be found.

SELECT * from dm. TMP_TEST; -- no data is found, even if the temporary table TMP_TEST contains data.

 

Differences between temporary and permanent tables

SQL> SELECT TABLE_NAME, TABLESPACE_NAME,"LOGGING",　　  "TEMPORARY", DURATION, "MONITORING"　　 FROM DBA_TABLES WHERE TABLE_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 tablespace, but the script above shows that the temporary table is not specified in the data dictionary, the temporary table is NOLOGGING, and the DURATION is SYS $ SESSION

The DML operation speed of the temporary table is relatively fast, but the Redo Log is also generated, but the same DML statement is less than the Redo Log generated by the permanent dml, which is actually used in applications, a nologging Permanent Table (intermediate table) is often created to save intermediate data and replace the temporary table. What are the advantages and disadvantages of the two, it is hard to say that it is clear (Welcome to discuss it ).

 

Temporary table usage

 

When to use a temporary table? What is the difference between using a temporary table and using an intermediate table?

I think it is an application description of the temporary table by David Dai as needed. I think it is a very vivid description of the application scenario of the temporary table: for an e-commerce website, different consumers shop on the website, which is an independent SESSION. Buy items in the shopping cart and settle the items in the shopping cart. That is to say, the information in the shopping cart must be saved throughout the SESSION. At the same time, there are still some consumers who often give up purchasing the goods at the final checkout. If the purchase information of the consumer is directly stored in the final table (PERMANENT), it will inevitably put a lot of pressure on the final table. In this case, you can create a temporary table (on commit preserve rows. The data is valid only during the SESSION period. ORACLE automatically TRUNCATE the temporary data after the valid data is successfully transferred to the final table. ORACLE also automatically TRUNCATE the data that is not settled, instead of coding control, the final table only processes valid orders, reducing the pressure on frequent DML operations.

1: when processing a batch of temporary data that requires multiple DML operations (insert, update, etc.), we recommend that you use a temporary table.

2: when some tables need to be connected for multiple times in the query. (To obtain the target data, associate A, B, and C, and to obtain another target data, associate D, B, C ....)

For temporary tables and intermediate tables (NOLOGGING, saving intermediate data, and deleting after use), it is more suitable for storing intermediate data. I personally prefer to use temporary tables, we do not recommend that you use an intermediate table.

 

Notes

1)The lob object is not supported. This may be the consideration of the designer based on the running efficiency, but temporary tables cannot be used when this function is required in actual applications. This is the case in many documents on the Internet. I did not check whether the version does not support lob objects. At least in ORACLE 10 Gb, temporary tables support 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, 'afd' 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)The primary/foreign key relationship is not supported.

3)Temporary tables cannot store data permanently.

4)Temporary table data will not be backed up, restored, and no log information will be generated for its modifications.

5)Temporary tables do not have DML locks

DML locks are not acquired on the data of the temporary tables. The LOCK statement has no effect on a temporary table, because each session has its own private data.

6)Although the DML operation on the temporary table is faster, the Redo Log is also generated, but the same DML statement is less than the Redo Log generated by the permanent dml. See the official documentation:

DML statements on temporary tables do not generate redo logs for the data changes. however, undo logs for the data and redo logs for the undo logs are generated. data from the temporary table is automatically dropped in the case of 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, and triggers.

8)If you want to DROP a session-level temporary table that contains data, you must first truncate the data. Otherwise, an error is reported.

SQL> DROP TABLE TMP_TEST PURGE;

Drop table TMP_TEST PURGE

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

SQL> TRUNCATE TABLE TMP_TEST;

Table truncated

SQL> DROP TABLE TMP_TEST PURGE;

Table dropped