Usage and significance of temporary Oracle tables

Explain several usage and significance of Oracle temporary tables can be said to be a good way to improve database processing performance, when there is no need to store, only stored in the Oracle temporary tablespace. I hope this article will help you. 1. Preface Oracle Logo currently applies to all applications that use Oracle as the database support platform. Most of the systems with a large amount of data, that is, the data volume of tables is generally more than one million pieces of data. Of course, creating partitions in Oracle is a good choice, but when you find that your application has multiple table associations, and most of these tables are large, when you associate a table, you find that the result set obtained after one or more tables are joined is very small and the query speed for this result set is very fast, in this case, I want to create a "temporary table" in Oracle ". My understanding of temporary tables: Create a table in Oracle. This table is not used for any other functions, but mainly used for some special functions of the software system, when you run out, the data in the table is useless. After creating a temporary Oracle table, it basically does not occupy the tablespace. If you do not specify that the temporary table (including the index of the temporary table) is empty, the data you insert into the temporary table is stored in the temporary tablespace (TEMP) of the ORACLE system ). 2. Create an Oracle temporary table for temporary tables. There are two types of temporary tables: temporary tables at the session level of www.2cto.com. 1) SESSION-level temporary table because the data in this temporary table is related to your current SESSION. When your current SESSION does not exit, the data in the temporary table will still exist, when you exit the current SESSION, all the data in the temporary table is lost, of course, if you log on to another SESSION at this time, you will not be able to see the data inserted into the temporary table in another SESSION. That is, 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, data is cleared. Create Global Temporary Table Table_Name (Col1 Type1, Col2 Type2 ...) on Commit Preserve Rows; example: create global temporary table Student (Stu_id Number (5), Class_id Number (5), Stu_Name Varchar2 (8), Stu_Memo varchar2 (200 )) on Commit Preserve Rows; 2) a transaction-level temporary table is a transaction-related temporary table. When a transaction is committed or rolled back, the data in the temporary table will be truncated by itself, the other content is consistent with the SESSION-level temporary table (including when the SESSION is exited, the transaction-level temporary table will be automatically truncated ). Create Global Temporary Table Table_Name (Col1 Type1, Col2 Type2 ...) on Commit Delete Rows; example: create global temporary table Classes (Class_id Number (5), Class_Name Varchar2 (8), Class_Memo varchar2 (200) on Commit delete Rows; 3) differences between the two types of temporary tables: on commit preserve rows for the www.2cto.com session-level temporary tables; and on commit delete rows for transactions, session-level temporary tables are truncated only when the session ends. Transaction-level temporary tables, whether commit, rollback, or session ends, Data in tb temporary tables will be truncated. 4) when to use temporary table 1) When an SQL statement is associated with two or more tables and associated with some small tables. You can split a large table and store a small result set in the temporary table. 2) some temporary data may need to be stored during program execution, this data needs to be used throughout the program session. 4. Limitations of the temporary table 1) the lob object is not supported. This may be the consideration of the designer's running efficiency. However, when this function is required in practical applications, the temporary table cannot be used. 2) The primary and foreign key relationships are not supported. Therefore, we can create temporary tables by ourselves for the above reasons to make up for the shortcomings of the temporary oracle tables. The above are all tested by myself, however, the following method is found on the Internet. I have not tested it, but I think it is very feasible. I have time to test the method for creating www.2cto.com: 1. Create the table structure of the temporary data table as a regular table, but add a SessionID column to the primary key of each table to distinguish different sessions. (Lob columns and primary foreign keys can be included) 2. Write a user logout trigger, when the user ends the session, all records inserted by the session are deleted (SessionID is equal to the session ID ). 3. When writing data, the program writes the current session ID to the table. 4. When the program reads data, it only reads records with the same ID as the current session. Extended Feature Enhancement design: 1. You can create a view on the data table. The view filters records based on the SessionID of the current session. 2. The SessionID column in the data table can be implemented through Trigger to achieve transparency to the application layer. 3. Advanced users can access global data for more complex functions. Advantages of extended temporary tables: 1. It implements the same functions as Oracle session-based temporary tables. Www.2cto.com 2. Supports lob data types such as SDO_GEOMETRY. 3. Supports primary and foreign key connections between tables, and the primary and foreign key connections are also session-based. 4. Advanced users can access global data to implement more complex functions.