Create global temporary table and its differences with SQL Server

 

1 Overview

In addition to permanent tables, the ORACLE database can also create temporary tables. These temporary tables are used to save the data of a SESSION or the data required in a transaction. When the session exits or the user commits a commit or rollback transaction, the data in the temporary table is automatically cleared, but the structure and metadata of the temporary table are also stored in the user's data dictionary.

In addition to permanent tables, Oracle can create temporary tables to hold session-private data that exists only for the duration of a transaction or session.

Temporary tables are supported by Oracle9i and Oracle8i.

2. Details

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

A session-level temporary table means that the data in the temporary table only exists in the session lifecycle. When the user exits the session, Oracle automatically clears the data in the temporary table.

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

The data in the temporary table is only valid for the current Session. Each Session has its own temporary data and cannot access the data in the temporary table of other sessions. Therefore, DML locks are not required for temporary tables.

The create global temporary table statement creates a temporary table that can be transaction-specific or session-specific. for transaction-specific temporary tables, data exists for the duration of the transaction. for session-specific temporary tables, data exists for the duration of the session. data in a temporary table is private to the session. each session can only see and modify its own data. DML locks are not acquired on the data of the temporary tables. the LOCK statement has no effect on a temporary table, because each session has its own private data.

When a session ends (the user Exits normally and the user does not exit normally and the ORACLE instance crashes) or a transaction ends, Oracle executes the TRUNCATE Statement on the table in the session to clear the temporary table data. however, data in other temporary session tables is not cleared.

A truncate statement issued on a session-specific temporary table truncates data in its own session. It does not truncate the data of other sessions that are using the same table.

DML statements on temporary tables do not generate redo logs for the data changes. however, undo logs for the data and redo logs for the undo logs are generated. data from the temporary table is automatically dropped in the case of session termination, either when the user logs off or when the session terminates abnormally such as during a session or instance failure.

You can index a temporary table and create a view on the temporary table. Similarly, the index on the temporary table is temporary and only valid for the current session or transaction. The temporary table can have a trigger.

You can create indexes for temporary tables using the create index statement. indexes created on temporary tables are also temporary, and the data in the index has the same session or transaction scope as the data in the temporary table.

You can create views that access both temporary and permanent tables. You can also create triggers on temporary tables.

3. Create a temporary table

The definition of a temporary table is visible to all sessions, but the data in the table is only valid for the current SESSION or transaction.

Creation method:

1) on commit delete rows defines how to create 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 how to create 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;

Row not selected.

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

 

 

4 similarities and differences between 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. They are automatically deleted when they are no longer used.

There are two types of temporary tables: local and global, both of which have different names, visibility, and availability. The name of the local temporary table starts with a single numeric sign (#). They are only visible to the current user connection and are deleted when the user is disconnected from the Microsoft SQL Server instance. The name of the global temporary table starts with a mathematical sign (#). After being created, it is visible to all users. All users who reference this table are deleted when they are disconnected from SQL Server.

 

The SQL statement uses the name specified for table_name In the CREATE TABLE statement to reference the temporary TABLE:

Create table # MyTempTable (cola int primary key)

Insert into # MyTempTable VALUES (1)

 

If a local temporary table is created by a stored procedure or an application executed by multiple users at the same time, SQL Server must be able to differentiate the tables created by different users. Therefore, SQL Server adds a digital suffix to the table name of each local temporary table internally. The full name of a temporary TABLE stored in the sysobjects table of the tempdb database is composed of the TABLE name specified in the create table statement and the digital suffix generated by the system. To allow append suffixes, the table name table_name specified for the local temporary table cannot exceed 116 characters.

 

Unless the drop table statement is used to explicitly remove a temporary TABLE, the system automatically removes the temporary TABLE when it exits its scope:

When the stored procedure is complete, the local temporary table created in the stored procedure is automatically removed. All nested stored procedures executed by the stored procedure of the created table can reference this table. However, the process that calls the stored procedure to create this table cannot reference this table.

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

 

The global temporary table is automatically removed when the session for this table is created and other tasks are stopped to reference it. The association between tasks and tables is only maintained during the lifecycle of a single Transact-SQL statement. In other words, when the session for creating a global temporary table ends, the table is automatically removed after the last Transact-SQL statement that references the table is completed.

 

For example, if you create a table named employees, anyone who has the security permission 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, you can only perform operations on the table and delete the table when the connection is disconnected. 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 other users after you create it, delete it when you disconnect it. If this table is used by other users after creation, SQL Server deletes the table after all users are disconnected.

 

The local temporary table created in the stored procedure or trigger is different from the temporary table created before the stored procedure or trigger is called. If a query references a temporary table with two temporary tables with the same name at the same time, it does not define which table to resolve the query. A nested stored procedure can also create a temporary table with the same name as the temporary table created by calling its stored procedure. All references to table names in nested stored procedures are interpreted as tables created for this nested procedure. For example:

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)

Insert into # t VALUES (99)

GO

EXEC Test1

GO

 

The following is the result set:

(1 row (s) affected)

Test1Col

-----------

1

(1 row (s) affected)

Test2Col

-----------

2

 

When creating a local or global temporary TABLE, the create table syntax supports all the constraint definitions except the foreign key constraint. If the foreign key constraint is specified in the temporary table, the statement returns a warning message indicating that the constraint has been ignored and the table will still be created, but it does not have the foreign key constraint. Temporary tables cannot be referenced in the foreign key constraint.

Consider using Table variables instead of temporary tables. A temporary table is useful when you need to create an index explicitly on a temporary table or use table values for multiple stored procedures or functions. Generally, table variables provide more effective query processing.

Differences from Oracle:

1. the SQL SERVER temporary TABLE is a "memory TABLE", which is stored in the memory. The ORACLE temporary TABLE is stored in the data dictionary unless DROP TABLE is executed.

2. SQL SERVER temporary tables do not support transaction-level functions similar to ORACLE temporary tables.

3. the SQL SERVER local temporary table (#) is similar to the ORACLE 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. When no pointer references the memory area, SQL SERVER Automatically releases the global temporary table.

5 because ORACLE is not a database in memory. therefore, if ORACLE frequently creates and deletes temporary tables like SQL SERVER, the performance will be affected. therefore, ORACLE retains the definition of the temporary TABLE until you DROP the TABLE.

6. in ORACLE, if multiple users need to share a table (similar to SQL SERVER's global temporary table ##). permanent tables can be used, and columns that can uniquely identify users can be added to the table. trigger and view. when the user exits, the data in the corresponding table is deleted based on the unique information of the login user. this method brings a certain amount of load to ORACLE.