In some case, using inline views (subquery) is better than Temporary Table 😎😎
1. What is Temporary Table ?
Temporary table is used to stored temporary data. You create it ONCE and only ONCE, not every session. The rows you insert into it are visible only to your session, and are automatically deleted (i.e., TRUNCATE, not DROP) when you end you session ( or end of the transaction, depending on which "ON COMMIT" clause you use).
2. Some features of Temporary Table
- You can use Query and DML statements to manipulate the Temporary Table like a normal table.
- Temporary table uses Temporary tablespace to store data.
- Data is only valid within a Session or a Transaction.
- Even if the data is committed, each session can only see the data in their session.
- Because each session has its own piece of data, it is possible to avoid data conflicts, such as overwriting each other’s data.
- Temporary table automatically cleans up data at the end of Session or Transaction.
Beside that
- You can create trigger, index on temporary table.
- When using the truncate (DDL) command, only the data on the current session is lost.
- When exporting, the structure of the Temporary table is also exported as a regular table.
3. Types of Temporary Table
There are 2 types of Temporary Table
3.1 Transaction Temporary Table
Data is only valid in one Transaction, every time we end a transaction (commit, rollback) the data in the temporary table will be deleted.
3.2 Session Temporary Table
Data is only valid in one Session, every time we end a session the data in the temporary table will be deleted.
4. Additional notes
- The table is a regular object visible to other sessions. It is a bad practice to frequently create and drop temporary tables in Oracle.
- Temporary tables can be useful but they are commonly abused in Oracle. They can often be avoided by combining multiple steps into a single SQL statement using inline views (subquery).
- Oracle 18c added private temporary tables, which are single-session in-memory objects. Private temporary tables can be dynamically created and dropped.
Names of private temporary tables must be prefixed with “ORA$ptt_”
