Shulou(Shulou.com)06/01 Report--

ORACLE database can not only save permanent table, but also create temporary table temporary tables. These temporary tables are used to hold data for a session SESSION or data needed in a transaction. When the session exits or the user commits the commit and rolls back the rollback transaction, the data of the temporary table is automatically emptied, but the structure of the temporary table and metadata are also stored in the user's data dictionary.

1. Session-level temporary table

Session-level temporary table means that the data in the temporary table only exists in the session life cycle. When the session ends, Oracle automatically clears the temporary table data. So there is a common knowledge point here, that is, the data of each session can only be seen by yourself, and the data seen by different sessions is their own (different data). This knowledge point is used a lot in optimization scenarios.

Format:

Create Global Temporary Table Table_Name

(xxx int) On Commit Preserve Rows

two。 Transaction-level temporary table

A transaction-level temporary table means that the data in the temporary table exists only in the transaction life cycle.

Create Global Temporary Table Table_Name

(xxx int)

On Commit Delete Rows

When a transaction ends (commit or rollback), Oracle automatically clears the data in the temporary table. The data of the same transaction-level temporary table is independent from session to session.

The most common scenarios for business optimization are as follows:

1 users flush the data into the database in batches at a specific time

(2) the database background lands the user data first through a transfer table (XXX)

(3) according to all kinds of logic, insert into the data of XXX table to various business tables.

Question 1: many developers design XXX as a regular table, and the developer idea that this is actually only a transit temporary table, the design of the index is naturally ignored by many people.

Question 2: this XXX comes in through multi-user brushing data, and the amount of data involved should be very large. If it is designed as a common table, some processed data must be properly delete, which obviously affects the business efficiency.

The performance problems of this business can be greatly improved by using the characteristics of temporary tables.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.