Teradata Basic Questions

1. What language is used to access a Teradata table?

• SQL

2. What are five Teradata database objects?

• Tables, views, macros, triggers, and stored procedures

3. What are four major components of the Teradata architecture?

• PE, AMPs, Vdisks, and Message Passing Layer

4. What are views? 

• Subset of rows and columns or one or more tables
• Virtual tables
• Window into one or more tables

5. What are macros?

• Predefined, stored set of SQL statements

Teradata -- Data Dictionary Directory (DD/D)

The DD/D ( Data Dictionary Directory) ...

• is an integrated set of system tables
• contains definitions of and information about all objects in the system
• is entirely maintained by the RDBMS
• is “data about the data” or “metadata”
• is distributed across all AMPs like all tables
• may be queried by administrators or support staff
• is accessed via Teradata supplied views

Examples of DD/D views:

DBC.Tables ---> information about all tables
DBC.Users ---> information about all users
DBC.AllRights ---> information about access rights
DBC.AllSpace ---> information about space utilization

Teradata Objects

Hi,

• There are eight types of objects which may be found in a Teradata database/user.

           ----> Tables – rows and columns of data 
           ----> Views – predefined subsets of existing tables
           ----> Macros – predefined, stored SQL statements
           ----> Triggers – SQL statements associated with a table
           ----> Stored Procedures – program stored within Teradata
           ----> Join and Hash Indexes – separate index structures stored as objects within a database
           ----> Permanent Journals – table used to store before and/or after images for recovery

• These objects are created, maintained and deleted using Structured Query Language (SQL).
•  Object definitions are stored in the Data Dictionary / Directory (DD/D).

Relational Database Concepts

What is a Database?

• Database – A collection of permanently stored data that is:

               ---> Logically related (data relates to other data)

               ---> Shared  (many users may access data)

               ---> Protected  (access to data is controlled)

               ---> Managed  (data has integrity and value)

 

Relational Databases:

• A Relational Database consists of a set of logically related tables.
• A table is a two dimensional representation of data consisting of rows and columns.

Primary Key:

Primary Key values uniquely identify each row in a table.

In a relational model,

• A Primary Key is required for every table.
• Only one Primary Key is allowed in a table.
• It may consist of one or more columns.
• Primary Keys cannot have duplicate values.
• Primary Keys cannot be NULL.
• Primary Keys are considered “non-changing” values.

Foreign Key:

Foreign Key (FK) values identify table relationships.

• FK’s are optional  -  not all tables have them
• More than one FK is allowed per table
• FK’s can be made up of more than one column
• Duplicate values are allowed
• Missing (NULL) values are allowed
• Changes are allowed
• Each FK value must exist somewhere as a PK value

Advantages of a Relational Database Approach:

The advantages of a Relational Database compared to other database methodologies are many.

Relational database methodology:

• Is easy to use
• Is easy to understand
• Models the business, not the processes
• Is data-driven versus application driven
• Makes applications easier to build
• Supports trend toward end-user computing
• Is the industry standard for most large enterprises
• Allows businesses to respond to changing conditions more flexibly than other types

Teradata’s Advantages

Hi,

Here are some main advantages of  Tearada.

• Unlimited, Proven Scalability – amount of data and number of users; allows for an enterprise wide model of the data.
• Unlimited Parallelism – parallel access, sorts, and aggregations.
• Mature Optimizer – handles complex queries, up to 64 joins per query, ad-hoc processing.
• Models the Business – 3NF, robust view processing, & provides star schema capabilities.
• Provides a “single version of the truth”.
• Low TCO (Total Cost of Ownership) – ease of setup, maintenance, & administration; no re-orgs, lowest disk to data ratio, and robust expansion utility (reconfig).
• High Availability – no single point of failure.
• Parallel Load and Unload utilities – robust, parallel, and scalable load and unload utilities such as FastLoad, MultiLoad, TPump, and FastExport.

Teradata Support Environment Commands

Hi,

Below mentioned are the few Support Environment commands in Teradata:

• DATEFORM  Specify INTEGERDATE or ANSIDATE.
• LOGTABLE   Acquires or creates the Restart Log Table.
• LOGON   Connects multiple sessions to Teradata.
• LOGOFF   Terminates the utility operation.
• ACCEPT   Input parameters to Support Environment.
• RUN   Specifies an external script file.
• IF … THEN  Identifies statements to be executed if certain conditions 
• ELSE  are true (.IF) or (.ELSE) otherwise. 
• ENDIF  Resumes the program after a .IF condition.
• ROUTE  Specifies output file other than SYSPRINT.
• DISPLAY  Writes messages to a specific destination.
• SET  Assigns a data type and value to a variable.
• SYSTEM  Submits an operating system command to the client environment.

Teradata Database & User

Hi,

Here we will learn what is teradata Database & Teradata user.

Teradata Database:

• A Teradata database is a defined logical repository for:

                   ---> Tables

                   ---> Views

                   ---> Macros

                   ---> Triggers

                   ---> Stored Procedures

• Attributes that may be specified for a database:

                  ---> Perm Space - maximum amount of space available for tables

                  ---> Spool Space - maximum amount of work space available for requests

                  ---> Temp Space - maximum amount of temporary table space

• A Teradata database is created with the CREATE DATABASE command.

Example:

 

CREATE DATABASE Database_2 FROM Sysdba   

 AS PERMANENT = 20e9, SPOOL = 200e6;

“Database_2” is owned by “Sysdba”. A database is empty until objects are created within it.

Teradata User: 

• A Teradata user is a database with an assigned password.
• A Teradata user may logon to Teradata and access objects within:

                      ---> itself
                      ---> other databases for which it has access rights

• Examples of attributes that may be specified for a user:

• A user is an active repository while a database is a passive repository.
• A user is created with the CREATE USER command.

Example:

CREATE USER User_C FROM User_A
AS  PERMANENT = 10e6
  ,SPOOL = 100e6
  ,TEMPORARY =  80e6
  ,PASSWORD = lucky_day ;

“User_C” is owned by “User_A”. A user is empty until objects are created within it.

                      ---> Perm Space - maximum amount of space available for tables
                      ---> Spool Space - maximum amount of work space available for requests
                      ---> Temp Space - maximum amount of temporary table space

How Large is a Trillion?

• 1 Kilobyte  = 103   = 1000 bytes
• 1 Megabyte = 106   = 1,000,000 bytes
• 1 Gigabyte = 109   = 1,000,000,000 bytes
• 1 Terabyte = 1012  = 1,000,000,000,000 bytes
• 1 Petabyte = 1015  = 1,000,000,000,000,000 bytes
• 1 million seconds  = 11.57 days
• 1 billion seconds = 31.6 years
• 1 trillion seconds = 31,688 years
• 1 million inches     = 15.7 miles
• 1 trillion  inches     = 15,700,000 miles  (30 roundtrips to the moon)
• 1 million square inches = .16 acres = .0002 square miles
• 1 trillion square inches = 249 square miles   (larger than Singapore)
• $1 million =   < $ .01 for every person in U.S.
• $1 billion =   $  3.64 for every person is U.S.
• $1 trillion =   $ 3,636 for every person in U.S.

Teradata Utility -- Tpump

Following are the features of Tpump utility in Teradata:

• Allows near real-time updates from transactional systems into the warehouse.
• Best fit for low volume data maintenance.
• Unlike Multiload, Tpump uses row hash lock allows concurrent updates on the same table.
• INSERT,UPDATE, DELETE supported.
• No restrictions applied for tables with SI, RI, triggers etc.
• No limit on number of concurrent sessions.
• Speed can be tuned dynamically.

Example:

.LOGTABLE Logtable001_ml;
.LOGON  tdp3/user2,tyler;
.BEGIN LOAD SESSIONS 4 ERRORTABLE ET_Employee;
.LAYOUT Employee_Trans;
 .FILLER  in_Transcode   1  CHAR(3);
 .FIELD    in_EmpNo         *  SMALLINT;
 .FIELD    in_DeptNo         *  SMALLINT;
 .FIELD    in_Salary           *  DECIMAL (8,2);
.DML LABEL  Payroll   DO INSERT FOR MISSING UPDATE ROWS ;
 UPDATE  Employee  SET  Salary = :in_Salary
  WHERE  EmpNo = :in_EmpNo;
 INSERT  INTO  Employee  (EmpNo,  Salary)
  VALUES  (:in_EmpNo,  :in_Salary);
.DML LABEL Terminate ;
 DELETE  FROM  Employee  WHERE  EmpNo = :in_EmpNo;
 INSERT INTO Employee_History  (EmpNo, DeptNo)
  VALUES  (:in_EmpNo,  :in_DeptNo);
.IMPORT  INFILE  infile1
 LAYOUT  Employee_Trans
 APPLY  Payroll  WHERE  in_Transcode = 'PAY'
 APPLY  Terminate  WHERE  in_Transcode = 'DEL';
.END LOAD;
.LOGOFF;

Teradata V13.0 New Features -- Performance Features

Below mentioned are some of the new features of Teradata V13.0

Performance Features:

Teradata developers and DBA’s are famed for their pursuit of increased performance. There is no better satisfaction than seeing a complex query targeting multi million row tables return results in mere seconds.
Teradata V 13.0 doesn’t bring about any drastic new features that improves performance but has tried to improve and enhance existing features

1. Collect Statistics:

Statistics Collection has been enhanced by

• Improved sampled statistics which improves sampled statistics for the number of unique values for partitioning columns and for several types of tables.
• Restrictions on collecting statistics for join and hash indexes, global temporary and volatile tables, unhashed tables, and partitioning columns has been removed. This allows more flexible use of statistics on a variety of tables and also enhances the cardinality estimation infrastructure of the Optimizer.
• The following restrictions on collecting statistics are now removed:

                 • Multicolumn statistics on join and hash indexes

                 • System-derived PARTITION statistics on partitioned join indexes

                 • Single-column, multicolumn, and PARTITION statistics on volatile tables

                 • Statistics on unhashed tables

                 • Sampled statistics on the partitioning columns of a partitioned primary index

• Using an aggregate cache rather than a traditional sort, which affects sampled and full table statistics. This improves the performance of aggregation in statistics collection when there are duplicates and the total groups fit into the cache memory.
• Skipping local aggregation when the column has unique/nearly unique data avoids local sort operations
• Skipping global aggregation when collecting statistics on multiple columns (with a uniqueness defined on a subset of them) avoids rows having to be redistributed and sorted again globally.

2. Count (*) Optimization:

The count function now reads the cylinder index rather than performing a full table scan to determine the number of records in a table when:

• There are no WHERE or GROUP BY clauses in the query.
• Count is on a NOT NULL column. In such cases, the row count itself is the column count.

Reading the cylinder index when these conditions apply improves the performance of count(*).

3. DPE for Inclusion/Exclusion Product Joins:

Teradata Database can now perform inclusion or exclusion product joins with dynamic partition elimination (DPE) when there are equality join terms between partitioning columns at one or more partitioning levels of a PPI table and another relation.

Enhancements also include support for:

• Product join DPE and rowkey-based merge join with cross terms
• Semi-product join DPE with a subquery involving multiple tables
• Semi-product join DPE with a subquery when connecting terms involve aggregate functions

4. Enhanced Performance of Unspooled PPI Merge Joins:

This feature enhances the performance of queries between a PPI table and a subquery when a merge join without spooling is not possible because all primary index columns are not involved in the connecting condition (IN, NOT IN, = ANY, <> ALL). Performance is particularly improved for large PPI tables.

Points to note:

a) The EXPLAIN text for some queries might change.
b) The following constraints apply:

• Exclusion joins that involve correlated join terms are not eligible for DPE.
• To be eligible for DPE, single-column partition statistics must exist on the PPI table that is being joined directly.
• Only inner joins are supported.
• For exclusion product join DPE:
• Each column in the connecting condition from the PPI table must be a partitioning column (occur in the partitioning expression of the table).
• Each partitioning level in the PPI table must be a RANGE_N expression with a simple column reference specified as the test value.

5. Group By and Distinct Performance Equivalence:

The query optimization process includes rewrite and planning enhancements that can help queries perform better if they specify:

• The DISTINCT option
• The GROUP BY clause
• Both

6. Handling Redundant DISTINCT Detection and Removal:

The feature identifies, tracks, and propagates column uniqueness information. Considering the uniqueness attribute lets the Optimizer avoid, for example:

• Eliminating duplicates when a DISTINCT clause is specified in an SQL SELECT
• Applying an aggregation step
• Applying the last aggregation step in Partial Group By cases
• Removing duplicates in a subquery before it is joined with an outer query

Points to note:

• This feature does not introduce any new syntax or impact existing applications.
• The performance improvements resulting from this feature are automatically triggered in applicable cases. No user intervention is required.

7. Implement Smart Local Aggregation Decisions:


a). Fetches rows into an aggregate cache to aggregate rows that have the same keys.

b). When the first cache overflows before the local sort is done, determines the number of distinct keys if the local sort is completed.

c). Skips the local sort if the ratio of total rows/number of distinct keys is less than the OCES  
     LocalSortTheshold.
This feature improves the performance of many aggregate functions, such as SUM, COUNT, MIN, and MAX.

8. Improve Performance of Index Wizard PPI Analysis:

• Partitioned Primary Indexes (PPIs)
• The Optimizer table descriptors Index Wizard generates when it processes the INITIATE PARTITION ANALYSIS statement

Caching PPIs and Optimizer table descriptors means that Index Wizard does not have to regenerate them each time it references them. This saves time and resources when Index Wizard evaluates the costs associated with indexes.

9. Increased Join/Subquery Limit:


This is particularly critical for applications that either use query generators or that join over views involving many tables.

Points to note:

• Having a large number of tables with many connections between them can lead to query optimization times of perhaps several hours.
• Can use the MaxJoinTables cost profile option to limit the number of tables to less than 128.

10. Increased Maximum Number of AWTs per AMP:


Points to note:
Because there are many configuration options and customer systems are different, customers should work with Teradata support personnel for performance tuning of large memory systems.

11. Increase Maximum Value of DBS Control Cylinders Saved for PERM:

Requests for perm cylinders compete with requests for spool and temp cylinders. If a spool or temp cylinder are unavailable, the request fails with little rollback required.
However, when PERM cylinders are unavailable, a lengthy rollback is required. Increasing the Cylinders Saved for PERM limit helps avoid the lengthy rollback required when PERM cylinders are unavailable.

12. Inner and Outer Join Elimination Enhancements:

• Removes the restriction that the column set acting as the primary key in the referenced table in a soft RI constraint must be a USI or declared to be UNIQUE or a PRIMARY KEY.
• Enables several new inner and outer join elimination performance enhancements.

13. JI/AJI Enhancements:

• Select the best aggregate join index available for rewriting a query on a cost basis when multiple aggregate join indexes have been defined
• Perform more cost-based query rewrites using aggregate join indexes, spooled derived tables, outer joins, and extended grouping sets
• Produce better join plans by using join indexes with Partial Group By optimizations
• Benefits: JI/AJI Enhancements improves the quality and performance of query plans.

14. Large Object (LOB) Loader:

This result sin more Flexible CLIv2 Interface. Previously, Teradata Tools and Utilities products had to know where and how to obtain LOB data to transmit it to Teradata Database, adding complexity to the process.

This feature enables Teradata Tools and Utilities products to better support deferred mode LOB handling by
providing a standardized interface for specifying the data by file name.
The API for the data transfer operations is CLIv2, and it uses a new parcel, ElicitDataByName, to elicit the client LOB data. This parcel contains the file name, path name, or whatever name is needed to locate the LOB data on the client.

15. Non-Key Access Paths Enhancements:

 The following code enhancements improve Optimizer access path planning:

• Constraint scans for single SELECT statements and joins on base tables and join indexes.
• Integration of LikeScan into constraint scan.
• Integration of RangeScan for non-value-ordered (non-VO) column secondary indexes (SIs) into constraint scan.
• BitMap scan on indexes used for constraint scan.
• IN-list access path for aggregation statements and joins when the IN-list is on the nonunique secondary index (NUSI) of a base table and join index.
• Benefits: Better use of access paths to base tables and join indexes improves query performance.

16. RESET WHEN Ordered Analytic Clause:


17. Top N Enhancements:

Benefits: Extended Functionality- The value of n can now be passed into the operator by a macro, stored procedure, or USING request modifier parameter.
Processing Optimizations - This feature:

• Adds an AMP runtime optimization for TOP n PERCENT operations.
• Extends the “any n” optimization to INSERT … SELECT and CREATE TABLE … AS requests, views, and derived tables for the case where n < 10,000. Previously, only SELECT requests were optimized for “any n” processing.
• Avoids redistributing the rows for the hash partitioning case when the grouping columns of a window function contain the primary index columns of the source relation.
• Adds a RankLimit optimization for a TOP n operation that does not specify the WITH TIES option.
• Adds runtime optimizations for TOP n in a request that specifies an ORDER BY specification.

Points to note:

• The EXPLAIN text for some queries might change.
• The performance of several types of queries will be improved.
• The array feature is not supported.

This feature extends the functionality of the TOP n operator and incorporates several new processing optimizations for both TOP n and “any n” requests.Ordered analytic window functions support a new optional RESET WHEN clause for specifying conditional partitioning. The RESET WHEN clause can appear in the OVER() phrase of ROW_NUMBER, RANK, PERCENT_RANK, and window aggregate functions, such as AVG and SUM. During evaluation, if the condition specified by the RESET WHEN clause is true, a new dynamic partition is created over which the ordered analytic function executes. Sequence Analysis-Analysis situations that look for patterns can benefit from being able to create a partition dynamically while computing a window function.This feature lets Teradata Database interpret deferred mode LOB data in a USING request modifier row as a client file name. This is achieved by adding the BY NAME phrase to the USING request modifier option <LOB> AS DEFFERED. The system can then send the file name back to the client application so it can open the named file. The client then sends the LOB data in the opened file back to Teradata Database as USING row data.This feature provides the Optimizer with the ability to:The maximum allowable value for the Cylinders Saved for PERM setting in DBS Control has been increased from 65,535 to 524,287.This feature increases the total number of available AMP Worker Tasks (AWTs), automatically configures the minimum and maximum AWT limits for work types, and optionally allows utilities to run under exclusive sets of work types. This allows Teradata to take better advantage of large-memory computer systems.This feature increases the limit of tables, views, or both per query block from 64 to 128. This enables more Complex Queries With Larger Numbers of Joins by enabling the Optimizer to create join plans for larger numbers of tables. This feature provides an internal DBS caching mechanism for:This feature introduces smart on-time decision making to the SUM step. The SUM step performs these on-time decision-making tasks:

Teradata Multiple Choice Questions

Hi All,

Please find below some of  the Teradata Questions & Answers:

1. What are the following object required for Teradata Database-Prem space?.

 a) Tables & Stored Procedure b) Tables & Views
 c) Macros & Triggers d) Views & Stored Procedures

Answer: a.

 

2. Global Temporary Tables are stored in the TD are

 a) Perm Space b) Spool Space c) Temp Space

Answer : c

 

3. Terabytes represent

 

a) 10 power 15 b) 10 power 12 c) 10 power 18  d) 10 power 24

 

Answer: b

 

4. SMP Stands for

    a) Symmetric Multi-processor b) Symmetric Massive-Processor
    c) System Multi-processor d) Symmetric Multi-Processor

 

Answer: a

 

5. Total Number of nodes suppport by BYNET are

       a)510 SMP Nodes a) 512 SMP Nodes b) 513 SMP Nodes c) 514 SMP Nodes

 

Answer: b

 

6. The definition of TPump is continuous update of tables; performs insert, update, and delete operations or a combination of those operations on multiple tables using the same source feed.

   a) true b) false
  
Answer: a

 

7. Teradata Database facilities for software falut tolerance on

 a) Vproc Migration & Fallback tables b) AMP Clusters & Journaling
 c) Backup/Archive/Restore d) Table Rebuild Utility e) All of the above
        f) None of the above

Answer: e

 

8. Success/Failure/Error responses from Teradata are received by

 a) Request Parcel b) Data parcel c) Response Parcel

Answer: c)

 

9. Explain command displays the execution plan of the query chosen by the Teradata Optimizer

     a) False b) True

Answer: b

 

10. HASHROW funtions is 

 a. Returns the hash bucket number that corresponds to a specified row hash value.
 b. Returns the hexadecimal row hash value for an expression
 c. Returns the AMP number of the primary AMP corresponding to the specified hash bucket number.
 d. Returns the identification number of the fallback AMP corresponding to the specified hash bucket.

Answer: b

Regards,

Venkat...

Teradata V12.0 New Features

Teradata being a Datawarehousing database, performance in terms of data distribution and data retrival is one of the basic expectations. Teradata has tried to revisit and enhance its existing performance features rather than introducing something new, in its version Teradata 12.0.

2.1.  Optimizer Cost Estimation

Teradata Optimizer uses various internal functions to compute the cost for execution of given query. The cost is returned by the optimizer on the below mentioned factors:-

• Calculated number of blocks in a relation
• Cost of various disk operations
• Number of AMPs used for an operation
• Number of AMPs configured per node
• Cost of sorting a spool file
• Cost of duplicating rows across AMPs
• Hashing costs

The above mentioned parameters are there after used to optimize or tune the SQL.

To facilitate cost optimization, Teradata Database 12.0 provides a framework for cost

estimation within which cost function parameter values are administered, updated, and

deployed in a uniform and convenient manner.

• Users can change the cost profile to be used with sessions logged in under a given user profile.
• DBC.Profiles.CostProfileName, if non-null, specifies the cost profile to be used for sessions started by any user assigned to this user profile.
• In Teradata 12.0 we can have COST PROFILE as NULL or cost_profile_name in CREATE or MODIFY PROFILE statement. This leads to support of the following statement in Teradata 12.0  DIAGNOSTIC SET PROFILE prof-name ON FOR SESSION; 
• Teradata Database 12.0 supports the following diagnostic request statements:

a) DIAGNOSTIC COSTPRINT:

Extracts Optimizer costing information, including cost profile information, from SystemFE.Opt_Cost_Table and dumps it to the DBS I/O window.

b) DIAGNOSTIC DUMP COSTS:

Extracts TPA-calculated Optimizer cost information and DBS Control Record flag

information from a target system and writes it to the following tables on that system:

SystemFE.Opt_Cost_Table or SystemFE.Opt_DBSCtl_Table

c)   DIAGNOSTIC HELP COSTS:

Reports information about the active cost profiles and TLE cost segments.

d) DIAGNOSTIC SET COSTS:

Instructs the Optimizer on the test system to use the Optimizer cost parameters and

relevant DBS Control Record information from target_system_name for its emulations for a period defined by the scope level.

e) DIAGNOSTIC HELP PROFILE:

       Reports costs parameter information for the specified scope level.

       f)     DIAGNOSTIC SET PROFILE:

      Instructs the Optimizer on the test system to activate or deactivate a cost profile at   the specified  

      level. The system propagates activated cost profiles to successor levels.

      g) DIAGNOSTIC DUMP SAMPLES:

      Extracts random AMP samples from a target system and writes them to 

      SystemFE.Opt_RAS_Table  on that system.

       h) DIAGNOSTIC HELP SAMPLES:

       Returns, for each scope level, the random AMP statistical samples that have been set.

       i) DIAGNOSTIC SET SAMPLES:

       Instructs the Optimizer on the test system to use the random AMP samples from    

       target_system_name for its emulations

2.1.  Statistics Enhancements:

Collecting statistics has been enhanced to capture more data demographic information so that the             Optimizer can generate more accurate plans than it previously could.

2.2.1 Increased Statistics Intervals:

The maximum number of intervals for statistics on an index or column is increased from 100 to       200.

The increase in the number of statistics intervals:

• Improves single table cardinality estimates that are crucial for join planning. Having more intervals gives a more granular view of the demographics.
• Increases the accuracy of skew adjustment because of the higher number of modal frequencies that can be stored in a histogram.
• Does not change the procedure for collecting or dropping statistics, although it affects the   statistics collected.

The time and spool space needed to collect statistics can be greater with the larger number of intervals, so an option is available to continue to use 100 intervals. You can decrease the maximum number of intervals using the OCES cost profile. The name of the relevant flag is MaxStatsInterval. The default is set to 200.

2.2.2 Extrapolating Statistics Outside Range:

• The Optimizer extrapolates statistics on rolling columns. A rolling column has a constantnumber of rows per value and a varying number of unique values. Examples of rolling columns are those having a DATE or TIMESTAMP data type. 

• For these columns, the demographics of existing data never changes, and only new data can add new distinct values to the column.

• You can control date extrapolation of statistics by setting the ExtrapolateStatistics flag in the OCES cost profile. The default setting for this flag is TRUE.

2.2.3 Collecting Statistics for NULLS

Teradata Database 12.0 expands null-related demographic information as follows:

• An all-null fields statistic, new in this release, is a count of all rows in the collected row set that have nulls in all of the columns on which statistics are collected. The name of this statistic is NumAllNulls. 
• A null fields statistic, retained from earlier releases, is a count of all rows in the collected row set that have one or more nulls in any of the columns on which statistics are collected. The name of this statistic is NumNulls.

The NumAllNulls statistic adds one column to the output of a HELP STATISTICS request.

2.2.4 Collecting AMP Level Statistics Values

• The Optimizer uses a new and accurate Average AMP-local Rows per Value (AvgAmpRPV)  statistic. This statistic adds one column to the output of a HELP STATISTICS request.

• AvgAmpRPV is an average of the average of each AMP in the system over the number of rows               per value for a NUSI column set. AvgAmpRPV is useful for detecting and reacting to skewed             distribution.

2.3.  Enhanced Query Rewrite Capability

Teradata Database 12.0 collects all the previously dispersed query rewrite modules, moving components that had previously been within the Resolver and the Optimizer to a new Query Rewrite subsystem, to the stage in query processing that occurs just after the Resolver phase and just before the Optimizer phase. 

The Query Rewrite subsystem takes the ResTree for the query (call it Q), the version that the Resolver produces, and produces a semantically identical ResTree' (where ' indicates the word prime), which it then passes to the Optimizer. The rewritten query (call it Q') has two

properties:

• It is semantically identical to the original query Q.

• It runs faster than Q.

2.4 Replace Poorly Performing Generic Plan with Specific Plan Automatically

As part of the Parameterized Statement Caching Improvements feature, the Parser uses a cost profile parameter, CompareEstimates, to determine if it should reparse a request that would have generated a poor generic performance plan in order to generate a specific plan. 

The CompareEstimates default value is 0, which means the Parser will not reparse the plan.  The value can be changed in the cost profile.

The value of reparsing and generating a specific plan is this. Reparsing avoids an obviouslyunder-performing generic plan execution even once. This provides a performance benefit for parameterized queries.

2.5  Hash Bucket Expansion

Teradata Database 12.0 supports either 65,536 or 1,048,576 hash buckets for a system. The larger number of buckets primarily benefits systems with thousands of AMPs, but there is no disadvantage to using the larger number of buckets on smaller systems.

On systems with:

• 65,536 hash buckets, the system uses 16 bits of the 32-bit RowHash to index into the hash map.
• 1,048,576 hash buckets, the system uses 20 bits of the 32-bit RowHash as the index.

2.6 Multilevel Partitioned Primary Index

Teradata Database 12.0 supports a Multilevel Partitioned Primary Index (MLPPI) wherever a Partitioned Primary Index (PPI) is supported. This includes:

• Base tables
• Global temporary tables
• Volatile tables
• Noncompressed join indexes

Multilevel partitioning allows each partition to be subpartitioned. Each level must define at least two partitions. The number of levels of partitioning cannot exceed 15. The limit is 65,535 partitions for a combined partitioning expression. The number of levels of partitioning may be further restricted by other limits such as the maximum size of the table header or data dictionary column sizes.

2.7  Remove Restriction for Table Function

Teradata Database 12.0 lifts the requirement that a relation used as input for a table function must also be referenced in the FROM clause of the SELECT statement that invokes the table function.

This specification is still supported, but is no longer required.

Instead, if the table function arguments are fields from a derived table, the derived table can be declared within the WITH clause of the SELECT statement. Otherwise, table function arguments that are columns from a base table or view are allowed without having to reference the base table or view in the FROM clause.

2.8  MRG Performance Improvement

Teradata Database 12.0 reduces export response time and MRG (short for BYNET Merge subsystem) network overhead.

2.9  Scope of Covering Index is broadened

Optimizer enhancements make better use of the covering index by introducing a constraint scan of an index subtable as an access path for aggregate queries. This avoids having to perform a full table scan.

2.10 Compression on Soft and Batch Referential Integrity Column

Teradata Database 12.0 supports data compression on:

• Foreign key (FK)
• Unique Secondary Index (USI) primary key (PK).

TeraData Overview

What is Teradata?

Teradata is a Relational Database Management System (RDBMS):

• Designed to run the world’s largest commercial databases
• Preferred solution for enterprise data warehousing
• Open, UNIX-MP-RAS or NT-based system platforms
• Compliant with ANSI industry standards
• Runs on single (SMP) or multiple (MPP) nodes
• Database server to client applications throughout the enterprise
• Uses parallelism to manage terabytes of data

Why Teradata?

1) Teradata is the world leader in large scalable data warehousing.

2) Teradata:

• Supports more warehouse data than all competitors combined.
• Supports easy scalability from a small (10 GB) to a massive (100+TB) database.
• Provides a query optimizer with approximately 20 years of experience in large-table query planning.
• Does not require complex indexing schemes, complex data partitioning or time-consuming reorganizations.
• Supports ad hoc querying against the detail data in the warehouse, not just summary data in a data mart.
• Designed and built with parallelism from day one (not a parallel retrofit).
• There are more than 250 1TB or larger warehouses in the field.

Teradata Competitive Advantages:

• Unlimited, Proven Scalability
• Most Concurrent Users
• Unlimited Parallelism - Parallel sorts/aggregations, temporary tables - “Shared-Nothing” architecture
• Mature Optimizer - Complex queries, joins per query, ad-hoc processing
• Model the Business - 3NF, robust view processing, star schema
• Lowest TCO - ease of setup & maintenance, robust parallel utilities, no re-orgs, lowest disk to data ratio, robust expansion utility
• High Availability - no single point of failure, scalable data loading, parallel load utilities

 Teradata—A Brief History:

• 1979 --- Teradata Corp founded in Los Angeles, California.

          Development begins on a massively parallel database computer.

• 1982 --- YNET technology is patented.
• 1984 --- Teradata sells first DBC/1012 to Wells Fargo Bank  of California. Total revenue for year - $3 million.
• 1986 --- Product of the Year!
• 1989 --- Teradata and NCR partner on next generation of DBC.
• 1990 --- First Terabyte system installed and in production.
• 1991 --- NCR is acquired by AT&T. Teradata revenue  $280M.
• 1992 --- Teradata is merged into NCR.
• 1995 --- Teradata Version 2 for UNIX operating systems released.
• 1996 --- NCR only vendor to run multi-user TPC-D Benchmarks.
• 1996 --- AT&T spins off NCR Corporation with Teradata product.
• 1997 --- Teradata database becomes industry leader in scalable data warehousing.
• 1997 --- 100 Gigabyte TPC-D Benchmark leader.
• 1998 --- 24 Terabyte system in production.
• 1999 --- 1 TB TPC-D winner
• 2000 --- 50+ TB system in production! Teradata Division created.