TeradataSQL调优资源-CSDN文库

Teradata

SQL调优

需积分: 23 154 浏览量 2018-03-21 15:00:40 上传评论收藏 263KB DOC 举报

资源推荐

资源详情

资源评论

Teradata SQL 调优

1.优化过程：依照运行时间，数据量和复杂度来定位瓶颈。查看 sql 执行计划，判断其合理

性。性能监控 ==》目标选取 ==》性能分析 ==》过程优化 ==》运行跟踪（性能监控）注

意：每个过程中都会产生必须的文档；

2.性能分析：

• Review PDM --表定义 --PI 的选择 --表的记录数与空间占用

• Review SQL --关联的表 --逻辑处理复杂度 --整体逻辑 --多余的处理

• 测试运行 --响应时间 • 查看 EXPLAIN --瓶颈定位

3.过程优化：

• 业务规则理解 --合理选取数据访问路径

• PDM 设计 --调整 PDM

• SQL 写法不优化，忽略了 Teradata 的机理与特性 --调整 SQL

• Teradata 优化器未得到足够的统计信息 --Collect Statistics

4.Multiple Insert/select --> Multi-Statement Insert/Select

* 并行插入空表不记录 Transient Journal

* 充分利用 Teradata 向空表 Insert 较快以及并行操作的特性如：

• 现状 INSERT INTO ${TARGETDB}.DES (Party_Id ,Party_Name ... ) SELECT … FROM

SRC1 ; INSERT INTO ${TARGETDB}.DES (Party_Id ,Party_Name ... ) SELECT … FROM

SRC2 ; INSERT INTO ${TARGETDB}.DES (Party_Id ,Party_Name ... ) SELECT … FROM

SRC3 ; 说明：串行执行，多个 Transaction

• 优化后：

INSERT INTO ${TARGETDB}.DES (Party_Id ,Party_Name ... )

SELECT … FROM SRC1 ;INSERT INTO ${TARGETDB}.DES (Party_Id ,Party_Name ... )

SELECT … FROM SRC2 ;INSERT INTO ${TARGETDB}.DES (Party_Id ,Party_Name ... )

SELECT … FROM SRC3 ; 说明：并行执行，单个 Transaction 5.Insert/Select with

Union/Union all --> Multi-Statement Insert/Select * Union 需要排除重复记录，Union all 虽不

需要排重，但都需要占用大量的 Spool 空间，都需要进行重新组织数据如：

现状：

INSERT INTO ${TARGETDB}.DES (Party_Id ,Party_Name ... )

SELECT … FROM SRC1 ; UNION ALL SELECT … FROM SRC2 ;

UNION ALL SELECT … FROM SRC3 ;

… 调整后: INSERT INTO ${TARGETDB}.DES (Party_Id ,Party_Name ... ) SELECT … FROM

SRC1 ;INSERT INTO ${TARGETDB}.T01_DES (Party_Id ,Party_Name ... ) SELECT … FROM

SRC2 ;INSERT INTO ${TARGETDB}.T01_DES (Party_Id ,Party_Name ... ) SELECT … FROM

SRC3 ;

6.排除重复记录

* 针对单表内的重复记录使用 ROW_ NUMBER 函数排重 * 排重方式多了一层子查询 * 增加

了大量的数据重新分布的时间

现状： …… INSERT INTO ${TARGETDB}.T01_INDIV (Party_Id ,Party_Name ... ) SELECT

COALESCE(b1.Party_Id,'-1') , COALESCE(TRIM(b1.Party_name),'') ... FROM ( select party_id

party_name, … , ROW_NUMBER() OVER (PARTITION BY Party_Id ORDER BY

Party_Name ) as rownum from ${TEMPDB}.T01_INDIV b1 … ) AA where AA.rownum ＝ 1

……

建议做法： INSERT INTO ${TEMPDB}.T01_INDIV … INSERT INTO $

{TEMPDB}.T01_INDIV … …… INSERT INTO ${TARGETDB}.T01_INDIV

(Party_Id ,Party_Name ... ) SELECT party_id party_name, … From ${TEMPDB}.T01_INDIV b1

Qualify ROW_NUMBER() OVER (PARTITION BY Party_Id ORDER BY Party_Name ) = 1 •

运用 Qualify + ROW_ NUMBER 函数 • SQL 语句简洁明了 • 避免子查询优化前 explain：

…… 4) We do an all-AMPs STAT FUNCTION step from PTEMP.VT_T01_INDIV_cur by way

of an all-rows scan with no residual conditions into Spool 5 (Last Use), which is assumed to be

redistributed by value to all AMPs. The result rows are put into Spool 3 (all_amps), which is built

locally on the AMPs. 5) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of

an all-rows scan into Spool 1 (all_amps), which is built locally on the AMPs. The result spool file

will not be cached in memory. The size of Spool 1 is estimated with no confidence to be 6,781,130

rows. The estimated time for this step is 16.01 seconds. 6) We do an all-AMPs RETRIEVE step

from Spool 1 (Last Use) by way of an all-rows scan with a condition of ("ROWNUMBER = 1")

into Spool 8 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to

order Spool 8 by row hash. The result spool file will not be cached in memory. The size of Spool 8

is estimated with no confidence to be 6,781,130 rows. The estimated time for this step is 1 minute.

7) We do an all-AMPs MERGE into PDATA.T01_INDIV from Spool 8 (Last Use). 优化后

explain: …… 4) We do an all-AMPs STAT FUNCTION step from PTEMP.VT_T01_INDIV_cur

by way of an all-rows scan with no residual conditions into Spool 5 (Last Use), which is assumed

to be redistributed by value to all AMPs. The result rows are put into Spool 3 (all_amps), which is

built locally on the AMPs. 5) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by

way of an all-rows scan with a condition of ("Field_10 = 1") into Spool 1 (all_amps), which is

redistributed by hash code to all AMPs. Then we do a SORT to order Spool 1 by row hash. The

result spool file will not be cached in memory. The size of Spool 1 is estimated with no confidence

to be 6,781,130 rows. The estimated time for this step is 1 minute. 6) We do an all-AMPs MERGE

into PDATA.T01_INDIV from Spool 1 (Last Use). BTEQ 中不能用 length 函数 LENGTH()不是

Teradata 的标准函数，但是 Teradata SQL Assitant 支持它。今天在 bteq 中用此函数，报错，

搞半天，终于知道原因之所在。具体操作如下： SELECT CASE WHEN ETL_JOB LIKE

'CHK_%' THEN SUBSTR(TRIM(etl_job),5,LENGTH(TRIM(etl_job))-4) ELSE ETL_JOB END

etl_job ,CAST('200811'||'01' AS DATE format

'YYYYMMDD') ,Last_JobStatus ,max(Last_Endtime) ,JobType FROM PV_AUTO.ETL_JOB

WHERE etl_job in (……) group by 1,2,3,5 ; 报错信息如下： THEN

SUBSTR(TRIM(etl_job),5,LENGTH(TRIM(etl_job))-4) $ *** Failure 3706 Syntax error:

expected something between '(' and the 'TRIM' keyword. Statement# 1, Info =450 思维惯性，以

为 sql 中存在不可见字符，遂重写报错部分，不见效。突然想起， TERADATA 的标准求字

符串长度的函数为 CHAR,CHARS,CHARACTER 等，将 THEN

SUBSTR(TRIM(etl_job),5,LENGTH(TRIM(etl_job))-4) 改成 THEN

SUBSTR(TRIM(etl_job),5,CHARS(TRIM(etl_job))-4)，问题解决。

Teradata 数据压缩

一：数据压缩的好处

1. Teradata 数据压缩可以节省存储空间，从而让相同的存储单元存储更多的业务数据；

2. 由于查询时需要检索的数据量相对要少，可以减少 I/O,并且缓存中可以存储更多的压缩

过的逻辑行，从而改善数据库性能；

3. Teradata 使用的压缩算法非常有效，因为压缩原因，获取数据需要更少的的磁盘访问，

算法将节省的 CPU 时间用来来执行压缩操作；

4. 可以将压缩节省出来的空间创建高级索引；

二：.teradata 数据压缩 Teradata 版本 v2r5 采用无损压缩算法，允许在一列上对多达 255 个

数据值进行压缩，数据压缩不会造成信息的丢失。一般数据压缩的粒度可以分为数据行和

数据块。Teradata 数据压缩的粒度是数据行，这是日常数据操作的粒度，数据行压缩可以

独立的针对列进行，数据块压缩不行，并且数据块压缩都会造成额外的压缩/解压开销，会

降低数据库性能。Teradata 可以直接访问压缩后的数据行—访问数据行时它不需要对数据

做重构或者解压操作。前面说过，Teradata 可以独立的对行中的某列做压缩操作。如果某

列允许有空值，空值也允许被压缩。最好的压缩候选项为此列中出现频率最高的值，压缩

后的值存放在表头。每行前面有 1 bit 数据来标识此行有没有被压缩。非主索引的定长列都

可以是 Teradata 压缩的候选项。如下的数据类型可以被压缩，括号中为该数据类型的长度

（限制）： - Integer Date (4) - CHAR (N, where N < 256) - BYTEINT (1) - SMALLINT (2) -

INTEGER (4) - FLOAT/REAL (8) - DOUBLE (8) - DECIMAL (1, 2, 4 or 8) - BYTE (N, where N

< 256) 如果某列中有高频率出现的值，那么该列可以有很高的压缩比，常见的有下面一些

情形： - NULLs - Zeros - Default values - Flags - Spaces - Binary indicators (e.g., T/F) 在经分系

统中，一些产品字段，如 Brand_Id,prd_id 等，都会有很高的压缩比。 Teradata 压缩对应用

程序，ETL,查询等操作是完全透明的，并且其操作相当方便，用户只需要在表定义的时候

加上压缩信息就可以了。例如，下面是数据压缩的语法： CREATE TABLE Properties

( Address VARCHAR(40), City CHAR(20) COMPRESS (‘WuHan’), StateCode CHAR(2) ); 当记

录中出现“WuHan”的频率很高时，数据压缩将非常有效。

三： . ** 系统数据压缩操作方法基于以上数据压缩原理，我们开发了脚本

create_comp_ddl.pl，用来依照压缩规则生成包含压缩信息的建表 DDL. 创建数据库表脚本

文件用户配置文件名称输出脚本目标文件压缩值数目起始压缩表大小压缩值占比(百分

数)；对参数的解释如下：创建数据库表脚本文件，老表 ddl 名称(必须将 ddl 导出放在指

定目录)；用户配置文件名称，配置信息主要是除 varchar,pi,ppi 以外的不希望被压缩的列,

各列逗号分开将要生成的新表 ddl 文件名称；30，压缩值数目，表示取多少个压缩来做

压缩操作；100000000，压缩表大小阀值，当表大小小于此阈值时，不进行压缩；20，

当前所取压缩记录占表空间比例的阀值，当欲压缩记录占表空间比例小于此阈值时不做压

缩处理；操作步骤： 1)、获取压缩前的建表 ddl，保存为 src_table.ddl，文件名将作为参数；

2) 、运行 create_comp_ddl.pl 脚本，如 :perl create_comp_ddl.plc sr_table.ddl config.txt

target_table.sql 30 100000000 30 运行结果将生成新的建表 ddl(即 target_table.sql)； 3)、以新

的 ddl 建表，将老表的数据导入新表，确认无误后，删除老表； 4. 批量压缩若要压缩的表

较多，也可以采取批量压缩的方式。这里采用批处理执行 perl 脚本的方法来批量压缩。如

需要相继对 prd_prd_inst_hist 表和 pty_cust_hist 表做压缩，创建批处理 compression.bat,其内

容如下 : perl create_comp_ddl.plc src_table_prd.ddl config.txt tar_table_prd.sql 30 100000000

30>1.log perl create_comp_ddl.pl src_table_pty.ddl config.txt tar_table_pty.sql 30 100000000

30>2.log 注意，上述参数里面的源表 ddl 名和目标 ddl 名不能相同，否则第二次的结果可能

覆盖第一次结果. 常用 sql 使用技巧 LIKE 用法 select first_name,last_name from employee

where last_name like all('%E%','%S%'); select first_name,last_name from employee where

last_name like any/some('%E%','%S%'); Teradata 缺省不区分大小写,如果要区分,可以使用其

扩展参数 CASESPECIFIC select first_name,last_name from employee where

last_name(CASESPECIFIC) like '%Ra%'; 通配符作为一般字符使用例: LIKE "%A%%AAA__"

ESCAPE"A" 在这个表达式中,讲字母 A 定义为 ESCAPE 字符,其中: 第一个%为通配符: 第一

个 A 和其后的%联合表示字符% 第三个%为通配符第二个 A 和其后的 A 联合表示字符 A;

第四个和其后的'_'联合表示字符_ 最后一个'_'为通配符 NULL 的使用当进行升序排列

时,NULL 在数字列排列在负数前,在字符排列在空格前优先级和括号逻辑运算符的缺省优先

级顺序从高到低为:NOT.AND.OR NOT NOT 既可以否定操作符,也可以否定条件表达式否定

操作符 : select first_name,last_name,employee_number from employee where

department_number not =301; 否定条件 : SELECT first_name,last_name,employee_number

FROM employee WHERE NOT(department_number=301); 字符型数据在 ANSI 标准中关于字

符型数据定义了两类:CHAE 和 VARCHAR,Teradata 除了上述两类基本字符数据外,还扩展了

LONG VARCHAR 类型,它等同于 VARCHAR(64000),是最长的字符串 char(size)固定长度的

字符串最大长度:64000 字节 varchar(size) char varying(size) character varying(size) 这三个是

可变长度字符串,最大长度:64000 字节 long varchar 等同于 varchar(64000) 二进制数据二进制

数据类型是 Teradata 的扩展,ANSI 标准没有此类型 BYTE(size) 固定长度的二进制串默认值:

(1)最大值:64000 字节 varbyte(size) 可变长的二进制串默认值:(1)最大值:64000 字节数字型数

据在 ANSI 标准中关于数字型数据定义了四

类:SMALLINT.INTEGER.FLOAT.DECIMAL,Teradata 还扩展了 BYTEINT 和 DATE smallint

整数范围:-32768~32767 integer 整数范围:-2147483648~2147483647 decimal(size,dec) 小数

最大:18 位 numeric(precision,dec) decimal 的同义词 float 表示浮点数 float[(precision)] 通 float

real 同 float double precision 双精度浮点数 byteint 有符号整数范围-128~127 date 特殊整数,

格式为 YYMMDD 或 yyymmdd 表示日期图形数据 graphic[(n)] 固定长度的图形字符串默认

长度:1 vargraphic(n) 可变长的图形字符串 long vargraphic 可变长的图形字符串算术运算符 *

*(求幂) mod(取模) 系统变量 date 当前系统日期 time 系统时间 user 当前登陆的用户 database

当前缺省的数据库字符常量.数字常量和计算模式字符文字在 ANSI 方式下区分大小写,而在

Teradata 缺省模式下不区分大小写数字型常量最多可以包含 15 个数字,数字前面的零是无意

义的计算模式是指在 SQL 的 SELECT 语句中直接进行数学计算,如下: select 2*2593; 日期计

算例 :1997 年 3 月 31 日的表达方式 year=(1997-1900)*10000=970000 month=(3*100)=300

day=31 date=970331 于日期有关的数据函数 1.EXTRACT ANSI 标准中 EXTRACT 函数允许

选取日期和时间中任意段或任意间隔的值,Teradata 中 EXTRACT 函数支持日期数据中选取

年 . 月 . 日 . 从时间数据中选取小时 . 分钟和秒 SELECT DATE; 96/11/07 SELECT

EXTRACT(YEAR FROM DATE); 1996 SELECT EXTRACT(MONTH FROM DATE+30); 12

SLEECT EXTRACT(DAY FROM DATE+2); 09 SELECT TIME; 14:52:32 SELECT

EXTRACT(HOUR FROM TIME); 14 SELECT EXTRACT(SECOND FROM TIME+30);

INVALID TIME 2.ADD_MONTHS 利用 CAST 作数据转换 ANSI 标准中利用 CAST 函数将一

种数据类型转换成另一种数据类型 select cast(salary_amount as integer) from employee; select

cast(salary_amount as dec(6,0)) from employee; select cast(last_name as char(5)) from employee

where department_number=401; Teradata 也可以利用 CAST 函数来完成上面的操作,另外,它也

作了扩充 . 为了完成上面相同的操作 , 也可以使用下面的表达方式 select

salary_amount(integer); select salary_amount(dec(6,0)); select last_name(char(5)); Teradata 对

CAST 函数本身也作了扩展,比如为了将显示结果以大写表示,可以使用下面的 SQL 语句

select cast(last_name as char(5) uppercase) from employee where department_number=401; 简单

的宏宏(Macro)的基本特征是: 可以包含一条或多条 SQL 语句可以包含多个 BETQ 语句可以

包含注解存储在数据字典中宏的定义 create macro birthday_list as (select

last_name,first_name,birthdate from employee where department_number=201 order by birthdate;

); 宏的执行 exec birthday_list; 宏的删除 drop macro birthday_list; 宏的显示和改变 show

macro birthday_list 使用 replace macro 命令可以改变宏的定义子查询基本子查询复杂子查询

在子查询中可以使用一些限制符,如下所示: =any 等于 in not=all 等于 not in =some 等于 in

EXISTS 在子查询中的使用 EXISTS 可以使用在自查询中,用来表示查询至少返回一行.如果

前面加上否定词 not,则表示查询时无记录存在.exists 可以代替 in,而 not exists 可以代替 not in

select 'YES' where exists(select department_number from department where department_number

not in(select department_number from employee)); select true where exists(select * from

employee where department_number=600); 关于子查询的一些基本规则子查询必须用括号括

起来子查询可以是 in 或 not in 字句的操作目标也可以是 exists 或 not exists 字句的操作目标

支持限定词 all.any.some 支持 like 或 not like 子查询中可以指定匹配多个字段子查询结果均

为唯一值,即自动去除重复记录,相当于自动加上 distinct 关键词 order by 不能用于子查询内

子查询最多可以指定 64 个表或视图 Fastload 1. 简介： Teradata 作为数据仓库行业的老大，

其对数据并行处理能力令人钦佩，而 Fastload 工具填充数据的速度绝对可以让任何人惊讶。

本文就 Fastload 工具的使用作一介绍，希望能帮助读者快速掌握这个工具的使用。 Fastload

支持批处理的脚本编写方式，也支持交互式的方式。其功能就是从数据文件中把大批数据

快速插入 Teradata 数据库。在数据仓库建设阶段对 ETL 是非常重要的手段，平均比其他第

三方 ETL 工具快 3 倍以上。另外 Fastload 也提供了错误-恢复执行功能，能够继续先前由

于各种原因停下来没有完成的工作。 Fastload 能够从主机、专线、或者一般的 TCP/IP 连接

的计算机节点上运行。使用限制：必须是空表，并且该表上没有外健，也没有除了 UPI 或

者 NUPI 以外的索引。还有要注意的一点，就是即使要上载的表是 MULITISET（允许重复

纪录）的，FASTLOAD 也不会将重复的纪录装入。 2. Fastload 运行过程介绍共有 2 个步骤，

数据装载和数据排序过程装载过程：接收从数据源文件传来的大量数据，并且按照 HASH

算法把数据进行分布，分布到对应的 AMP 里面去。数据记录被写入没有排序的数据块中。

数据排序：把装载步骤生成的大量数据块进行排序操作，并且把数据块写入磁盘。 3. 支持

的文件格式在使用 FASTLOAD 时候，数据源文件有很严格的规则。 FASTLOAD 支持 5 种

文件格式 a） DATA 文件：使用 FASTEXPORT 或者 BTEQ 生成的数据文件。(用.export data

file=… 命令生成)。 b） INDICDATA 文件：和第一种的区别在于文件包含了 NULL 的信息

(用.export indicdata file=…命令生成)。 c） VARTEXT 文件：变长的纪录字段，每个字段之

间用某个特定的字符分隔。 d）无格式文件：需要指定某个字段的起始位置和长度，还要

指定换行符的长度（需要注意的是 UNIX 里面用一个字节长度表示换行，WINDOWS 里面

是 2 个）例如文件格式为： +470000000542+0010+470000000659+Jan 01 2003+

+470000000543+0011+470000000660+Jan 11 2003+ +470000000544+0012+470000000661+Jan

23 2003+ 就必须这样定义字段（ WINDOWS 环境） define SERV_ID (char(12)),

deliml(char(1)), CUST_ID (char(4)), delim2(char(1)), CONTRACT (char(12)), delim3(char(1)),

C_DATE (char(11)), delim4(char(1)), newlinechar(char(2)) e）二进制文件：用得比较少。 4.

Fastload 的 .Begin Loading 命令介绍 .begin loading dbname.tablename errorfiles

dname.e1,dname.e2,checkpoint 1000 indicators; dbname 表示要装入的数据库名称 tablename

表示要装载的表的名称（必须已经存在） e1 和 e2 可以随意指定（不能和数据库中已经存

在的对象重名）用来记录当 FASTLOAD 不成功时候的错误信息，对应错误的数据包等信

息，并且能够利用这 2 个表进行一旦出现上传友问题时候，不用全部重新开始上传的恢复

机制。 Checkpoint 1000 表示每 1000 条记录检查一次，在第一步骤暂停情况下，可以不用

重新从第一条记录开始，而从最后一次检查点处继续装载过程。 Indicators 表示当数据文件

里面有表示空子段的标示时，可以保留空子段。前提是要上传的文件时使用 fastexport 或者

bteq 的 .import indicdata file=…;方式生成的数据文件。 5. Fastload 的“错误恢复执行”功能

a）如果数据在装载过程出错（数据库空间不够或者用户中止程序的执行等），在排除错误

后可以重新执行 FASTLOAD 脚本，如果指定了 CHECKPOINT 参数，数据将在通过

CHECKPOINT 点处开始继续装载。如果没有指定 CHECKPOINT,系统将不知道从哪里开始

是准确的，从第一条开始重新装载。 b）多次装载，这个功能非常有用，比如要装载的表

剩余39页未读，继续阅读

评论收藏

内容反馈

ljun_199006

粉丝: 0
资源: 3

Teradata SQL调优

Teradata_SQL_编写规范

teradata培训及性能调优

SQL性能优化经验

Teradata SQL Advanced

SQL调优

基于Teradata的SQL性能调优.pdf

Teradata SQL

Teradata SQL 基础教程

Teradata SQL(中文).pdf

Teradata SQL Reference Fundamentals

teradata sql命令语句

Teradata SQL教程

Teradata_SQL

Teradata SQL的基础教程

BI ETL informatica Teradata SQLLoader 数据仓库

Teradata SQL 教程宝典

Teradata sql 说明书

Teradata SQL Functions, Operators, Expressions, and Predicates

Teradata SQL Assistant for Microsoft Windows User Guide

Teradata RDBMS SQL

Teradata_SQL基础教程.pdf

Introduction_to_Teradata_SQL.pdf

Teradata-SQL.rar_teradata_teradata sql

NCR_teradata_SQL执行分析

TERADATA SQL Reference:UDF, UDM, and External Stored Procedure Programming

最新资源