Temp Table - Part 4 - Global temporary tables

Global Temporary tables

• The base definition for the global temporary table is created using CREATE GLOBAL TEMPORARY TABLES syntax.

The base definitions are stored in data dictionary.

• The instances of the global temporary tables are materialized using any of the following SQL's

1. INSERT
    
2. INSERT SELECT
    
3. DROP STATISTICS
    
4. COLLECT STATISTICS
    
5. CREATE INDEX .. ON TEMPORARY ….
    
6. DROP INDEX … ON TEMPORARY …

• Users temp space is used by the materialized instances.

• Global temporary tables survive system restarts.

• Each instance of global temp table is local to the session.

• Each instance of the global temporary table can be dropped manually. If not the instance will get dropped automatically at the end of the session. Note that only the materialized instance gets dropped and not the base definition.

• Global table instances start out empty initially.

• Like Volatile tables , Global table also have LOG/NO LOG and ON COMMMIT PRESERVE/DELETE  ROWS options available  on them.

• Each of the session can have its own materialized instance. However data in the both instances cannot be shared.

• Example of creating a Global temporary table definition

CREATE GLOBAL TEMPORARY TABLE G_TEMP, NO FALLBACK , NO LOG

(

Empid integer,

Salary decimal(10,2)

Deptno integer

) ;

The ON COMMIT DELETE ROWS clause is the default, so it does not need to appear in the CREATE TABLE statement. If you want to use the command ON COMMIT PRESERVE ROWS, you must specify that in the CREATE TABLE statement. 

We can use ALTER TABLE syntax to change the defaults as show below

ALTER TABLE G_TEMP, ON COMMIT PRESERVE ROWS;

• For every materialized instance there is a row inserted in the table DBC.Temptables

select * from DBC.Temptables;

HostNo	SessionNo	TableId	BaseDbId	BaseTableId	AccountDbId	StatisticsCnt
1	800401	15-80-91-75-00-00	00-00-D4-05	D7-02-12-9E-00-00	00-00-3B-BE	5
1	800401	15-80-96-75-00-00	00-00-D4-05	D7-02-33-A1-00-00	00-00-3B-BE	-1
1	800401	15-80-97-75-00-00	00-00-D4-05	D7-02-D0-A1-00-00	00-00-3B-BE	-1
1	800401	15-80-9C-75-00-00	00-00-D4-05	29-02-B8-CE-00-00	00-00-3B-BE	1
1	800401	15-80-9D-75-00-00	00-00-D4-05	2B-02-71-CA-00-00	00-00-3B-BE	1
1	800401	15-80-A5-75-00-00	00-00-D4-05	E5-02-1E-3A-00-00	00-00-3B-BE	-1

Above shows at the moment there are 6 materialized instances of global temporary tables.

All of them belong to same session 800401.

• Logically all three types of tables- Derived, Volatile and Global temporary are types of temporary tables. However in Teradata terms, temporary table means Global Temporary tables.

The keyword TEMPORARY is used in SQL only for global temporary tables.

• Dropping Global temporary tables

1. Dropping only the instance

DROP TEMPORARY TABLE sales;

The keyword TEMPORARY in the command indicates the command applies to the instance.

2. DROP TABLE sales;

This drops the base table definition and local instance if present .

However this will fail if there are any other instances on the system under any other user id.

3. DROP TABLE sales ALL;

This syntax drops the base definition and also all the existing instances for all the users.

However this will fail if any instance is in transaction.

ALL and TEMPORARY are mutually exclusive.

• SHOWing table definition:

1. Seeing DDL of the base definition
   
   SHOW TABLE sales;

2. Seeing the DDL of the temporary table instance

SHOW TEMPORARY TABLE sales;

Again the rule is that keyword TEMPORARY applies to the instance of the table.

• We can use ALTER table query to change the definition of the base, But it will not work if there are any materialized instances.

• GRANT and REVOKE commands apply only to the base definitions. User needs to have access to the base definition to materialize the instances. Once the instances are created no rights are checked.

• HELP INDEX

1. HELP INDEX sales;

Shows the indexes defined on the base .

2. HELP TEMPORARY INDEX sales;

Shows the indexes defined on the materialized instance. The materialized instances can have different indexes than the base in case they are created or dropped on the instances itself after its materialized.

• HELP STATS

1. HELP STATS sales;

Shows statistics defined on the base definition.

2. HELP TEMPORARY STATS sales;

Shows stats defined on the materialized instances.

Additional stats could be defined on materialized instances.

• Creating secondary indexes

1. CREATE INDEX (deptno) on Sales;

This creates an index on the base definition.

The query will fail if we have any materialized instances.

We cannot have a named index on global tables.

2. CREATE INDEX (deptno) ON TEMPORARY sales;

This creates index on the materialized instance only.

• Dropping Secondary indexes:

1. DROP INDEX (deptno) ON Sales;

Drops index on the base definition.

2. DROP INDEX (deptno) ON TEMPORARY Sales;

Drops the materialized instance of Sales.

• Collecting stats:

We can define stats on the base table or collect stats on the materialized instances.

When done on base table, it actually defines stats.

When done on the instances, it collects the stats and stored in data dictionary.

1. COLLECT STATS ON sales COLUMN (deptno);

Defines the Statistics on the column deptno.

This would fail if there are materialized instances.

2. COLLECT STATS ON TEMPORARY sales COLUMN(deptno)

Collect stats on the instance.

• DROP stats

We can drop stats on the base definition or the materialized instances

1. DROP STATS ON sales COLUMN(deptno);

Drops stats defined on the base table. This would fail if there are any materialized instances.

2. DROP STATS ON TEMPORARY sales COLUMN(deptno);

Drops stats on the materialized instances.