Temporary Table (Derived, Global, Volatile)

We use temporary tables just like permanently defined database tables, with several important exceptions and restrictions. Temporary tables persist across transactions in a session and are automatically dropped when a session terminates. Temporary tables have the following operational limitations:

  • exist only while a user session is alive
  • are not visible to other sessions or transactions
  • cannot be altered using the ALTER TABLE, RENAME TABLE, or RENAME COLUMN statements
  • do not get backed up
  • cannot be used as data providers to views
  • cannot be referenced by foreign keys in other tables
  • are not displayed by the SHOW command

Temporary tables persist across transactions in a session and are automatically dropped when a session terminates or expires.

Global Temporary Table

A Global Temporary Table (GTT), is a structure that is defined once in the data dictionary. Any number of sessions can insert into and reference their own local copy of the global table structure. Global temporary tables are tables that exist only for the duration of the SQL session in which they are used. The contents of these tables are private to the session, and the system automatically drops the table at the end of that session. However, the system saves the global temporary table definition permanently in the Data Dictionary. The saved definition may be
shared by multiple users and sessions with each session getting its own instance of the table.

CREATE [SET|MULTISET] GLOBAL TEMPORARY TABLE tablename
…table definitions
…column definitions
…index definitions

Volatile Table

A Volatile temp tables (VTT), by way of contrast, are created and used only within the context of a single session, and are never written to the dictionary. If you need a temporary table for a single use only, you can define a volatile table. The definition of a volatile table resides in memory but does not survive across a system restart.
Using volatile tables improves performance even more than using global temporary tables because the system does not store the definitions of volatile tables in the Data Dictionary. Access-rights checking is not necessary because only the creator can access the volatile table.

CREATE [SET|MULTISET] VOALTILE TABLE tablename
…table definitions
…column definitions
…index definitions
ON COMMIT [DELETE|PRESERVE] ROWS

Derived Table

Derived tables are created, used and dropped within a query. These are used to store intermediate results within a query.

SELECT MT.A, MT.B, DT.C

FROM

MAIN_TABLE MT,

(select NEW_table from  where D >= 100) DT

where MT.K1=DT.K2;

How do you Generate Sequence in Teradata?

Sequences can be generated in Teradata using the existing functions

  • for storing purpose : Identity Column
      • syntax:
        SEQ_NUM decimal(10,0) NOT NULL GENERATED ALWAYS AS IDENTITY
                   (START WITH 1
                    INCREMENT BY 1
                    MINVALUE 1
                    MAXVALUE 2147483647
                    NO CYCLE)

        generated always/by default as identity

      • (start with <value> increment by <value> min<value> max<value> no cycle)
      • Identity columns do not guarantee a sequence – they only guarantee unique ids because the increment by 1 is handled vproc local
  • for display purpose : CSUM 

Spool, Perm, temp space management

NOTE: There is no concept of table space in Teradata.

There are three different types of space defined in Teradata:

 

Perm Space Spool Space Temp Space
Permanent space is the space which is utilized for all databases. It is used to hold all the data of the databases. This space isn’t pre-allocated.  NOTE: Teradata’s Spool and Temp Space is the perm space that is not currently being used. Spool space is used to hold data for Intermediate Query results. Once the query is complete, the space is released. It only holds data. It is active up to the current session only. Spool space is same as that of the root, unless specified. If there is no limit defined for a particular database or user, limits are inherited from parents. In theory, a user could use all unallocated space in the system for their query. The best way to estimate spool usage is to run explains on all of your queries. Just remember that this is an estimate of spool usage not the actual spool usage. Temporary space is the amount of space which can be used to create temporary tables – either volatile tables or global temporary tables. Data is active up to the current session only. Tables created in Temp Space will survive a restart. Temp space is the unused permanent space.
The amount of permanent space is divided by the number of AMPs. Whenever per AMP limit exceeds, an error message is generated. Spool space is divided by the number of AMPs. Whenever per AMP limit exceeds,
the user will get a spool space error.
Temp space is also divided by the number of AMPs

 

The perm space can be further divided into:

 

Maxperm PeakPerm Currentperm
Maxperm is the total permanent space specified for that user/database.In other words, MaxPerm is the total amount of bytes available for data storage in a database. PeakPerm is the maximum permspace ever utilised after it has been last reset.In other words, PeakPerm is the peak amount of bytes that have ever been stored in a database. Currentperm is the current space which is being utilized by database/user/table.In other words, CurrentPerm is the actual amount of used bytes in a database.

DBC Tables or System tables on Teradata


System tables uses to track the database objects. In essence, access is a set of relational database tables (the system tables) about a set of relational database tables (the user project). System tables should not be altered directly by any user. For example, do not attempt to modify system tables with DELETE, UPDATE, or INSERT statements, or user-defined triggers.

System tables are much like the tables you create in a database – with a few exceptions. First, they are identified internally as system objects so that you can differentiate your regular tables from system tables. Secondly, some system tables are inherently read-only. System table are documented here solely for the purpose of giving us a better understanding of how Teradata RDBMS works internally. The following table describes each of the system tables.

DBC.TVM
Describes each table, view, or macro on the Teradata RDBMS. Identify table.


DBC.TVFIELDS
Describes each column on the Teradata RDBMS.


DBC.User
Identify user.

DBC.ACCESSRIGHTS
Describes each database and userid on the Teradata RDBMS.




DBC.DBASE
Describes each database and userid on the Teradata RDBMS. Identify database.


DBC.INDEXES
Describes columns contained in indexes in the Teradata RDBMS.


DBC.ResUsage
Log resource usage data.

Example

SELECT ACCOUNTNAME, USERNAME, SUM(CPUTIME), SUM(DISKIO) FROM
DBC.AMPUSAGE
WHERE SUBSTR(ACCOUNTNAME, 1, 2) = ‘CB’
GROUP BY USERNAME, ACCOUNTNAME
ORDER BY USERNAME, ACCOUNTNAME;
 *** Query completed. 9 rows found. 4 columns returned.
 *** Total elapsed time was 2 seconds.
AccountName     UserName             Sum(CpuTime)      Sum(DiskIO)
————–  ————-  ——————  —————
CB990902        JANETJONES               1,498.64        3,444,236
CB990903        JANETJONES                 934.23        1,588,764
CB990904        JANETJONES                 883.74          924,262
CB990905        JANETJONES                 214.99          200,657
CB990902        JOHNSMITH                  440.05          396,338
CB990903        JOHNSMITH                  380.12          229,730
CB990904        JOHNSMITH                  112.17          184,922
CB990905        JOHNSMITH                   56.88           99,677
CB990902        SAMOREILLY                 340.34          410,178