Netezza Temporary Tables and Examples

  • Post author:
  • Post last modified:February 28, 2018
  • Post category:Netezza
  • Reading time:4 mins read

Like any other database, Netezza also supports temp or temporary tables. There are three types of Netezza temporary tables; all of them have advantage over traditional temporary tables.

netezza temporary tables

Netezza Temporary Tables

Netezza temporary tables are local to the user session. You can use temporary table repeatedly within a user session for multiple times. Like derived tables, temporary tables also use Netezza swap partition space.

You can drop and recreate the temporary table within the user session. However, these temporary tables are not visible outside the user session. You do not need any extra privileges to create the temporary tables in Netezza.

You can use the DISTRIBUTE ON, ORGANIZE ON clause, constraints on temporary or temp tables.

Related reading:

Netezza Temporary Table Syntax:

Below is the syntax that you can use to create the temp tables in Netezza. Creating TEMP table options are similar to create regular table:

CREATE [ TEMPORARY | TEMP ] TABLE [IF NOT EXISTS] <table>
( <col> <type> [<col_constraint>][,<col> <type> [<col_constraint>]…]
[<table_constraint>[,<table_constraint>… ] )
[ DISTRIBUTE ON { RANDOM | [HASH] (<col>[,<col>…]) } ]
[ ORGANIZE ON { (<col>) | NONE } ]
[ ROW SECURITY ];

Netezza Temporary Table Example:

Below is the example that demonstrate the create temp table:

TRAINING.ADMIN(ADMIN)=> 
 CREATE TEMP TABLE name (
 code CHARACTER(5) CONSTRAINT firstkey PRIMARY KEY,
 title CHARACTER VARYING(40) NOT NULL,
 did DECIMAL(3) NOT NULL,
 date_prod DATE,
 kind CHAR(10),
 len INTERVAL HOUR TO MINUTE
 ) DISTRIBUTE ON (code);

Netezza Derived Tables

Read About Netezza Derived Table and Examples

Netezza External Tables

Netezza external tables are also type of Netezza temporary tables that are created on SMP HOST. A Netezza external table is known in the data dictionary and requires additional privileges to create them.

List all the Temporary Tables Available in Netezza System

Use the SHOW TEMP TABLE command to display all of the temporary tables that currently exist on the system.

Below is the Examples to display all temporary tables in the system:

TRAINING.ADMIN(ADMIN)=> SHOW TEMP TABLE; 
 DATABASE | SCHEMA | TABLENAME | ALLOCATED_BYTES | USED_BYTES 
----------+--------+-----------+-----------------+------------ 
(0 rows)

Also Read:

Tags: , ,