Temp Tables- Part 1 - Introduction

There are 3 types of temp tables:

1. Global temp tables

2. Volatile tables

3. Derived tables

What are the disadvantages of using permanent tables for temporary purposes:

1. We need to have separate steps to create and populate tables

2. It takes perm space to create these tables.

3. We need to drop the table explicitly after we are done using the tables.

4. Data dictionary access is needed for creating and dropping the tables.

Derived Tables	Volatile temp tables	Global temporary tables
These tables are local to the query. The tables exists for the duration of the query Makes use of spool space. No data dictionary involvement needed Tables are created using SQL incorporated within the query.	These tables are local to the session and not just the query. The table is discarded at the end of the session. Makes use of spool space. No Data dictionary involvement needed Table must be explicitly created using the CREATE VOLATILE TABLE syntax.	This table is also local to the session and not just the query. The Table instance is discarded at the end of the session. The way Global temp tables are different from the volatile table is that global table has a definition in the Data dictionary . This data definition can be shared by multiple users. Makes use of temp space. Data dictionary involvement necessary as the definition is stored in data dictionary. The table definition in the data dictionary is created using the CREATE GLOBAL TEMPORARY TABLE syntax. The instances of the tables are materialized using many other ways explained later.