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