Shulou(Shulou.com)06/01 Report--

Temporary table concept

Temporary tables are used to save intermediate result sets during a transaction or session. The data saved in the temporary table is only visible to the current session, and none of the sessions can see the data of other sessions; even if the current session has already committed (commit) the data, no other session can see its data. For temporary tables, there is no multi-user concurrency problem, because one session does not block another session by using one temporary table, and even if we "lock" the temporary table, it does not prevent other sessions from using their own temporary tables.

Temporary tables are stored by default in the user's default temporary space, and you can also use the TABLESPACE command to specify the temporary tablespace where the temporary table is located.

Temporary tables can create temporary indexes, views, and triggers. The data of the temporary table will not be backed up, restored, and there will be no log information for its modifications. Although the DML operation on the temporary table is faster, the Redo Log is also generated, but the same DML statement produces less Redo Log than the DML on the PERMANENT table.

Basic grammar

CREATE GLOBAL TEMPORARY TABLE [schema. ] Table [(relational_properties)] [ON COMMIT {DELETE | PRESERVE} ROWS] [physical_properties] [table_properties]

For detailed syntax instructions, see the official document: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7002.htm#i2149006

Temporary table classification

There are two types of ORACLE temporary tables: session-level temporary tables and transaction-level temporary tables.

1.ON COMMIT DELETE ROWS

It is the default parameter of the temporary table, which means that the data in the temporary table is only valid in the transaction process (Transaction). After the transaction is submitted (COMMIT), the temporary period of the temporary table will be automatically truncated (TRUNCATE).

2.ON COMMIT PRESERVE ROWS

It indicates that the contents of the temporary table can exist across transactions in the current session, but when the session ends, the temporary period of the temporary table will be discarded as the session ends, and the data in the temporary table will naturally be discarded.

Demonstration of the use of temporary tables

1. Transaction-based temporary table

-- create temporary table zx@ORCL > create global temporary table temp_transaction 2 (id number, 3 name varchar2 (10)) 4 on commit delete rows;Table created.--session1 insert test data zx@ORCL > insert into temp_transaction values (1 row created.zx@ORCL * from temp_transaction) ID NAME--1 zx-- simultaneously session2 queries the temporary table and inserts the test data SQL > select * from temp_transaction; unselected row SQL > insert into temp_transaction values; 1 row has been created. SQL > select * from temp_transaction; ID NAME--2 lx--session1 commit operation zx@ORCL > commit;Commit complete.zx@ORCL > select * from temp_transaction;no rows selected--session2 query data, commit operation SQL > select * from temp_transaction ID NAME--2 lxSQL > commit; submission completed. SQL > select * from temp_transaction; No rows selected

From the above operation process, we can see that different sessions are completely isolated from the operation of the temporary table, and after commit, the transaction-based temporary table data will "disappear".

two。 Session-based temporary table

Zx@ORCL > create global temporary table temp_session 2 (id number, 3 name varchar2 (10)) 4 on commit preserve rows;Table created.zx@ORCL > insert into temp_session values (1 row created.zx@ORCL * from temp_session; ID NAME- 1 zxzx@ORCL > commit;Commit complete.zx@ORCL > select * from temp_session) ID NAME- 1 zxzx@ORCL > truncate table temp_session;Table truncated.zx@ORCL > select * from temp_session;no rows selected--truncate data "disappears" zx@ORCL > insert into temp_session values (1 row created.zx@ORCL > commit;Commit complete.zx@ORCL > select * from temp_session) ID NAME- data still exists after 1 zx--commit-- exit the session and re-enter zx@ORCL > exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, OLAP Data Mining and Real Application Testing options [oracle@rhel6] $zxSQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 1 22:46:13 2017Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionszx@ORCL > select * from temp_session;no rows selected

From the above operation, based on the session temporary table, the data will not "disappear" after commit, but will only "disappear" after truncate or exit the session.

View the existence of temporary tables

Zx@ORCL > col table_name for a30zx@ORCL > col tablespace_name for a30zx@ORCL > col DURATION for a30zx@ORCL > select table_name,TABLESPACE_NAME,LOGGING,TEMPORARY,DURATION,MONITORING from user_tables TABLE_NAME TABLESPACE_NAME LOGGING TEM DURATION MONITORIN -- T TT YES N YESSESS_STATS TT YES N YESTEMP_SESSION NO Y SYS$SESSION NOTEMP_TRANSACTION NO Y SYS$TRANSACTION NO

Temporary watch use

When do you use a temporary watch? What's the difference between using a temporary watch and using an intermediate watch?

I think it is applied when needed. The following is an application description of the temporary table by David Dai. I think it vividly illustrates the application scenario of the temporary table: for an e-commerce website, different consumers shop on the site, it is an independent SESSION, the shopping cart is put into the shopping cart, and finally the goods in the shopping cart are settled. That is, the information in the shopping cart must be saved throughout the SESSION. At the same time, there are some consumers who often give up buying goods when they finally check out. If the consumer purchase information is directly stored in the final table (PERMANENT), it will inevitably cause great pressure on the final table. Therefore, for such a case, the method of creating a temporary table (ON COMMIT PRESERVE ROWS) can be used to solve it. The data is only valid during the SESSION period. For the valid data that has been settled successfully, ORACLE automatically TRUNCATE the temporary data after it is transferred to the final table; for the data that gives up settlement, ORACLE also automatically TRUNCATE without coding control, and the final table only processes valid orders, reducing the pressure of frequent DML operations.

1: when dealing with a batch of temporary data and requiring multiple DML operations (insert, update, etc.), it is recommended to use temporary tables.

2: when some tables are in the query and need to be used for join multiple times. (in order to obtain the target data, we need to associate A, B, C, and in order to obtain another target data, we need to associate D, B, C.)

With regard to temporary tables and intermediate tables (NOLOGGING, save intermediate data, delete after use), which is more suitable for storing intermediate data, I personally prefer to use temporary tables rather than intermediate tables.

Restrictions on temporary tables

Temporary tables cannot be partitioned, clustered, or indexed organized tables

Temporary tables cannot have foreign keys

Temporary tables cannot contain nested tables

Temporary tables do not support parallel UPDATE, DELETE, and MERGE

Temporary tables do not support distributed transactions

Delete temporary table

Zx@ORCL > drop table temp_transaction;Table dropped.-- if delete Times error ORA-14452: attempt to create, alter or drop an index on temporary table already in use indicates that a session is using the temporary table and cannot be deleted

Reference:

Http://www.cnblogs.com/kerrycode/p/3285936.html

"the Art of 9I10G11G programming goes deep into database architecture"

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.