Views are database objects that are built by the query. Views can be built using a single table or multiple tables by way of join. Their definition is stored permanently in data dictionary but they don’t store copy of the data. Data for the view is built dynamically. A view may contain a subset of rows of the table or a subset of columns of the table. You can use regular SELECT statement to retrieve data from Views. An existing view can be modified using REPLACE VIEW statement. An existing view can be dropped using DROP VIEW statement.

Create/Replace a View

CREATE/REPLACE VIEW <viewname>
AS
<select query>;

Drop a view

DROP VIEW <view name>;

Advantages of Views

Views provide additional level of security by restricting the rows or columns of a table.

Users can be given access only to views instead of base tables.

Simplifies the use of multiple tables by pre-joining them using Views.

EXPLAIN command returns the execution plan of parsing engine in English. It can be used with any SQL statement except on another EXPLAIN command. When a query is preceded with EXPLAIN command, the execution plan of the Parsing Engine is returned to the user instead of AMPs

Some Explain plans are:

Full Table Scan (FTS) – each and every row of the table is accessed (Example when there is no where condition)

Unique Primary Index – When the rows are accessed using Unique Primary Index, then it is one AMP operation

Unique Secondary Index – When the rows are accessed using Unique Secondary Index, it’s a two amp operation.

Compression is used to reduce the storage used by the tables. In Teradata, compression can compress up to 255 distinct values including NULL. Since the storage is reduced, Teradata can store more records in a block. This results in improved query response time since any I/O operation can process more rows per block. Compression can be added at table creation using CREATE TABLE or after table creation using ALTER TABLE command.

Limitations

Only 255 values can be compressed per column.

Primary Index column cannot be compressed.

Volatile tables cannot be compressed.

Multi-Value Compression (MVC)

The following table compresses the field DepatmentNo for values 1, 2 and 3. When compression is applied on a column, the values for this column is not stored with the row. Instead the values are stored in the Table header in each AMP and only presence bits are added to the row to indicate the value. Multi-Value compression can be used when you have a column in a large table with finite values.

Think of scenarios where table contains columns other than the index, using which the data is frequently accessed. Teradata will perform full table scan for those queries. Secondary indexes are needed to resolve this issue.

Secondary indexes are an alternate path to access the data. There are some differences between the primary index and the secondary index.

Secondary index is not involved in data distribution.

Secondary index values are stored in sub tables. These tables are built in all AMPs.

Secondary indexes are optional.

They can be created during table creation or after a table is created.

They occupy additional space since they build sub-table and they also require maintenance since the sub-tables need to be updated for each new row.

There are two types of secondary indexes:

Unique Secondary Index (USI)

Non-Unique Secondary Index (NUSI)

A Unique Secondary Index allows only unique values for the columns defined as USI. Accessing the row by USI is a two amp operation.

A Non-Unique Secondary Index allows duplicate values for the columns defined as NUSI. Accessing the row by NUSI is all-amp operation.

[SET/MULTISET]: SET tables discard completely duplicate records however MULTISET allows multiple instance of rows. So it depends on the situation. If you know that you will be getting distinct rows , it is advisable to go with MULTISET table as it will not check for duplicate rows. Hence, saving time and enhancing performance. However if you know that you would be getting duplicate records however you need to process only one of the duplicates rows, Go with SET tables. TERADATA default is SET however ANSI default is MULTISET.

FALLBACK: FALLBACK is TERADATA mechanism to save table data in case of AMP Failure. If you define any table with FALLBACK option , a duplicate copy of table data is maintained in some other AMP. SO in case of AMP Failure, the FALLBACK AMP is used. For critical tables, it is recommended to use FALLBACK option. However it also comes with more Storage Space utilisation disadvantage. In TERADATA, default is NO FALLBACK.

PERMANENT JOURNALS:There are four types of Permanent Journals you can use: BEFORE, AFTER, DUAL BEFORE, DUAL AFTER. The BEFORE JOURNAL holds the image of impacted rows before any changes are made. AFTER JOURNAL holds the image of affected rows after changes are done. In DUAL BEFORE/AFTER Journal, two images are taken and are stored in two different AMP’s. As it was also discussed earlier that PERMANENT JOURNALS use PERMANENT SPACE so if these JOURNALS are not required any more, it is advisable to drop JOURNALS to save some space.

 Volatile Table: The data inserted into a volatile table is retained only during the user session. The table and data is dropped at the end of the session. These tables are mainly used to hold the intermediate data during data transformation.

 Global Temporary Table: The definition of Global Temporary table are persistent but the data in the table is deleted at the end of user session.

 Derived Table: Derived table holds the intermediate results in a query. Their lifetime is within the query in which they are created, used and dropped.

Set Versus Multiset

Teradata also classifies the tables as SET or MULTISET tables based on how the duplicate records are handled. A table defined as SET table doesn’t store the duplicate records, whereas the MULTISET table can store duplicate records.

Account String Expansion (ASE) is a mechanism that enables AMP usage and I/O statistics to be collected. ASE supports performance monitoring for an account string.

TDPTMON

Teradata Director Program (TDP) User Transaction Monitor (TDPTMON) is a client routine that enables a system programmer to write code to track TDP elapsed time statistics.

System Management Facility

The System Management Facility (SMF) is available in the Multiple Virtual Storage (MVS) environment only. This facility collects data about Teradata Database performance, accounting, and usage.

Performance Monitor/Application Programming Interface

The PM/API provides hooks into the Performance Monitor and Production Control (PM and PC) functions resident within the Teradata Database. PM and PC data is available through a log-on partition called MONITOR using a specialized PM/API subset of the Call-Level Interface version 2 (CLIv2) routines.

Metadata has been around for as long as there have been programs and data. However, in the world of data warehouses, metadata takes on a new level of importance. Examples of metadata include data model, database design, column properties, data warehouse attributes, etc.

For instance, column metadata would include columns names in order, physical structure of the columns, data types, lengths, nullability of the columns and encoding used.

In terms of data warehouse, we can define metadata as follows.

Metadata is the road-map to a data warehouse.

Metadata in a data warehouse defines the warehouse objects.

Metadata acts as a directory. This directory helps the decision support system to locate the contents of a data warehouse.