Oracle temporary table learning Summary

Recently, temporary tables in Oracle need to be used for work reasons. It is found that there are still many differences between Oracle temporary tables and SQL-Server temporary tables. In order to thoroughly understand the performance of the temporary Oracle table and to better decide whether to use the temporary table in the project, I spent some time reading the documents. Next, we will summarize the learning of temporary tables during this period.

We know that in addition to creating a permanent table in the database, we can also create a temporary table ). So why do we need a temporary table? What advantages does it bring to us? Compared with a permanent table, a temporary table has the following advantages:

1. Temporary tables are faster to operate than permanent tables. Because the temporary table does not need to insert entries into the catalog table, the use of the temporary table does not need to access the catalog table, so there is no competition for the catalog table.

2. Only the app that creates a temporary table can access the temporary table. There is no lock when processing the temporary table. Because 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.

3. If the Not Logged option is specified, no logs are recorded during temporary table processing. Therefore, if a large amount of temporary data is used in only one session of the database, saving the data to the temporary table can greatly improve the performance.

4. The data in the temporary table does not exist permanently (the biggest difference from the Permanent Table). When a session ends or the transaction ends, the database automatically clears the data. In addition, temporary Oracle tables are not deleted after they are created (different from SQL-server ).

Temporary table details
There are two types of temporary tables in Oracle:

1. Session-level temporary table: (on commit preserve rows) is a temporary table in which data is valid only in the current session. When the current session is closed or a new connection is established, the content in the data table is cleared.

View plain
Create global tempopary table TEMP1
(Id number (12) Primary key, name varchar2 (10 ))
On commit preserve rows;
We use the Insert into statement to Insert a record to the TEMP1 table. After Commit, We can query this record through the Select statement.

When the current session is closed, the database is reconnected, And the Select statement is used for query, we find that the table TEMP1 does not have this record.

2. Transaction-level temporary table: (on commit delete rows) indicates that the index data is valid only in the current transaction.

View plain
Create global temporary table Temp2 (id number (12) Primary key, name varchar2 (10) <pre name = "code" class = "SQL" style = "margin-top: 4px; margin-right: 0px; margin-bottom: 4px; margin-left: 0px; background-color: rgb (240,240,240); "> ON COMMIT DELETE ROWS
Generally, if no special table is specified during table creation, transaction temporary tables are created by default. Therefore, on commit delete rows can be omitted.
We use the Insert Into statement to Insert a record to Temp2. If the record is not Commit, we can directly Select the record and check the record. After Commit, the temporary table data is found to be empty after the Select query.

Comparison between temporary Oracle tables and SQL-Server temporary tables

1. Temporary tables in SQL-Server are "memory tables" stored in TempDB. The definition of the temporary Oracle Table is stored in the data dictionary. You need to delete the temporary Oracle Table by executing Drop Table.

2. The temporary table in SQL-Server does not have a transaction-level temporary table.

3. the global temporary table (#) in SQL Server 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.

4. the SQL-Server local temporary table (#) is similar to the session-level temporary table of Oracle, But Oracle does not delete the table after the session ends.

5. Oracle is not a memory database. Therefore, if Oracle creates and deletes temporary tables frequently like SQL-server, the performance will be affected. Therefore, Oracle requires you to Drop the Table on your own.

6. in Oracle, If you want multiple users to share a table (similar to the global temporary table in SQL-Server), you can use the Permanent Table and add columns that can uniquely identify the user in the table. The trigger and view are used to delete the data in the corresponding table based on the unique information of the logged-on user when the user exits.

The author's "gavinloo's column"