Creation and management of Oracle temporary tables
In Oracle database, temporary tables are used to store intermediate results during query execution. Unlike other databases, Oracle's temporary tables are not automatically deleted after use.
Create global temporary table
Global temporary tables are visible to all sessions in the database, and their data exists until the session that created them ends. To create a global temporary table, use the following syntax:
<code class="language-sql">CREATE GLOBAL TEMPORARY TABLE <表名> ( <列名> <数据类型> ) ON COMMIT PRESERVE ROWS;</code>
ON COMMIT PRESERVE ROWS
clause ensures that the table data is retained even after the transaction is committed.
Populate temporary table
After creating the temporary table, you can populate it using the INSERT
statement as follows:
<code class="language-sql">INSERT INTO <表名> SELECT * FROM <源表>;</code>
Alternatively, a temporary table can be created and populated in a single statement using the CREATE...AS SELECT
statement:
<code class="language-sql">CREATE GLOBAL TEMPORARY TABLE <表名> AS SELECT * FROM <源表>;</code>
Private temporary table (Oracle 18c and above)
Oracle 18c introduced private temporary tables, which are stored in memory and available only to the current session. These tables are created and dropped dynamically:
<code class="language-sql">CREATE PRIVATE TEMPORARY TABLE <表名> AS SELECT * FROM <源表>;</code>
Abuse of temporary tables
Although temporary tables are useful, they can also be abused in Oracle. Excessive use of temporary tables can lead to performance issues and resource contention. To avoid misuse, use temporary tables with caution and consider using inline views when possible.
The above is the detailed content of How Do I Create and Manage Temporary Tables in Oracle?. For more information, please follow other related articles on the PHP Chinese website!