The usage and meaning of Oracle temporary table

Oracle temporary tables can be said to be a good way to improve the performance of database processing, stored only in the Oracle temporary table space when there is no need to store them. Hope this article can be helpful to everyone.

1. Preface

At present, all use Oracle as database support platform application, most of the data volume of the system, that is, table data volume is generally in millions above data volume.

Of course it's a good choice to create partitions in Oracle, but when you find that your application has more than one table associated, and most of these tables are large, and when you're connected, you find that one or several tables are associated with a very small result set and the query gets the result set very quickly, So this time I consider creating a "temp table" in Oracle.

My understanding of the temporary table: Create a table in Oracle that doesn't need any other functionality, is used primarily for some specific features of its own software system, and is useless when you run out of data in a table. When Oracle's temporary tables are created, they do not occupy the table space, and if you do not specify the empty table (including the index of the temporary table), the data you insert into the temporary table is stored in the temporary tablespace of the Oracle System (TEMP).

2, the creation of temporary tables

To create an Oracle temporary table, you can have two types of temporary tables:

(1) Session-level temporary tables

(2) A temporary table at the transaction level.

1 session-level temporary tables because the data in this temporary table is related to your current session, when your current session does not exit, the data in the temp table still exists, and when you exit the current session, the data in the temp table is all gone. Of course, at this time, if you log in with another session, you cannot see the data inserted into the temporary table in another session. The data inserted by two different sessions is irrelevant. When a session exits, the data in the temporary table is truncated (truncate TABLE, that is, the data is emptied). Session-level temporary table creation method:

Create Global Temporary Table table_name

(Col1 type1,col2 Type2 ...) On Commit Preserve Rows;

Example:

Create global temporary table Student

(stu_id number (5),

CLASS_ID Number (5),

Stu_name Varchar2 (8),

Stu_memo varchar2) on Commit Preserve Rows;

2 A transaction-level temporary table means that the temporary table is related to the transaction, when a transaction commits or a transaction is rolled back, the data in the temporary table is truncated to itself, and the other content is consistent with the temporary table at the session level (including the temporary table at the transaction level is automatically truncated when the sessions are exited). How to create a transaction-level temporary table:

Create Global Temporary Table table_name

(Col1 type1,col2 Type2 ...) On Commit Delete Rows;

Example:

Create global temporary table Classes

(class_id number (5),

Class_name Varchar2 (8),

Class_memo varchar2) on Commit delete Rows;