Teradata Publications Related to Database Design - Teradata VantageCloud Lake
Lake - Database Reference
Deployment
VantageCloud
Edition
Lake
Product
Teradata VantageCloud Lake
Release Number
Published
February 2025
ft:locale
en-US
ft:lastEdition
2025-11-21
dita:mapPath
ohi1683672393549.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
ohi1683672393549
Database Reference
Database International Character Set Support
Managing International Language Support
Implementation Overview
Character Set Categories
System Processing of Character Data
System Default Character Support
Default Client Character Set Support
US-ASCII Character Set
US-ASCII
Differences between TD-ASCII and US-ASCII
US-ASCII Repertoire Comparison
Usage Notes
Use Cases for US-ASCII
Example: US-ASCII Attempted Import of a French String Literal
Example: US-ASCII Attempted Usage of a French Object Name
Standard Language Support Mode
Object Names
Object Naming Controls
Rules for Object Naming
UNICODE Characters Allowed in Object Names
Display of Incompatible Object Names and Literals
Client Character Set Options
Teradata Support for Common Client Character Sets
Client Character Set Support
Client Character Set Compatibility with Server Character Sets
Client Character Set Compatibility by Client Type
EBCDIC Client Character Set Support
ASCII Client Character Set Support
UTF8 Client Character Set Support
UTF16 Client Character Set Support
Endianness
Error Messages
Using Standard Teradata Client Character Sets
System Determination of Client Character Set
Assigning the Default Client Character Set during Client Setup
Assigning the Default Client Character Set by HostId
DBC.HostsInfoV View
Finding the LogicalHostId
Assigning the Default Client Character Set for a Session
Server Character Sets
Character Data Storage
Working with Server Character Set Defaults
Default Server Character Set Options
Specifying the Default Server Character Set
LATIN Server Character Set
Client Character Sets Supported with LATIN
Differences between ASCII and Teradata LATIN
Detecting ASCII and Teradata LATIN Differences
Using LATIN Characters above ASCII X'7F'
Supported Characters
UNICODE Server Character Set
Sharing UNICODE Data among Heterogeneous Clients
Supported UNICODE Client Character Sets
UNICODE Server Character Set Definition
Areas of Unicode
KANJISJIS Server Character Set
Sharing KANJISJIS Data among Heterogeneous Clients
Supported KANJISJIS Client Character Sets
KANJISJIS Character Set Limitations
GRAPHIC Server Character Set
Collation Sequences
Standard Collation Sequences
Collation Sequence Ordering
ASCII and EBCDIC Collations
CHARSET_COLL Collation
JIS_COLL Collation
MULTINATIONAL Collation
Available Collation Sequences
Multilevel Collation
MULTINATIONAL Collation Rules
Example: Teradata Standard Multinational Collation Equivalence Class
Equivalence Classes and Ordering in Class
Making MULTINATIONAL the Default Collation Sequence
MULTINATIONAL Collation Related Information
Character Conversion
External Character Conversion
Application Model with Single Form of Use
Example: UTF8 Form-of-Use
Example: Data Returned in ASCII
Working with Export Widths
Interpret the Export Width Rule Set for EXPECTED
Viewing the Current Export Widths for a Session
Exception Handling
Illegal Form-of-Use
E2I Conversion Exceptions
I2E Conversion Exceptions
Error Characters
Truncation Exceptions
Truncation Exceptions and Fixed Length Columns
Truncation Exceptions When Converting Fixed Length Columns
Truncation of a Pass Through Character (PTC)
Other Truncation Exceptions
Truncation and Transaction Mode
Truncation Exceptions in ANSI Mode
Truncation Exceptions in Teradata Mode
Truncation Examples
UNICODE PASS THROUGH
Pass-Through Characters
Enabling and Disabling UNICODE PASS THROUGH
UNICODE PASS THROUGH Functionality
Importing Unicode Characters in a Pass Through Session
Exporting from a UNICODE Server Character Set
Ill-formed Code Unit Sequences
UNICODE PASS THROUGH Usage Notes
UPT Restrictions
Character Shorthand Notation
Japanese Encodings and Mapping Standards
JIS X 0201
JIS X 0208
Shift-JIS (DOS Kanji) Encoding
Shift-JIS (DOS Kanji) Usage Notes
Shift-JIS Encoding
Selected Characters for Shift-JIS Kanji
Shift-JIS Encoding for Kanji
Shift-JIS Encoding: Detailed View
Shift-JIS (DOS Kanji) Related Information
ASCII Mappings
Client ASCII to Server LATIN
Client ASCII to Server Unicode
EBCDIC Mappings
Client EBCDIC to Server LATIN
Client EBCDIC to Server Unicode
Mappings for String Functions
Pad Character Translation
Space Folding
Non-GRAPHIC Characters
UNICODE Halfwidth to Fullwidth
UNICODE Fullwidth to Halfwidth
UNICODE to LATIN
LATIN Uppercase to Lowercase
Multibyte KANJISJIS Lowercase to Uppercase
Single-Byte KANJISJIS Lowercase to Uppercase
UNICODE Lowercase to Uppercase
UNICODE Uppercase to Lowercase
KanjiShiftJIS Mappings
KANJISJIS to KANJISJIS Single-Byte
UNICODE to KanjiShiftJIS Single-Byte
Character Set Files
Download Character Set Files as Zip File
Data Dictionary
What the Data Dictionary Stores
Data Dictionary Views
Download Teradata Studio or Teradata Studio Express
Unicode Views
Compatibility Views
X Views
Non-X Views
Other Views
Access to Data Dictionary Views
Default PUBLIC Privileges for Views
Views for Which PUBLIC Privileges Are not Granted by Default
Compatibility View Privileges
Trusted Sessions Support in X Views
Querying Data Dictionary Views
How to See All Columns of a View
Querying X and Non-X Views
Using HELP and COMMENT
Tracking Resource Usage
AllSpace and TableSize Views
AMPUsage View
Example: Using DiskspaceV
Compiling AMPUsage Statistics
Tracking User Activity
Tracking Logon Rules
Tracking Privileges
Accessing PM/API-Related Data in the Data Dictionary
Example: Using SessionInfoV to Determine Who Is Using the Monitor
Example: Using AllRightsV to Determine User Access Rights
Example: Using LogOnOffV to Determine Which Users Were Forced Off the System
Using System Views
System Calendar View
Sys_Calendar.CALENDAR_TD1310
To Revert the View to the Current Version
Privilege
Example: Using Sys_Calendar.Calendar
SQLJ System Views
JAR_JAR_USAGE View
Example: Using SQLJ.Jar_Jar_Usage
JARS View
Example: Finding JAR Objects Accessible to a USER from SQLJ.Jars
ROUTINE_JAR_USAGE View
Example: Determining Users JAR Objects Using SQLJ.Routine_JAR_Usage
User Installed Files View
Using Unicode Views to Update Object Names
Teradata QueryGrid
TDMaps
Views Reference
Data Dictionary View Categories
TDMaps Views
AccessLogV
Usage Notes
Example: Using AccessLogV
AccLogRulesV
Usage Notes
Example: Using AccLogRulesV
AccountInfoV[X]
Usage Notes
Example: Using AccountInfoV
Related Topics
ActionHistoryV[X]
Usage Notes
ActionsV[X]
Usage Notes
Example: Updating ActionsTbl Row
All_RI_ChildrenV[X]
Usage Notes
All_RI_ParentsV[X]
Usage Notes
AllGlobalSpaceV[X]
Usage Notes
AllRightsV[X]
Usage Notes
Example: Using AllRightsV
AllRoleRightsV
Usage Notes
Example: Using AllRoleRightsV
AllSpaceV[X]
Usage Notes
Example: Using AllSpaceV
AllTempTablesV[X]
Example: Using AllTempTablesVX
AMPUsageV[X]
Usage Notes
Example: Using AMPUsageV
Related Topics
ArchiveLoggingObjsV[X]
Example: Using ArchiveLoggingObjsV
AssociationV[X]
Usage Notes
Example: Using AssociationV
Related Topics
AuthorizationsV[X]
Usage Notes
Example: Using AuthorizationsV
BusinessCalendar
Usage Notes
Examples: Queries of Calendar Data
Related Topics
BusinessCalendarExceptions
Usage Notes
BusinessCalendarPatterns
Usage Notes
CDSTableSizeV
CharSetsV
Usage Notes
Example: Using CharSetsV
CharTranslationsV
Usage Notes
Example: Using CharTranslationsV
ChildrenV[X]
Usage Notes
Example: Using ChildrenV
CloudGrantsV[X]
ComputeGroupStatusV[X]
ComputeGroupsV[X]
ComputeMapsV
ComputeProfilesV[X]
ComputeStatusV[X]
CollationsV
Usage Notes
Example: Using CollationsV
Related Topics
ColumnStatsV
Usage Notes
To Get Information Not Contained in This View
Example: Using ColumnStatsV
Related Topics
ColumnsV[X]
Usage Notes
Example: Using ColumnsV
Example: Select the CommentString Column from ColumnsV
Related Topics
ColumnUseCountV[X]
Usage Notes
Example: Using ColumnUseCountV
ConnectRulesV
Example: Select the CONNECT THROUGH Privileges for All Proxy Users from ConnectRulesV
Example: Select the CONNECT THROUGH Privileges for a Proxy User from ConnectRulesV
ConstraintFunctionsV
ConstraintValuesV
CostProfiles_v
Usage Notes
CostProfileTypes_v
Usage Notes
CostProfileValues_v
Usage Notes
Database_Default_JournalsV[X]
Usage Notes
Example: Using Database_Default_JournalsV
Databases2V[X]
Usage Notes
DatabasesV[X]
Usage Notes
Example: Using DatabasesV
DatabaseUseCountV[X]
Usage Notes
Example: Using DatabaseUseCountV
DatasetSchemaDependenciesV
Usage Notes
DatasetSchemaInfoV
Usage Notes
DBCInfoV
Example: Using DBCInfoV
DBQLRulesV
Usage Notes
Example: Using DBQLRulesV
DeleteAccessLogV
Usage Notes
Example: Using DeleteAccessLogV
DeleteOldInDoubtV
Usage Notes
Example: Using DeleteOldInDoubtV
DeleteUseCountV[X]
Usage Notes
Example: Using DeleteUseCountV
DiskGlobalSpaceErrorV
Usage Notes
DiskSpaceErrorV
Usage Notes
Related Topics
DiskSpaceV[X]
Usage Notes
Examples: Using DiskSpaceV
ErrorTblsV[X]
Usage Notes
Example: Using ErrorTblsV
Events_ConfigurationV[X]
Usage Notes
Example: Using Events_ConfigurationV
Events_MediaV[X]
Usage Notes
Example: Using Events_MediaV
EventsV[X]
Usage Notes
Example: Using EventsV
ExclusionListsV[X]
Usage Notes
ExportWidthV
How to Interpret ExportWidthRuleSet Column Values
Related Topics
ExpStatsV
Usage Notes
To Get Information Not Contained in This View
Example: Using ExpStatsV
Related Topics
ExternalSPsV[X]
Usage Notes
Example: Using ExternalSPsV
ForeignTablesInfoV[X]
ForeignTablesV[X]
FunctionAliasInfoV[X]
Usage Notes
FunctionAliasV[X]
FunctionsV[X]
Usage Notes
Example: Using FunctionsV
GlobalDBSpaceV[X]
Usage Notes
Example Requests for Space Information
HostsInfoV
Usage Notes
Example: Using HostsInfoV
IndexConstraintsV[X]
Usage Notes
Example: Select a List of Tables and Join Indexes with Partitioning from IndexConstraints
Example: Select a List of Tables and Join Indexes with Single-level Partitioning from IndexConstraints
IndexStatsV[X]
Usage Notes
To Get Information Not Contained in This View
Example: Using IndexStatsV
Related Topics
IndexUseCountV[X]
Usage Notes
Example: Using IndexUseCountV
IndicesV[X]
Usage Notes
Example: Using Indices
InDoubtLogV
Usage Notes
InsertUseCountV[X]
Usage Notes
Example: Using InsertUseCountV
JoinIndicesV
Usage Notes
Example: Create Join Index
Example: Using JoinIndicesV
JournalsV[X]
Example: Using JournalsV
LogOnOffV[X]
Usage Notes
Example: Using LogOnOffV
Related Topics
LogonRulesV
Usage Notes
MapGrantsV[X]
Usage Notes
MapListsV[X]
Usage Notes
MapsV[X]
Usage Notes
MultiColumnStatsV
Usage Notes
To Get Information Not Contained in This View
Example: Using MultiColumnStatsV
Related Topics
MultiExpStatsV
Usage Notes
To Get Information Not Contained in This View
Example: Using MultiExpStatsV
Related Topics
MultiVersionInfoTblV[X]
OFSTablesV[X]
ObjectsInSparseMapsV
Usage Notes
ObjectListsV[X]
Usage Notes
ObjectUseCountV[X]
Usage Notes
Example: Using ObjectUseCountV
PartitioningConstraintsV[X]
Usage Notes
Examples
Related Topics
PeriodsV[X]
Usage Notes
Example: Using PeriodsVX
ProfileAsgdSecConstraintsV[X]
Usage Notes
ProfileInfoV[X]
Usage Notes
Example: Using ProfileInfoV
QryLockLogXMLV
Usage Notes
Example: Using QryLockLogXMLV
Related Topics
QryLogAmpDataV
Example: Using QryLogAmpDataV
QryLogClientAttrV
Usage Notes
Examples: Using QryLogClientAttrV
QryLogEventHisV
Usage Notes
Example: Using DBC.QryLogEventHisV
Example: Selecting All Columns from QryLogEventHisV
Example: Using QryLogEventHisV to Show Flex Throttle Events
QryLogEventsV
Usage Notes
Example: Using QryLogEventsV
Example: Using QryLogEventsV to Query Flex Throttle Columns
QryLogExceptionsV
Usage Notes
Example: Using QryLogExceptionsV
QryLogExplainDocV
Usage Notes
Example: Using QryLogExplainDocV
Improving Explain Text Readability
QryLogExplainV
Usage Notes
Example: Using QryLogExplainV
QryLogExtAmpDataV
Example: Using QryLogExtAmpDataV
QryLogExtAmpDataV1V
Example: Using QryLogExtAmpDataV1V
QryLogExtAmpDataV2V
Example: Using QryLogExtAmpDataV2V
QryLogExtAmpDataV3V
Example: Using QryLogExtAmpDataV3V
QryLogExtAmpDataV4V
Example: Using QryLogExtAmpDataV4V
QryLogExtAmpDataV5V
Example: Using QryLogExtAmpDataV5V
QryLogExtAmpDataV6V
Example: Using QryLogExtAmpDataV6V
QryLogExtAmpDataV7V
Example: Using QryLogExtAmpDataV7V
QryLogExtPeDataV
Example: Using QryLogExtPeDataV
QryLogExtPeDataV1V
Example: Using QryLogExtPeDataV1V
QryLogExtPeDataV2V
Example: Using QryLogExtPeDataV2V
QryLogExtPeDataV3V
Example: Using QryLogExtPeDataV3V
QryLogExtPeDataV4V
Example: Using QryLogExtPeDataV4V
QryLogExtPeDataV5V
Example: Using QryLogExtPeDataV5V
QryLogExtPeDataV6V
Example: Using QryLogExtPeDataV6V
QryLogExtPeDataV7V
Example: Using QryLogExtPeDataV7V
QryLogFeatureListV
Usage Notes
Examples: Using QryLogFeatureListV
QRYLOGFEATUREUSECOUNTV
Usage Notes
QryLogFeatureUseJSON
Usage Notes
Example: Using QryLogFeatureUseJSON
QryLogObjectsV
Usage Notes
Example: Using QryLogObjectsV
QryLogParamJSON
Usage Notes
Example: Select JSON Data from QryLogParamJSON
Example: Select PARAMINFO from QryLogParamJSON
Example: Extract JSON Name/Value Pairs from the JSON Document
Example: Extract Specific JSON Values from the JSON Document
QryLogParamV
Usage Notes
Example: Using QryLogParamV
QryLogSQLDocV
Usage Notes
Example: Using QryLogSQLDocV
Improving SQL Text Readability
QryLogSQLV
Usage Notes
Example: Using QryLogSQL
QryLogStepsV
Usage Notes
Step Descriptions
Example: Using QryLogStepsV
Example: Using QryLogStepsV with ServerByteCount
QryLogSummaryV
Usage Notes
Rows Generated by the THRESHOLD Option
Rows Generated by the SUMMARY Option
Example: Using QryLogSummaryV
QryLogTdwmSumV
Usage Notes
Example: Select from DBC.QryLogTDWMSum
QryLogTDWMV
Usage Notes
Example: Select from QryLogTDWMV
QryLogUtilityV
Usage Notes
Example: Using QryLogUtilityV
QryLogV
Usage Notes
Example: Select a Specific QueryID from QryLogV
Example: Track Data Volume for Specific Load Jobs from QryLogV
Example: Using StmtDMLRowCount
QRYLogXMLDocV
Usage Notes
Example: Export XML Documents from QryLogXMLDocV
Example: Verifying XMLTextDoc is a Valid XML Document
Example: Serialize XMLTextDoc Document into a String
Improving XML Text Readability
Related Topics
QryLogXMLV
Usage Notes
Examples Using QryLogXMLV
Related Topics
RCC_ConfigurationV[X]
Example: Using RCC_ConfigurationV
RCC_MediaV[X]
Example: Using RCC_MediaV
ReconfigDeleteOrderV
Usage Notes
ReconfigInfoV
Usage Notes
ReconfigRedistOrderV
Usage Notes
ReconfigTableStatsV
Usage Notes
RepCaptureRulesV
Example: Using RepCaptureRulesV
RepTablesV[X]
ResolvedDTSV[X]
Usage Notes
Example: Using ResolvedDTSV
RestrictedWordsV
RI_Child_TablesV[X]
Usage Notes
RI_Distinct_ChildrenV[X]
Usage Notes
RI_Distinct_ParentsV[X]
Usage Notes
RI_Parent_TablesV[X]
Usage Notes
RoleInfoV[X]
Example: Using RoleInfoV
RoleMembersV[X]
Usage Notes
Example: Using RoleMembersV
SecConstraintsV[X]
Usage Notes
SecurityDefaultsV
Related Topics
SecurityLogV[X]
Usage Notes
ServerInfoV[X]
Usage Notes
ServerV[X]
Usage Notes
SessionInfoV[X]
Usage Notes
Example: Using SessionInfoV
Related Topics
SettingsV
Usage Notes
SHOWCOLCHECKSV[X]
Usage Notes
SHOWTBLCHECKSV[X]
Usage Notes
Software_Event_LogV
Usage Notes
Example: Using Software_Event_LogV
SparseMapAmpsV
Usage Notes
StatsV
Usage Notes
To Get Information Not Contained in This View
Example: Using StatsV
Related Topics
StatUseCountV[X]
Example: Using StatUseCountV[X]
StorageGrantsV[X]
StorageNVPInfoV[X]
StorageV[X]
Table_LevelConstraintsV[X]
Usage Notes
Tables2V[X]
Usage Notes
Tables3V[X]
Usage Notes
TableSizeV[X]
Usage Notes
Examples: Using TableSizeV
TableStatsV
Usage Notes
To Get Information Not Contained in This View
Example: Using TableStatsV
Related Topics
TablesV[X]
Usage Notes
Example: Using TablesV
Related Topics
TableTextV[X]
Usage Notes
Example: Using TableTextV
TableToSparseMapSizingV[X]
Usage Notes
TblSrvInfoV[X]
Usage Notes
TblSrvV[X]
Usage Notes
TempTableStatsV
Usage Notes
Related Topics
TriggersV[X]
Usage Notes
Example: Using TriggersV
Related Topics
UpdateUseCountV[X]
Usage Notes
Example: Using UpdateUseCountV
User_Default_JournalsV[X]
Example: Using User_Default_JournalsVX
UserGrantedRightsV
Usage Notes
Example: Using UserGrantedRightsV
UserRightsV
Usage Notes
Example: Using UserRightsV
UserRoleRightsV
Usage Notes
Example: Using UserRoleRightsV
UsersV
Usage Notes
Example: Using UsersV
Related Topics
UsrAsgdSecConstraintsV[X]
Usage Notes
ZoneGuestsV[X]
Usage Notes
ZonesV[X]
Usage Notes
Data Dictionary Tables
How Tables Are Created
Accessing Tables
Nonhashed Tables
DBCExtension Tables
Updating Tables
Character Data
Maintaining System Logs
Data Dictionary Logs that Require Manual Purging
Manually Deleting Old Log Data
View Column Values
AccessRight Column
ConstraintType Column
ExceptionValue Column
JournalFlag Column
NoSQLDataAccess Column
ProcessOffline Column
RoutineKind Column
StatsSource Column
TableKind Column
TimeDimension Column
VTCheckType Column
LogonSource Column Fields and Examples
Mainframe-Attached Systems Using the CLIv2 API
Workstation-Attached Systems Using CLIv2 API
Example: An Archive Job
Example: A BTEQ Job
JDBC Driver API
Example: LogonSource
ODBC Driver API
Example: Teradata ODBC Driver
.NET Data Provider for Teradata API
Example: .NET Data Provider for Teradata API
PDE Internal Session
Example: PDE Internal Session
Data Types for Unicode Views
Database Objects
Database Administration
Setting Up Your Administrative Environment: All DBAs
Logging in to the Operating System
User DBC
Setting Up the Database Administrator User
Creating the Database Administrator Profile
Creating the Database Administrator User
Granting Database Privileges to User DBADMIN
Setting Up the Compute Cluster Administrative Users
Creating Compute Cluster Administrative Users
Granting CREATE COMPUTE GROUP Privileges to the Compute Cluster Administrator
Granting CREATE COMPUTE PROFILE Privileges to the Vantage Cloud Administrator Profile
Granting Compute Cluster Privileges to Roles
Granting Users Access to Compute Clusters
Setting Up Compute Cluster Administrative Users - SQL Examples
Creating a Spool Reserve Database
Guidelines for Reserving Minimum Spool Space
Working with Compute Resources: Compute DBAs
SUSPEND COMPUTE and RESUME COMPUTE
SUSPEND COMPUTE
SUSPEND COMPUTE Syntax
SUSPEND COMPUTE Syntax Elements
SUSPEND COMPUTE Syntax Rules
SUSPEND COMPUTE Usage
SUSPEND COMPUTE Examples
RESUME COMPUTE
RESUME COMPUTE Syntax
RESUME COMPUTE Syntax Elements
RESUME COMPUTE Syntax Rules
RESUME COMPUTE Usage
RESUME COMPUTE Examples
Databases and Users in Teradata: All DBAs
Databases and Users
Space Used by Databases and Users
Creating or Owning Objects in the Hierarchy
Creating a Database or User
The System Users
System User DBC
System User SYSTEMFE
System User SYSADMIN
Other System Databases and Users
As-A-Service System Users
Recommended Hierarchy
Vantage Storage Overview
Object Storage Space Management
CREATE TABLE STORAGE Option
Setting DEFAULT STORAGE for Users and Databases
Working with Databases: All DBAs
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
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 to Populate New Table
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
Working with Views Related Information
Getting View Column Information
Using BTEQ Scripts to Create Database Objects
Working with Stored Procedures and User-defined Functions: Application DBAs
Creating Stored Procedures
Example 1: Compiling and Running a Stored Procedure with a Cursor
Example 2: Creating a Stored Procedure to Update Accrued Interest
Creating User-Defined Functions
Example: Creating a User-Defined Function to Verify a Date
Working with Users, Roles, and Profiles: Operational DBAs
Overview of Establishing Users
Types of Users
Assessing Database User Needs
Best Practices for Creating Users
Creating User Accounts
Determining Account for User Session
Creating User Profiles
Use of Profiles
Precedence of Values for Profile Parameters
Creating Profiles
Dropping Profiles
Working with Database Users
Creating Database Users
Creating Temporary Passwords for First Login
Using Roles to Manage User Privileges
User Types and Minimum Required Privileges
Types of Privileges
Limitations on Using Roles
Create User Roles
When to Grant Privileges to a Role
Grant Privileges to a Role
Grant User Membership in a Role
Dropping a Role
Defining the Default Role for a User
Assigning the Default Role
Dropping the Default Role
Granting Privileges Directly to Users
Granting Privileges to Users Prerequisites
Example: Granting Privileges on Sys_Calendar to All Users
Grant Privileges to a User
Automatic Privileges for Creators
Automatic Privileges for Created Users and Databases
Granting Privileges to Users Related Information
Setting Up Compute Cluster Roles, Compute Profiles, and Compute Groups and Allowing Users Access
Compute Maps
CREATE COMPUTE GROUP: SQL
Creating Compute Profiles
Creating Compute Cluster Roles
Granting Users Access to Compute Groups
Loading and Exporting Data: Application DBAs
Teradata Parallel Transporter
Basic Teradata Query for Loading Tables
Differences between BTEQ and Teradata PT
Updating Tables
Loading Data into Analytics Database
Restarts and Aborts on BTEQ Jobs with Identity Column
FastLoad Utility
FastExport Utility
MultiLoad Utility
Maximum Number of Concurrent FastLoad, MultiLoad, and FastExport Jobs
Utility Management
Teradata Parallel Data Pump
Loading Geospatial Data
Loading Unicode Data with UNICODE PASS THROUGH
BTEQ, Teradata Parallel Transporter, and JDBC Examples
Teradata Studio Example
Usage Notes
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
Example: Investigating Why a Job Ran Slower Today
Example: Performance Anomaly Detection
Example: Capacity Planning
Working with Sessions and Accounts: Operational DBAs
Session Modes and Transaction Processing
Transaction Processing in ANSI Session Mode
Transaction Processing in Teradata Session Mode
Setting the Session Mode
Checking for Logged On Sessions
Getting Session Information
Troubleshooting Problems with Sessions
Working with Accounts
Purpose of System Accounting
Creating Accounts
Migrating Accounts from VantageCloud Enterprise or VantageCore
Working with System Accounting Views
DBC.AccountInfoV
DBC.AMPUsage View
Account String Variable
ASE Substitution Variables
Restrictions and Usage Rules
Using ASE with Client Utilities
Managing Space: Operational DBAs
Types of Space
Global Space Accounting
Example: Create a User with a Skew Limit
Considerations for Global Space Accounting
Interaction between Skew Factor and a Profile
Interaction between the Skew Factor and Soft Limits
Fixing Issues with Space Accounting
FixAllocatedSpace
FixAllocatedSpace Syntax
FixAllocatedSpace Usage Notes
FixAllocatedSpace Example
FixCurrentSpace
FixCurrentSpace Syntax
FixCurrentSpace Usage Notes
FixCurrentSpace Example
Identifying Space Issues by Querying System Views
Querying the DiskSpaceV View to Find System Space Issues
Querying the TableSizeV View
Use of the AllSpaceV View
Example Requests for Space Information
Transferring Ownership and Permanent Space
Transferring Permanent Space
Increasing the Spool Space Limit for a User
Finding and Fixing Skewed Tables by Querying the TableSizeV View
Example: Finding Skewed Tables by Querying the TableSizeV View
Defining Temporary Space Limits
Protecting Transactions by Reserving Cylinders for Perm Space
Example: Creating a Macro for Space Usage Reporting
Other Ways to View Space Usage
Managing Data Blocks
Managing Cylinders
Giving One User to Another
Maintaining the Database: Operational DBAs
Database Maintenance Tasks
Managing Accumulated Log Data
Database Logs
Method for Maintaining Database Logs
Telemetry Reporting Service
Deleting Old Log Data Manually from System Tables
Maintaining Data Dictionary Tables
Clearing the System Logs
Clearing Values Manually in the DBC.Acctg Table
Archiving and Resetting Accumulators and Peak Values
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 Queries: Operational DBAs
Redrive Protection for Queries
Recommendations for Common Query Problems
Finding and Fixing Skewed Tables
Finding and Resolving Lock Contentions
Lock Levels
Improving Query Performance Using COLLECT STATISTICS: Application DBAs
Statistics Collection
When to Collect Statistics
Collecting Statistics
Stale Statistics
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
Working with Special Cases
Collecting Statistics on Tables Protected with Row-Level Security
Collecting Statistics on Null
Tracking Query Behavior with Database Query Logging: Operational DBAs
DBQL Overview
DBQL Uses
Overview of Collection Options
Logging Rules for Applications
Best Fit Rule Hierarchy
Rules Validation
SQL Logging Statements
SQL Logging Considerations
XML Query Plan Logging
Capturing DBQL Using 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
DBQL Macros
DBQL Tables
DBQL Views
Shredding the Lock Plan Information in the XML Lock Log Table, 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
Things to Consider When Logging DBQL Data
Logging Overhead
Options for Flushing the DBQL Cache
Flushing the DBQL Caches Manually
Change the DBQL Performance Stats Cache Size
Logging Scenarios
Scenarios of Logging Accounts
Scenarios of Detailed Logging
Scenarios of Threshold and Summary Options
Example: OBJECT Data for One Query
Example: STEP Data for One Query
Examining the Logged Data
Comparing CollectTimeStamp Value between Tables
Using QueryID for Joins on DBQL Tables
Maintaining the Logs
Methods for Minimizing Log Table Size
DBQL Objects You Cannot Change
Reviewing or Ending Current Rules
Reviewing Rules
Changing DBQL Rules for Specific Users or Accounts
Ending DBQL Logging for Specific Users or Accounts
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
Using EXPLAIN to Analyze Request Plans
EXPLAIN Request Modifier
Generating an EXPLAIN with Teradata Studio
Generating an EXPLAIN with BTEQ
Capturing Request Plan Steps
Query Capture Facility
Capturing Request Plans Using DBQL
Relationship between BEGIN QUERY CAPTURE and BEGIN QUERY LOGGING
Query Capture Database
Creating the Query Capture Database Tables
Creating QCD Tables
Creating the Query Capture Database Tables Procedure Using BTEQ
Dropping Query Capture Database Tables
Dropping Query Capture Database Tables Procedure Using BTEQ
Querying QCD Tables
QCD Request Macros and Views
QCD Macro and View Versions
User Categories
Specific User Category Privileges
Building Baseline Transaction Profiles
Using Maps to Position Table Data across AMPs: All DBAs
Map Overview
Scenarios for Using Maps to Move Table Data
Initial Setup for Managing Maps
A Simple Process for Changing the Map for a Table
Analyzing and Optimizing Map Placement
Introduction to the Advisor and Mover Tools
Enabling Step Logging to Aid in Map Analysis
Excluding Objects from Map Reassignments
Analyze Maps
ActionsTbl
Review Recommended Map Actions
Customize the List of Recommended Map Actions
Rename Maps after a System Expansion
Sessions Used for Reassigning Maps
Estimating the Time Needed to Reassign Maps
Decisions Required before Reassigning Maps
Reassign the Map for a Table
Limiting the Resources for Map Reassignments
Stopping the Map Reassignments Gracefully
Stopping Map Reassignments Abruptly
Managing Restarts
Restart Cleanup during the Advisor Process
Restart Cleanup during the Mover Process
Other Cleanup Tasks
Handling a Second Contiguous Map after a System Expansion
Removing Historical Data
Adjusting Space Limits and Skew Settings
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
Teradata System Limits
System Limits
Miscellaneous System Limits
Message Limits
Storage Limits
Gateway and Vproc Limits
Hash Bucket Limits
Database Limits
Name and Title Size Limits
User Limits
Table and View Limits
Spool Space Limits
BLOB, CLOB, XML, and Related Limits
Macro, UDF, SQL Procedure, and External Routine Limits
Query and Workload Analysis Limits
Secondary and Join Index Limits
Reference Index Limits
SQL Request and Response Limits
Row-Level Security Constraint Limits
Session Limits
Teradata Crashdumps
Password Restricted Words
Frequently Used Words
Frequently Used Names
Database Design
Database Design for Vantage
Database Engine 20
Data Placement to Support Parallel Processing
Row Distribution
Shared-Nothing Architecture
AMP Ownership of Data
Other Applications of Hashing for Parallel Processing
Synchronization of Parallel Operations
Synchronized Table Scans
Spool Reuse
Synchronized BYNET Operations
Design Considerations
Usage Considerations: OLTP and Data Warehousing
Usage Considerations: Summary Data and Detail Data
Usage Considerations: Simple and Complex Queries
Usage Considerations: Ad Hoc Queries
Databases and Data Modeling
Database Design Life Cycle
Designing for OLTP and Designing for Data Warehousing Support
ANSI/X3/SPARC Three Schema Architecture
High-Level Architecture
Detailed Architecture
Requirements Analysis
Logical Database Design
Activity Transaction Modeling
Physical Database Design
Indexes and Maps
Primary Indexes and Primary AMP Indexes (Overview)
Data Access Considerations for Choosing a PI or PA
Data Distribution Considerations for Choosing a PI or PA
Unique PIs
Nonunique PIs and PAs
Joins and Colocation
Tables with No Primary Index
Secondary Indexes (Overview)
Join Indexes (Overview)
Hash Indexes (Overview)
Index Considerations
Index Type Comparisons
Evaluating Indexes
Maps
Primary Index, Primary AMP Index, and NoPI Objects
Primary Indexes and Primary AMP Indexes
Primary Index Defaults
Unique and Nonunique Primary Indexes
Partitioned and Nonpartitioned Primary Indexes
Nonpartitioned Primary Indexes
Partitioned Primary Indexes
Partitioning Expression Data Type Considerations
Column Partitioning a Table or Join Index
Choosing an Indexing Method for a Column-Partitioned Table or Join Index
Benefits and Considerations for Column-Partitioned Tables with a Primary AMP Index
Benefits and Considerations for Column-Partitioned Tables with a Primary Index
NoPI Tables, Column-Partitioned NoPI Tables, and Column-Partitioned NoPI Join Indexes
INSERT ... SELECT into NoPI Tables
Uses for Nonpartitioned NoPI Tables
Rules and Limitations for NoPI and Column-Partitioned Tables
Manipulating Nonpartitioned NoPI Table Rows
Column Partitioning
Options for Storing Data on an AMP
Column-Partitioned Tables
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
Deleting Rows from a Column-Partitioned Table
Updating a Column-Partitioned Table
Operations and Utilities for Column-Partitioned Tables
Storage and Other Overhead Considerations for Partitioning
Advantages and Disadvantages of Partitioned Primary Indexes
Usage Recommendations for Row Partitioning
Row Partition Elimination
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
Partitioning CHECK Constraints for Single-Level Partitioning
Single-Level Partitioning Example
Stage 1: First single-level partitioning of the orders table
Stage 2: Second single-level partitioning of the orders table
Stage 3: Third single-level partitioning of the orders table
Stage 4: Fourth single-level partitioning of the orders table
Single-Level Partitioning Case Studies
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
Multilevel Partitioning Example
Stage 1: First multilevel row partitioning of the orders table
Stage 2: Altering the row partitioning expressions
Three-Level Row 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
Summary of Primary Index Selection Criteria
Principal Criteria for Selecting a Primary Index
Uniform Data Distribution
Optimal Data Access
Criteria for Selecting a Primary Index
Considerations for Choosing a Primary Index
Partitioning Guidelines
Evaluating the Relative Merits of Partitioning and Not Partitioning
Selecting a Primary Index for a Queue Table
Column Distribution Demographics and Primary Index Selection
Effects of Skew on Query Processing
SQL Scripts for Detecting Skew
Scenario 1
Query Workload
Problem Statement
Analysis of Row Partitioning Benefits
Conclusions
Scenario 2
Analysis of Row Partitioning Benefits
Conclusions
Scenario 3
Analysis of Partitioning Benefits
Conclusions
Scenario 4
Performance Considerations for Primary Indexes
Guideline for All Primary Indexes
Guidelines for Row Partitioning
General Considerations
Unique Primary Index Considerations
Nonunique Primary Index Considerations
Duplicate Row Checks for SET Tables with NUPIs
Minimizing Duplicate NUPI Row Checks
Secondary Indexes
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
Nonunique Secondary Indexes
Relationship between a NUSI Subtable Row and Base Table Rows
NUSI Access and Performance
Selectivity Considerations
NUSI Bit Mapping
NUSIs and Query Covering
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 Usage Summary
USI Summary
NUSI Summary
Secondary Index Access Summarized by Example
Table Definition
Sample Queries
Join Indexes
When Join Indexes Are Useful
Rules for Using the ROWID Keyword in a Join Index Definition
Rules for Using the System-Derived PARTITION Column in a Join Index Definition
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: Choosing between a PPI and a Value-Ordered NUSI
Collecting Statistics on a Join Index
Fallback with Join Indexes
Limits for 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 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
Join Indexes and NUSIs
Join Index Design Tips
When to 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
Specifying a Row-Partitioned or a Value-Ordered Sort Key for Range Queries
Join Index Benefits and Costs
Cost
Join Index Costs Summary
Benefit and Benefit Percentage
Payback
Cost of Disk Resources
Cost of Join Index Creation
Example: Join Index Creation
Creation and Elapsed Query Times for Different Join Indexes
Cost of Join Index Maintenance
Types of Join Index Examined for This Analysis
Join Index Maintenance Cost as Function of Hits for Each Data Block
Join Index Maintenance Cost as a Function of Row Size
Join Index Maintenance Cost as a Function of Insert Method
Four Insertion Methods
Comparative Elapsed Times to Insert
Summary Evaluation
Generalizations Derived from These Tests
Cost/Benefit Analysis for Join Indexes
Join Index Benefits Summary
Computing the Benefits of Join Indexes
Computing the Query Ratio
Computing the Payback Factor
Join Index Types
Simple Join Indexes
Defining a Simple Join Index on a Binary Join Result
Table Definitions
Query Plans
Materialized Join Index
Defining and Using a Simple Join Index with an n-way Join Result
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
Deciding between Single-Table Join Index and Ordinary Join Index
Table Definitions
Decision: Ordinary Join Index or 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
Example
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
Example: 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
Single-Table Join Indexes
Aggregate Join Indexes
Join Index Maintenance Considerations
Examples: Row-at-a-Time INSERT Maintenance Overhead
Set Processing Alternative
Join Index Definition Restrictions
Restrictions on Number of Join Indexes Defined for Each Base Table
Restrictions on the Number of Columns for Each Referenced Base Table
Restrictions on the Use of the System-Derived PARTITION[#L n] 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 for Each 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 General Covering Rules for Extra Tables in Join Index Definition
Example: All Outer Joins in Join Index Definition
Example: All Inner Joins with Aggregation in Join Index Definition
Example: All Inner Joins with Aggregation in Join Index Definition
Example: More Inner Joined Tables in Aggregate Join Index Definition than in Query
Example: Join Index Left Outer Joined on Six Tables
Example: More Tables Referenced by Join Index Definition than Referenced by Query
Example: Using a Join Index that Has an Extra Inner Join in Its Definition
Examples: 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 Join Index
Considerations for Measuring Disk Space
Value-Ordered Storage of Join Index Rows
Join Index Interactions with Other Teradata Systems and Features
Vantage Features that Do Not Support Join Indexes
Triggers
Permanent Journal Recovery
Load Utilities
Teradata Parallel Data Pump
Designing for Database Integrity
Sources of Data Quality Problems
Data Warehousing Institute 2002 Survey
Data Warehousing Institute 2005 Follow-Up Survey
Database Constraints and Enterprise Business Rules
Definitions
Semantic Data Integrity Constraints
Logical Integrity Constraints
Relations, Relation Values, and Relation Variables
The Logic of Database Integrity
The Closed World Assumption
How Relational Databases Are Built from Logical Propositions
Inclusion Compatibilities
Semantic Integrity Constraint Types
Domain Constraints
Column-Level Constraints
Row-Level Security Constraints
Table-Level Constraints
Database-Level Constraints
Semantic Constraint Specifications
Referential Constraints
Naming Constraints
CHECK Constraints
Foreign Key Constraints
PRIMARY KEY Constraints
Unique Constraints
Semantic Constraint Enforcement
Updatable Cursors and Semantic Database Integrity
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
Disk I/O Integrity Checking
Reading or Repairing Data from Fallback
Designing for Missing Information
Semantics of SQL Nulls
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
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
Using Data Compression
Compression Types Supported by Vantage
Identifying Uncompressed, Single-Value Compressed, and MultiValue Compressed Tables
Multivalue Compression
Multivalue Compression Procedure
Multivalue Compression Example
Guidelines for Using Multivalue Compression
Tradeoffs between Multivalue Compression and Storage Requirements for Compressed Values
Algorithmic Compression
Algorithmic Compression Procedure
Algorithmic Compression Usage Notes
Algorithmic Compression Restrictions
Using Custom ALC Algorithms
Row Compression
Row Header Compression
Autocompression
Using Join Index Row Compression
Block-Level Compression
Software-Based Block-Level Compression
Temperature-Based Block-Level Compression
Enabling Compression for Individual Tables Using DDL
Setting the Compression Algorithm for Individual Tables Using DDL
Setting the Software Compression Level for Individual Tables Using DDL
Restrictions and Usage Notes
Compressing Data Loaded into Empty Tables Set to MANUAL
Block-Level Compression Usage Notes
CPU Considerations on Different Teradata Platforms
Getting 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
Statistics, Including Estimated Compression Ratio
Getting BLC Information
System and AMP Outages during Compression
Choosing a Software-Based Compression Scheme
Combining Compression Methods
Database-Level Capacity Planning Considerations
Capacity Planning
Extended Data Lifetimes
Cool and Icy Data
Warm, Hot, and Very Hot Data
Storing Data Efficiently
Specifying DECIMAL and NUMERIC Precisions
Specifying INTEGER Precisions
Using Standardized Encodings
Storing NUMBER 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
Packed64 Row Structure for a Partitioned Table
Row Structure for Aligned Row Format Systems
Aligned Row Structure for a Partitioned Table with 65,535 or Fewer Combined Partitions
Containers and Space
Container Contents
Containers and Autocompression
Row Structure for Containers (COLUMN Format)
Container for the Delete Column Partition
Row Structure for Subrows (ROW Format)
Alignment of Containers and Subrows
Column Partitioning
Consumption of Disk Space by Populated and Empty Partitions
Byte Alignment
Row Length Characteristics and Row Components
Example: Algorithmic Compression But No Multivalue Compression
Example: Multivalue Compression But No Algorithmic Compression
Example: Mix of Multivalue and Algorithmic Compression
Join Index Row Structures
Join Index Row Structure for Packed64 Format Systems
Packed64 Row Structure for Uncompressed Join Index with Nonpartitioned Primary Index
Packed64 Row Structure for Row Compressed Join Index with Nonpartitioned Primary Index
Join Index Row Structure for Aligned Row Format Systems
Aligned Row Format Structure: Uncompressed Join Index with Nonpartitioned Primary Index
Aligned Rows for Uncompressed Join Index with Partitioned Primary Index, ≥65,535 Combined Partitions
Aligned Row Format Join Index Row Layout for a Compressed Join Index with an Nonpartitioned Primary Index
Presence Bits
Meaning of Presence Bits
Number of Presence Bits Required to Represent Compressed Values
Table Headers
Field 1
Fields 2-4
Field 5
Fields 6-12
System-Derived and System-Generated Columns
ROWID Columns
Object Identifier Columns
System-Derived and System-Generated Column Data Types
Data Type Considerations
Numeric Data Types
Differences between Exact and Approximate Predefined 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)
DATASET Data Type
Array Data Types
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 Assigned to Databases, Users, or Profiles
Different Disk Space Views Return Different Results
Sizing Base Tables, LOB Subtables, XML Subtables, and Index Subtables
Sizing Base Tables and Join Indexes
Sizing a LOB or XML Subtable
Sizing a Column-Partitioned Table
Sizing a Unique Secondary Index Subtable
Sizing a Nonunique Secondary Index Subtable
Sizing User-Defined Routines
Sizing a Reference Index Subtable
Sizing Spool Space
Spool Space Categories
Spool Space Sources
Spool Limits
Guidelines for Allocating Spool Space
Sizing a Query Capture Database
Sizing Table Space Empirically
General Procedure
Explicit Procedure
Querying DBC.TableSizeV
Table Sizing Summary
System-Level Capacity Planning Considerations
Database Size Considerations
Permanent Space Allocations
Estimating Database Size Requirements
Allocating Space for External Stored Procedure and User-Defined Function Bodies
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
Global Space Accounting
Determining Available User Table Data Space
Depot Area Overhead
Data Table Overhead
Tables Area Requirements
Dictionary and Spool Space Requirements
User TEMP Space Requirements
CRASHDUMPS User Space Requirements
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
Design Issues for Tactical Queries
Tactical Queries Defined
Scalability Considerations for Tactical Queries
Scalability Is a Relative Concept
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
Primary Cluster and Compute Cluster Nodes
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 Considers 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
All-AMP Queries
All-AMP Tactical Queries and Partitioned Tables
Application Opportunities for Tactical Queries
Multiple-Statement Requests
Coding Multiple-Statement Requests
ACCESS Locking and Multiple-Statement Requests
Macros and Tactical Queries
Cached Plans
Other Tools Useful for Monitoring and Managing Tactical Queries
Monitoring Active Work
Notation Conventions for Database Design
Table Column Definition and Constraint Abbreviations
Character Symbols
Predicate Calculus and Set Theory Notation Used in This Document
Dependency Theory Notation Used in This Document
Teradata System Limits
System Limits
Miscellaneous System Limits
Message Limits
Storage Limits
Gateway and Vproc Limits
Hash Bucket Limits
Database Limits
Name and Title Size Limits
User Limits
Table and View Limits
Spool Space Limits
BLOB, CLOB, XML, and Related Limits
Macro, UDF, SQL Procedure, and External Routine Limits
Query and Workload Analysis Limits
Secondary and Join Index Limits
Reference Index Limits
SQL Request and Response Limits
Row-Level Security Constraint Limits
Session Limits
Summary Physical Design Scenario
Prerequisites for the Process Review
Process Review
Sample Worksheet Forms
Domains Form
Constraints Form
System Form
Report/Query Analysis Form
Table Access Summary By Column Form
Table Form
Row Size Calculation Form for Byte-Packed Format Systems, parts 1 and 2
Row Size Calculation Form for Byte-Aligned Format Systems, parts 1 and 2
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
Compression Methods
Multivalue Compression Method
Algorithmic Compression Method
Block-Level Compression Method
Temperature-Based Block-Level Compression Method
Row Compression Method
Autocompression Method
Row Header Compression Method
Database Design for an Object File System
Teradata Publications Related to Database Design
Database Messages
Jerry Klindt, Paul Sinclair, Steve Molini, Jeremy Davis, Rama Krishna Korlapati, Hong Gui, and Stephen Brobst, Partitioned Primary Index Usage Orange Book , 541-0003869
No Primary Index (NoPI) Table User Guide Orange Book , 541-0007565Increased Partition Limit and Other Partitioning Enhancements Orange Book , 541-0009027Teradata Columnar Orange Book , 541-0009036