Introduction to the actual usage and significance of Oracle temporary tables

We all know that temporary Oracle tables can improve the processing performance of Oracle databases. If we do not need to store temporary Oracle tables, they can only be stored in the actual application space of temporary Oracle tables. I hope it will help you in this regard.

Currently, all applications that use Oracle as the database support platform have a large amount of data, that is, the data volume of tables is generally more than one million data volume.

Of course, creating partitions in Oracle is a good choice, but when you find that your application has multiple table associations, and most of these tables are large, when you associate a table, you find that the result set obtained after one or more tables are joined is very small and the query speed for this result set is very fast, in this case, I want to create a "temporary table" in Oracle ".

My understanding of temporary tables: Create a table in Oracle. This table is not used for any other functions, but mainly used for some special functions of the software system, when you run out, the data in the table is useless. After creating a temporary Oracle table, it basically does not occupy the tablespace. If you do not specify that the temporary table (including the index of the temporary table) is empty, the data you insert into the temporary Oracle table is stored in the temporary tablespace (TEMP) of the ORACLE system ).

Temporary table Creation

Create an Oracle temporary table. There are two types of temporary tables:

Session-level temporary table

Transaction-level temporary table.

1) SESSION-level temporary table because the data in this temporary table is related to your current SESSION. When your current SESSION does not exit, the data in the temporary table will still exist, when you exit the current SESSION, all the data in the temporary table is lost, of course, if you log on to another SESSION at this time, you will not be able to see the data inserted into the temporary table in another SESSION. That is, 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, data is cleared. 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(200)) on Commit Preserve Rows ;  
 
 

2) Transaction-level temporary tables refer to the Oracle temporary tables related to transactions. When a transaction is committed or the transaction is rolled back, the data in the temporary tables will be truncated by themselves, the other content is consistent with the SESSION-level temporary table (including when the SESSION is exited, the transaction-level temporary table will be automatically truncated ). How to create a temporary transaction 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(200)) on Commit delete Rows ;  
 
 

3) differences between the two temporary tables

Session-level temporary tables use on commit preserve rows, while transaction-level temporary tables use on commit delete rows. In usage, session-level data is truncated only when the session ends temporary tables, in addition, the data in the temporary transaction table is truncated, whether it is commit, rollback, or session termination.

4) when to use the Oracle temporary table

1) When an SQL statement is associated with two or more tables, it is associated with some small tables. You can split a large table and store a small result set in a temporary table.

2) some temporary data may need to be stored during program execution, which is required throughout the program session.

3. Example: omitted

4. Limitations of temporary tables

1) the lob object is not supported. This may be the consideration of the designer's running efficiency, but temporary tables cannot be used when this function is required in actual applications.

2) The primary/foreign key relationship is not supported.

Therefore, for the above reasons, we can create a temporary table by ourselves to make up for the shortcomings of the temporary oracle table.

The above are all tested by myself, but below is the method found on the Internet. I have not tested it, but I think it is very feasible and I have time to test it.

Creation method:

1. Create the table structure of the temporary data table as a regular table, but add a SessionID column to the primary key of each table to distinguish different sessions. (Lob columns and primary foreign keys can be included)

2. Write a user logout trigger to delete all records inserted in the session when the user ends the session (SessionID is equal to the session ID ).

3. When writing data, the program writes the current session ID to the table.

4. When the program reads data, it only reads records with the same ID as the current session.

Extended feature design:

1. You can create a view on the data table. The view filters records based on the SessionID of the current session.

2. The SessionID column in the data table can be implemented through Trigger to achieve transparency to the application layer.

3. Advanced users can access global data for more complex functions.

Advantages of extended temporary tables:

1. implements the same features as Oracle session-based temporary Oracle tables.

2. Supports lob data types such as SDO_GEOMETRY.

3. Supports primary and foreign key connections between tables, and the primary and foreign key connections are also session-based.

4. Advanced users can access global data for more complex functions