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
Ø 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
