Course Description
This course defines the processes & procedures to follow when designing & implementing a Teradata system. It includes Teradata data distribution, access, & use of derived data. Similarities between join & aggregation processing, and the implementation of Referential Integrity are also discussed. Various compression forms are explained in detail. Extensive hands-on labs help reinforce learning.
Prerequisites
To get the most out of this training, one should have the following knowledge or experience.
- Introduction to Teradata Database
- Teradata SQL
- Familiarity with Relational Database Modeling
Target Audience
This course is designed for:
- Database Administrators
- Architects/Designers
- Application Developers
Learning Objectives
Upon accomplishment of this course, participants will be able to:
- Differentiate a logical data model from a physical database model
- Describe data distribution and hashing on a Teradata platform
- Explain the structure and characteristics of primary and secondary indexes
- Explain the Teradata file system and how space limits are defined and enforced
- Explain the costs and benefits of Partitioned Primary Indexes & table constraints, including the Referential Integrity constraint
- Determine why, where, & when Statistics should be collected, & how they support the Optimizer develop efficient join plans
- Explain the options available in the CREATE TABLE and CREATE INDEX statements, & how they affect system performance
- Compare the expenses and benefits of denormalization
Content Outline
- Recommended Prerequisite Knowledge
- Course Objectives
- Course Modules
- Data Warehouse Usage Evolution
- The Stages of Database Development
- Customer Service Logical Model – ERA Methodology Diagram
- Different Models, Different Terms
- Attributes
- Entities and Relationships in the LDM
- Normal Forms
- Normalization
- Database Design Components
- Understanding Change Rating
- Understanding Access
- Extended Logical Data Model
- Physical Data Model
- The Principles of Index Selection
- Review Questions
- The Teradata Hierarchy
- Owners, Parents, and Children
- Creating Objects
- Permanent Space Terminology
- Spool and Temp Space Terminology
- Assigning Space Limits
- Summary
- Review Questions
- Lab Exercises
- Data Distribution
- Hashing
- Primary Index Hash Mapping
- Hash Maps
- Primary Hashmap
- Hash Maps for Different Systems
- Fallback Hash Map
- Reconfiguration
- MAPS TD 16.10 Reconfiguration
- MAPS TD 16.10 Sparse Maps
- Hash Related Expressions~
- Hashing – Numeric Data Types
- Multi-Column Hashing
- Additional Hash Examples
- Using Hash Functions to View Distribution
- Row Retrieval via PI Value – Overview
- Names and Object IDs
- Table ID
- Row ID
- AMP File System – Locating a Row via PI
- Accessing the Row within the Data Block
- General Row Layout
- AMP Read I/O Summary
- Review Questions
- Lab Exercises
- AMP Write I/O
- New Row INSERT
- Master Index Access
- Cylinder Index Access
- Use Free Space within Block
- Expand the Block
- Block and Row Terminology
- Block Splits (INSERT and UPDATE)
- Cylinder Full
- Mini-Cylpack
- Space Utilization
- 1MB Data Blocks
- 1MB Table Headers (TD14.10)
- Summary
- Review Questions
- Module 5 - DBS Control Settings
- System Level Defaults
- DBS Control Record – General Fields
- MaxDecimal
- Date and Time Parameters
- Primary Index Defaults
- DBS Control Record – File System Fields
- Free Space Percent
- MiniCylPackLowCylProd
- DefragLowCylProd
- PermDBSize~
- PermDBAllocUnit
- JournalDBSize
- Cylinders Saved for Perm
- Merge Datablocks
- Teradata 14.10 – 1MB Spool Rows
- DBS Control Record – Performance Fields
- DisableSyncScan
- ReDistBufSize
- Support of Hash Joins
- DBS Control Record – Compression
- Enabling Compression
- Controlling Compression Operations
- Temperature Based Block Level Compression
- Summary
- Primary Index Choice Criteria
- CREATE TABLE – Indexing Rules
- Primary Index Characteristics
- Multi-Column Primary Indexes
- Primary Index Considerations
- PKs and Duplicate Rows
- Primary Index Demographics
- Column Distribution Demographics for a PI Candidate
- SQL to View Data Demographics
- TableSize View
- SQL to View Data Distribution
- Summary
- Review Questions
- Lab Exercises
- What is a NoPI Table?
- Reasons to Consider Using NoPI Tables
- Creating a Table without a PI
- Distribution of Rows (or Blocks) for a NoPI Table
- The Row ID for a NoPI Table
- Loading Data into a NoPI Table (SQL)
- Loading Data into a NoPI Table (Utility)
- Archive and Reconfig Issues
- NoPI Table Options
- Summary
- Review Questions
- Secondary Indexes
- Choosing a Secondary Index
- Secondary Index Subtables
- USI Subtable General Row Layout
- USI Hash Mapping
- NUSI Subtable General Row Layout
- NUSI Hash Mapping
- Secondary Index Considerations
- Single NUSI Access (Between, Less Than, or Greater Then)
- Dual NUSI Access
- EXPLAIN of ORed NUSI’s
- NUSI Bit Mapping
- EXPLAIN of a SELECT (BMSMS)
- Value-Ordered NUSIs
- Hash-Ordered NUSIs
- Hash-Ordered NUSI Example
- Partitioned Primary Indexes (PPI)
- Logical Example of Row Partitioning
- How is Partitioning Implemented?
- PPI Details
- Primary Index Access (Non-partitioned Table)
- Primary Index Access (Row Partitioned Table)
- Simple Partitioning Example
- Partitioning with CASE_N and RANGE_N
- Special Partitions with CASE_N and RANGE_N
- NO CASE (NO RANGE) or UNKNOWN
- TIMESTAMP PPI
- SQL Use of PARTITION Keyword
- SQL Use of CASE_N
- Using ALTER TABLE
- With Row Partitioned Tables
- Populated Tables
- NO RANGE is not Defined
- NO RANGE is Defined
- WITH DELETE
- DROP/ADD
- ADD Clause on Partitioning Expression
- ALTER TABLE TO CURRENT
- EXPLAIN Terminology for PPI Tables
- Example 6 – Partition Elimination with a PPI Table
- Multilevel Partitioning Concepts
- How is the MLPPI Partition # Calculated?
- Summary
- Review Questions
- Lab Exercises
FAQs
To attend the training session you should have an operational Desktops or Laptops with required specification along with good internet connection to access the labs.
We would always recommend you to attend the live session to practice & clarify the doubts instantly and get more value from your investment. However, due to some contingency if you have to skip the class Radiant Techlearning would help you with the recorded session of that particular day. However, those recorded sessions are not meant only for personal consumption and NOT for distribution or any commercial use.
Radiant Techlearning has a data center containing the Virtual Training environment for the purpose of participant’s hand-on-practice.
Participants can easily access these labs over Cloud with the help of remote desktop connection.
Radiant virtual labs provide you the flexibility to learn from anywhere in the world and in any time zone.
The learners will be enthralled as we engage them in real world and industry Oriented projects during the training program. These projects will improve your skills and knowledge and you will gain better experience. These real time projects, they will help you a lot in your future tasks and assignments.