Oracle creates temporary tables

Oracle Temporal tables can be said to be a good way to improve the performance of database processing, when there is no need to store , only in the Oracle temporary table space. I hope this article will be helpful to everyone.

　　1. Preface

At present, all applications using Oracle as a database support platform, most of the data volume of a large system, that is, the table of data is generally the amount of data above millions.

Of course, creating partitions in Oracle is a good choice, but when you find that your app has more than one table associated with it, and that most of these tables are large, and you're associated with one or a few tables, the resulting set of results is very small and the query gets the result set very quickly, So this time I'm thinking of creating a "temp table" in Oracle.

My understanding of the temporal table: Create a table in Oracle, which is not used for other functions, mainly for some unique features of your own software system, and is useless when you run out of data in the table. When Oracle's temporary table is created, it does not occupy the table space, and if you do not specify a temporary table (including the index of the staging table), the data you insert into the temporary table is stored in the temporary table space (temp) of the Oracle system.

　2. Creation of temporary tables

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

Session-level temporal tables

A temporary table at the transaction level.

1) session-level temporary table because the data in this temporary table is related to your current session, the data in the temporary table is still present when you do not exit, and when you exit the current session, the data in the temporary table is all gone. Of course, at this point, if you log in another session, you won't see the data inserted into the temporary table in the other session. That is, the data inserted by the two different sessions is incoherent . When a session exits, the data in the temporary table is truncated (TRUNCATE TABLE, the data is emptied). Session-level Temporal 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 refers to a transaction-related temporary table, when the transaction commits or the transaction rollback, the data in the temporary table will be truncated itself, the other content and session-level temporal table consistency (including when exiting the session, the transaction-level temporary table will be automatically truncated). 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) Differences between the two types of temporary tables

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

　　4) When to use temporary tables

1), when an SQL statement is associated with a table of 2 and more, and is associated with some small table. A large table can be split and a smaller result set is stored in a temporary table

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

　　3. Example:

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

(1)

CREATE GLOBAL Temporary TABLE Admin_work_area
(StartDate DATE,
EndDate DATE,
Class CHAR (20))
On COMMIT PRESERVE ROWS;

(2)

CREATE GLOBAL Temporary TABLE Admin_work_area
2 (StartDate DATE,
3 EndDate DATE,
4 class CHAR (20))
5 on COMMIT DELETE ROWS;

　　4. Deficiencies of the temporary table

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

2) Primary foreign key relationship not supported

So, for these reasons, we can create a temporary table ourselves to compensate for the shortcomings of the Oracle temp table.

The above are my test, but the following is the search method on the Internet, I did not test, but I think the feasibility is very strong, there is time to test

　　To create a method:

1. Create the table structure of the temporary data table in the form of a regular table, but add a SessionID column in the primary key of each table to distinguish between different sessions. (Can have LOB columns and primary foreign keys)

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

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

4. When the program reads the data, it only reads the same record as the current session ID.

Extended design with enhanced features:

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

2. The SessionID column in the data table can be implemented by trigger to achieve the transparency of the application layer.

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

Advantages of extending temporary tables:

1. Implements the same functionality as Oracle's session-based temporary tables.

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 session-based.

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

Another blog: http://blog.sina.com.cn/s/blog_7f8a215d0100uuan.html