Oracle Learning Notes (ii)--Temporary tables

The introduction of Oracle temporary tables is a good strategy for multi-table cascading queries or complex transactions with large data volumes. As a result, you often encounter situations where you need to work with stored procedures and temporary tables when you are addressing actual requirements. Here's a summary of how Oracle creates temporary tables and considerations:

First, create a temporary table

The Oracle staging table is divided into two types: the reply period and the transaction period, and their creation syntax is basically the same.

• Session Temp Table

CREATE GLOBAL temporary TABLE on COMMIT PRESERVE ROWS;

• Transaction Period temporary table

CREATE GLOBAL temporary TABLE on COMMIT DELETE ROWS;

Ii. the difference between the temporal table of the session and the temporary table in the transaction period

In a transaction-related temporal table, the data exists only during the transaction. In the session-related temporal table, the data only exists during the conversation. The data in the staging table is private to a session. Each session can only query and modify data that belongs to this session. Locking (Lock) is not required for DML operations on temporal table data. The LOCK statement is not valid for temporary tables because each session can only manipulate its private data.

TRUNCATE statements executed for a session-related temporal table (session-specific temporary table) will only clear (TRUNCATE) The data belonging to this session, not the data belonging to other sessions in this temporary table.

Users can create indexes for temporary tables (temporary table) using the CREATE INDEX statement. Indexes created on temporary tables are also temporary, and the lifetime of the index data is the same as the lifetime of the data in the temporary table.

* Users can create views that simultaneously access a permanent table (permanent table) and a temporary table (temporary tables). Users can also create triggers (trigger) on temporary tables.

Temporary tables (temporary table) use temporary segments (temporary segment) to store data. Unlike a permanent table (permanent table), Oracle does not allocate segments (segment) When creating temporary tables and temporary indexes, and segments are allocated the first time an INSERT (or create TABLE as SELECT) statement is executed. The select,update that was executed before the first INSERT occurred, or the DELETE statement operation was an empty table.

The user is able to perform DDL operations on the session (ALTER Table,drop table,create INDEX, etc.) when there are no sessions and a temporary table (temporary table) binding (bound). When an INSERT statement is executed on a temporary table, the session is bound to this temporary table. The TRUNCATE statement executed on the staging table at the end of the session will unbind the (unbound) session from this temporary table. For a transaction-related (transaction-specific) temporary table, executing a COMMIT or ROLLBACK will unbind the session from this temporary table.

Third, when should we choose the temporary table

The most common use cases for temporary tables are in stored procedures. For example, the results of some complex searches are saved for later use, thus avoiding repeated queries. Another possible use scenario is when some large number of multi-table queries are made, and the result set data for some queries is small. We can save this part of the data in a temporary table, thereby reducing the number of cascading queries of multiple tables to improve the retrieval speed. This is a better choice for session-level staging tables.

The temporary table itself is not difficult to use, but we often forget its existence in production. As far as my personal experience is concerned, using temporal tables in certain specific requirements can actually reduce the complexity of queries and improve query speed.

Oracle Learning Notes (ii)--Temporary tables