Collecting Statistics on Join Index Columns - Advanced SQL Engine - Teradata Database
Database Administration
Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1093
lifecycle
previous
Product Category
Teradata Vantage™
Introduction
Overview
Prerequisites
Changes and Additions
Setting Up Your Administrative Environment: All DBAs
Overview
Logging in to the Operating System
About Root Logons
About Tdtrusted Logons
For More Information
About User DBC
Setting Up the Database Administrator User
Creating the Database Administrator Profile
Creating the Database Administrator User
Granting Database Privileges to User DBADMIN
Creating a Spool Reserve Database
Guidelines for Reserving Minimum Spool Space
Using Viewpoint Alerts
Setting Up Alert Actions
Creating Alerts
Setting Up Teradata Viewpoint Alerts for Space Usage
About the Global Default Parameters
About Databases and Users in Teradata: All DBAs
Overview
About Databases and Users
Space Used by Databases and Users
Creating Versus Owning Objects in the Hierarchy
About Creating a Database or User
The System Users
System User DBC
System User SYSTEMFE
System User SYSADMIN
Other System Databases and Users
Recommended Hierarchy
Teradata Secure Zones Overview
Working with Databases: All DBAs
Overview
About Database Creation
Best Practices for Database Creation
Creating the Tables Database
Creating the Views Database
Working with Table Access Privileges for Views
Dropping a Database or User
Transferring Ownership with GIVE
Working with Tables and Views: Application DBAs
Overview
Choosing a Primary Index
Guidelines for Choosing Primary Index Columns
Unique and Nonunique Primary Indexes
Row Partitioning
No Primary Index (NoPI) Tables
Relationship of Primary Indexes, Primary Keys, and Foreign Keys
Creating Tables
Converting Existing Database Tables to Vantage Tables
Considerations for Defining Tables
Creating Tables in Teradata
Copying a Table
Copying Statistics From a Base to a Target Table
Dropping a Table
Recreating a Table
Using INSERT…SELECT
Specifying Fallback Tables
Working with Views
Creating Views Procedure
Example: Creating a View to Limit User Access
Example: Creating a View to Generate a Report
Reference Information
Obtaining Information about View Columns
Using BTEQ Scripts to Create Database Objects
Reference Information
Working with Stored Procedures and User-defined Functions: Application DBAs
Overview
Creating Stored Procedures
Example 1: Compiling and Executing a Stored Procedure with a Cursor
Example 2: Creating a Stored Procedure to Update Accrued Interest
For More Information
Creating User-defined Functions
Example: Creating a User-defined Function To Verify a Date
For More Information
Working with Users, Roles, and Profiles: Operational DBAs
Overview
Overview of Establishing Users
User Access Methods
Types of Users
Assessing Database User Needs
Best Practices for Creating Users
Creating User Accounts
Reference Information
Creating Account Strings
Determining the Account for a User Session
Creating User Profiles
About Profiles
Creating Profiles
Dropping Profiles
Working with Database Users
About Users
Creating Users
Creating Temporary Passwords for First Login
Using Roles to Manage User Privileges
User Types and Minimum Required Privileges
Types of Privileges
Privileges That Must Be Explicitly Granted
Creating User Roles
Granting Privileges to a Role
Reference Information
Granting User Membership in a Role
Reference Information
Defining the Default Role for a User
Reference Information
Granting Privileges Directly To Users
Prerequisites
Example: Granting Privileges on Sys_Calendar to All Users
Reference Information
Granting Privileges to a User
Automatic Privileges for Creators
Automatic Privileges for Created Users and Databases
Reference Information
About Viewpoint Monitoring Privileges
Setting Up Teradata Viewpoint Users
Reference Information
Configuring User Dashboards
Creating a Dashboard
Loading and Exporting Data: Application DBAs
Overview
About Teradata Parallel Transporter
About the Load Operator
About the Update Operator
About the Export Operator
About Basic Teradata Query for Loading Tables
Differences Between BTEQ and Teradata PT
Updating Tables
Loading Data into Teradata Vantage
Reference Information
Restarts and Aborts on BTEQ Jobs with Identity Column
About Reading Committed Data While Loading to the Same Table
About Cleaning up Load Isolated Tables
About FastLoad
About FastExport
About MultiLoad
Maximum Number of Concurrent FastLoad, MultiLoad, and FastExport Jobs
Utility Management
Teradata Parallel Data Pump
Restarts on TPump Jobs with Identity Column
Loading Geospatial Data
Loading Unicode Data with Unicode Pass Through
Interpreting LOAD Utility Status
Choosing the Best Utility for Your Purpose
Definitions
Guidelines for Inserting Rows
Guidelines for Deleting Rows
Guidelines for Other Batch Operations
Utility Job Performance Analysis and Capacity Planning
Examples of Using the DBQL Utility Job Log Table
Working with Sessions and Accounts: Operational DBAs
Overview
About Session Modes and Transaction Processing
Transaction Processing in ANSI Session Mode
Transaction Processing in Teradata Session Mode
Setting the Session Mode
Reference Information
Checking for Logged On Sessions
Obtaining Session Information
Troubleshooting Problems with Sessions
Working with Accounts
Purpose of System Accounting
Creating Accounts
Working with System Accounting Views
About DBC.AccountInfoV
About the DBC.AMPUsage View
Logging Resource Usage Data with Account String Variables
Enabling Account String Expansion
Setting the Default Account String to Use ASE
Examples
About the ASE Substitution Variables
Restrictions and Usage Rules
About Using ASE With Client Utilities
Managing Space: Operational DBAs
Overview
About Types of Space
Capacity Planning
About Global Space Accounting
About Fixing Issues with Space Accounting
FixAllocatedSpace Procedure
FixCurrentSpace Procedure
About Identifying and Correcting System-level Space Problems
About Using the System Health Portlet to Find System Space Issues
Identifying Space Problems Using Viewpoint Space Usage Portlet
Identifying Space Issues by Querying System Views
Querying the DiskSpaceV View to Find System Space Issues
Querying the TableSizeV View
Warning About the AllSpaceV View
Example Requests for Space Information
Transferring Ownership and Permanent Space
Transferring Permanent Space
Reallocating Perm Space to Resolve Space Issues
Increasing the Spool Space Limit for a User
Finding and Fixing Skewed Tables by Querying the TableSizeV View
Example of Finding Skewed Tables by Querying the TableSizeV View
About Defining Temporary Space Limits
About Protecting Transactions by Reserving Cylinders for Perm Space
About Creating a Macro for Space Usage Reporting
About Viewing Space Usage Trends for Databases and Users
Other Ways to View Space Utilization
Teradata Studio
The Ferret Utility and Equivalent Macros
Data Space Tool Macros
About Managing Data Blocks
About Setting Data Block Sizes Using SQL
About Setting Data Block Sizes Using the DBS Control Utility
About Setting Data Block Merging Limits
About Managing Cylinders
About Setting Free Space Percent Limits
About Finding Loosely Packed Tables with the SHOWFSP Command
About Freeing Cylinders Using PACKDISK
About Giving One User to Another
About Adjusting Perm Space Limits Using SQL
Adjusting Perm Space Limits Using Viewpoint
Maintaining the Database: Operational DBAs
Overview
About Database Maintenance Tasks
Managing Accumulated Log Data
About Database Logs
Methods for Maintaining Database Logs
Setting Up Automated PDCR Log Maintenance
Performance Data Collection Portlet Jobs
Reference Information
Deleting Old Log Data with Teradata Viewpoint
Deleting Old Log Data Manually from System Tables
About Managing Vproc Status and Initializing AMP Disks with Vproc Manager
About Maintaining Data Dictionary Tables
About Purging the System Logs
Clearing Out Values Manually in the DBC.Acctg Table
About Archiving and Resetting Accumulators and Peak Values
About Correcting DBC.DataBaseSpace and DBC.DBase Values
About Collecting Statistics on Data Dictionary Tables
Data Dictionary Tables That Require Maintenance
Housekeeping on an Ad-Hoc Basis
Updating Software (Applying Proper Patches)
Archiving, Restoring, and Recovering Data: Operational DBAs
Archive/Restore Utility Support
Managing Database Resources: Operational DBAs
Overview
About Managing I/O with Cylinder Read
About Tracking Cylinder Read Resource Usage
Managing the Database Workload with Teradata Active System Management
Determining Workload Management Needs
Defining a Ruleset
About Ensuring a Consistent Response Time for a Workload
Managing Sessions and Transactions with Query Banding
About Defining Name-Value Pairs for Session and Transaction Query Bands
Example: Setting a Query Band For a Session
Example: Removing a Query Band From a Session
Example: Setting a Query Band for the Current Transaction
About Defining Name-Value Pairs for Profile Query Bands
Example: Creating a Query Band in a Profile
About Defining Profile Query Bands with Ignore Query Band Values
Example: Creating a Query Band in a Profile with Ignore Query Band Values
About Query Bands and Proxy Users
Finding the Origin of a Query Using Query Bands
Other Uses for Query Bands
For More Information
Tracking Object Use and Table Changes
Objects That Can Be Tracked
Table Changes That Can Be Tracked
Enabling Object Use Counts
Controlling How Often Counts Are Recorded
Views That Report Count Data
Resetting Use Counts
Usage Recommendations
Example: Object Use Counts for Tables
About Analyzing Trends with Account String Expansion Variables
About Resource Usage Trend Analysis
About ResUsage Macros and Tables
Enabling RSS Logging
Managing Queries: Operational DBAs
Overview
Redrive Protection for Queries
Enabling Redrive Protection
Disabling Redrive Protection
Setting Redrive Protection for Users, Accounts, and Profiles
Redrive Restrictions
Example: Redrive Settings and Session Behavior
Example: Redrive Recommendations
Reference Information
Recommendations for Common Query Problems
Identifying Poorly Performing Queries
Controlling Poorly Performing Queries
Finding and Fixing Skewed Tables
Finding and Resolving Lock Contentions
About Lock Contentions
Investigating Query Blocks and Delays
Improving Query Performance Using COLLECT STATISTICS: Application DBAs
Overview
About Statistics Collection
Required Privileges
Resource Considerations
About Automated Statistics Management
When To Collect Statistics
Collecting Statistics
Recommendations for Large Tables
Reference Information
About Stale Statistics
About SUMMARY Statistics
Skipping Unneeded Statistics Recollection with the THRESHOLD Option
Sampling Statistics with the USING SAMPLE Option
Example: User-Specified Sample Percentage
Example: System-Determined Sample Statistics
For More Information
Working with Special Cases
Collecting Statistics on Tables Protected with Row-Level Security
Collecting Statistics on Null
Collecting Statistics on the Data Dictionary
Tracking Query Behavior with Database Query Logging: Operational DBAs
Overview
DBQL Overview
DBQL Uses
How to Use DBQL
Overview of Collection Options
Best Fit Rule Hierarchy
Rules Validation
SQL Statements That Should Be Captured
SQL Logging Statements
SQL Logging Considerations
XML Query Plan Logging
Capturing DBQL Through the Account String
SQL Logging by Workload Type
SQL Statements to Control Logging
BEGIN/REPLACE/END QUERY LOGGING Statements
WITH Logging Options
LIMIT Logging Options
Mode Logging Option
Setting DBQL Logging Algorithm
DBQL Macros
DBQL Tables
About Querying DBQL Tables
DBQL Views
Shredding the Lock Plan Information in the XML Lock Log Table, DBQLXMLLockTbl
Shredding the Data in the DBQLXMLLockTbl
Output of Shredding
Shredded_LockTbl: Blocking Transaction Lock Shredding Target Table
Investigating Deadlocks: Querying Shredded_LockTbl
Shredded_TransTbl: Blocked Transaction Lock Shredding Target Table
Query Data Storage and Protection
Protection for a Committed Row
Things to Consider When Logging DBQL Data
Logging Overhead
Options for Flushing the DBQL Cache
About Flushing the DBQL Caches Manually
Changing DBQL Cache Size
Changing the DBQL Performance Stats Cache Size
Logging Scenarios
Scenarios of Logging Accounts
Scenarios of Detailed Logging
Scenarios of Threshold and Summary Options
Example of OBJECT Data for One Query
Example of STEP Data for One Query
Examining the Logged Data
Comparing CollectTimeStamp Value Between Tables
Using QueryID for Joins on DBQL Tables
Zone-level Query Logging
About Query Logging Privileges in a Zoned System
For More Information
Maintaining the Logs
Methods for Minimizing Log Table Size
DBQL Objects You Cannot Change
Archiving DBQL Data
Reviewing or Ending Current Rules
Reviewing Rules
About Ending DBQL Logging for Specific Users and/or Accounts
About Ending All DBQL Logging
Effects of Dynamically Enabling/Replacing/Disabling Logging on Current Rules
Granting DBQL Administrative Privileges to Other Users
Analyzing Requests and Request Plans: Application DBAs
Overview
Using EXPLAIN to Analyze Request Plans
About the EXPLAIN Request Modifier
Generating an EXPLAIN with Teradata Studio
Generating an EXPLAIN with BTEQ
Capturing Request Plan Steps
About the Query Capture Facility
About Capturing Request Plans Using DBQL
Relationship Between BEGIN QUERY CAPTURE and BEGIN QUERY LOGGING
DBS Control Parameters for Tuning the Workload Cache Limits for Index and Partition Analysis
About the Query Capture Database
Deleting Query Plans from a QCD
Creating the Query Capture Database Tables
About Creating QCD Tables
Procedure Using the Visual EXPLAIN Utility
Procedure Using BTEQ
Dropping Query Capture Database Tables
About Dropping QCD Tables
Procedure Using Visual EXPLAIN
Procedure Using BTEQ
About Querying QCD Tables
About QCD Request Macros and Views
QCD Macro and View Versions
User Categories
Specific User Category Privileges
Building Baseline Transaction Profiles
Working with System Information and Global Defaults: Operational DBAs
Overview
Viewing the Software Release and Version
Reviewing or Changing System-Wide Parameters
About International Character Set Settings and Defaults
International Language Support Mode
Default Server Character Sets
Client Character Sets
Changing Character Set Defaults
Changing Collation Defaults
Changing Time Zones, Currencies, and Formats for Date, Time, and Numbers
Changing Time Zone Strings and Rules
Client Configuration Overview
Teradata Director Program for Mainframe-Attached Clients
Workstation-Attached Clients
Communicating with Teradata Vantage
Sessions Defined
Controlling Session Defaults
Client-Server Applications Connectivity
Mainframe Environment
Background
CP and CUA
Software Components
Mainframe Sessions
Session Pools
TDP Functionality
TDP Exits
Memory Management
Using TDP Commands
Network Environment
Functionality
Software Components
The Teradata Gateway
Displaying Network and Session Information
Controlling Network Sessions
Controlling Trace Logs
For More Information
Troubleshooting: Operational DBAs
Overview
Tools for Troubleshooting and Administrating
Tools for Troubleshooting Client Connections
Mainframe Connection Tools
Network Connection Tools
Troubleshooting Spool Space Problems
Using the Update Space Utility
Adjusting for Low Available Free Memory
Solving Partitioning and RI Validation Errors
Incident Information Checklist
Monitoring Transaction Recovery
Starting Recovery Manager
Stopping Recovery Manager
Recovery Manager Commands
Resolving Orphan or Phantom Spool Issues
Repairing Data Corruption
Using Maps to Position Table Data across AMPs: All DBAs
Map Overview
Scenarios for Using Maps to Move Table Data
Enabling Teradata MAPS Architecture
Initial Setup for Managing Maps
A Simple Process for Changing the Map for a Table
Viewpoint Portlet for Analyzing and Optimizing Map Placement
Introduction to the Advisor and Mover Tools
Enabling Step Logging To Aid in Map Analysis
About Excluding Objects from Map Reassignments
Analyzing Maps
About the ActionsTbl
Reviewing Recommended Map Actions
Customizing the List of Recommended Map Actions
Renaming Maps after a System Expansion
Sessions Used for Reassigning Maps
About Estimating the Time Needed to Reassign Maps
Decisions Required before Reassigning Maps
Reassigning the Map for a Table
About Limiting the Resources for Map Reassignments
About Stopping Map Reassignments
Stopping the Map Reassignments Gracefully
Stopping Map Reassignments Abruptly
About Managing Restarts
Restart Cleanup during the Advisor Process
Restart Cleanup during the Mover Process
Other Cleanup Tasks
About Handling a Second Contiguous Map after a System Expansion
About Removing Historical Data
About Adjusting Space Limits and Skew Settings
Performance Management: All DBAs
Overview
Implementing Performance Management
About Performance Management Setup
Monitoring the System
Measuring System Conditions
Using Alerts to Monitor the System
Suggested Alerts and Thresholds
Weekly and/or Daily Reports
Collecting Statistics
Guidelines for Collecting Statistics
Collecting Statistics on Skewed Data
Collecting Statistics on Join Index Columns
About PARTITION Statistics
Setting Up Database Query Logging
Performance Effects of Query Logging
Performance Information in DBQL Tables
Recommended DBQL Logging Scenarios
DBQL Collection Recommendations
Using DBQL Tables
Collecting Useful DBQL Historical Data
Managing Query Performance
Finding and Fixing Problem Queries
Alternate Query Investigation Methods
Managing Skewed Processing
Skewed Query Processing Across Nodes
Skewed Query Processing Across AMPs
Identifying and Fixing Skewed Tables
Performance Effects of Skewed Row Distribution
Identifying Skewed Distribution of Table Data
System Tables Subject to Skewing
Using Teradata Viewpoint to Find Skewed Tables
Finding Skewed Tables in DBC.TableSizeV
Finding Uneven Distribution Using Hash Functions
Primary Index and Skewed Row Distribution
Identifying and Managing Blocked Queries
Locking Overview
Investigating Query Blocks and Delays
Reducing Deadlocks
Handling Blocked Internal DBQL Requests
Working with Transaction Rollbacks
Effects on Performance
RollbackPriority
Minimizing or Avoiding Rollbacks
Detecting a Rollback in Progress
Example
Canceling Rollbacks
Noticing a Slow Rollback of an ALTER TABLE Statement
Managing the Database Workload
Evaluating Peak Utilization Periods
Assessing a Busy System
Job Scheduling Around Peak Utilization
Using Canary Queries
ASE Impact on PE and AMP Performance
Workload Priority Management
Charge Back
Managing Database Resources to Enhance Performance
Performance and Space Management
Managing System Disk Space
Managing Spool Space
Managing Cylinders
Freeing Space on Cylinders
Managing I/O with Cylinder Read
Managing Free Space
Determining a Value for FSP
PACKDISK and FreeSpacePercent
Data Compression and Performance
Managing Data Block Usage
Performance and Memory Management
About Shared Memory
About Memory-Consuming Features
Investigating the Need to Add Memory
ResUsage and Cache Hit Rates
Using Memory Effectively
About Managing Free Memory
Managing FSG Cache
Using DBS Control Fields to Manage Memory
Periodic Maintenance and Performance
Cleaning Up Logs
DefragLowCylProd
Capacity Planning
Monitoring CPU Usage
Monitoring Disk Utilization
Monitoring Traffic
Performance Considerations when Expanding or Upgrading the Database System
Adding Disk Arrays
Adding Vprocs
Adding Memory
Scaling
Checking the Performance of an Upgrade
Considerations for Administering a System with Teradata Unity : All DBAs
Overview
Creating a Database or User
Creating Roles and Profiles
Creating Tables and Views
Querying the AMPUsage and AccountInfoV Views
Managing Space
Restarting Teradata Vantage
Investigating Lock Contention
Analyzing Queries and Query Plans
Making Global and User-Level Configurations Consistent
For More Information
Teradata System Limits
Overview
System Limits
Database Limits
Session Limits
Handling Teradata Crashdumps: Operational DBAs
Overview
Teradata Vital Infrastructure and Crashdumps
TSET File Pack and the DBC.TSETQueryText Table
Crashdumps Overview
Dump Types
Teradata Crashdumps Processes
Teradata Crashdump Formats
Teradata Crashdump Sizes
Viewing Teradata Crashdump Messages
Locating Crashdumps
Configuring the Crashdumps Database
Ad-Hoc Creation of DBC.Crashdumps with DIPCRASH
Checking DBC.Crashdumps Space Allocation and Usage
Allocating More Space to DBC.Crashdumps Database
Automatic Versus Forced Crashdump
Automatic Teradata Crashdumps
Forced Teradata Crashdumps
Commands for Forcing a Teradata Crashdump
Handling Teradata Crashdumps
Save Considerations
Listing Teradata Crashdumps Using CSP
Manually Saving Teradata Crashdumps to the Crashdumps Database
Manually Saving Crashdumps to Another Teradata System
Manually Saving Teradata Crashdumps to Stream Files
Handling an Interrupted Dump Save Operation
Error Logging
Saving Crashdumps to Disk
Sending Crashdumps to Teradata
Debugging Crashdumps
Attaching a Raw Dump and Displaying the Backtrace
Deleting Teradata Crashdumps
Fault Isolation Diagnostics
Troubleshooting Crashdumps
Additional Information
Teradata Links
For non-sparse join and hash indexes, the Optimizer inherits the row count and all histograms from the base tables. Teradata recommends that you collect statistics on base tables only and not on the non-sparse join or the hash indexes.
For sparse join indexes, Teradata recommends that you collect statistics on base tables and sparse join indexes separately.
For non-compressed join indexes, the Optimizer performs all-AMP sampling to estimate the join index row count accurately.
For compressed sparse join indexes, Teradata recommends that you collect PI statistics. The All-AMP random sampling that the Optimizer performs may not reflect an accurate row count because of the compression.