Oracle temporary table (session temp table and transaction temporary table)

temporary table and temporary table classification

A temporary table is the kind of introduction of a new table in Oracle 8i. A temporary table in an Oracle database is a special kind of table that can be used as a way of temporarily saving data. A session-level temporary table refers to the existence of data in a conversation cycle, and a transaction-level temporary table after a commit or rollback, the data is purged. Of course, at the end of the session, the transaction-level temporary table data is also cleared.

Second, the session-level temporary table

The code to create the session-level temporary table is as follows:
Create global temporary table < temporary table name > (field List) on commit preserve rows

Everyone will have a question, this statement does not have a session in this keyword, how to know that this create a temporary table of the Oracle SQL statement is created a session-level temporary table? So Hongo tells you, preserve this keyword is represented as the session-level temporary table. If the preserve here is changed to delete, it becomes a transaction-level temporary table.

You can do an experiment to demonstrate that the data in the session-level temporary table is not lost after commit commits.

1 Create the session-level temporary table, table name MyTable

Session-level temporary table creation:

The code is as follows

Copy Code

CREATE GLOBAL Temporary TABLE tmp_test ( ID number, NAME VARCHAR2 (32) On COMMIT PRESERVE ROWS; Or CREATE GLOBAL Temporary TABLE tmp_test on COMMIT PRESERVE ROWS As SELECT * from TEST; Action Example: sql> CREATE GLOBAL Temporary TABLE tmp_test ( ID number, NAME VARCHAR2 (32) On COMMIT PRESERVE ROWS; Table created Sql> INSERT into Tmp_test SELECT 1, ' Kerry ' from DUAL; 1 row inserted Sql> COMMIT; Commit Complete Sql> SELECT * from Tmp_test; ID NAME ---------- ---------------- 1 Kerry Sql> INSERT into Tmp_test SELECT 2, ' rouce ' from DUAL; 1 row inserted Sql> ROLLBACK; Rollback Complete Sql> SELECT * from Tmp_test; ID NAME ---------- ---------------------- 1 Kerry Sql>

2 Insert a piece of data into the Mytalbe table

The code is as follows	Copy Code
INSERT INTO mytable values (' Hello ')

3) Submit a Commit
4 Query the data in the temporary table MyTable

The code is as follows	Copy Code
SELECT * FROM Mytalbe

We can see that there is data in the last mytable. This shows that after the commit, the data in the MyTable session-level temporary table does not disappear. But we end the session, log back in, and query the data select * FROM MyTable, when the record no longer exists because the system automatically clears the record when the session ends.

third, transaction-level temporary table

The code for creating a transaction-level temporary table is as follows:

The code is as follows	Copy Code
Create global temporary table < temporary table name > (field List) on commit delete rows

You see, the only difference between this and the creation of a temporary table at the session level is to replace preserve with delete.

In the test session, you can create a transaction-level temporary table like the one above, and write data to it. Then, after the commit, check to see if the contents still exist in the table. The correct result should be the transaction level temporary table after the commit, the data is cleared.

How to create a transaction-level temporary table:

The code is as follows

Copy Code

CREATE GLOBAL Temporary TABLE tmp_test ( ID number, NAME VARCHAR2 (32) On COMMIT DELETE ROWS; Or CREATE GLOBAL Temporary TABLE tmp_test on COMMIT deletes as SELECT * from TEST; sql> CREATE GLOBAL Temporary TABLE tmp_test ( ID number, NAME VARCHAR2 (32) On COMMIT DELETE ROWS; Table created Sql> INSERT into Tmp_test SELECT 1, ' Kerry ' from DUAL; 1 row inserted Sql> SELECT * from Tmp_test; ID NAME ---------- ---------------------- 1 Kerry Sql> COMMIT; Commit Complete Sql> SELECT * from Tmp_test; ID NAME ---------- ------------------------ Sql>

3: About temporary tables are visible only to the current session or transaction. Each session can only view and modify its own data.

With DM User Login database, open session 1, create temporary table tmp_test

The code is as follows

Copy Code

CREATE GLOBAL Temporary TABLE tmp_test ( ID number, NAME VARCHAR2 (32) On COMMIT DELETE ROWS; Or CREATE GLOBAL Temporary TABLE tmp_test on COMMIT deletes as SELECT * from TEST; sql> CREATE GLOBAL Temporary TABLE tmp_test ( ID number, NAME VARCHAR2 (32) On COMMIT DELETE ROWS; Table created Sql> INSERT into Tmp_test SELECT 1, ' Kerry ' from DUAL; 1 row inserted Sql> SELECT * from Tmp_test; ID NAME ---------- --------------------- 1 Kerry Sql> COMMIT; Commit Complete Sql> SELECT * from Tmp_test; ID NAME ---------- ----------------------- Sql>

Log on to the database with the SYS user, open session 2

SELECT * from Dba_tables WHERE table_name= ' tmp_test '--you can find temporary table data

SELECT * from DM. Tmp_test; --no data is found, even if there is data in the tmp_test temporary table.

the difference between type temporary tables in two

The session-level temporary table takes on commit preserve rows, while the transaction level takes on commit delete rows; The session level is truncated only when data in the temporary table of the session ends, and the transaction-level temporary table is either commit, ROLLBAC K or the session is over, the data in the temp table will be truncated

4 when to use temporary tables

1, when the table associated with a SQL statement is 2 and above, and is associated with some small tables. A large table can be split and a smaller set of results is stored in a temporary table.

2, the program execution process may need to store some temporary data, this data in the whole process of the session need to use and so on.

3. Example: slightly

4. Deficiencies of the temporary table

1 does not support LOB objects, which may be based on the operational efficiency of the designer, but it is not possible to use temporary tables when the functionality is actually needed in the application.

2 does not support primary foreign key relationships

So, for the above reasons, we can create our own temporary tables to make up for the deficiencies of the Oracle temp table.

The above are all I have tested, but the following is the online search method, I specifically did not test, but think the feasibility is very strong, have time to test.

To create a method:

1. Create a table structure of temporary data tables as a regular table, but add a SessionID column to the primary key of each table to differentiate between sessions. (You can have LOB columns and primary foreign keys)

2. Write a user logoff trigger to delete all the records inserted by this session (SessionID equals the record for this session ID) when the user ends the session.

3. When the program writes the data, the current session ID (SessionID) is written to the table.

4, when the program reads the data, only read the same record as the current session ID.

Feature-Enhanced extended design:

1, you can set up a view on the datasheet, the view of the record filter condition is the current session of the SessionID.

2, the SessionID column in the datasheet can be implemented through trigger to achieve the transparency of the application layer.

3. Advanced users can access global data to achieve more complex functionality.

Benefits of extending temporary tables:

1, the same functionality as Oracle's session-based temporary table is implemented.

2, support Sdo_geometry and other LOB data types.

3, support the primary foreign key connection between tables, and the primary foreign key connection is also based on the session.

4. Advanced users can access global data to achieve more complex functionality