Basic concepts and examples of temporary tables

1. The temporary table's basic knowledge temporary table is supported only in oracle 8i  and above products. Oracle databases can also create temporary table temporary tables, in addition to saving permanent tables. These temporary tables are used to hold data for session sessions, or to hold data that is required in a transaction. When a session exits or a user commits a commit and rolls back a rollback TRANSACTION, the temporary table's data is automatically emptied, but the structure of the temporary table and the metadata are stored in the user's data dictionary. After Oracle's temporary tables are created, the table space is largely unoccupied, and the temporary tables are not stored in the user's tablespace, but in the temporary tablespace specified by  Schema . If you do not specify an empty table (including the index of the temporary table), the data that you insert into the temporary table is stored in the temporary tablespace of the Oracle System (TEMP). You can create indexes, views, triggers for temporary tables, import definitions of exported tables using the export and import tools, but you cannot export data. The definition of a table is visible to all sessions. The index built on the temporary table is also temporary and is valid only for the current session or transaction .   although the DML operation of the temporary table is faster, it is also to produce  redo log , just the same DML statement, compared to the The dml  of  PERMANENT  produces less redo log . Disadvantages of temporary tables: 1. LOB objects are not supported, which may be a designer's consideration for operational efficiency, but it is not possible to use temporary tables when the functionality is really needed in the actual application. 2. Primary foreign key relationships    features and performance (compared to normal tables and views) are not supported   1. Temporary tables are valid only within the current connection   2. Temporary tables are not indexed, so it is not recommended to use   3 if the amount of data is large or if you are querying multiple times. Data processing is more complicated when the table is fast, whereas the view quickly     4. The cursor: open cursor for  ' sql  is recommended when querying data only Clause ';  temporary table application: For an E-commerce site, different consumers on the website shopping, is an independent  session, buy goods into the shopping cart, and finally the goods in the cart to settle. That is, you must save the information in your shopping cart throughout the session. At the same time, there are some consumers, oftenGive up the purchase of the goods at the end of the bill. If, directly to store consumer information in the final table (permanent), the final table will inevitably cause very great pressure. Therefore, for this case, you can use the method of creating a temporary table (on commit preserve rows) to resolve it. The data is valid only during  SESSION , and when the valid data for settlement success is transferred to the final table, ORACLE automatically truncate  the temporary data, and the data,oracle  for discarding the settlement is also automatically   TRUNCATE&NBSP, without coding control, and the final table deals only with valid orders, easing the frequent DML pressure. Another application of temp table  is the intermediate data that holds data analysis. 2.  Create temporary table Oracle temporary table, there are two types: session-level temporary table transaction-level temporary table. 2.1.  session-level temporary tables because the data in this temporary table is related to your current session, the data in the temporary table still exists when you do not exit the current sessions, and when you exit the current conversation, the data in the temporary 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). Note: This is to say that the,oracle truncate  data is only allocated to different  Session  or  transaction  temp segment   data, instead of  TRUNCATE  the whole table data. The data is still there when the commit, and the data is the same when the rollback is rolled back. .  session-level temporary table creation method:     CREATE GLOBAL  temporary table table_name  (<column specification>)   on commit  preserve rows; Or   CREATE GLOBAL TEMPORARY TABLE TABLE_NAME ON COMMIT  preserve rows as select * from table_name;    Example: SQL> CREATE  global temporary table tt (Id number (2))  on commit preserve rows  ; Table has been created. sql> select * from tt; Row Sql> insert into tt values (1) not selected;  1  line has been created. sql> commit; Submit completed. sql> select * from tt;         id----------          1 sql> insert into tt values (2);  1  line has been created. sql> select * from tt;         id----------          1          2 sql> rollback; Fallback is complete. sql> select * FROM TT;         id----------          1 2.2  Transaction-specific temporary tables (default type)   The temporary table is related to transactions, and when a transaction commits or a transaction is rolled back, the data in the temporary table is truncated to its own, and the other content will be consistent with the session-level temporary table (including when exiting the sessions). Temporary tables at the transaction level are also automatically truncated). Once a commit, the data is automatically  TRUNCATE  out. .  transaction-level temporary table creation method:     CREATE GLOBAL  temporary table table_name  (<column specification>)   On commit delete  ROWS; or  create global temporary table table_name on commit delete rows.  AS SELECT * FROM TABLE_NAME;    Create global temporary table  TABLE_NAME;   in the case of unspecified types, the default is the transaction temp table. Sql> create global temporary table tt2 (Id number (2))  ON COMMIT  DELETE ROWS ; Table has been created. sql> select * from tt2; Row not selected Sql> insert into tt2 valueS (1);  1  line has been created. sql> select * from tt2;      id----------     1 sql> commit; Submit completed. sql> select * from tt2; Row not selected sql>