15.10 - Teradata Database Design - Teradata Database

Product: Teradata Database
Release Number: 15.10
Language: English (United States)
Last Update: 2018-10-06

Preface
Audience
Supported Software Releases and Operating Systems
Prerequisites
Changes to This Book
Product Safety Information
Teradata Database Optional Features
Chapter 1 Designing for the Data Warehouse
The Heart of the Data Warehouse
Data Marts and Data Warehouses
Data Marts
Independent Data Marts
Dependent Data Marts
Logical Data Marts
Common Problems With Data Marts
Avoiding the Data Mart-Centric Approach
The Teradata Concept of the Data Warehouse
Special Design Considerations for the Teradata Data Warehouse Environment
Born To Be Parallel
Born To Be Parallel
Designing for Parallel Processing in the Data Warehouse Environment
Data Placement to Support Parallel Processing
Data Placement to Support Parallel Processing
Row Distribution
Shared-Nothing Architecture
AMP Ownership of Data
Other Applications of Hashing for Parallel Processing
Intelligent Internodal Communication
Intelligent Internodal Communication
Optimal Network Communication Strategies
Special Purpose Communications Transport
Conservation of Network Bandwidth
Optimizing Queries for a Parallel Environment
Parallel Awareness
Request Parallelism
Within-Step Parallelism
Multistep Parallelism
Multistatement Request Parallelism
Synchronization of Parallel Operations
Synchronization of Parallel Operations
Spool Reuse
Synchronized BYNET Operations
Design Considerations
Usage Considerations: OLTP and Data Warehousing
Usage Considerations: OLTP and Data Warehousing
OLTP
Data Warehousing
Usage Considerations: Summary Data and Detail Data
Observing the Effects of Summarization
Information Value of Summary Data
Proactive Use of Detail Data
Usage Considerations: Simple and Complex Queries
Relationship Between Query Complexity and the Value of Its Answer
Valuable Information and Time
Usage Considerations: Ad Hoc Queries
Ad Hoc Queries and Enterprise Tactical Support
Ad Hoc Queries and Enterprise Discovery
Business Situations That Drive the Need for Ad Hoc Queries
Chapter 2 Logical Data Modeling
Databases and Data Modeling
Database Design Life Cycle
Designing for OLTP and Designing for Data Warehousing Support
Designing for OLTP and Designing for Data Warehousing Support
Data Warehousing Support
OLTP
ANSI/X3/SPARC Three Schema Architecture
High Level Architecture
Detailed Architecture
Key
Requirements Analysis
Logical Database Design
Normalization
Activity Transaction Modeling
Activity Transaction Modeling
Physical Database Design
Chapter 3 Requirements Analysis
Getting the System Right the First Time
Supporting User Access to the System
Producing a Reasonable Estimate of Costs
Developing an Enterprise Data Model
Things To Avoid When Developing an Enterprise Data Model
Chapter 4 Semantic Data Modeling
The Entity-Relationship Model
The Entity-Relationship Model
The Relational Model for Database Management
The Entity-Relationship Model for Database Management
Applications of the E-R Model
Entities, Relationships, and Attributes
Entities, Relationships, and Attributes
Types of Entities
Definition of a Relationship
Definition of Attribute
Definition of a Derivative Attribute
Translating Entities and Relationships Into Tables
Translating Entities and Relationships Into Tables
Definition of a Non-Prime Table
Definition of an Associative Table
Guideline for Naming Associative Tables
Relationship Theory
Relationship Theory
Definition of Connectivity
Definition of Cardinality
Definition of Existence Dependency
One-to-One Relationships
Modeling 1:1 Relationships
Guideline for Placing the Foreign Key
One-to-Many Relationships
Modeling 1:M Relationships
Many-to-Many Relationships
Modeling M:M Relationships
Guideline for Drawing M:M Tables for Users
Moving From an Entity-Relationship Analysis to Normalization
Chapter 5 The Normalization Process
Why Teradata Encourages a Fully Normalized Schema
Built-In Support for Fully-Normalized Databases
Original Teradata Design Goals Strongly Coupled With Normalization
The Key, the Whole Key, and Nothing But the Key
Normalization as a Logical Process
The Cost of Normalization
Properties of Relations and Their Logical Manipulation
Properties of Relations and Their Logical Manipulation
Logical Operations on Relations
Functional, Transitive, and Multivalued Dependencies
Functional, Transitive, and Multivalued Dependencies
Inference Axioms for Functional Dependencies
Inference Axioms for Multivalued Dependencies
The Normal Forms
The Objective of Normalization
Types of Decomposition
Third and Boyce-Codd Normal Forms
Definition of Third Normal Form
Definition of Boyce-Codd Normal Form
Relationship Between 3NF and BCNF
Meaning of Third Normal Form in This Manual
Decomposing Relations
Decomposing Relations for an Inherently Parallel Database Environment
Identifying Candidate Primary Keys
Procedure for Identifying Primary Keys
Surrogate Keys
Rules for Primary Keys
Guidelines for Selecting Primary Keys
Foreign Keys
Rules
The Referential Integrity Rule
Purpose
Entity Integrity and Foreign Key Nulls
Enforcing the Referential Integrity Rule
Enforcing Referential Integrity in Teradata
Definition of a Referencing (Child) Table
Definition of a Referenced (Parent) Table
Definition of a Primary Key
Definition of a Foreign Key
Why Referential Integrity Is Important
Referential Integrity Constraints
Rules for Referential Integrity Constraints
Domains and Referential Integrity
Definition of a Domain
Table Definition for Examples
Domain Rules for Primary Keys
Questions and Answers for Primary Key Domains
Domain Rule for Foreign Keys
Questions and Answers for Foreign Key Domains
Normalization and Database Design Problems
Preserving Functional Dependencies
Full Normalization and Dependency Preservation
General Procedure for Achieving a Normalized Set of Relations
Advantages of Normalization for Physical Database Implementation
How Normalization Is Beneficial for Physical Databases
Data Dependence and Data Independence
Recursive Relationships
Arguments Against Denormalizing the Physical Database Schema to Increase Its Usability
Arguments Against Denormalizing for Performance
Denormalized Physical Schemas and Ambiguity
Referential Integrity and a Denormalized Schema
Denormalized Views Versus Physical Denormalization of the Database Schema
Dimensional Analysis
Unbalanced Hierarchies and Unnormalized Physical Schemas
Analyses Driven By Normalized Relationships
Costs of Denormalization
Cross-Functional Analysis and Denormalization
No Crystal Ball
Design For Decision Support and Tactical Analysis
Design for Flexible Access Using Views
Chapter 6 Denormalizing the Physical Schema
Denormalization, Data Marts, and Data Warehouses
Denormalization Issues
Commonly Performed Denormalizations
Alternatives to Denormalization
Denormalizing With Repeating Groups
Reasons to Denormalize With Repeating Groups
Reasons Not to Denormalize With Repeating Groups
Denormalizing Through Prejoins
Reasons to Denormalize Using Prejoins
Reasons Not to Denormalize Using Prejoins
Denormalizing Through Join Indexes
Reasons to Denormalize Using Join Indexes
Derived Data Attributes
Approaches to Handling Standalone Derived Data
Guidelines for Handling Standalone Derived Data
Reasons Not to Denormalize Using Derived Data
Denormalizing Through Global Temporary and Volatile Tables
Using Global Temporary Tables and Volatile Tables to Avoid Denormalization
Using Global Temporary and Volatile Tables to Enhance Performance
Denormalizing Through Views
Prejoin With Aggregation
Dimensional Views
Dimensional Modeling, Star, and Snowflake Schemas
Dimensional Modeling, Star, and Snowflake Schemas
Fact Tables and Dimension Tables
Star Schema
Snowflake Schema
The E-R Model Versus the DM Model
Chapter 7 Teradata Database Indexes and Partitioning
Types of Indexes
Types of Indexes
Nonunique Indexes
Primary Index Types
Secondary Index Types
Join Index Types
Hash Indexes
Advantages of Indexes
Disadvantages of Indexes
Indexes and Partitioning
Selectivity of Indexes and Partitioning
Rowhash and RowID
Row Distribution for NoPI Tables
Row Distribution for Primary Indexed and Primary AMP Indexed Tables
Partitioned Tables and Join Indexes
Using EXPLAIN Request Modifiers to Determine the Usefulness of Indexes
Comparing EXPLAIN Request Reports Graphically
Analyzing EXPLAIN Request Reports Using SQL
Target Level Emulation
Using Indexes to Enhance Performance
Determining Index Usage
Keys and Indexes
Keys and Indexes
Definition of an Index
Differences Between Keys and Indexes
SQL and Indexes
SQL and Indexes
Role of the WHERE Clause in SQL DML Requests
How the Optimizer Uses the WHERE and ON Clauses
Predicates That Invoke Indexes
Predicates That Invoke Full-Table Scans
Primary-Indexed, Primary-AMP-Indexed, and NoPI Tables
Default Primary Index
Restrictions on Indexes
Altering a Primary Index or Primary AMP Index
Secondary Indexes
Use of Secondary Indexes
Restrictions on Secondary Indexes
Types of Secondary Index
Secondary Index Storage
Join Indexes
Use of Join Indexes
Restrictions on Join Indexes
Types of Join Index
Join Index Storage
Hash Indexes
Use of Hash Indexes
Restrictions on Hash Indexes
Hash Index Storage
Reference Indexes
Indexing and Hashing
Indexing Mechanisms
Hashing Mechanisms
Tradeoffs Between Hashing and Indexing
Advantages of Hashing Over Indexing
Disadvantages of Other Row Partitioning Methods
Advantages of Indexing Over Hashing
Teradata Database Hashing Algorithm
Hash Bucket Number
Uniqueness Value
Hash Collisions
Minimizing Hash Collisions
Hash Maps
Number of Hash Buckets Per System
Hash Map Consistency Across Configurations
The Problem of Skew
Definitions for the Six Database Hash Maps
Hash Map Lookup
Hash-Based Table Partitioning to AMPs
Row Assignment for Primary-Indexed Tables
Row Assignment for Primary-Indexed Column-Partitioned Tables
Row Assignment for Primary AMP Indexed Column-Partitioned Tables
Row Assignment for NoPI Tables
Row Assignment for FastLoad Operations Into Nonpartitioned NoPI Tables
Row Assignment for FastLoad Operations Into Column-Partitioned Tables
Row Assignment for INSERT … SELECT Operations Into NoPI Tables
Row-Partitioned Row Assignment
Hash-Related Functions
Using HASHROW
Hashed Row and RowID Access
Hashed Row and RowID Access
Cylinder Index
Data Block
Hashing and Data Types
Hashing On Bit Patterns, Not Values
Considerations for Indexing
Teradata Database Index Comparisons
Table Access Summary
Index Selection Summary
Nonspecific Factors
Partitioning Type
Column Data Type Factors
Space Utilization Factors
Demographic Factors
Application Factors
Transaction Factors
DML Factors
Tables and Index Types that Cannot Have a Primary Index or Primary AMP Index
Chapter 8 Primary Index, Primary AMP Index, and NoPI Objects
Primary Indexes and Primary AMP Indexes
Purposes of a Primary Index or Primary AMP Index
Restrictions on Primary Indexes and Primary AMP Indexes
Primary Index Dimensions
Primary Index Defaults
Exceptions to the PrimaryIndexDefault Setting
Guidelines for NoPI Tables
Problematic Selection of a Primary Index Column Set
Unique and Nonunique Primary Indexes
Unique Primary Indexes
Nonunique Primary Indexes
Polyinstantiation
Row-partitioned and Nonpartitioned Primary Indexes
Nonpartitioned Primary Indexes
Row-partitioned Primary Indexes
Partitioning Expression Data Type Considerations
Partitioning a Table or Join Index
Using Partition-Level BAR Restore
About Choosing a Primary Indexing Method for a Column-Partitioned Table or Join Index
Benefits and Considerations for Column-Partitioned Tables with a Primary Index
Benefits and Considerations for Column-Partitioned Tables with a Primary AMP Index
NoPI Tables, Column-Partitioned NoPI Tables, and Column-Partitioned NoPI Join Indexes
INSERT... SELECT into NoPI and Column-Partitioned NoPI Tables
Uses for Nonpartitioned NoPI Tables
Rules and Limitations for NoPI and Column-Partitioned Tables
Manipulating Nonpartitioned NoPI Table Rows
Column-Partitioned NoPI Tables and Join Indexes
Options for Storing a NoPI Table
About Column-Partitioned NoPI Tables
Example: Nonpartitioned NoPI Table
Example: Column-Partitioned NoPI Table With No Autocompression
Example: Column-Partitioned NoPI Table With Autocompression
Column Partitioning Performance
Cases Where Positive Performance Effects Are Most Likely To Occur
Cases Where Negative Performance Effects Are Most Likely To Occur
Autocompression
Examples
Autocompression and Spools
Autocompression Interactions With User-Specified Compression Methods
Checking the Effectiveness of Autocompression
Using the NO AUTO COMPRESS Option
Anticipated Workload Characteristics for Column-Partitioned Tables and Join Indexes
General Performance Guidelines for Column Partitioning
Guidelines for Queries, Contexts, and Table Maintenance for Column Partitioning
Guidelines for Partitioning Column-Partitioned Tables and Join Indexes
Guidelines for Specifying Table and Column Attributes for Column Partitioning
Guidelines for Specifying Compression for Column-Partitioned Tables and Join Indexes
Guidelines on Optimizing I/O Operations, CPU Usage, and Disk Space Usage for Column-Partitioned Tables and Join Indexes
Guidelines for Collecting Statistics on Column-Partitioned Tables and Join Indexes
Comparing the Number of I/O Operations Required to Answer the Same SELECT Request
Locks and Concurrency When Accessing a Column-Partitioned Table
Selecting Rows From a Column-Partitioned Table
Joins With a Column-Partitioned Table, Join Index, or Spool
Bulk Loading a Column-Partitioned Table
Setting the Optimal Teradata Parallel Data Pump SERIALIZE Option
Maintenance Cost for Column-Partitioned Tables and Join Indexes
Column-Partitioned Table as a Source Table in a Data Moving Request
Deleting Rows From a Column-Partitioned Table
Updating a Column-Partitioned Table
Effect of Skewed Data on Column-Partitioned NoPI Tables
Operations and Utilities for Column-Partitioned Tables
Storage and Other Overhead Considerations for Partitioning
Storage and Other Overhead Considerations for Partitioning
Storage Considerations for Column-Partitioned Tables and Join Indexes
Column Partitions With COLUMN Format
Column Partitions With ROW Format
Memory Limitations and Partitioning
Memory Limitations and Partitioning
PPICacheThrP DBS Control Parameter
How Teradata Database Controls Memory Usage by Partitions
Advantages and Disadvantages of Partitioned Primary Indexes
Effects of Partition Cardinality On Query Performance
Selecting the Partitioning Granularity
Determining the Maximum Number of Partitions for a Partitioned Database Object
Assigning Excess Combined Partitions to Partitioning Levels
Usage Recommendations For Partitioning
Usage Recommendations For Partitioning
Basing Partitioning on Modulo Partitioning of a Numeric Column
Basing the Partitioning Expression on Two or More Numeric Columns
Basing the Partitioning Expression on a CASE_N Function
Considerations for Basing a Partitioning Expression on a CASE_N Function
Basing a Partitioning Expression on a RANGE_N Function
Considerations for Basing the Partitioning Expression on a RANGE_N Function
Basing the Partitioning Expression on a RANGE_N Character Column
Using CASE_N and RANGE_N in SELECT Requests
Workload Characteristics, Queries, and Row Partition Elimination
Workload Characteristics and Row Partitioning
Workload Characteristics and Partitioning
Workload Characteristics and Joins
General Recommendations for Using Row-Partitioned Tables and Join Indexes
Single-Level Partitioning
Single-Level Partitioning
Single-Level Partitioning Example
Single-Level Partitioning Case Studies
Determining an Optimal Partitioning Scheme
Multilevel Partitioning
Multilevel Partitioning
Partitioning CHECK Constraint for Multilevel Partitioning
Partitioning CHECK Constraints
Row Partition Elimination With Multilevel Partitioning
Importance of Partition Order for Specifying Partitioning Expressions
Different Multilevel Row Partitioning of the Same Table
Detailed Multilevel Partitioning Example
Detailed Multilevel Partitioning Example
Combined Partitioning Expression for the Sales Table
How Rows for the Sales Table Are Grouped on an AMP
Performance Implications of Multilevel Row Partitioning
Row Partition Elimination
Performance Gains Realized From Row Partition Elimination
Row-Partitioned and Nonpartitioned Primary Index Access for Typical Operations
Summary of Primary Index Selection Criteria
Principal Criteria for Selecting a Primary Index
Uniform Data Distribution
Optimal Data Access
Index Column Volatility
Criteria for Selecting a Primary Index
Considerations for Choosing a Primary Index
Partitioning Guidelines
Evaluating the Relative Merits of Partitioning Versus Not Partitioning
Primary Index Properties
Secondary Considerations for Selecting a Primary Index
Distribution Demographics
Distribution Guidelines
Access Demographics
Primary Index Value Retrieval Access: Definition
Primary Index Value Retrieval Access: Guidelines for Nonpartitioned Selection
Primary Index Value Retrieval Access: Partitioning Guidelines for Selection
About Join Access
Join Access: Column Selection Guidelines
Volatility of Indexed and Partitioning Column Values
Selecting a Primary Index for a Queue Table
Column Distribution Demographics and Primary Index Selection
Distribution Demographics
Number of Distinct Values
Number of Rows Per Distinct Value
Number of Null Rows
Effects of Skew on Query Processing Summarized
SQL Scripts For Detecting Skew
Table Structure and Update Schedule
Query Workload
Problem Statement
Analysis of Row Partitioning Benefits
Conclusions
Table Structure and Update Schedule, Query Workload, and Problem Statement
Analysis of Row Partitioning Benefits
Conclusions
Table Structure and Update Schedule
Query Workload
Problem Statement
Analysis of Partitioning Benefits
Conclusions
Dealing With Ambiguous Scenarios
Table Structure and Update Schedule
Query Workload
Problem Statement
Analysis of Partitioning Benefits
Conclusions
Locating a Row Using Its Primary Index
Process for Locating a Row Using Its Unique Primary Index
Primary Index Reads
Performance Considerations for Primary Indexes
Guideline for All Primary Indexes
Guidelines for Row Partitioning
General Considerations
Unique Primary Index Considerations
Nonunique Primary Index Considerations
Normalization, Denormalization, and Primary Index Usage
Denormalized Table Set
Normalized Table Set
Duplicate Row Checks for NUPIs
Performance Effects of Duplicate NUPI Row Checks
Duplicate NUPI Row Read I/O as a Function of Number of Rows Inserted
Guidelines for Keeping NUPI Duplicates Below 100 Rows Per Value
Number of Reads for Inserting 10, 100, and 1,000 NUPI Duplicate Rows
Cumulative Number of Data Block Read I/Os as a Function of Duplicate NUPI Inserts
Minimizing Duplicate NUPI Row Checks
Eliminating NUPI Duplicate Row Checks
Optimizing Performance by Using Multicolumn NUPIs
Advantages and Disadvantages of Multicolumn NUPIs
Keep the Number of Duplicate NUSI Value Rows Per Table Below 100
Primary Index Uniqueness and Row Distribution
Case Study 1: Fewer Distinct Primary Index Values Than AMPs
Case Study 2: Highly Nonunique Primary Index Values
Case Study 3: Highly Singular Primary Index Values
Case Study 4: Unique Primary Index Values
Chapter 9 Secondary Indexes
Purpose
Restrictions
Space Considerations
Unique Secondary Indexes
Using Unique Secondary Indexes to Enforce Row Uniqueness
USI Access
Unique Secondary Indexes and Performance
Creating a Unique Secondary Index as a Composite of a Row-Level Security Constraint Column and a NUPI Column Set
Restrictions on Load Utilities
USI Hashing
USI Subtable Row Structure
Using Unique Secondary Index Maintenance and Rollback to Optimize Query Design
Nonunique Secondary Indexes
Relationship Between a NUSI Subtable Row and Base Table Rows
NUSI Row Structure
NUSI Access and Performance
Using NUSIs
NUSIs and Block Size
Selectivity Considerations
Low Selectivity Indexes
High Selectivity Index
Generic NUSI Read Process Flow
Multiple NUSI Access
Multiple NUSI Access and Composite NUSI Access
Example NUSI Definitions
AND Equality Condition
OR Equality Condition
Queries Using BETWEEN, LESS THAN, GREATER THAN, or LIKE Operators
NUSI Bit Mapping
Performance Advantages of NUSI Bit Mapping
Restrictions for When Bit Mapping Is Used
Using NUSIs For Complex Conditional Expressions
Computing NUSI Bit Maps
Determining If Bit Mapping Is Being Used
NUSIs and Query Covering
Criteria
Column Candidacy for Covered Access
Value-Ordered NUSIs and Range Conditions
Limitations
Importance of Consecutive Indexes for Value-Ordered NUSIs
Typical Uses of Value-Ordered and Hash-Ordered NUSIs
Selecting a Secondary Index
Optimal Data Access
Criteria for Selecting a Secondary Index
Secondary Index Access Summarized by Example
Configuration
Table Definition
Sample Queries
Query 1
Query 2
Query 3
Secondary Index Build and Access Operations Summarized
Secondary Index Build Process Table
Secondary Index Usage Summary
USI Summary
NUSI Summary
Chapter 10 Join and Hash Indexes
Join Indexes
Rules for Using the ROWID Keyword in a Join Index Definition
Rules for Using the System-Derived PARTITION Column in a Hash or Join Index Definition
Rules for Using Join Indexes on Load-Isolated Tables
Default Column Multivalue Compression for Join Index Columns When the Referenced Base Table Column Is Compressed
Compression of Join Indexes at the Block Level
Summary of Join Index Functions
Similarities of Join Indexes to User Data Tables
Join Index Applications
Partial Query Coverage
Designing for Range Queries: Guidelines for Choosing Between a PPI and a Value-Ordered NUSI
Collecting Statistics on a Join Index
Fallback With Join Indexes
Limits for Hash and Join Indexes
Using Join Indexes
Performance and Join Indexes
Partial Covering Multitable Join Indexes
Covering Bind Terms
Using Single-Table Join Indexes
Using Outer Joins to Define Join Indexes
Defining Join Indexes with Inequality Conditions
Defining Join Indexes on UDT Columns and Expressions
Refreshing Join Indexes
Using Aggregate Join Indexes
Join Indexes and the Optimizer
System Processing of Join Indexes
Join Index Optimizations
Protecting a Join Index with Fallback
Collecting Statistics for Join Indexes
Costing Considerations for Join Indexes
Space Costs for Join Indexes
Maintenance Costs for Join Indexes
Join Indexes Versus NUSIs
Join Index Design Tips
When You Should Consider Defining a Join Index
Using Outer Joins to Define Simple Join Indexes
Collecting Statistics on the Columns and Indexes for Join Indexes
Basing a Join Index on Foreign Key-Primary Key Equality Conditions
Adding Join Constraints That Facilitate Joining to Other Tables
Adding Join Constraints That Facilitate Joining to Other Tables
Join Index Benefits and Costs
Cost
Join Index Costs Summary
Benefit and Benefit Percentage
Payback
Cost of Disk Resources
Cost of Join Index Creation
Cost of Join Index Maintenance
Maintenance Costs of Join Indexes
Maintenance Cost as a Function of Number of Hits Per Data Block
Maintenance Cost as a Function of Row Size
Join Index Maintenance Cost as a Function of Insert Method
Cost/Benefit Analysis of Join Indexes
Cost/Benefit Analysis for Join Indexes
Join Index Benefits Summary
Computing the Benefits of Join Indexes
Computing the Query Ratio
Computing the Payback Factor
Example of Computing a Payback Factor for a Simple Join Index
Example of Computing a Payback Factor for an Aggregate Join Index
Two More Brief Examples
Join Index Types
Simple Join Indexes
Simple Join Index Example
Simple Join Indexes and Partial Query Covering
Defining a Simple Join Index on a Binary Join Result
Defining a Simple Join Index on a Binary Join Result
Example Query Request
Query Plan: No Join Index Defined
Query Plan: Join Index Defined
Materialized Join Index
Defining and Using a Simple Join Index With an n-way Join Result
Join Index Definition
EXPLAIN for Query With Complicated Predicates
Single-Table Join Indexes
Functions of Single-Table Join Indexes
Column-Partitioned Single-Table Join Indexes
Maintenance Costs of Single-Table Join Indexes
Parameterized Queries and Single-Table Join Indexes
Related Strategies
Single-Table Join Index
Table Definitions
Example Query Request
Decision: Ordinary Join Index Versus Single-Table Join Index
Single-Table Join Index Definition
General Procedure for Defining a Single-Table Join Index
Aggregate Join Indexes
Functions of Aggregate Join Indexes
Related Strategies
Aggregate Join Index With EXTRACT Function
Sparse Join Indexes
Sparse Join Indexes and Query Optimization
Performance Impact of Sparse Join Indexes
Using Outer Joins in Join Index Definitions
Join Index With Outer Join in Its Definition
Extended Query Coverage With Outer Joins in Index Definition
More On Outer Join Index Coverage of Queries
Using Outer Joins to Define Join Indexes
Redefined Join Index
Materialized Join Index
Creating Join Indexes Using Outer Joins
Table Definitions
Join Index Definition
EXPLAIN for Query With Simple Predicate
EXPLAIN for Query With More Complicated Predicate
EXPLAIN for Query With Aggregation
EXPLAIN for Query With Base Table-Join Index Table Join
EXPLAIN for Query Against Single Table
Join Indexes and Tactical Queries
Join Indexes and Tactical Queries
Aggregate Join Indexes
Join and Hash Index Maintenance Considerations
Examples of Row-at-a-Time INSERT Maintenance Overhead
Set Processing Alternative
Join Index Definition Restrictions
Restrictions on Number of Join Indexes Defined Per Base Table
Restrictions on the Number of Columns Per Referenced Base Table
Restrictions on the Data Type of Join Index Columns
Restrictions on the Use of the System-Derived PARTITION[#Ln] Column
Restrictions on Outer Join Definitions
Restrictions on Secondary Index Definitions
Restrictions on Built-In Functions and Join Index Definitions
Restriction on Number of Join Indexes Selected Per Query
Restrictions on Partial Covering by Join Indexes
Restriction on Coverage by Join Indexes When a Join Index Definition References More Tables Than a Query
Rules for Whether Join Indexes With Extra Tables Cover Queries
Examples That Obey the General Covering Rules for Extra Tables in the Join Index Definition
Examples of Exceptions to the General Rules for Extra Tables in the Join Index Definition
Restrictions on Join Index Aggregate Functions
Restrictions on Sparse Join Index WHERE Clause Predicates
Restrictions on Join Index ORDER BY Clauses
Restrictions on Load Utilities
Improving Join Index Performance
Selecting the Primary Index for Join Indexes
Selecting Secondary Indexes for Join Indexes
Statistics and Other Demographic Data for Join Indexes
Guidelines for Collecting Statistics on Multitable Join Indexes
Guidelines for Collecting Statistics on Single-Table Join Indexes
Collecting Statistics on Base Table Columns Instead of Single-Table Join Index Columns
Guidelines for Collecting Statistics On Single-Table Join Index Columns
Join Index Storage
Physical Join Index Row Compression
Guidelines for Using Row Compression With Join Index Columns
Determining the Space Overhead for a Hash or Join Index
Considerations for Measuring Disk Space
Example Measurement 1
Example Measurement 2
Value-Ordered Storage of Join Index Rows
Value Ordering and Range Queries
Hash Indexes
Comparison of Hash Indexes and Single-Table Join Indexes
Summary of Hash Index Functions
Similarities of Hash Indexes to Base Tables
Similarities of Hash Indexes to Secondary Indexes
Hash Index Applications
Compression of Hash Index Rows
Fallback With Hash Indexes
Restrictions for NoPI Tables
Restrictions on Load Utilities
Restriction on Partial Coverage of Queries Containing a TOP n or TOP m PERCENT Clause
Compression of Hash Indexes at the Block Level
Collecting Statistics on Hash Index Columns
General Guidelines
Collecting Statistics on Base Table Columns Instead of Hash Index Columns
Hash Index Definition Restrictions
Restrictions for NoPI Tables
Restrictions on Number of Hash Indexes Defined Per Base Table
Restrictions on Number of Columns Per Referenced Base Table
Restrictions on the Data Type of Hash Index Columns
Restrictions on the Use of the System-Derived PARTITION[#Ln] Column in a Hash Index Definition
Restriction on Number of Hash Indexes Selected Per Base Table
Hash and Join Index Interactions With Other Teradata Database Systems and Features
Summary
Triggers
Permanent Journal Recovery
Load Utilities
Teradata Parallel Data Pump
Archive and Recovery
Tradeoffs for Join or Hash Indexes
Chapter 11 Designing for Database Integrity
Sources of Data Quality Problems
Sources of Data Quality Problems
Data Warehousing Institute 2005 Follow-Up Survey
The Problem of Capturing the External World as Data
Data Quality Problems Compromise Information Security
The Role of Human Error in Creating Bad Data
Database Constraints and Enterprise Business Rules
Semantic Data Integrity Constraints
Logical Integrity Constraints
Definitions of Terms
Relations, Relation Values, and Relation Variables
The Logic of Database Integrity
The Closed World Assumption
How Relational Databases Are Built From Logical Propositions
An Airline Reservation System
Inclusion Dependencies
Inference Axioms for Inclusion Dependencies
Semantic Integrity Constraint Types
Domain Constraints
Column-Level Constraints
Row-Level Security Constraints
Table-Level Constraints
Database-Level Constraints
Semantic Constraint Specifications
Performance Issues for Referential Integrity Constraints
Benefits of Referential Integrity
Overhead Costs of Referential Integrity
Join Elimination and Referential Integrity
Standard Referential Integrity and Batch Referential Integrity
Referential Constraints
Naming Constraints
CHECK Constraints
Foreign Key Constraints
Rules for Both Column-Level and Table-Level Foreign Key Constraints
Rules for Column-Level Foreign Key Constraints Only
Rules for Table-Level FOREIGN KEY … REFERENCES Constraints Only
Rules for FOREIGN KEY … REFERENCES Referential Constraints Only
Referential Integrity Constraint Checks
Overhead Costs of Standard Referential Integrity
PRIMARY KEY Constraints
UNIQUE Constraints
Semantic Constraint Enforcement
Updatable Cursors and Semantic Database Integrity
Between-Transaction Integrity Issues
Within-Transaction Integrity Issues
Semantic Integrity Constraints for Updatable Views
Specifying Integrity Constraints in an Updatable View Definition
Updatable View Inheritance
Summary of Fundamental Database Principles
Physical Database Integrity
CHECKSUM Integrity Checking and Physical Database Integrity
FALLBACK Protection and Physical Database Integrity
Disk I/O Integrity Checking
Levels of Disk I/O Integrity Checking
Disk I/O Integrity Checking Detects and Logs Errors But Does Not Fix Them
About Reading or Repairing Data from Fallback
Conditions That Support Reading or Repairing Data from Fallback
Chapter 12 Designing for Missing Information
Semantics of SQL Nulls
Types of Missing Values
Inconsistencies in How SQL Treats Nulls
Bivalent and Higher-Valued Logics
First Order Predicate Logic and Bivalent Logic
The Closed World Assumption Revisited
Number of Logical Operators Supported for Bivalent and Trivalent Logics
SQL Support for a Consistent Trivalent Logic
Alternatives To Nulls for Representing Missing Information
Systematic Use of Default Values
Redesigning the Database to Eliminate the Need for Nulls
Taking the “Partial Nothing” Argument To Its Logical Extreme
Solving the Problem By Taking Simple Projections
Manipulating Nulls With SQL
Logical and Arithmetic Operations on Nulls
Nulls and Arithmetic Operators and Functions
Nulls and Comparison Operators
Nulls and Aggregate Functions
Nulls and DateTime and Interval Data
Nulls and CASE Expressions
NULLIF and COALESCE
NULL Literals
Rules for Using NULL as a Literal
Data Type of NULL Literals
Hashing on Nulls
Null Sorts as the Lowest Value in a Collation
Searching for Nulls Using a SELECT Request
Searching for Nulls and Nonnulls In the Same Search Condition
Excluding Nulls From Query Results
Nulls and the Outer Join
Semantics of Nulls in the Outer Join
Chapter 13 Using Data Compression
Compression Types Supported by Teradata Database
Identifying Uncompressed, Single-Value Compressed, and MultiValue Compressed Tables
Multivalue Compression
Procedure
Multivalue Compression Example
Guidelines for Using Multivalue Compression
Tradeoffs Between Multivalue Compression and Storage Requirements for Compressed Values
Tradeoffs Between Multivalue Compression and Storage Requirements for Compressed Values
Storage of Compressed Values
Algorithmic Compression
Procedure
Restrictions
Using Custom ALC Algorithms
Related Topics
Row Compression
Row Header Compression
Autocompression
Using Hash Index and Join Index Row Compression
Block-Level Compression
Hardware-Based Block-Level Compression
Coexistence of Software BLC and Hardware BLC
Software-Based Block-Level Compression
Enabling BLC and Temperature-Based BLC at the System Level
About Temperature-Based Block-Level Compression
Enabling Compression for Individual Tables Using DDL
Compressing Tables, Databases, and Data Types Manually Using Ferret
Compressing Data Loaded into Empty Tables Set to MANUAL
Compressing Data Loaded into Empty Subtables Set to AUTOTEMP
Interaction between Block-Level Compression and TVS Temperature Query Band Values
Controlling BLC at the System Level Using the DBS Control Utility
Block-Level Compression Usage Notes
CPU Considerations on Different Teradata Platforms
Finding Tables and Databases with Compressed Data Blocks
Obtaining Information about Tables with Compressed Data Blocks
Estimated Space Savings Percentage for Manually Compressed Tables
File System Information in an SQL Table
Detailed Compression Statistics for Tables
Estimated Compression Ratio
Statistics, Including Estimated Compression Ratio
Considerations for Choosing a Method To Obtain BLC Information
About System and AMP Outages During Compression
CPU Costing for Software-Based BLC
Estimating BLC Space Savings and CPU Usage
Choosing a Software-based Compression Scheme
Combining Compression Methods
Chapter 14 Database-Level Capacity Planning Considerations
Capacity Planning
Extended Data Lifetimes
Cool and Icy Data
Warm, Hot, and Very Hot Data
Storing Data Efficiently
Storing Data Efficiently
Specifying INTEGER Precisions
Using Standardized Encodings
Storing NUMBER Data
Storing VARCHAR, VARBYTE, and VARCHAR(n) CHARACTER SET GRAPHIC Data
Storing Character Data
Base Table Row Format
General Row Structure
Sector Alignment
General Row Structure When Compressing Variable Length Columns
Row Structure for Packed64 Systems
Row Structure for Aligned Row Format Systems
Containers and Subrows
Column Partitioning
Consumption of Disk Space by Populated and Empty Partitions
Byte Alignment
Row Length Characteristics
Row Components
Hash and Join Index Row Structures
Hash and Join Index Row Structure for Packed64 Format Systems
Hash and Join Index Row Structure for Aligned Row Format Systems
Secondary Index Subtable Row Structures
Presence Bits
Number of Presence Bits Required to Represent Compressed Values
Table Headers
Table Header Components
Sizing Structured UDT Columns
Storage Structure of a Structured UDT Data Type
Storage Structure of a Nested Structured Data Type
Equation For Sizing a Structured UDT
System-Derived and System-Generated Columns
System-Derived and System-Generated Columns
About System-Generated Columns
ROWID Columns
Object Identifier Columns
System-Derived and System-Generated Column Data Types
Data Type Considerations
Data Types And Hashing
Data Type Size Differences For Packed64 and Aligned Row Format Architectures
Numeric Data Types
Numeric Data Types
Exact Numeric Data Types
Approximate Numeric Data Types
Floating Point NUMBER Types
Integer Data Types
Non-INTEGER Numeric Data Types
Byte Data Types
DateTime Data Types
Interval Data Types
Period Data Types
Character Data Types
XML/XMLTYPE Data Type
JSON Data Type (Text-Based Format)
JSON Data Type (Binary Format)
JSON Data Type (Universal Binary Format)
User-Defined Data Types
Array Data Types
Geospatial Data Types
MBR
MBB
ST_GEOMETRY
Row Size Calculation
Procedure for Packed64 Systems
Procedure To Determine the Exact Row Size for Aligned Row Systems
Sizing Databases, Users, and Profiles
Modifying the Disk Space Currently Assigned to Databases, Users, or Profiles
Different Disk Space Views Return Different Results
Sizing Base Tables, LOB Subtables, XML Subtables, and Index Subtables
Critical Sizing Variables
Sizing a Column-Partitioned Table
LOB and XML Sizing Variables
Sizing Base Tables, Hash Indexes, and Join Indexes
Calculating a Typical Block Size
Sizing a Column-Partitioned Join Index
Table Sizing Equations
Sizing a LOB or XML Subtable
LOB and XML Subtable Rows and Skew
LOB and XML Subtable Sizing Equation
LOB and XML Subtables and Fallback
Sizing a Unique Secondary Index Subtable
USI Sizing Equation
Sizing a Nonunique Secondary Index Subtable
Special Considerations for NUSI Size Estimates
NUSI Sizing Equation
NUSI Space Considerations
Sizing User-Defined Routines
Sizing a Reference Index Subtable
Reference Index Sizing Equation
Sizing Spool Space
Intermediate Spool Space
Output Spool Space
Persistent Spool Space
Volatile Spool Space
Sources of Spool Space
Spool Limits
Guidelines for Allocating Spool Space
Sizing a Query Capture Database
Capacity Planning for a Query Capture Database
Sizing Table Space Empirically
General Procedure
Explicit Procedure
Querying DBC.TableSizeV
Table Sizing Summary
Chapter 15 System-Level Capacity Planning Considerations
Database Size Considerations
Secure Zones Inside a Database
System Disk Contents
Boot Disk Contents
Data Disk Contents
Reserved Space
Permanent Space
Data Disk Space
Permanent Space Allocations
System User DBC
System User SYSTEMFE
System User SYSADMIN
Estimating Database Size Requirements
Estimating Database Size Requirements
Allocating PERM, TEMP, and Spool Space
Estimating Administrative Spool Space Requirements
Estimating Administrative TEMP Space Requirements
Estimating WAL Log Space Requirements
WAL Log Data Block Size
Determining Available User Table Data Space
Depot Area Overhead
Data Table Overhead
Allocation Map and Statistics Overhead
Tables Area: Dictionary and Spool Space Requirements
Tables Area: User TEMP Space Requirements
Tables Area: CRASHDUMPS User Space Requirements
Tables Area: User Spool Space Requirements
Rules for Using the Spool Space Equations
Field Mode Spool Space Sizing Equation
Record and Indicator Mode Spool Space Sizing Equation
Calculating Total PERM Space Requirements
Designing for Backups
Terminology
Product Issues
The Scenario
A Solution
Chapter 16 Design Issues for Tactical Queries
Tactical Queries Defined
Scalability Considerations for Tactical Queries
Scalability Considerations for Tactical Queries
Effect of Data Volume Growth on Tactical Query Response Times
Effect of Growth in Concurrent Users on Tactical Query Response Times
Effect of Configuration Expansion on Tactical Query Response Times
Localizing the Work
Single-AMP Operations Using a Primary Index
Two-AMP Operations: USI Access and Tactical Queries
NUSI Access and Tactical Queries
Group AMP Operations
Determining When the Optimizer Will Consider Group AMP Processing
Few-AMP Joins and Tactical Queries
Tactical Queries Benefit From Nested Joins
Database Design Techniques to Support Localized Work
Single-AMP Queries and Partitioned Tables
Recommendations for Tactical Queries and Row-Partitioned Tables
Sparse Join Indexes and Tactical Queries
Sparse Join Index Defined on One Row Partition
Considerations For Using Sparse Join Indexes With Dense NUPIs
All-AMP Queries
Coding Suggestions for All-AMP Tactical Queries
All-AMP Tactical Queries and Partitioned Tables
Group AMP Check in Final Query Step
Application Opportunities for Tactical Queries
Multistatement Requests
Coding Multistatement Requests
ACCESS Locking and Multistatement Requests
Macros and Tactical Queries
Cached Plans
Other Tools Useful for Monitoring and Managing Tactical Queries
Monitoring Active Work
Recommended Monitoring Activities
Using Canary Queries
Appendix A Notation Conventions
Table Column Definition and Constraint Abbreviations
Character Symbols
Predicate Calculus and Set Theory Notation Used in This Manual
Dependency Theory Notation Used in This Manual
Appendix B Teradata System Limits
System Limits
Miscellaneous System Limits
Message Limits
Storage Limits
Gateway and Vproc Limits
Hash Bucket Limits
Interval Histogram Limits
Database Limits
Name and Title Size Limits
Table and View Limits
Spool Space Limits
BLOB, CLOB, XML, and Related Limits
User-Defined Data Type, ARRAY Data Type, and VARRAY Data Type Limits
Macro, UDF, SQL Procedure, and External Routine Limits
Query and Workload Analysis Limits
Secondary, Hash, and Join Index Limits
Reference Index Limits
SQL Request and Response Limits
Row-Level Security Constraint Limits
Session Limits
Appendix C Designing With Task-Oriented Profiles
Concepts, Policies, User Profiles, and Rules
Concepts, Policies, User Profiles, and Rules
Flow Diagram of the Database Structure
End User Profiles
Rules for End User Profiles
Rules for Batch_Profile
Rules for Query_Database
Rules for Upsert_Database
Appendix D Summary Physical Design Scenario
Prerequisites for the Process Review
Process Review
Appendix E Sample Worksheet Forms
Appendix F Designing Tables for Optimal Performance
Minimizing Table Size
Reducing the Number of Table Columns
Adjusting the DATABLOCKSIZE and MERGEBLOCKRATIO Table Parameters
Adjusting FREESPACE
Using Identity Columns, Compression, and Referential Integrity for Optimal Performance Design
Using Indexes to Enhance Performance
Understanding the Effects of Altering Tables
Appendix G Compression Methods
Multivalue Compression
Benefits of Multivalue Compression
Best Use of Multivalue Compression
Restrictions and Limitations of Multivalue Compression
Algorithmic Compression
Benefits of Algorithmic Compression
Best Use of Algorithmic Compression
Restrictions and Limitations of Algorithmic Compression
Block-Level Compression
Benefits of Block-Level Compression
Best Use of Block-Level Compression
Restrictions and Limitations of Block-Level Compression
Temperature-Based Block-Level Compression
Benefits of Temperature-Based Block-Level Compression
Best Use of Temperature-Based Block-Level Compression
Restrictions and Limitations of Temperature-Based Block-Level Compression
Row Compression
Benefits of Row Compression
Best Use of Row Compression
Restrictions and Limitations of Row Compression
Autocompression
Benefits of Autocompression
Best Use of Autocompression
Restrictions and Limitations of Autocompression
Row Header Compression
Benefits of Row Header Compression
Best Use of Row Header Compression
Restrictions and Limitations of Row Header Compression
Appendix H References
Glossary

Back to home page