Oracle TEMP Table Global temporary table

Temporary tables: Like normal tables, there is a structure, but the management of the data is not the same, the temporary table stores the transaction or the intermediate result set of the session, the data saved in the temporary table is only visible to the current session, all sessions do not see the data of the other session, even if the other session submitted, also do not see. Temporary tables do not have concurrency behavior because they are independent of the current session.

When creating a temporary table, Oracle creates only the structure of the table (defined in the data dictionary), does not initialize the memory space, and when a session uses a temporary table, Oralce allocates a piece of memory space from the current user's temporary table space. This means that the temporary table is allocated storage space only when data is inserted into the staging table.

Temporary tables are divided into transaction-level temporary tables and session-level temporary tables.
A transaction-level temporary table is valid only for the current transaction, via the statement: on COMMIT DELETE ROWS specified.
Session-level temporary tables are valid for the current session, specified by the statement: on COMMIT PRESERVE rows statement.

Example of usage (in Scott mode):
CREATE GLOBAL Temporary TABLE session_temp_tab on COMMIT PRESERVE ROWS as SELECT * from EMP WHERE 1=2;
The on COMMIT PRESERVE rows statement specifies that the temporary table created is a session-level temporary table, and the data in the staging table is stored until we disconnect or manually execute delete or truncate
In, and only the current session can be seen, other sessions are not visible.

CREATE GLOBAL Temporary TABLE transaction_temp_tab on COMMIT DELETE ROWS as SELECT * from EMP WHERE 1=2;
The on COMMIT DELETE rows statement specifies that the temporary table created is a transaction-level temporary table, which persists until commit or rollback, and the data in the table is automatically purged after the transaction commits.

Note: The transaction level temporary table must commit the transaction (commit) at processing time, otherwise the following error will be reported; ORA-01410: Invalid ROWID

INSERT INTO Session_temp_tab SELECT * from EMP;
INSERT INTO Transaction_temp_tab SELECT * from EMP;


Sql> Select COUNT (*) from Session_temp_tab;

COUNT (*)
----------
14

Sql> Select COUNT (*) from Transaction_temp_tab;

COUNT (*)
----------
14
Sql> commit;

Commit Complete

Sql> Select COUNT (*) from Session_temp_tab;

COUNT (*)
----------
14

Sql> Select COUNT (*) from Transaction_temp_tab;

COUNT (*)
----------
0


When the data in the transaction level temporary table is cleared automatically after commit, the result is 0 when querying again;
Sql> Disconnect;
Not logged on

Sql> Connect Scott/tiger;
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as Scott

Sql> Select COUNT (*) from Transaction_temp_tab;

COUNT (*)
----------
0

Sql> Select COUNT (*) from Session_temp_tab;

COUNT (*)
----------
0
When you reconnect, the data in the session-level staging table is automatically deleted.

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

5.tempporary 
is also one of the heap tables, with two types of temporary tables.
sql> Create global temporary table GT1 as SELECT * from Dba_objects;

Table created.

Sql> SELECT * from gt1;--no data because the on commit delete rows

No rows selected
sql> Create global temporary is omitted Table GT2 on commit delete rows as select * from Dba_objects;

Table created.

-There is also a temporary table that retains data at the time of submission
sql> create global temporary table GT3 on commit preserve rows as select * from Dba_objects ;

Table created.

Sql> Select COUNT (1) from GT3;

  count (1)
----------
     10042
--Another cmd
sql> select COUNT ( 1) from GT3;

  count (1)
----------
         0
This table is a real table. This table structure can be seen in any session. But the data for each session is isolated. The definition remains in the data dictionary, and the data is saved in a temporary table space. The
uses it to reduce the generation of logs.
10g, to create this table belongs to which user, the default is placed in the user's default temporary table space. 11g can be placed in a single table space.

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

In Oracle8i or later, you can create the following two types ofTemp Table：
1. Session-specific Temporal tables
CREATE GLOBAL Temporary <TABLE_NAME> (<column specification>)
On COMMIT PRESERVE ROWS;

2. Transaction-specific temporal tables
CREATE GLOBAL Temporary <TABLE_NAME> (<column specification>)
On COMMIT DELETE ROWS;
CREATE GLOBAL TemporaryTABLEMytemptable
Although the temporary table is built, but you try to insert a record and then use a different connection to ascend to select, the record is empty, understand, I put the following two words again:
--on Commit DELETE ROWS indicates that the staging table is a transaction designation, after each commitORACLEThe table will be truncated (all rows are deleted)
--on COMMIT PRESERVE ROWS indicates that the staging table is session-specific and Oracle truncates the table when the session is interrupted.
The problem of conflict is not to be considered.

A temporary table is just the data that is used to save the current session, and the data exists only during a transaction or session.

Creates a temporary table from the Create GLOBAL temporary table command, for a temporary table of transaction type,
Data is only present during a transaction, and for a session-type temporary table, the data exists during the session.

The data for the session is private to the current session. Each session can only see and modify its own data. DML locks are not added to
Data on the temporary table. The following statement controls the existence of a row.

On COMMIT DELETE rows table name row is only visible during transaction
On COMMIT PRESERVE rows table name rows are visible throughout the session

You can create indexes, views, and departures for temporary tables, and you can import the exported tables using the export and import tools.
Defined, but cannot export data. The definition of a table is visible to all sessions.

Temporary Tables temporary table
1 Introduction
In addition to saving permanent tables, the Oracle database can also establish temporary table temporary tables. These temporary tables are used to hold the session data,
Or the data that is required to be saved in a transaction. When the session exits or the user commits a commit and rolls back the rollback transaction, the data for the temporary table is automatically emptied,
However, the structure of the temporal table and the metadata are also stored in the user's data dictionary.
Temporary tables are only supported in oracle8i and above products.
2 Detailed Introduction
The Oracle staging table is divided into session-level temporary tables and transaction-level temporary tables.
A session-level temporary table is the data in a staging table that exists only during the session lifecycle, and when the user exits the end of the session, Oracle automatically clears the data from the staging table.
A transaction-level temporary table refers to the data in a staging table that exists only during the transaction life cycle. When a transaction ends (commit or rollback), Oracle automatically clears the data from the staging table.
The data in the temporary table is valid only for the current session, each session has its own temporary data, and it cannot access data from the temporary table of the other session. So
A DML lock is not required for temporary tables. When a session ends (the user quits gracefully when the user exits the Oracle instance crashes) or a transaction ends, Oracle
The table executes the TRUNCATE statement to empty the temporary table data. However, the data in the other session staging table is not emptied.
You can index temporary tables and build views on temporary tables. Similarly, indexes that are built on temporary tables are temporary and valid only for the current session or transaction.
Temporary tables can have triggers.
3 Creating a temporary table
The definition of a temporary table is visible to all session sessions, but the data in the table is valid only for the current session or transaction.
Method of Establishment:
1) on COMMIT DELETE ROWS defines a method for establishing a transaction-level temporary table.
CREATE GLOBAL Temporary TABLE Admin_work_area
(StartDate DATE,
EndDate DATE,
Class CHAR (20))
On COMMIT DELETE ROWS;
EXAMPLE:
sql> CREATE GLOBAL Temporary 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 a method for creating a session-level temporary table.
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 Temporary 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 rows were selected.

Session 2 does not see data for temporary tables in session 1.

4 Similarities and differences between Oracle temporal tables and SQL Server temp tables

SQLSERVERTemp table
You can also create temporary tables. Temporary tables are similar to permanent tables, but temporary tables are stored in tempdb and are automatically deleted when they are no longer in use.
There are both local and global types of temporary tables, which differ in name, visibility, and availability. The name of the local temporary table begins with a single number sign (#);
They are only visible to the current user connection, and when the user is from Microsoft? SQL Server? 2000 the instance is removed when the connection is disconnected. The name of the global temporary table is in mathematical notation
(# #), which is visible to any user after it is created, and is deleted when all users referencing the table are disconnected from SQL Server.
For example, if you create a table named employees, anyone whoDatabaseThe table can be used with security permissions on the table, unless it has been deleted.
If you create a local temporary table named #employees, only you can perform operations on the table and delete the table when you disconnect. If you create a global temporary table named # #employees
, any user in the data table can perform operations on the table. If the table is not being used by other users after you create it, the table is deleted when you disconnect. If the table is created in your
SQL Server deletes the table after all users are disconnected after it is used by other users
Different:
1. The SQL Server staging table is a "memory table" in which the table is stored in memory. The table definition remains in the data dictionary unless the drop table is executed by the Oracle temp table.
2. SQL Server temporary tables do not have functionality similar to those on the Oracle Temp table transaction level.
3 SQL Server local temporary tables (#) are similar to Oracle's session-level temporary tables, but Oracle does not delete tables when the session exits.
4 The Global temporary table (# #) for SQL Server refers to multiple connections that share the same piece of memory. SQL Server automatically frees the global temporary table when no pointer references the region of memory.
5 because Oracle is not an in-memory database. Therefore, if Oracle-like SQL SERVER frequently creates and deletes temporary tables, it must affect performance.
Therefore, Oracle retains the definition of the temporary table until the User drop table.
6 in Oracle, if more than one user is required to share a table (a global temporary table like SQL Server # # #). You can use a permanent table,
and add some columns in the table that uniquely identify the user. Take advantage of triggers and views. When the user exits, the data in the corresponding table is deleted based on the unique information of the logged-on user.
This approach brings a certain amount of load to Oracle.

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

There are many values in the in of SQL, how to handle it can improve efficiency?

Recently encountered a tricky problem, from the outside to get a set of ID data, to be compared with a table in Oracle, to take out the same ID data from Oracle. For example, pub has a member of the moderator, moderator is a subset of members, the moderator list exists in Oracle, but the membership list does not exist inside. Suppose pub is in the lottery, once an hour, the winning membership list is passed to Oracle through the winning system. I would like to make a list of the members who have just won the lottery is the name of the moderator, what to do.

In the real world, the number of data obtained by external k,oracle is dozens of k to hundreds K, which is a key factor for efficiency. The only way I can think of is to construct multiple SQL statements that pass these IDs in as list values (in the list items cannot be more than 1000, so multiple SQL may be required).

With questions consulted Yangtingkun, the answer is that efficiency is not too high and not too low, Oracle may do a full table scan, but the efficiency is faster than you one by one. Actually executed, in inside passed 300 parameters, get execution plan as follows (do is INDEX RANGE SCAN):

Execution plan
----------------------------------------------------------
Plan Hash value:1656459801

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------
| 0 |         SELECT STATEMENT |   |  284 |     1136 | 1 (0) | 00:00:01 |
|  1 |         Inlist ITERATOR |       |       |            |          | |
|* 2 | INDEX RANGE scan|   Xxxxxx|  284 |     1136 | 1 (0) | 00:00:01 |
-----------------------------------------------------------------------------

It's a bit of a hassle to spell multiple SQL statements, but it's not an efficient way to do it. Think about it, it's better than me to insert the data into the table, through the table Association to do to make it easier, because when the table is built I still need to maintain the data in the tables.

"Oh, in that case, you build a temporary watch!" ”
"Global temporary table?" ”
"Well, this watch is designed to solve this problem!" Temporary data can only be inserted into the inside, the on Submit keyword is set to the session is valid or the transaction is valid, each session does not see the data between each other, does not affect each other. ”

Haha, the original problem is so simple ah! Save the trouble of data maintenance, get the table associated with efficient data processing, the only thing that will affect the efficiency of the problem is to temporary table inserted data, one is obviously very slow, consider using stored procedures to achieve, hehe.

Inserting multiple rows of data, using the Union

Db. Excutenonquery ("Delete from T863_Y_UKIDS_XL");
Ssqlinserttemp = "INSERT into T863_Y_UKIDS_XL (iukid)" + sxlsqlvalue;

INSERT into T863_y_ukids (iukid)

SELECT 123 from DUAL
UNION SELECT 456 from DUAL
UNION SELECT 1233 from DUAL
UNION SELECT 4526 from DUAL
UNION SELECT 12333 from DUAL
UNION SELECT 456 from DUAL

Oracle TEMP Table Global temporary table