This article mainly introduces the Oracle database of temporary table usage, I hope to be helpful to everyone's study and work.
One: Grammar
In Oracle, you can create the following two types of temporary tables:
(1) session-specific temporary tables
CREATE GLOBAL Temporary ()
On COMMIT PRESERVE ROWS;
(2) A transaction-specific temporary table
CREATE GLOBAL Temporary ()
On COMMIT DELETE ROWS;
CREATE GLOBAL Temporary TABLE mytemptable
The temporary table is created, but if you insert a record and then use a different connection to mount the Select, the record is empty.
--on COMMIT Delete rows Description The temporary table is a transaction specified, and Oracle truncates the table after each commit (delete all rows)
--on COMMIT PRESERVE ROWS indicates that the temporary table is a session-specific, and Oracle truncates the table when the session is interrupted.
Two: Dynamic creation
Create or Replace procedure pro_temp (v_col1 varchar2,v_col2 varchar2) as
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 has completed successfully.
Time used: 00:00:00.79
15:24:08 sql> desc t_temp;
ERROR:
ORA-04043: Object T_temp does not exist
Three: Features and performance (compared to normal tables and views)
Temporary tables are valid only within the current connection
Temporary tables are not indexed, so if the amount of data is larger or queries are repeated, it is not recommended
When the data processing is more complicated, the table is fast, whereas the view is quick.
A cursor is suggested when querying data only: Open cursor for ' SQL clause ';
==================
temporary table attributes
1. All sessions in the database can access the same temporary table, but only the sessions that insert data into a temporary table can see the data it itself inserts.
2. You can designate temporary tables as transaction-related (default) or session-Related:
3. If there are records in the temporary table, the table cannot be deleted. That is, the table cannot be drop.
4, although the temporary table does not produce "REDO", but it is to produce "UNDO " classification and creation of temporary tables 1, session-specific temporary table
Records are left in this table until the session is disconnected or physically deleted by delete or truncate.
CREATE GLOBAL Temporary TABLE <TABLE_NAME> (<column specification>)
On COMMIT PRESERVE ROWS; 2, transaction-specific temporary tables
When a transaction is committed, records inserted in the transaction are not retained and automatically deleted.
CREATE GLOBAL Temporary TABLE <TABLE_NAME> (<column specification>)
On COMMIT DELETE ROWS; 3, DDL syntax annotation
the "GLOBAL" here represents the definition of a temporary table, which is visible to all sessions. Create a temporary table only create GLOBAL temporary table, and no other commands in the form of the create temporary table. temporary table deletion 1, delete session-specific temporary tables
To quickly delete such temporary tables, you must first truncate the data in the table and then drop the table structure. If you use the delete command to delete records from a table first, you cannot delete the table directly. Deletes a table structure in another session or in a new session only after the current session exits.
Error using Deltete after drop table:
Sql> DELETE tmp_test;
8 rows deleted
Sql> commit;
Commit Complete
sql> drop table tmp_test;
Ora-14452:attempt to create, alter or drop a index on temporary table already
This is because when the on COMMIT PRESERVE rows clause is used, the lock is added (row-x).
Type=to
To Lock "temporary Table Object Enqueue"
See doc id:186854.1 specifically 2, delete transaction-specific temporary tables
There are not as many restrictions on the on COMMIT DELETE ROWS clause. After a commit, the record is automatically cleared and the table can be deleted directly. allocation of table spaces for temporary tables
temporary tables are not allocated for table space when they are created. When a user uses a temporary table to store data, it allocates storage space from the user's default temporary table space. reference materials
1, http://blog.itpub.net/post/10/8764
SQL optimization case two with temporary tables-statistics for temporary tables
2, Http://www.itpub.net/178008.html&highlight=%C1%D9%CA%B1%B1%ED
The use of temporary tables in three kinds of mainstream databases. doc
MS SQL Server
SQL Server supports temporary tables. Temporary tables are those whose names begin with a pound sign (#). If the temporary table is not dropped when the user disconnects, SQL Server automatically drops the temporary table. Temporary tables are not stored in the current database, but are stored in the system database tempdb.
There are two types of temporary tables:
Local Temp table: The name of the local temporary table begins with a single number sign (#), which is visible only to the current user connection, and is deleted when the user disconnects from a Microsoft SQL Server 2000 instance.
Global temporary table: the name of the global temporary table begins with a mathematical symbol (# #) and is visible to any user after it is created. If the tables are not explicitly dropped before the connection to create the global temporary table is dropped, the tables are dropped as long as all other tasks stop referencing them. When a connection to a global temporary table is disconnected, new tasks cannot refer to them again. The relationship between the task and the table is dropped as soon as the current statement is executed, and therefore the global temporary table is typically dropped whenever a connection to create a global temporary table is disconnected.
For example, if you create a table named employees, anyone who has security permissions to use the table in the database can use the table unless it has been deleted. If you create a local temporary table named #employees, only you can perform an operation on the table and delete the table when you disconnect. If you create a global temporary table named # #employees, any user in the datasheet can perform an action on the table. If the table is not used by another user after you create it, the table is deleted when you disconnect. If the table is used by another user after you create it, SQL Server deletes the table after all users have disconnected.
Many of the traditional uses of temporary tables can now be replaced by variables that have a table data type.
ORACLE
Oracle supports temporary tables. Temporary tables are used to hold intermediate results during a transaction or session. Data saved in a temporary table is visible only to the current session, and no session can see data from other sessions, even after the current session commit data. Multi-user parallelism is not a problem, and one session never blocks another session from using a temporary table. Even if you lock a temporary table, a session does not block other sessions from using temporary tables. Temporary tables are much less redo than normal tables, however, because temporary tables must produce undo information that contains data, a certain number of redo logs are generated.
Temporary tables will allocate space from the current log in the user's temporary table space, or, if accessed from a program that has a defined right, will use the temporary tablespace of the program owner. A global temporary table is actually just a template for the table itself. The behavior of creating temporary tables does not include allocation of storage space, nor does it include initial allocations. Therefore, when a session first places data in a temporary table at run time, a temporary segment of the session is created. Because each session obtains its own temporary segments, each user may allocate space for temporary tables in different tablespaces. USER1 's default temporary table space is TEMP1, and his temporary table allocates space from the TEMP1, USER2 's default temporary tablespace is TEMP2, and his temporary table allocates space from the TEMP2.
Temporary tables are created only once per database and do not have to be created in each stored procedure. Temporary tables always exist unless manually deleted by him. Temporary tables exist as objects in the data dictionary and always remain empty until there is a session in which the data is placed. Oracle allows you to create views and stored procedures that are based on temporary tables.
A temporary table can be either session based or transactional. The on COMMIT PRESERVE rows clause makes the temporary table a session-based pattern. Rows are left in this table until the session is disconnected or physically deleted by delete or truncate. The on COMMIT DELETE rows clause makes the temporary table a transactional pattern. When the session is committed, the line disappears. The automatic cleanup process for this temporary table does not incur additional overhead.
In Oracle, temporary tables needed by an application should be created during program installation, not when the program is run. (This is different from the use of MS SQL Server or Sybase)
One disadvantage of temporary tables in any database is that the optimizer does not actually have real statistical functionality in the temp table. However, in Oracle, a series of better statistical guesses can be set in a temporary table by dbms_stats packages.
DB2
You can use the DECLARE GLOBAL temporary table statement to define temporary tables. DB2 temporary tables are session-based and are isolated between sessions. When the session ends, the data for the temporary table is deleted and the temporary table is implicitly unloaded. The definition of a temporary table does not appear in Syscat.tables
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 creates a temporary table of users named Gbl_temp. The names and descriptions of the columns that are used to define this user temporary table are exactly the same as the names and descriptions of the EMPLTABL columns. An implicit definition includes only column names, data types, nullable attributes, and column default properties. All other column properties are not defined, including unique constraints, external keyword constraints, triggers, and indexes. When a COMMIT is performed, all data in the table is deleted if the with HOLD cursor is not opened on the table. Changes made to a user's temporary table are not logged. The user temporary table is placed in the specified user temporary table space. This table space must exist, or the declaration of this table will fail.
User Defined temp table does not support:
LOB-type columns (or LOB-based single value type columns)
User-defined type columns
LONG VARCHAR Column
DATALINK column
End of document.
