How to manage temporary tables in Oracle

1, the concept of temporary table

Temporary table (Global temporary table): Online shopping for e-commerce

Temporary tables are used to store private data for a session or transaction. Once a temporary table is established, its structure persists, but its data is valid only within the current transaction or within the current session.

2, the basic knowledge of the temporary table

Temporary tables are only supported in Oracle 8i and above products. In addition to saving permanent tables, Oracle databases can also create temporary tables temporary 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.

When Oracle's temporary tables are created, they do not occupy the table space, and the temporary tables are not stored in the user's table space, but in the temporary tablespace specified by the 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. An index built on a temporary table is also temporary and is valid only for the current session or transaction.

Although the DML operation on the temporary table is faster, it is also to generate Redo log, just the same DML statement, which produces less Redo log than the permanent DML.

3, the shortage of 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 temporary tables when the functionality is actually needed in the application.

(2) The primary foreign key relationship is not supported

4, characteristics and performance (compared to normal tables and views)

(1) Temporary tables are valid only within the current connection

(2) Temporary tables are not indexed, so if the amount of data is large or multiple queries are not recommended, use

(3) When the data processing is more complicated, the table is fast, whereas the view is quick.

(4) A cursor is suggested when querying only data: Open cursor for ' SQL clause ';

5, the Application of temporary table:

For an E-commerce site, different consumers on the site shopping, is an independent session, the purchase of goods into the shopping cart, the final shopping cart in the goods to settle. That is, you must save the information in your shopping cart throughout the session. At the same time, there are some consumers, often end of the bill to give up the purchase of goods. 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. Data is valid only during session, Oracle automatically TRUNCATE temporary data after valid data is transferred to the final table, and Oracle automatically TRUNCATE the data that discards the settlement without encoding control, and the final table deals with only valid orders. Reduced the frequent DML pressure.

Another application of Temp Table is the intermediate data that holds the data analysis.

6. Create temporary tables

(1) Automatically delete records when transactions are committed, based on transactions

Sql> Create global temporary table

2 temp_01 (id int) on commit delete rows;--Delete record on transaction commit

sql> INSERT INTO temp_01 values (1);

sql> INSERT INTO temp_01 values (2);

Sql> select * from temp_01;

Id

----------

1

2

Sql> commit;--Submit

Commit complete.

Sql> select * from temp_01;

No rows selected--record has been deleted

(2) automatic deletion of records when the user exits session based on sessions

Sql> Create global temporary table

2 temp_02 (id int) on commit preserve rows;

Table created.

sql> INSERT INTO temp_02 values (1);

1 row created.

sql> INSERT INTO temp_02 values (2);

1 row created.

Sql> commit;

Commit complete.

Sql> SELECT * from temp_02;--submit also see records

Id

----------

1

2

Sql> Conn/as sysdba--session ended, record deletion

Connected.

Sql> select * from temp_02;

No rows selected

Cuug

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/