With the release of Teradata 14 comes the whole new set of feature and enhancement which can change the way the Teradata warehouse environment was thought, The bundle pack new feature provides ease to developer with new function along ease for DBA in maintaining the Stats for performance.
Below are the list of new feature needs to be looked on while evaluating Teradata 14+ version's
| Teradata 13 | Teradata 14 | |
|
1.Collect Statistics Optimization a)Improved Sampled Statistics i)improves sampled statisticsfor the number of unique values for partitioning columnsb) Restrictions Removed on Collecting statistics i)Multicolumn statistics on join and hash indexes ii)System-derived PARTITION statistics on partitioned join indexes iii)Single-column, multicolumn and PARTITION statistics on volatile tables iv)Statistics on unhashed tables v)Sampled statistics on the partitioning columns of PPI |
1. Collect Statistics Enhancements a) Adding the SUMMARY option to collect table-level statistics b) Adding a number of internal enhancements to the Optimizer with respect to histogram structure and use, including: c) Maintaining statistics history. d) Enhancing extrapolation methods for stale statistics. e) Enhancing sampling options. f) Storing statistics data in their native Teradata data types without losing precision. |
|
|
2. Count(*) Optimization a)The count function now reads the cylinder index rather than performing a full table scan |
2. Automated Statistics Management a)Teradata Database can manage statistics collection for you, simplifying database administration by ensuring that the needed statistics are collected at the right time. |
|
|
3. Dynamic partition elimination(DPE) a)Enhanced Performance of Unspooled PPI Merge Joins |
3. Active Fallback, Phase II Phase I allowed the data block to be read from the fallback copy. Phase II of this feature repairs the primary copy of a data block from fallback when an unrecoverableb it error occurs. |
|
|
4. Group By and Distinct Performance Equivalence a) While using DISTINCT, the query optimization process rewrite it to GROUP BY at the background. So both are same now |
4. Expansion by Business Days a)Adds support for the following new business anchors: WEEK_BEGIN, WEEK_END, QUARTER_BEGIN, QUARTER_END, YEAR_BEGIN and YEAR_END. |
|
|
5. Handling Redundant DISTINCT Detection and Removal a)Internal enhancement for Distinct |
5. Encryption Enhancements a)The Blowfish encryption algorithm. |
|
|
6. Implement Smart Local Aggregation Decisions a)This feature improves the performance of many aggregate functions, such as SUM, COUNT, MIN, and MAX. |
6. Hash Join Enhancements a)Enhanced performance of outer joins, inclusion and exclusion semi joins, joins with partition elimination, and joins with cross terms. |
|
|
7.Increased Join/Subquery Limit a)Enables More Complex Queries With Larger Numbers of Joins i.e. from 64 to 128. |
7. Increased Maximum Number of Vprocs a)The maximum number of virtual processors (vprocs) supported per Teradata Database system has been increased from 16,384 to 30,720. |
|
|
8.Inner and Outer Join Elimination Enhancements a)Enables several new inner and outer join elimination performance enhancements |
8. Increased Partition Limits a)This feature increases limits related to partitioned primary indexes (PPIs) and their partitioning expressions. The new limits for partitioning expressions also apply to column-partitioned NoPI tables and column-partitioned NoPI join indexes. |
|
|
9. JI/AJI Enhancements a)Produce better join plans by using join indexes with Partial Group By optimizations |
9. Indexes on UDT Columns a)You can now create primary and secondary indexes on most types of user-defined type (UDT) columns. |
|
|
10.Non-Key Access Paths Enhancements a)Better use of access paths to base tables and join indexes improves query performance |
10. FastLoad Support for Temporal Tables a)FastLoad can now inserts into temporal tables. |
|
|
11. Top N Enhancements a)This feature extends the functionality of the TOP n operator and incorporates several new processing optimizations for both TOP n and “any n” requests. |
11. Multiple WITH/WITH RECURSIVE Clauses a)The number of WITH or WITH RECURSIVE request modifiers that can be specified with a DML request increases from one to any number. |
|
|
12. Implicit DateTime a)Default DateTime values for the CREATE/ALTER TABLE statement |
12. NUMBER Data Type a)Increased compatibility with other databases, which include a similar NUMBER data type. |
|
|
13. New SHOW Access Right a)The SHOW access right allows a grantee to access a table definition without accessing any of its data. |
13. Row-Level Security a)Teradata row-level security (RLC) allows you to restrict data access by row. Hierarchical category: Security Classification i) Labels: Top Secret = 4, Secret = 3, Classified = 2, Unclassified = 1 Non-Hierarchical category: Country ii)Labels: USA = 4, UK = 3, Germany = 2, France = 1 |
|
|
14.Simplified Query Capture Database DBQL XML Query Plan Logging a) Capturing Query Capture Database (QCD)-like information is now less complex, enabling better performance of logging query and workload information. |
14. Secure Password Storage and Retrieva a) Passwords and other logon string data in coded file locations that the Teradata Wallet application manages tdpid/username,$tdwallet (password_alias) or tdpid/username,$tdwallet |
|
|
15.No Primary Index Tables a)The syntax for the CREATE TABLE statement has been changed to permit user data tables to be created without a primary index. Such tables are referred to as NoPI (No Primary Index) tables. i)Enhanced performance for FastLoad bulk data loads into staging tables. ii)Enhanced performance for TPump Array INSERT minibatch loads into staging tables |
15. SQL ARRAY/VARRAY Data Type a) ARRAY, a user-defined multidimensional data type with up to 5 dimensions and a user-defined maximum number of elements, all of the same specific data type. b)VARRAY, an Oracle- compatible form of the ARRAY data type. |
|
|
16. Period Data Types a) Period data type value, indicates when a particular event starts and ends. |
16. Temperature-Based Block-Level Compression a)Teradata Database now provides temperature-based block-level compression (TBBLC), which automatically compresses cold (infrequently accessed) data and automatically decompresses data when it becomes warm (more frequently accessed). |
|
|
17. Transfer Statistics a)CREATE TABLE AS statement that uses the WITH DATA AND STATISTICS option. |
17. Teradata Columnar a) Permits efficient access to selected data, which reduces query I/O. Adds flexibility in defining a partitioned table or join index.Such flexibility provides opportunities to improve workload performance. Enables the optimizer to exclude unneeded column partitions, significantly enhancing query performance. |
|
|
18. New Teradata Reserved Words CONNECTAR CTCONTROL CURRENT_ROLEAR CURRENT_USERAR EXPAND EXPANDING GETAR RESIGNALAR SIGNALAR UNTIL_CHANGED VARIANT_TYPE XMLPLAN |
18.New Teradata Reserved Words a. NUMBER b.TD_ANYTYPE |
|
|
19. New Teradata Nonreserved Words APPLNAME CLASS_ORIGINAN COMMAND_FUNCTIONAN COMMAND_FUNCTION_CODEAN CONDITIONAR CONDITION_IDENTIFIERAN CONDITION_NUMBERAN CREATOR DIAGNOSTICSAN DOWN EXCEPTION GLOP LDIFF+ MEETS+ MEMBERAR MESSAGE_LENGTHAN MESSAGE_TEXTAN MOREAN NODDLTEXT NUMBERAN OLD_NEW_TABLE OWNER P_INTERSECT+ P_NORMALIZE+ PERIOD PRECEDES+ PRIORAN RDIFF+ RESET RETURNED_SQLSTATEAN ROW_COUNTAN RULES RULESET SUBCLASS_ORIGINAN SUCCEEDS+ THROUGH TRANSACTION_ ACTIVEAN XML |
19. New Teradata Nonreserved Words a. ANCHOR_HOUR b. ANCHOR_MILLISECOND c. ANCHOR_MINUTE d. ANCHOR_SECOND e. ARRAY f. AUTO g. AUTOTEMP h. BLOCKCOMPRESSION i. CALENDAR j. EXPORTWIDTH k. IPARTITION l. MANUAL m. MAXINTERVALS n. MAXVALUELENGTH o. NEVER p. ORDINALITY q. PARTITION#L16 r. QUARTER_BEGIN s. QUARTER_END t. RANGE#L16 u. ROWIDGEN v. ROWIDGEN2 w. STATSUSAGE x. VARRAY y. WEEK_BEGIN z. WEEK_END aa. YEAR_BEGIN bb. YEAR_END |
|
|
20. Obsolete Teradata Tools and |
20. Equality Joins on Skewed Tables Teradata Database uses a new hybrid join method for joins with equality join conditions where one or both sources are skewed.The hybrid join method is called Partial Redistribution and Partial Duplication (PRPD).Teradata Database splits the sources of the join into separate spools, performs several regular joins between those separate spools and combines the join results.The number of split joins is either 2 (if only one source is skewed) or 3 (if both sources are skewed on different values).PRPD can be used even if the join columns on both tables have more than one skewed value. The Optimizer chooses the PRPD method only if it is less costly than other join methods. |
|
| Utilities Products | ||
| Obsolete | Replacement Product | |
| Software | ||
| Product | ||
| Teradata | Teradata | |
| Multitool | Database | |
| command line utilities | ||
| PMON | Teradata | |
| Viewpoint – | ||
| Various system management | ||
| portlets | ||
| Teradata | Teradata | |
| Dynamic | Viewpoint – Workload | |
| Workload | Designer portlet | |
| Manager | ||
| Teradata | Teradata | |
| Manager | Viewpoint – | |
| Various system management | ||
| portlets | ||
| Teradata SQL Assistant Web Edition | Teradata SQL Assistant | |
|
21. Algorithmic and Multi-Value Compression Enhancements Multi-Value Compression (MVC) now compresses table columns defined with the following data types: a. TIME and TIME WITH TIME ZONE b.TIMESTAMP and TIMESTAMP WITH TIME ZONEAlgorithmic Compression (ALC) now allows you to define your own algorithms to compress and decompress columns with the following data types: a. TIME and TIME WITH TIME ZONE b.TIMESTAMP and TIMESTAMP WITH TIME ZONE c. BLOB/CLOB d.Distinct LOB-type UDTs,with some restrictions |
||
|
22. Block Level Compression Enhancements a)Finer control over the types of tables that are compressed. b)Single-command compression of all qualifying tables in a database. Improved performance from compressing only the fallback data subtables. |
||
|
23. Derived Periods You can now define a derived period column using two DateTime columns. |
||
|
24. 1 MB Data Blocks / 1 MB Spool Rows Teradata Database creates and stores data blocks up to 1 MB for most systems. The prior maximum data block size was 128 KB. The maximum size of an internal spool row is 1 MB. The prior maximum size was 64 KB. |
||
| 25. Importing from an Oracle Database Provides dynamic access to Oracle data Allows customers to continue linking to related Oracle databases for data after migrating their data warehouse to Teradata | ||
|
26. New Ordered Analytical and Aggregate Functions CUME_DIST calculates the cumulative distribution of avalue in a group of values. DENSE_RANK returns the rank of a row in an ordered group of rows according to the value of one or more columns, assigning the next rank value to the next unique value in the face of ties. Rank values are not skipped in the event of ties. FIRST_VALUE returns the first row from a partition. LAST_VALUE returns the last value from a partition.Teradata Database 14.10 supports the following new aggregate functions: PERCENTILE_CONT takes a percentile value and a sort specification and returns an interpolated value that would fall into that percentile value (an inverse distribution function). PERCENTILE_DISC takes a percentile value and a sort specification and returns an element from the set (an inverse distribution function). MEDIAN is a specific case of PERCENTILE_CONT where the percentile value is 0.5. |
||
|
27. Sort Ordering of NULL Values you can specify whether NULL values sort first or last in: You have more control and flexibility with respect to sort ordering ofNULL values. |
||
|
28. Update-Delete-InsertCounts Tracks the number of insert, delete, and update operations performed on tables in Teradata Database to better enable the Optimizer to estimate cardinalities. |
||
|
29. Teradata Intelligent Memory The permanent table data used most often now remains in-memory for faster access. The data stays in VERYHOT cache until other data is used more often and replaces it in the cache. Teradata Virtual Storage (TVS) automatically determines the hottest data. |
||
