Teradata数据库基础教程

4星 · 超过85%的资源需积分: 3 157 浏览量更新于2024-08-02 1 收藏 1.55MB PDF 举报

"Teradata 基础 - Teradata 数据库 SQL 参考指南，基础篇，版本12.0" 本文将深入探讨 Teradata 数据库的基础知识，这是一本详细且易于理解的官方文档，涵盖了 Teradata 的核心概念、SQL 参考以及版本12.0的相关信息。Teradata 是全球领先的企业级数据仓库解决方案之一，它为企业提供高效的数据存储、管理和分析能力。 Teradata Database 是 Teradata 公司的核心产品，设计用于处理大规模、复杂的数据集。其架构基于并行处理技术，使得数据查询和分析速度显著提高。在 Teradata 中，数据被分散在多个物理节点上，通过数据分区和负载均衡实现高可用性和性能优化。 SQL Reference 部分详细阐述了 Teradata 对 SQL（结构化查询语言）的支持。SQL 是用于管理关系数据库的标准语言，Teradata 扩展了标准 SQL，增加了许多特定的功能，如并行查询、多维分析和分布式查询等。这些功能使得 Teradata 能够处理复杂的商业智能和数据分析任务。在 Fundamentals（基础）章节中，读者可以了解到 Teradata 的基本操作、数据类型、表和视图的创建、索引的使用、数据导入导出、事务处理以及安全性管理等内容。这些基础知识对于理解和操作 Teradata 数据库至关重要。文档还可能涵盖 Teradata 的版本12.0的新特性与改进，比如可能包括性能提升、新的数据压缩算法、更强大的查询优化器和对大数据支持的增强等。版本升级通常伴随着更好的性能和更多的功能，以适应不断变化的业务需求。此外，文档中可能会提到与其他硬件和软件厂商的合作与集成，例如 Adaptec、AMD、BakBone、EMC、GoldenGate、HP、Intel、IBM 等。这些合作伙伴的产品和解决方案可以与 Teradata 数据库协同工作，提供数据备份、存储、高速传输和灾备等服务。这份 Teradata 基础文档是学习和掌握 Teradata 数据库系统的重要资源，无论你是数据库管理员、数据分析师还是开发者，都能从中受益。通过深入阅读和实践，你将能够充分利用 Teradata 的强大功能，解决企业级数据管理中的各种挑战。

Chapter 1: Objects

Tables

16 SQL Reference: Fundamentals

Materialized instances of a global temporary table share the following characteristics with

volatile tables:

• Private to the session that created them.

• Contents cannot be shared by other sessions.

• Optionally emptied at the end of each transaction using the ON COMMIT PRESERVE/

DELETE rows option in the CREATE TABLE statement.

• Activity optionally logged in the transient journal using the LOG/NO LOG option in the

CREATE TABLE statement.

• Dropped automatically when a session ends.

For details about the individual characteristics of global temporary and volatile tables, see

“Global Temporary Tables” on page 18 and “Volatile Tables” on page 22.

Queue Tables

Teradata Database supports queue tables, which are similar to ordinary base tables, with the

additional unique property of behaving like an asynchronous first-in-first-out (FIFO) queue.

Queue tables are useful for applications that want to submit queries that wait for data to be

inserted into queue tables without polling.

When you create a queue table, you must define a TIMESTAMP column with a default value

of CURRENT_TIMESTAMP. The values in the column indicate the time the rows were

inserted into the queue table, unless different, user-supplied values are inserted.

You can then use a SELECT AND CONSUME statement, which operates like a FIFO pop:

• Data is returned from the row with the oldest timestamp in the specified queue table.

• The row is deleted from the queue table, guaranteeing that the row is processed only once.

If no rows are available, the transaction enters a delay state until one of the following occurs:

• A row is inserted into the queue table.

• The transaction aborts, either as a result of direct user intervention, such as the ABORT

statement, or indirect user intervention, such as a DROP TABLE statement on the queue

table.

To perform a peek on a queue table, use a SELECT statement.

For details about creating a queue table, see “CREATE TABLE (Queue Table Form)” in SQL

Reference: Data Definition Statements. For details about the SELECT AND CONSUME

statement, see SQL Reference: Data Manipulation Statements.

Global

temporary

trace

Global temporary trace tables are useful for debugging external routines (UDFs,

UDMs, and external stored procedures). During execution, external routines can

write trace output to columns in a global temporary trace table.

Like global temporary tables, global temporary trace tables have persistent

definitions, but do not retain rows across sessions.

Type Usage

Chapter 1: Objects

Tables

SQL Reference: Fundamentals 17

Error Logging Tables

You can create an error logging table that you associate with a permanent base table when you

want Teradata Database to log information about the following:

• Insert errors that occur during an SQL INSERT … SELECT operation on the permanent

base table

• Update and insert errors that occur during an SQL MERGE operation on the permanent

base table

To enable error logging for an INSERT … SELECT or MERGE statement, specify the

LOGGING ERRORS option.

You can use the information in the error logging table to determine how to recover from the

errors, such as which data rows to delete or correct and whether to rerun the request.

For details on how to create an error logging table, see “CREATE ERROR TABLE” in SQL

Reference: Data Definition Statements. For details on how to specify error handling for

INSERT … SELECT and MERGE statements, see SQL Reference: Data Manipulation

Statements.

IF a MERGE operation

or INSERT … SELECT

operation generates

errors that …

AND the request … THEN the error logging table contains …

the error logging

facilities can handle

completes • an error row for each error that the

operation generated.

• a marker row that you can use to

determine the number of error rows for

the request.

The presence of the marker row means the

request completed successfully.

aborts and rolls back when

referential integrity (RI) or

unique secondary index

(USI) violations are

detected during index

maintenance

• an error row for each USI or RI violation

that was detected.

• an error row for each error that was

detected prior to index maintenance.

The absence of a marker row in the error

logging table means the request was aborted.

reach the error limit

specified by the

LOGGING ERRORS

option

aborts and rolls back an error row for each error that the

operation generated, including the error that

caused the error limit to be reached.

the error logging

facilities cannot

handle

aborts and rolls back an error row for each error that the

operation generated until the error that the

error logging facilities could not handle.

Chapter 1: Objects

Global Temporary Tables

18 SQL Reference: Fundamentals

Global Temporary Tables

Introduction

Global temporary tables allow you to define a table template in the database schema,

providing large savings for applications that require well known temporary table definitions.

The definition for a global temporary table is persistent and stored in the data dictionary.

Space usage is charged to login user temporary space.

Each user session can materialize as many as 2000 global temporary tables at a time.

How Global Temporary Tables Work

To create the base definition for a global temporary table, use the CREATE TABLE statement

and specify the keywords GLOBAL TEMPORARY to describe the table type.

Once created, the table exists only as a definition. It has no rows and no physical instantiation.

When any application in a session accesses a table with the same name as the defined base

table, and the table has not already been materialized in that session, then that table is

materialized as a real relation using the stored definition. Because that initial invocation is

generally due to an INSERT statement, a temporary table—in the strictest sense—is usually

populated immediately upon its materialization.

There are only two occasions when an empty global temporary table is materialized:

• A CREATE INDEX statement is issued on the table.

• A COLLECT STATISTICS statement is issued on the table.

The following table summarizes this information.

Note: Issuing a SELECT, UPDATE, or DELETE on a global temporary table that is not

materialized produces the same result as issuing a SELECT, UPDATE, or DELETE on an

empty global temporary table that is materialized.

WHEN this statement is issued on a global

temporary table that has not yet been materialized …

THEN a local instance of the global temporary

table is materialized and it is …

INSERT populated with data upon its materialization.

CREATE INDEX … ON TEMPORARY …

COLLECT STATISTICS … ON TEMPORARY …

not populated with data upon its

materialization.

剩余313页未读，继续阅读

lnsbest

粉丝: 0
资源: 1

Teradata数据库基础教程

Teradata基础教程（中文）PDF

Teradata基础教程（中文）.pdf

teradata基础知识

Teradata数据库的主要组件

如何在Teradata SQL中使用数据类型和算术运算符进行有效的数据处理和计算？

在Teradata SQL中如何正确使用数据类型和算术运算符来执行数据转换和计算？请结合示例详细说明。

Teradata的CLDM5.0模型在电信业数据仓库中如何助力客户管理和业务量分析的数据整合与优化？

Teradata在构建电信业数据仓库时，如何结合CLDM5.0模型实现客户管理与业务量分析的数据整合与优化？

teradata金融数据模型( fs-ldm)v10.0 book-1

Teradata FS-LDM模型在金融行业中的应用价值体现在哪些方面？请结合FS-LDM的特点详细说明。

最新资源