Wednesday 27 June 2012

Oracle Temporary Table


Core part of Temporary Table

Ø  Data only visible from the current session

Added Advantages

Ø  Index can be created on temporary table
Ø  Check constraints is possible  (unicity, limited set of possible values…)
Ø  A view can reference a temporary table
Ø  No FOREIGN KEY, no storage allocation, no INITIAL extent, no tablespace
Ø  Temporary table allocates storage from the temporary tablespace of the connected user.

Why Temporary table is faster

Ø  Temporary tables generate no redo logs for their blocks.

What need to be avoided while using Temporary table.

Ø  Temporary tables generate undo, and the undo is logged.
Ø  So we must avoid deleting or updating from temporary tables. You can just let the temporary tables empty themselves automatically after a commit.
Ø  If billions of rows will be inserted producing a temporary tablespaces full, If such a process does not give performance increase

What should we do using Temporary table

Ø  You should use temporary tables mostly for inserting or selecting of data