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: