Explain in detail the usage of temporary tables in Oracle databases

This articleArticleThis article mainly introduces the usage of temporary tables in Oracle databases, hoping to help you learn and work.

I. Syntax

In Oracle, you can create two types of temporary tables:

(1) Session-specific temporary tables

Create global temporary ()

On commit preserve rows;

(2) temporary tables specific to transactions

Create global temporary ()

On commit Delete rows;

Create global temporary table mytemptable

Although the created temporary table exists, if a record is inserted and then mounted to select with another connection, the record is null.

-- On commit Delete rows indicates that the temporary table is specified by a transaction. After each commit, Oracle truncates the table (delete all rows)

-- On commit preserve rows indicates that the temporary table is specified by the session. When the session is interrupted, Oracle truncates the table.

Ii. Dynamic Creation

Create or replace procedure pro_temp (v_col1 varchar2, v_col2 varchar2)

V_num number;

Begin

Select count (*) into v_num from
User_tables where table_name ='t _ temp '';

-- Create temporary table

If v_num <1 then

Execute immediate ''create global temporary table t_temp (

Col1 varchar2 (10 ),

Col2 varchar2 (10)

) On commit Delete rows '';

End if;

-- Insert data

Execute immediate ''insert into t_temp values
(''' V_col1' ''', ''' v_col2 '''''')'';

Execute immediate ''select col1 from t_temp ''into v_num;

Dbms_output.put_line (v_num );

Execute immediate ''delete from t_temp '';

Commit;

Execute immediate ''drop table t_temp '';

End pro_temp;

Test:

15:23:54 SQL> set serveroutput on

15:24:01 SQL> exec pro_temp ('11', '22 '');

11

The PL/SQL process is successfully completed.

Used time: 00: 00: 00.79

15:24:08 SQL> DESC t_temp;

Error:

ORA-04043: the object t_temp does not exist

3. Features and performance (compared with common tables and views)

The temporary table is valid only for the current connection.

Temporary tables are not indexed. Therefore, it is not recommended if the data volume is large or multiple queries are performed.

When data processing is complex, tables are faster, and views are faster.

When only querying data, we recommend that you use the cursor: Open cursor for ''SQL clause '';

============================

 

Temporary table features

1All sessions in the database can access the same temporary table, but only sessions inserted into the temporary table can see the data inserted by the temporary table.

2You can specify a temporary table as transaction-related (default) or session-related:

3If there are records in the temporary table, the table cannot be deleted. NoDrop table.

4, Although temporary tables are not generated"Redo ",But it is to generate"Undo"Of

Temporary table category and Creation 1 Session-specific temporary tables

Record will be left in this table until the session is disconnected or passedDeleteOrTruncateDelete these records physically.

Create global temporary table <table_name> (<column specification>)

On commit preserve rows;

2 Temporary tables specific to transactions

After the transaction is committed, the records inserted in the transaction will not be retained and will be automatically deleted.

Create global temporary table <table_name> (<column specification>)

On commit Delete rows;

3 , DDL Syntax comment

"Global"Indicates the definition of a temporary table, which is visible to all sessions. Temporary tables are created onlyCreate global temporary tableAnd no otherCreate ****** temporary tableCommand.

Temporary table Deletion 1 Delete a temporary table exclusive to a session

To quickly delete such temporary tables, you must firstTruncateThe data in the table, and thenDropTable structure. If you useDeleteCommand to delete the table records first, you cannot directly Delete the table. The table structure is deleted from other sessions or new sessions only after the current session exits.

UseDelteteAfterDropTable error:

SQL> Delete tmp_test;

8 rows deleted

SQL> commit;

Commit complete

SQL> drop table tmp_test;

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

This is because"On commit preserve rows"Clause,Will apply a row lock(ROW-X ).

Type =

To lock "temporary table object enqueue"

For details, seeDoc ID: 186854.1.

2 Delete temporary tables specific to transactions

UseOn commit Delete rowsClause does not have so many restrictions.CommitAfter that, the record is automatically cleared and the table can be deleted directly.

Temporary table tablespace allocation

When creating a temporary table, no table space is allocated. When a user uses a temporary table to store data, the user allocates storage space from the default temporary table space.

References

1,Http://blog.itpub.net/post/10/8764

Use temporary tablesSQLOptimization Case 2-temporary table statistics

2,Http://www.itpub.net/178008.html&highlight=%C1%D9%CA%B1%B1%ED

Introduction: use of temporary tables in three mainstream Databases. Doc

SQL Server MS

SQL ServerTemporary tables are supported. Temporary tables are those whose names use the well number(#)Table. If the temporary table is not removed when the user disconnects,SQL ServerThe temporary table is automatically removed. Temporary tables are not stored in the current database, but stored in the system database.Tempdb.

There are two types of temporary tables:

Local temporary table: the name of the local temporary table is a single digit.(#)Headers; they are only visible to the current user connection.Microsoft SQL Server 2000The instance is deleted when it is disconnected.

Global temporary table: the name of the global temporary table is a mathematical symbol.(##)Headers are visible to all users after creation. If these tables are not explicitly removed before the connection to create a global temporary table is disconnected, the tables are removed as long as all other tasks stop referencing them. When the connection to the global temporary table is disconnected, the new tasks cannot reference them any more. After the current statement is executed, the association between the task and the table is removed. Therefore, the global temporary table is removed as long as the connection to the global temporary table is disconnected.

For exampleEmployeesIf you have the security permission to use the table in the database, you can use the table unless it has been deleted. If you create a project named# EmployeesTo delete a local temporary table. If you create a project named# EmployeesGlobal temporary table. Any user in the data table can perform operations on the table. If the table is not used by other users after you create it, delete it when you disconnect it. If the table is used by another user after creationSQL ServerDelete the table after all users are disconnected.

Currently, many traditional uses of temporary tables can beTableReplace data type variables.

Oracle

OracleTemporary tables are supported. A temporary table is used to save intermediate results during a transaction or session. The data stored in the temporary table is only visible to the current session, and no session can see data of other sessions, even in the current sessionCommitData is invisible in the future. Multi-User parallel is not a problem. One session never blocks another session using a temporary table. Even if the temporary table is locked, one session does not block other sessions using the temporary table. Temporary tables are generated more frequently than normal tables.RedoHowever, temporary tables must generateUndoInformation, so a certain numberRedoLogs.

the temporary table is allocated space from the current log of the user's temporary tablespace, or if you access the table from the Program with the defined permission, the temporary tablespace of the program owner is used. A global temporary table is actually a template of the table. Creating a temporary table does not include bucket allocation, it does not include initial allocation. Therefore, when a session first puts data in a temporary table during running, the temporary segment of the session will be created. Since each session obtains its own temporary segment, each user may allocate space for the temporary table in different tablespaces. user1 default the temporary tablespace is temp1 , his temporary table will be from temp1 , user2 default the temporary tablespace is temp2 , his temporary table will be from temp2 .

A temporary table is created only once in each database, and does not need to be created in each stored procedure. Temporary tables always exist unless they are manually deleted. Temporary tables exist as objects in the data dictionary and are always left blank until sessions are inserted into the data.OracleAllows you to create views and stored procedures based on temporary tables.

A temporary table can be session-based or transaction-based.On commit preserve rowsClause makes the temporary table session-based. The row is left in this table until the session is disconnected or passedDeleteOrTruncateDelete these rows physically.On commit Delete rowsClause makes the temporary table a transaction-based mode. When the session is submitted, the row disappears. There is no additional overhead in the automatic clearing process of this temporary table.

InOracle. (This isSQL Server MSOrSybase)

In any database, a disadvantage of a temporary table is that the optimizer does not actually provide statistical functions in the temporary table. HoweverOracle, A series of good statistical guesses can passDbms_statsSet the package in the temporary table.

DB2

AvailableDeclare global temporary tableStatement to define a temporary table.DB2Temporary tables are session-based and isolated between sessions. When the session ends, the data in the temporary table is deleted, and the temporary table is implicitly detached. Temporary tables are not defined inSyscat. TablesAppears

The following is an example of defining a temporary table:

Declare global temporary table gbl_temp

Like empltabl

On commit Delete rows

Not Logged

In usr_tbsp

This statement createsGbl_tempUser temporary table. Define this user temporary table The names and descriptions of the columns used areEmpltablThe column Name and description are identical. Implicit definition Only the column name, data type, empty attribute, and column default attribute are included. All other column attributes are not defined, including unique constraints, external keyword constraints, triggers, and indexes. RunCommitDuring operation, If this table is not enabledWith holdAll data in the table is deleted. Not recorded Changes made to the user's temporary table. The user temporary table is placed in the specified user temporary tablespace. This tablespace must exist, otherwise the declaration of this table will fail.

User-Defined temporary tables are not supported:

LobType column (or based onLob)

User-Defined type column

Long varcharColumn

DatalinkColumn

End of document.