Experience with Oracle temporary table usage

My understanding of the temporary table: Create a table in Oracle that doesn't need any other functionality, is used primarily for some specific features of its own software system, and is useless when you run out of data in a table. If you do not specify a table space for Oracle temporary tables (including the indexes of temporary tables), the data you insert into the temporary table is stored in the temporary tablespace (temp) of the Oracle system.

Creation of temporary tables

To create an Oracle temporary table, you can have two types of temporary tables:

Session-level Oracle temporary tables

A temporary table at the transaction level.

1 session-level temporary tables because the data in this temporary table is related to your current session, when your current session does not exit, the data in the temp table still exists, and when you exit the current session, the data in the temp table is all gone. Of course, at this time, if you log in with another session, you cannot see the data inserted into the temporary table in another session.

The data inserted by two different sessions is irrelevant. When a session exits, the data in the temporary table is truncated (truncate TABLE, that is, the data is emptied). Session-level temporary table creation method:

1.Create Global Temporary Table table_name

2. (Col1 type1,col2 Type2 ...) On Commit Preserve Rows;

Example:

1.create Global temporary table Student

2. (stu_id number (5),

3.CLASS_ID Number (5),

4.stu_name Varchar2 (8),

5.stu_memo varchar2) on Commit Preserve Rows;

2 A transaction-level temporary table means that the temporary table is related to the transaction, when a transaction commits or a transaction is rolled back, the data in the Oracle temporary table is truncated to its own, and the other content is consistent with the session-level temporary table (including the temporary table at the transaction level is automatically truncated when the sessions are exited). How to create a transaction-level temporary table:

1.Create Global Temporary Table table_name

2. (Col1 type1,col2 Type2 ...) On Commit Delete Rows;

Example:

1.create Global temporary table Classes

2. (class_id number (5),

3.class_name Varchar2 (8),

4.class_memo varchar2) on Commit delete Rows;

3 The difference between two types of temporary tables

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 ends, data in the Oracle temporary 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 Oracle 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 for a temporary datasheet in the form of a regular table, but add a SessionID <NUMBER> 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 Oracle 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