Temporary Tables Temporary Table
1 Introduction
Oracle Database In addition to saving permanent tables, you can also create a temporary table TEMPORY TABLES. These ones
The temporary table is used to save the data of a session session, or the data saved in a transaction. When the session exits or the user submits commit and rollback Rollback transaction,
The data of the temporary table is automatically emptied, but
The structure of the temporary table and the metadata are also stored in the user's data dictionary.
The temporary table is only supported in Oracle8i and the above products.
2 detailed introduction
Oracle temporary table is divided into session level
Temporary table and transaction level
Temporary tables.
Session level
Temporary table refers to
The data in the temporary table is only exists in the session life cycle. When the user exits the session, Oracle automatically clears
Temporary table data.
Transaction
Temporary table refers to
The data in the temporary table is only exists in the transaction life cycle. When a transaction ends (Commit or rollback), Oracle automatically clears
Temporary table data.
The data in the temporary table is only valid for the current session, and each session has its own temporary data and cannot access other session.
Data in the temporary table. therefore,
The temporary table does not require a DML lock.
When a session ends (the user does not properly exit the user does not have normal exit Oracle instance crash) or an end of the transaction, Oracle performs TRUNCATE statements to empty this session
Temporary table data. But will not empty other sessions
Data in the temporary table.
You can index
Temporary table and in
The temporary table is based on the view. Similarly, it is established
The index on the temporary table is also temporary, and it is also only valid for the current session or transaction.
The temporary table can have a trigger.
3 establishment
Temporary tables
The definition of a temporary table is visible to all session sessions, but the data in the table is only valid for the current session or transaction.
Establishment method:
1) On Commit Delete Rows defines the establishment of transaction
Temporary table method.
Create Global Temporary Table Admin_Work_Area
(STARTDATE DATE,
Enddate Date,
Class char (20))
On commit delete rows;
EXAMPLE:
SQL> CREATE GLOBAL TEMPORY TABLE Admin_Work_Area
2 (StartDate Date,
3 Enddate Date,
4 Class Char (20))
5 on commit delete rows;
SQL> CREATE TABLE PERMERNATE (A Number);
SQL> INSERT INTO Admin_Work_Area Values (sysdate, sysdate, 'temperary table ";
SQL> INSERT INTO Permernate Values (1);
SQL> commit;
SQL> SELECT * from admin_work_area;
SQL> Select * from permernate;
A
1
2) On Commit Preserve Rows defines the creation session level
Temporary table method.
Create Global Temporary Table Admin_Work_Area
(STARTDATE DATE,
Enddate Date,
Class char (20))
On commit preserve rows;
EXAMPLE:
Session 1:
SQL> Drop Table Admin_Work_Area;
SQL> CREATE GLOBAL TEMPORY TABLE Admin_Work_Area
2 (StartDate Date,
3 Enddate Date, 4 Class Char (20))
5 On Commit Preserve Rows;
SQL> INSERT INTO Permernate Values (2);
SQL> INSERT INTO Admin_Work_Area Values (Sysdate, Sysdate, 'Session Temperary ";
SQL> commit;
SQL> Select * from permernate;
A
------------
1
2
SQL> SELECT * from admin_work_area;
StartDate Enddate Class
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
17-1 ô -03 17-1 ô -03 Session Temperary
Session 2:
SQL> Select * from permernate;
A
------------
1
2
SQL> SELECT * from admin_work_area;
No line is selected.
Session 2 does not see the session 1
Temporary table data.
4 Oracle Temporary Table and SQLServer temporary
SQL Server Temporary Table
You can also create
Temporary tables.
Temporary table is similar to permanent table, but
Temporary table is stored in TEMPDB and will be automatically deleted when it is no longer used.
There are two types of local and global
Temporary tables, both are different in name, visibility, and usability. local
The name of the temporary table is headed in a single numeric symbol (#); they are only visible to the current user connection; when the user is disconnected from the Microsoft® SQL ServerTM 2000 instance unconnected, it is deleted. Global
The name of the temporary table is headed by mathematical symbols (##), and it is visible to any user after creating, and when all references to the table are disconnected from SQL Server, it is deleted.
For example, if you create a table named Employees, anyone can use the table as long as you have secure permissions using the table in the database unless it has been deleted. If you create a local name called #ecployees
Temporary table, only you can perform operations on the table and remove it when disconnected. If you create a global name called ## Employees
Temporary table, any of the users in the data table can perform operations. If the table does not use other users after you create, the table is deleted when you disconnect the connection. If the table is used after you create other users, SQL Server deletes the table after all users are disconnected.
different:
1. SQL Server Temporary Table is a "memory table", which is stored in memory. Oracle Temporary Table Unless executed Drop Table, the table definition is retained in the data dictionary.
2. The SQL Server temporary table does not have functions similar to the Oracle Temporary Form Transaction Level.
3 SQL Server Local
Temporary table (#) with Oracle's session level
The temporary table is similar, but Oracle does not delete the table when the session exits.
4 SQL Server's global
Temporary table (##) means that multiple connects share the same memory. When there is no pointer to reference the memory area, SQL Server is automatically released.
Temporary tables.
5 Due to Oracle is not a database in memory. So if Oracle is similar to SQL Server
The temporary table is established and deleted, which will definitely affect performance. So Oracle will retain
Temporary table definitions until the user Drop Table.
6 In Oracle, if you need multiple users to share a table (Similar to SQL Server's global
