Compare the differences between the Oracle temporary table and the SQL Server temp table _oracle

Oracle database The process of creating temporary tables and the contrast with the different points of the SQL Server Temp table is the main content of this article, let's take a look at this part of the content, we hope to be able to help you.

1. Introduction

In addition to saving permanent tables, Oracle databases can also create temporary tables temporary tables. These temporary tables are used to hold data for session sessions, or to hold data that is required in a transaction. When a session exits or a user commits a commit and rolls back a rollback TRANSACTION, the temporary table's data is automatically emptied, but the structure of the temporary table and the metadata are stored in the user's data dictionary.

2. Detailed Introduction

Oracle Temporary Tables are divided into session-level temporary tables and transaction-level temporary tables.

A session-level temporary table refers to data in a temporary table that exists only during the session lifecycle, and when the user exits the session, Oracle automatically clears the data in the temporary table.

A transaction-level temporary table means that data in a temporary table exists only in the transaction lifecycle. When a transaction ends (commit or rollback), Oracle automatically clears the data in the temporary table.

Data in a temporary table is valid only for the current session, each session has its own temporary data, and cannot access data from other temporary tables in the session. Therefore, a staging table does not require a DML lock.

When a session ends (the user normally exits the user's abnormal exit from the Oracle instance crash) or a transaction ends, Oracle clears the temporary table data from the table executing the TRUNCATE statement for the session. However, the data in the other session temp table will not be emptied.

You can index temporary tables and build views on the basis of temporary tables. Similarly, indexes built on temporary tables are also temporary and are valid only for current sessions or transactions. Temporary tables can have triggers.

3. Establishment of temporary tables

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.

To establish a method:

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 ()) on 
COMMIT DELETE ROWS; 
EXAMPLE: 
sql> CREATE GLOBAL temporary TABLE admin_work_area 
2 (startdate date, 
3 enddate date, 
4 Class CHAR (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 session-level temporary tables. 
CREATE GLOBAL Temporary TABLE admin_work_area 
(startdate date, 
enddate date, 
class CHAR ()) on 
COMMIT PRESERVE ROWS; 

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 ()) 
   5 on COMMIT PRESERVE ROWS; 
sql> INSERT INTO permernate values (2); 
sql> INSERT into the 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??-17-1??-session Temperary 

Session 2:

Sql> select * from Permernate; 
A 
---------- 
1 
2 
sql> select * from Admin_work_area; 

Session 2 cannot see the data for the temporary table in session 1.

Similarities and differences between 4.Oracle temporary tables and SQL Server temporary tables

SQL Server can also create temporary tables. Temporary tables are similar to permanent tables, but temporary tables are stored in tempdb and 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 names of local temporary tables start with a single number sign (#), they are visible only to the current user connection, and are deleted when a user disconnects from a Microsoft SQL Server instance. The name of the global temporary table begins with a mathematical symbol (# #), which is visible to any user after it is created, and is deleted when all users referencing the table are disconnected from SQL Server.

The SQL statement refers to the temporary table using the name specified for table_name in the CREATE Table statement:

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY) 

If a local temporary table is created by a stored procedure or by an application that is executed by multiple users at the same time, SQL Server must be able to distinguish between tables created by different users. To do this, SQL Server appends a numeric suffix internally to the table name of each local temporary table. A temporary table stored in the sysobjects table of the tempdb database, whose full name consists of the table name specified in the CREATE table statement and the system-generated numeric suffix. To allow the suffix to be appended, the table name specified for the local temporary table table_name cannot exceed 116 characters.

Unless you explicitly drop a temporary table by using the DROP table statement, the temporary table is automatically dropped by the system when it exits its scope:

When the stored procedure completes, the local temporary table created in the stored procedure is automatically dropped. This table can be referenced by all nested stored procedures that are executed by the stored procedure that created the table. However, the process that invoked the stored procedure that created this table cannot reference this table.

All other local temporary tables are automatically dropped at the end of the current session.

Global temporary tables are automatically dropped when the session that created this table ends and other tasks stop referencing them. The association between a task and a table is maintained only within the life cycle of a single Transact-SQL statement. In other words, when the session that created the global temporary table ends, the last Transact-SQL statement referencing the table completes, and the table is automatically dropped.

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 database can perform operations 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.

A local temporary table created in a stored procedure or trigger differs from a temporary table of the same name created before a stored procedure or trigger is invoked. If a query references a temporary table, and there are two temporary tables with the same name, you do not define which table to resolve the query against. A nested stored procedure can also create a temporary table with the same name as a temporary table created by the stored procedure that invoked it. All references to table names in nested stored procedures are interpreted as tables created for the nested procedure, such as:

Create PROCEDURE Test2 
as 
create TABLE #t (x INT PRIMARY KEY) 
INSERT into #t VALUES (2) 
SELECT test2col = x From #t 
go 
CREATE PROCEDURE Test1 
as 
create TABLE #t (x INT PRIMARY KEY) 
INSERT into #t VALUES (1) 
   select test1col = x from #t 
EXEC Test2 
go 
CREATE TABLE #t (x INT PRIMARY KEY) 
inserts into #t VALUES ( Go 
EXEC Test1 Go 
 

Here is the result set:

(1 row (s) affected) 
Test1col  
-----------  
1  
(1 row (s) affected) 
Test2col  
-----------  

When you create a local or global temporary table, the CREATE TABLE syntax supports all constraint definitions other than the FOREIGN KEY constraint. If you specify the FOREIGN KEY constraint in a temporary table, the statement returns a warning that the constraint has been ignored and that the table is still created but does not have a FOREIGN key constraint. Temporary tables cannot be referenced in FOREIGN KEY constraints.

Consider using table variables without using temporary tables. Temporary tables are useful when you need to explicitly create an index on a temporary table, or when multiple stored procedures or functions need to use table values. In general, table variables provide more efficient query processing.

Different points from Oracle:

1. A SQL Server temp table is a "memory table" that is stored in memory. Oracle Temporary tables The table definition remains in the data dictionary unless the drop table is executed.

2. SQL Server temp tables do not have features similar to the Oracle Temp table transaction level.

The 3.SQL server local temporary table (#) is similar to Oracle's session-level temporary table, but Oracle does not delete the table when the session exits.

4.SQL server's global temporary table (# #) means that multiple connections share the same piece of memory. SQL Server automatically releases the global temporary tables when no pointers are available for the memory area.

5. Because Oracle is not an in-memory database. So if Oracle is similar to SQL Server's frequent creation and deletion of temporary tables, it will certainly affect performance. So Oracle retains the definition of the temporary table until the user drops table.

6. In Oracle, if multiple users are required to share a table (SQL Server-like global temporary table # #). You can take advantage of a permanent table and add some columns to the table that uniquely identify the user. Use triggers and views. When the user exits, Deletes data from the corresponding table based on the unique information of the logged in user. This approach brings a certain amount of load to Oracle.

About the difference between the Oracle temporary table and the SQL Server Temp table The relevant knowledge is introduced here, I hope this introduction can be harvested for you!