《TeraData基础培训PPT课件》由会员分享，可在线阅读，更多相关《TeraData基础培训PPT课件（167页珍藏版）》请在金锄头文库上搜索。
1、TeraData 基础培训基础培训Teradata学习推荐资料：学习推荐资料：TF v800 Read Book1.pdfTF v800 Read Book2.pdfTeradata SQL.pdfETL Automation Manual V2.5.2(GB).docperl编程编程24学时教程学时教程。Teradata Client安装安装Teradata SQL Assistant & Teradata AdministratorDSQLTeradata数据库及数据库及ETL调度工具调度工具Procedure、Macro、View、TablesPI、SI、PPI机制机制数据重分布及数据重
2、分布及JOIN机制机制SQL函数及开发经验函数及开发经验AgendaAgendaTeradata ClientTeradata Client安装安装 选择安装界面上 ，点击Install Product高亮处操作。Teradata Client安装安装Teradata ClientTeradata Client安装安装Teradata Client安装安装Teradata ClientTeradata Client安装安装BTEQ：Basic Teradata SQL 的缩写，SQL工具，DOS界面FastLoad：数据加载工具，可以从文本、数据库想Teradata导入数据。主要特点：1、加载
3、加载速度快，要求导入的目标表示空表；2、具有校验点（Checkpoint）和重启动（Restart）的功能。MutiLoad：数据加载工具，类似FastLoad，可以对导入的目标表进行增、删、改操作。FastExport：将Teradata数据库的数据备份出来。Teradata SQL Assistant： SQL工具，Windows界面Teradata Administrator：数据库管理工具Teradata Client安装安装Teradata Client安装安装配置配置CLICLI打开系统目录system32Driversetc（如2003系统：C:WINNTsystem32driv
4、ersetc）下的hosts文件填写Teradata数据库的CLI接口地址：如10.1.18.79 tedemocop110.1.18.80 tedemocop210.1.18.81 tedemocop310.1.18.82 tedemocop4说明：说明：第一部分为第一部分为TeradataTeradata的的ipip地址，后面为任意名字和地址，后面为任意名字和cop1cop1、cop2cop2的组合。的组合。CopNCopN指的是指的是TeradataTeradata的节点名称。的节点名称。打开odbc数据源管理器、选择系统dsn页点击【添加】按钮Teradata Client安装安装配置
5、配置ODBCODBC选择Teradata驱动程序，点击【Finish】按钮填DB Source、Teradata Info、Default DataBase、Uername、Password等选项点击【ok】按钮完成。Teradata Client安装安装Teradata SQL Assistant & Teradata AdministratorDSQLTeradata数据库及数据库及ETL调度工具调度工具Procedure、Macro、View、TablesPI、SI、PPI机制机制数据重分布及数据重分布及JOIN机制机制SQL函数及开发经验函数及开发经验AgendaAgendaTerad
6、ataSQL AssistantTeradataSQL AssistantTeradataSQL AssistantTeradataSQL Assistant输入输入SQL结果结果SQL历史历史数据数据库，库，表结表结构构TeradataSQL AssistantTeradataSQL Assistant连接数据库连接数据库实行并行实行并行SQL实行实行SQLTeradataSQL AssistantTeradataSQL AssistantTeradataSQL AssistantTeradataSQL Assistant加数据库加数据库数据浏览显示建表DDLTeradataSQL Assi
7、stantTeradataSQL AssistantTeradataSQL AssistantTeradataSQL Assistant将将SQL结果输出文档结果输出文档文档数据插入数据库文档数据插入数据库TeradataSQL AssistantTeradataSQL Assistant定义输出定义输出/插入的文插入的文档字段间档字段间隔符隔符TeradataSQL AssistantTeradataSQL Assistant如果有highlight，只提交highlight 的SQLTeradataSQL AssistantTeradataSQL Assistant最高结果行数数字加千位逗
8、号NULL用什么显示显示字段标题还是名字TeradataSQL AssistantTeradataSQL Assistant Help - Query Builder Help - Query BuilderTeradata Administrator启动Teradata Administrator后，先指定数据源，然后输入用户名和密码，即可建立与Teradata的连接。Teradata Administrator I的左面以层次型结构的方式形象地描述了Teradata的空间结构，最上面是DBC，接下来有一些系统用户和系统数据库，以及用户自创建的用户。系统的数据字典在DBC下面，通过右键选择,
9、就可显示DBC下所有的表、视图和宏。创建、调整、删除、复制用户与数据库，权限的设置，数据库中具体对象的操作等等，甚至还能通过它来递交SQL交易请求都可以通过它完成F12能显示记录操作记录和记录执行的对应的SQL修改库、用户、表、角色等从某个空间移走空间给另外个空间新建库、用户、表、角色等赋权功能选项可执行查询申请记录返回显示区数据库树形结构对象列表Teradata Administrator显示宏和存储过程显示该库或用户下视图该库和用户下对象相关的权限管理显示该库/表下的子库、表的空间情况显示表和索引列表记录返回显示区显示所有对象返回表、视图、宏、存储过程、触发器等对象相关信息列表Terada
10、ta Administrator返回表或视图的记录条数显示该对象（表、视图、宏、函数等）的定义语句显示表的统计信息显示该对象（表、视图）的列信息记录返回显示区浏览数据表的总空间表空间在各AMP分布情况对象的权限Teradata AdministratorTeradata Client安装安装Teradata SQL Assistant & Teradata AdministratorDSQLTeradata数据库及数据库及ETL环境环境Procedure、Macro、View、TablesPI、SI、PPI机制机制数据重分布及数据重分布及JOIN机制机制SQL函数及开发经验函数及开发经验Age
11、ndaAgendaDSQL是什么？是什么？DSQL 象象bteq一样能够可以把一样能够可以把SQL请求提交到请求提交到Teradata 系统。系统。D-sql 脚本和脚本和bteq脚本一样也是由控制命令和脚本一样也是由控制命令和sql 请求组成，同时在脚本中可以方便地使用请求组成，同时在脚本中可以方便地使用 “变量变量”。对于。对于Select语句结果返回到语句结果返回到DSQL的变量中，而的变量中，而bteq只是把结果在只是把结果在Console输出。输出。- 获取 PI ColumnSELECT ColumnName PICOLS FROM DBC.IndicesxWHERE DataBa
12、seName=$TARGETDB AND TableName=$HT AND IndexType=P AND columnName NOT IN($HT_s_date,$HT_e_date)ORDER BY columnPosition;.IF ERRORCODE0 THEN .QUIT 12;- 获取历史表中除了 s_date,e_date以外，其它字段SELECT ColumnName COLS FROM DBC.ColumnsxWHERE DataBaseName=$TARGETDB AND TableName=$HT AND columnName NOT IN($HT_s_date,$
13、HT_e_date)ORDER BY ColumnID;.IF ERRORCODE 0 THEN .QUIT 12;- 创建当日表 PI 与历史表同CREATE VOLATILE MULTISET TABLE $ND AS (SELECT $COLS FROM $TARGETDB.$HT ) WITH NO DATA PRIMARY INDEX($PICOLS) ON COMMIT PRESERVE ROWS;.IF ERRORCODE0 THEN .QUIT 12;DSQL功能介绍功能介绍Dsql 的启动方法的启动方法dsql c -f v1=txt1 v2=nn1 第一参数为数据库登陆信息
14、文件第一参数为数据库登陆信息文件,其内容包括：其内容包括：TD_SYS = dbc TD_USER=billTD_PASS=bill # 口令自动加密。#长度小于16时，认为是明文，加密后长度为16Dsql脚本中包含两类命令脚本中包含两类命令控制命令控制命令, 与与Bteq一样以一样以“.”开始开始SQL Request 命令，非以命令，非以“.”开始，以开始，以 “;” 和换行符作为结束。和换行符作为结束。DSQL功能介绍（工作变量）功能介绍（工作变量）DSQL工作变量工作变量DSQL 的工作变量为的工作变量为“字符串字符串”类型，可以出现在脚本的任意位置。使用方类型，可以出现在脚本的任意位
15、置。使用方法和法和perl/shell 的变量相似；的变量相似； $var1 或或 $var1DSQL 的工作变量，除了从初始参数文件中获取外，它增加了的工作变量，除了从初始参数文件中获取外，它增加了 select 结果结果转存到工作变量中的功能。这就使得转存到工作变量中的功能。这就使得 sql 脚本真正地脚本真正地“动态动态”了。了。变量的赋值方法：变量的赋值方法：1)从初始参数文件中获得从初始参数文件中获得2)从从select的结果中获得，变量名称就是的结果中获得，变量名称就是“字段名称字段名称”，变量的值为，变量的值为“字字段内容段内容”；当；当select的结果为多条记录时，变量的值为
16、该字段所有记录内的结果为多条记录时，变量的值为该字段所有记录内容以容以“,”连接起来的大字符串。连接起来的大字符串。3) 启动启动dsql 的附加参数，可以对变量赋值的附加参数，可以对变量赋值4) 通过控制命令通过控制命令 .run file= V5=abc V6=22DSQL功能介绍（流程控制）功能介绍（流程控制）DSQL控制命令控制命令DSQL 的控制命令与的控制命令与bteq的控制命令表现形式很相似；由于的控制命令表现形式很相似；由于ETL脚本很少使脚本很少使用用Bteq做数据的做数据的Import和和export, 所以所以DSQL舍弃了数据舍弃了数据Import和和export功能。
17、并且也舍弃了功能。并且也舍弃了.logon 命令，仅使用初始参数文件的内容进行命令，仅使用初始参数文件的内容进行Teradata的连接，脚本中所有的的连接，脚本中所有的SQL语句都是通过这个连接提交到语句都是通过这个连接提交到 TeradataDSQL 控制命令控制命令1) .IF 条件判断命令条件判断命令2) .Goto/.Label命令命令2) .Run File 子脚本调用子脚本调用3) .Return 子脚本返回子脚本返回4) .Quit DSQL 结束命令结束命令5) .OS 操作系统功能调用操作系统功能调用6) .Define SQL-Block 定义命令定义命令7) .Sleep
18、 程序挂起程序挂起 n 秒钟秒钟 DSQL 将会给将会给ETL带来带来?ETLETL脚本脚本“单一单一”的执行环境的执行环境脚本真正地脚本真正地“动态动态”脚本更简洁和脚本更简洁和“易读易读”可以取消一条记录一个字段的StageTable可以方便地把复杂SQL语句中的“单值”Derived Table 隔离出去Insert into target1(f1, f2) select f1,f2 from src1, (select max(f2) maxf2 from src2) MWhere f2 = maxf2-Select max(f2) maxf2maxf2 from src2;Inser
19、t into target1(f1,f2)Select f1,f2Where f2 = $maxf2maxf2DSQL 将会给将会给ETL带来带来?知识知识“共享共享”更方便更方便把那些同种类型的“处理过程”归纳总结，“抽象”成相对固定的算法，便于其他项目的使用；.run file= .run file=$HOME/bin/insert_his.dsql HT=tab1把那些对某种类型字段做同样的“操作”归纳总结，“抽象”成相对固定的宏定义(SQL Block),在其他ETL脚本中可以方便的重复使用,或在当前脚本中多次引用，从而提高脚本开发的效率.Define char8_to_date (1
20、,)Select &char8_to_date(f1), &char8_to_date(f2)Dsql 支持的支持的 OS-PlatformnWin32nLinux-i386 (Suse Linux 10 32-bit)nLinux-x64 (Suse Linux 10 64-bit)nMPRASTeradata Client安装安装Teradata架构架构Teradata SQL Assistant & Teradata AdministratorDSQLTeradata数据库及数据库及ETL调度工具调度工具Procedure、Macro、View、TablesPI、SI、PPI机制机制数据
21、重分布及数据重分布及JOIN机制机制SQL函数及开发经验函数及开发经验AgendaAgenda数据库环境数据库环境ETL AUTOMATION服务器端目录架构图服务器端目录架构图/APP 用来存放作业 (Job) 的作业指令文件 (任务脚本)。 在这个目录下会依照ETL系统来建立子目录， 在各系统的子目录下才是各所属作业所对应的目录。/APP/$SYS/$JOB/bin 用来存放JOB作业的脚本/bin 用来存放 ETL Automation 系统程序的执行文件。也会放一些自定义的函数包。/etc 用来存放 ETL Automation 机制的一些配置文件，如登录文件。 登录automatio
22、nETL调度工具调度工具Automation操作演示操作演示ETL调度工具调度工具Automation操作演示操作演示配置JOB配置JOB SourceETL调度工具调度工具Automation操作演示操作演示ETL调度工具调度工具Automation操作演示操作演示配置JOB DependencyETL调度工具调度工具Automation操作演示操作演示配置JOB streamETL调度工具调度工具作业调度和作业日志作业调度和作业日志重置作业状态ETL调度工具调度工具作业调度和作业日志作业调度和作业日志强制重跑(作业依赖条件满足情况下运行）立刻强制重跑(不判断作业依赖条件）ETL调度工具调度
23、工具作业调度和作业日志作业调度和作业日志作业调度和作业日志作业调度和作业日志查看日志ETL调度工具调度工具ETL调度工具调度工具作业日志作业日志点击view log File可以看明细日志，view Script File可以看脚本，这里可以对脚本更改，但严禁使用Teradata Client安装安装Teradata架构架构Teradata SQL Assistant & Teradata AdministratorDSQLTeradata数据库及数据库及ETL调度工具调度工具Procedure、Macro、View、TablesPI、SI、PPI机制机制数据重分布及数据重分布及JOIN机制机
24、制SQL函数及开发经验函数及开发经验AgendaAgendaProcedureREPLACE PROCEDURE DWMART_XXX.PROC_TEST(IN report_date DATE, OUT returnflag INTEGER)BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET returnflag = 0; END; CREATE MUTLISET TABLE TEMP_XX as . . ; INSERT INTO TEMP_XX . ; DELETE FROM TB_XXX WHERE STATISTICS_DT=C
25、AST(:report_date AS DATE FORMAT YYYYMMDD) ; INSERT INTO TB_XXX SELECT STATISTICS_DT ,. ,. FROM TEMP_XX GROUP BY 1,2,. ;SET returnflag = 1; END;参数变量声明In 输入Out 输出INOUT 输入输出参数变量声明变量赋值调存储过程CALL DWMART_XXX.PROC_TEST( 20100501,returnflag )MacroCREATE MACRO new_dept(dept INTEGER,budget DEC(10,2) DEFAULT 0,
26、name CHAR(30),mgr INTEGER)AS( INSERT INTO department(department_number,department_name,budget_amount,manager_employee_number)VALUES( :dept,:name,:budget,:mgr);SELECT department_number (TITLE number),department_name (TITLE name),budget_amouunt (TITLE budget),manager_employee_number (TITLE manager)FRO
27、M departmentWHERE department_number = :dept;);运行宏new_deptEXEC new_dept (505,610000.00, Marketing Research, 1007);ViewREPLACE VIEW XXXX.VIE_SYS_XXXASLOCKING TABLE TB_XXX_A FOR ACCESS LOCKING TABLE PDATA.TB_XXX_B FOR ACCESS LOCKING TABLE PDATA.TB_XXX_C FOR ACCESS SELECT T1.COLUMN1 ,. ,T2.COLUMN1 ,. ,T
28、3.COLUMN1 ,.FROM PDATA.TB_XXX_A T1 INNER JOIN PDATA.TB_XXX_B T2 ON T1.COLUMNXX = T2.COLUMNXX AND T1.COLUMNXY = T2.COLUMNXY INNER JOIN PDATA.TB_XXX_C T3 ON T1.COLUMNX = T3.COLUMNX WHERE T1.COLUMN1=XXXXX;REPLACE VIEW XXXX.VIE_SYS_XXXASSELECT T1.COLUMN1 ,. ,T2.COLUMN1 ,. ,T3.COLUMN1 ,.FROM PDATA.TB_XXX
29、_A T1 INNER JOIN PDATA.TB_XXX_B T2 ON T1.COLUMNXX = T2.COLUMNXX AND T1.COLUMNXY = T2.COLUMNXY INNER JOIN PDATA.TB_XXX_C T3 ON T1.COLUMNX = T3.COLUMNX WHERE T1.COLUMN1=XXXXX;TableSet不允许记录重复不允许记录重复CREATE SET TABLE pmart.RPT_NM_GRP_PRE_WARN_MON,( CAL_Month INTEGER TITLE 统计月份,ORG_NUM CHAR(12) TITLE 集团编号
30、,City_ID CHAR(3) TITLE 地市标识,ORG_SUBS_GRP_NUM CHAR(10) TITLE 集团用户群编号,ORG_Title VARCHAR(200) TITLE 集团名称,ORG_Level CHAR(2) TITLE 集团级别,STAT_Item_Code CHAR(2) TITLE 统计项,STAT_Value DECIMAL(18,2) TITLE 统计值) PRIMARY INDEX (ORG_NUM);MultiSet允许记录重复允许记录重复CREATE MULTISET TABLE pmart.RPT_NM_GRP_PRE_WARN_MON,( CA
31、L_Month INTEGER TITLE 统计月份,ORG_NUM CHAR(12) TITLE 集团编号,City_ID CHAR(3) TITLE 地市标识,ORG_SUBS_GRP_NUM CHAR(10) TITLE 集团用户群编号,ORG_Title VARCHAR(200) TITLE 集团名称,ORG_Level CHAR(2) TITLE 集团级别,STAT_Item_Code CHAR(2) TITLE 统计项,STAT_Value DECIMAL(18,2) TITLE 统计值) PRIMARY INDEX (ORG_NUM);假设原有1286449条记录插入:15285
32、3条记录耗时：15秒假设原有1286449条记录插入:152853条记录耗时：1秒UNIQUE建议：建议：Teradata中都用中都用 MultiSet,若真要限定唯一性，可以通过若真要限定唯一性，可以通过UPI或或USI实现实现Table永久表永久表临时表临时表可变临时表在一个会话中，能够被多个查询使用。占用SPOOLSPACE，在cache中保留表的定义，spool缓冲区中物化， 不使用数据字典每个会话可以建立64个，可以随时被手动删除，会话结束时自动删除。全局临时表每个会话最多可以物化32个全局临时表。空间占用Temporary Space，对会话而言，每个实例是本地的。物化表中内容与其
33、他会话不共享。会话结束后，物化的表被自动删除。(但基础定义仍然存储在数据字典中)导出表只有一个查询要求使用临时表，其他查询都不要求。空间占用SPOOL SPACE，使用 SELECT语句在spool缓冲区中创建导出表。查询结果只使用一次。永久表永久表CREATE MULTISET TABLE tctap.tap_c_kpi_assess_2 ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( KPI_ID CHAR(8) CHARACTER SET LATIN CASESPECIFIC TITLE K
34、PI标识标识 NOT NULL ,FREQ_ID CHAR(2) CHARACTER SET LATIN CASESPECIFIC TITLE 频度代码频度代码 NOT NULL ,DATA_DATE INTEGER TITLE 指标日期指标日期 NOT NULL ,DIM_VALUE_ID1 CHAR(8) CHARACTER SET LATIN CASESPECIFIC TITLE 维度维度1 NOT NULL DEFAULT Z ,DIM_VALUE_ID2 CHAR(8) CHARACTER SET LATIN CASESPECIFIC TITLE 维度维度2 NOT NULL DEF
35、AULT Z ,DIM_VALUE_ID3 CHAR(8) CHARACTER SET LATIN CASESPECIFIC TITLE 维度维度3 NOT NULL DEFAULT Z ,. ,DIM_VALUE_ID18 CHAR(8) CHARACTER SET LATIN CASESPECIFIC TITLE 维度维度18 NOT NULL DEFAULT Z ,DIM_VALUE_ID19 CHAR(8) CHARACTER SET LATIN CASESPECIFIC TITLE 维度维度19 NOT NULL DEFAULT Z ,DIM_VALUE_ID20 CHAR(8) C
36、HARACTER SET LATIN CASESPECIFIC TITLE 维度维度20 NOT NULL DEFAULT Z ,DIM_VALUE_IDn CHAR(8) CHARACTER SET LATIN CASESPECIFIC TITLE 维度维度n NOT NULL DEFAULT Z ,KPI_ASSESS_TYPE_ID CHAR(4) CHARACTER SET LATIN CASESPECIFIC TITLE KPI目标类型目标类型ID NOT NULL ,KPI_ASSESS_RESULT_ID CHAR(2) CHARACTER SET LATIN CASESPECI
37、FIC TITLE KPI目标值返回值目标值返回值ID NOT NULL ,KPI_ASSESS_VALUE DECIMAL(18,2) TITLE KPI目标值目标值)PRIMARY INDEX ( DATA_DATE ,KPI_ID , DIM_VALUE_ID1)PARTITION BY RANGE_N(DATA_DATE BETWEEN 20000101 AND 20191231)UNIQUE INDEX (KPI_ID,FREQ_ID,DATA_DATE,DIM_VALUE_ID1,DIM_VALUE_ID2,DIM_VALUE_ID3,.,DIM_VALUE_ID18,DIM_VA
38、LUE_ID19,DIM_VALUE_ID20,DIM_VALUE_IDn,KPI_ASSESS_TYPE_ID,KPI_ASSESS_RESULT_ID);COLLECT STATISTICS ON tctap.tap_c_kpi_assess_2 COLUMN ( DATA_DATE ,KPI_ID , DIM_VALUE_ID1);COLLECT STATISTICS ON tctap.tap_c_kpi_assess_2 COLUMN ( DATA_DATE );COLLECT STATISTICS ON tctap.tap_c_kpi_assess_2 COLUMN (KPI_ID,
39、FREQ_ID,DATA_DATE,DIM_VALUE_ID1,DIM_VALUE_ID2,DIM_VALUE_ID3,.,DIM_VALUE_ID18,DIM_VALUE_ID19,DIM_VALUE_ID20,DIM_VALUE_IDn,KPI_ASSESS_TYPE_ID,KPI_ASSESS_RESULT_ID);COLLECT STATISTICS ON tctap.tap_c_kpi_assess_2 COLUMN (KPI_ID);设计原则：设计原则：1、调整PI，在保留一定数据分布均匀度的基础上，把PI字段从N个减少为3个，提高数据查询速度。2、增加PPI，提高数据插入性能与查
40、询性能。3、增加USI，而不是把Multiset改为Set；虽然这两种方式都能保证数据记录的唯一性，但是前者效率会更高。4、增加统计信息，有助于系统优化SQL查询。可变临时表可变临时表建表语句：建表语句：CREATE VOLATILE MULTISET TABLE vt_RETAIN_ANLY_MON ( col1 , col2）PRIMARY INDEX (PI_Cols) ;复制表结构：复制表结构： CREATE MULTISET VOLATILE TABLE VT_APP_AGG_INDEX_H AS DWMart_DOA.DOA_APP_AGG_INDEX_H WITH DATA(NO
41、 DATA) ON COMMIT PRESERVE ROWS;CREATE VOLATILE MULTISET TABLE VT_RETAIN_ANLY_MON AS ( SELECT col1,col2, FROM WHERE GROUP BY . ）WITH DATA PRIMARY INDEX (PI_Cols) ON COMMIT PRESERVE ROWS;ON COMMIT PRESERVE ROWS;全局临时表全局临时表建表语句建表语句CREATE GLOBAL TEMPORARY TABLE gt_deptsal(deptno SMALLINT,avgsal DEC(9,2),
42、maxsal DEC(9,2),minsal DEC(9,2),sumsal DEC(9,2),empcnt SMALLINT)ON COMMIT PRESERVE ROWS;CREATE INDEX (empcnt)ON gt_deptsal;COLLECT STATISTICS ON gt_deptsal INDEX (deptno);COLLECT STATISTICS ON gt_deptsal COLUMN avgsal;HELP STATISTICS gt_deptsal;TEMPORARY导出表示例：示例：SELECT* FROM (SELECTdeptno,SUM(sal) A
43、S sal_sum FROMscott.emp GROUP BY deptno)tmp固化临时表固化临时表固化临时表，就是把查询结果存放到一张物理表。固化临时表，就是把查询结果存放到一张物理表。共下次分析或他人使用。共下次分析或他人使用。Session断开之后，仍然可以使用。断开之后，仍然可以使用。在性能优化中也常被用到的一种做法。在性能优化中也常被用到的一种做法。示例示例1： CREATE MULTISET TABLE tttemp.TMP_BOSS_VOIC AS （ SELECT * FROM pview.vw_net_gsm_nl) WITH NO DATA PRIMARY INDEX
44、 (subs_id);INSERT INTO tttemp.TMP_BOSS_VOICSELECT * FROM pview.vw_net_gsm_nl WHERE *;示例示例2：CREATE MULTISET TABLE tttemp.TMP_BOSS_VOIC AS （ SELECT * FROM pview.vw_net_gsm_nl WHERE *) WITH DATA PRIMARY INDEX (subs_id);示例示例3：(复制表，数据备份）复制表，数据备份）CREATE MULTISET TABLE tttemp.TMP_BOSS_VOIC AS pdata.tb_net_
45、gsm_nl WITH DATA ;修改表定义修改表定义常见的表定义修改操作：增加字段修改字段长度建议的操作流程建议的操作流程1.Rename table db.tablex as db.tabley;2.通过Show table语句获得原表db.tablex的定义3.定义新表： db.tablex4.Insert into db.tablex(。) select 。 From db.tabley;5.Drop table db.tabley;Teradata提供ALTER TABLE语句，可进行修改表定义但，不建议采用ALTER TABLE方式。Update/Delete操作操作UPDATE
46、 Customer SET Credit_Limit = Credit_Limit * 1.20 ; CREATE multiset TABLE Customer_N AS Customer with no data;INSERT INTO Customer_NSELECT Credit_Limit * 1.20 FROM Customer ;DROP TABLE Customer ;RENAME TABLE Customer_N TO Customer ;CREATE multiset TABLE Trans_N as Trans with no data;INSERT INTO Trans
47、_NSELECT * FROM Trans WHERE Trans_Date 981231;DROP TABLE Trans;RENAME TABLE Trans_N TO Trans;先建立空表，通过insert / select 方式插入数据这是非常快的操作！先备份，然后做变更操作，更加安全！对于大表进行Update/DELETE操作，将耗费相当多的资源与相当长的时间。Update/Delete操作，需要事务日志TJ（Transient Journal）以防意外中断导致数据受到破坏在Update/Delete操作中途被Cancel，系统则需回滚，这将耗更多的资源与时间！DELETE FRO
48、M Trans WHERE Trans_Date 1000;Examples of Full Table Scans:Logical Example of NPPI versus PPI4 AMPs with Orders Table defined with PPI on O_Date.RHO_#O_DateRHO_#O_DateRHO_#O_DateRHO_#O_Date35100706/0126100206/0124100406/0120100506/0139101106/0136101206/0132100306/0143101006/0114100106/0106100906/010
49、4100806/0108100606/0103101606/0217101306/0248102306/0207101706/0216102106/0245101506/0209101806/0227101406/0244102206/0202102406/0211101906/0222102006/0201102806/0312103106/0328103206/0310103406/0329103306/0334102906/0319102506/0340103506/0347102706/0325103606/0331102606/0346103006/0323104006/043010
50、3806/0442104706/0413103706/0421104506/0436104306/0405104806/0415104206/0433103906/0418104106/0438104606/0441104406/04 4 AMPs with Orders Table defined with NPPI.01102806/0312103106/0328103206/0310103406/0329103306/0334102906/0319102506/0340103506/0347102706/0325103606/0331102606/0346103006/030310160
51、6/0217101306/0248102306/0207101706/0216102106/0245101506/0209101806/0227101406/0244102206/0202102406/0211101906/0222102006/0214100106/0135100706/0139101106/0106100906/0126100206/0136101206/0104100806/0124100406/0132100306/0108100606/0120100506/0143101006/0123104006/0430103806/0442104706/0413103706/0
52、421104506/0436104306/0405104806/0415104206/0433103906/0418104106/0438104606/0441104406/04RHO_#O_DateRHO_#O_DateRHO_#O_DateRHO_#O_Date Teradata Client安装安装Teradata SQL Assistant & Teradata AdministratorDSQLTeradata数据库及数据库及ETL调度工具调度工具Procedure、Macro、View、TablesPI、SI、PPI机制机制数据重分布及数据重分布及JOIN机制机制SQL函数及开发经
53、验函数及开发经验AgendaAgendaJoin之前的重分布AMP1SELECT. . .FROMTable1 T1INNER JOINTable2 T2ONT1.A = T2.A;Join 的列都是两个表的的列都是两个表的PI不需要数据重分布不需要数据重分布.Subs_idBCPI100 214 433T2Subs_idBCPI100 725002Subs_idBCPI200 214433T2Subs_idBCPI200 725002AMP2话单表用户资料表Join之前的重分布之前的重分布AMP1SELECT. . .FROM用户资料表用户资料表 T1INNER JOIN客户资料表客户资料表
54、ON T1.Cust_id = T2.Cust_id;Join 的列都是在一个表上是的列都是在一个表上是PI，另外一个表上不是，另外一个表上不是PI是是PI的表不需要重分布的表不需要重分布.Subs_id Cust_idPI100 214T2Cust_idBCPI 214 725002Subs_id Cust_idPI200 214T2Cust_idBCPI408133009AMP2用户资料表客户资料表Join之前的重分布之前的重分布AMP1Subs_id Cust_idPI100 214 100 408T2Cust_idBCPI 214 725002Subs_id Cust_idPI200
55、214T2Cust_idBCPI408133009AMP2用户资料表客户资料表Subs_id Cust_id PI100 214 200 214Subs_id Cust_id PI 100 408Spool空间重分布的问题重分布的问题大表的代码字段与小表的PI字段SELECT city_name, COUNT(DISTINCT subs_id)FROM TB_Ofr_Subs AJOIN TB_CDE_City BON A.city_id = B.city_id如果选择重分布的策略，意味着将所有的用户按照地市重分布到所有的AMP上系统总共130个AMP、总共13个市意味着只有13个AMP有数据
56、，其它AMP根本不能被用到同时，由于A市的用户数较大，意味这A市所在的AMP上数据量很大，造成了13个AMP运行过程中的不均匀。Join之前的复制小表到所有的之前的复制小表到所有的AMPAMP1City_id City_namePI100 A市市 T2Cust_id City_idPI 214 200 City_id City_namePI200 A市市T2Cust_id City_idPI408 100AMP2地市代码表用户资料表 City_id City_name 100 A市市 200 B市市 City_id City_name 100 A市市 200 B市市Spool空间复制小表到Sp
57、ool空间1M rows1M rows1M rows1M rows1M rows1M rows1M rows1M rowsTableTable1M rows8M rowsSPOOL(表被复制到所有的表被复制到所有的AMP上上)1M rows8M rows1M rows8M rows1M rows8M rows1M rows8M rows1M rows8M rows1M rows8M rows1M rows8M rows数据已经到了一个数据已经到了一个AMP上，关联怎么做？上，关联怎么做？关联策略 Merge Join用来用来Join的记录必须位于相同的的记录必须位于相同的AMP上上Merge
58、Join 仅仅读取每个表一次.对于等值条件的Join，优化器经常会选用Merge Join.通常情况下比product join的效率更高.Merge join 处理流程处理流程:找到一个小表.如果需要:将一个或者两个表要用到的数据都放在Spool空间里.基于Join列的hash值将记录重分布到相应的AMP.根据Join列的hash顺序对spool里面的记录进行排序.对于Join列的Hash值相同的记录进行比较.与Product Join相比，比较次数大大降低.A3DataA3DataA3DataB7DataB7DataC4DataC4DataJoin ColumnHashA3DataB8Da
59、taC4DataJoin ColumnHash适用情况：适用情况：两个表的数据量都比较大时例如 100万 30万关联策略 Nested JoinsThis is a special join case.This is the only join that doesnt always use all of the AMPs.It is the most efficient in terms of system resources.It is the best choice for OLTP applications.To choose a Nested Join, the Optimizer m
60、ust have:An equality value for a unique index (UPI or USI) on Table1.A join on a column of that single row to any index on Table2.The system retrieves the single row from Table1.It hashes the join column value to access matching Table2 row(s).Example:SELECT E.Name,D.NameFROMEmployee EINNER JOIN Depa
61、rtment DONE.Dept = D.Dept WHEREE.Enum = 5;EmployeeEnumNameDeptPKFKUPI1BROWN2002SMITH3103JONES3104CLAY4005PETERS1506FOSTER4007GRAY3108BAKER310DepartmentDeptNamePKUPI150PAYROLL200FINANCE310MFG.400EDUCATION关联策略 Product JoinRows must be on the same AMP to be joined.DataDataDataDataDataDataDataDataDataDa
62、ta不对记录做排序如果内存里面放不下的时候需要多次读取某张表.Table1 的每条记录要与 Table2 的每条记录进行比对.满足条件的记录会被放到 spool空间中.之所以会被称作Product Join 是因为:总共的比较次数总共的比较次数 = Table 1 的记录条数的记录条数 * Table 2的记录条数的记录条数当内存里面不能存放某一个表的所有数据的时候，这种比较会变得非常的消耗资源，因为总是需要内外存的交换。如果没有where条件，Product Join通常会产生无意义的结果.Product Join 处理步骤:找到小表并在Spool空间中复制到所有AMP上.在每个AMP上，S
63、pool空间里的小表的每一行和大表在该AMP上的每一行做Join.适用情况：适用情况：大表非PI字段对小表例如 30万 50关联策略 Hash Join优化器技术有效的将小表放在优化器技术有效的将小表放在Cache内存中，并且与未排序的大表进行关联内存中，并且与未排序的大表进行关联.Row Hash Join的处理流程的处理流程:找到小表.重分布小表或者复制小表到各个AMP的内存中.将小表在Cache内存中按照join字段的 row hash顺序排序.将记录放在内存中.用大表的join字段的row hash在内存中进行折半查找.这种join将减少大表的排序、重分布或者拷贝.EXPLAIN 将会
64、看见类似于“Single Partition Hash Join”的术语.Join ColumnHashC4DataA3DataC6DataF6DataB7DataC4DataA3DataA3DataB8DataC4DataJoin ColumnHashCache Memory适用情况：适用情况：大表非PI字段对中等小的表例如 700万 1万Merge Join (e.g.)1）关联表）关联表PI相同相同 SELECT a.acyc_id, COALESCE(c.city_code,*), COALESCE(c.cmcc_brand_code,*), COALESCE(c.user_id,*)
65、 FROMdwpmdvw.vw_smsdetail_mon a LEFT JOIN dwpmdvw.vw_user_info_mon c ON a.user_id=c.user_id WHERE a.acyc_id=2006112）PI不同不同 SELECT COALESCE(a.city_code,*), COALESCE(a.cmcc_brand_code,*), COALESCE(a.user_id,*) FROM dwpmdvw.vw_user_info_mon a LEFT JOIN dwpdata.tf_f_customer b ON a.cust_id=b.cust_id WHE
66、RE a.acyc_id=200611 AND b.eparchy_code = 0913如何确认自己的查询效率很高如何确认自己的查询效率很高Explain SQL语句语句数据分布的变化（数据分布的变化（Redistribute，Duplicate）. which is redistributed by hash code to all AMPs Redistributing data (in SPOOL) in preparation for a join. which is duplicated on all AMPs Duplicating data (in SPOOL) from th
67、e smaller table in preparation for a join.Merge Join (Matching Primary Indexes)QUERYEXPLAIN SELECTLast_Name, First_Name,Area_Code, Phone_Number,ExtensionFROM Employee EINNER JOIN Emp_Phone PON E.Employee_Number = P.Employee_NumberORDER BY1, 2;EXPLANATION V2R6.1 EXPLAIN-1)First, we lock a distinct TF
68、ACT.pseudo table for read on a RowHash to prevent global deadlock for TFACT.P. 2)Next, we lock a distinct TFACT.pseudo table for read on a RowHash to prevent global deadlock for TFACT.E. 3)We lock TFACT.P for read, and we lock TFACT.E for read. 4)We do an all-AMPs JOIN step from TFACT.E by way of a
69、RowHash match scan with no residual conditions, which is joined to TFACT.P. TFACT.E and TFACT.P are joined using a merge join, with a join condition of (TFACT.E.Employee_Number = TFACT.P.Employee_Number). The input table TFACT.E will not be cached in memory, but it is eligible for synchronized scann
70、ing. The result goes into Spool 1 (group_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 1 by the sort key in spool field1. The result spool file will not be cached in memory. The size of Spool 1 is estimated with low confidence to be 52,000 rows. The estimated time for t
71、his step is 0.72 seconds. 5)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.-The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.72 seconds. Employee(26,000 rows)Emp_Phone(52,000 rows)Employee_Numbe
72、r Last_Name First_Name Area_Code Phone_Number ExtensionHash JoinHash JoinEmployee(26,000 rows)Department(1403 rows)Last_Name First_Name Dept_NameDept_NumberQUERYEXPLAIN SELECTLast_Name, First_Name,Dept_NameFROM Employee EINNER JOIN Department DON E.Dept_Number = D.Dept_Number;EXPLANATION V2R6.1 EXPL
73、AIN-: 4)We do an all-AMPs RETRIEVE step from TFACT.E by way of an all-rows scan with a condition of (NOT (TFACT.E.Dept_Number IS NULL) into Spool 2 (all_amps), which is redistributed by hash code to all AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning.
74、 The size of Spool 2 is estimated with high confidence to be 26,000 rows. The estimated time for this step is 0.52 seconds. 5)We do an all-AMPs JOIN step from TFACT.D by way of an all-rows scan with no residual conditions, which is joined to Spool 2 (Last Use) by way of an all-rows scan. TFACT.D and
75、 Spool 2 are joined using a single partition hash join, with a join condition of (Dept_Number = TFACT.D.Dept_Number). The result goes into Spool 1 (group_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 low confidenc
76、e to be 26,000 rows. The estimated time for this step is 0.33 seconds. 6)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.-The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.85 seconds. Product Join
77、QUERYEXPLAINSELECT D.Dept_Name, E.Employee_Number, E.Last_Name, E.First_NameFROM Employee E INNER JOIN Department D ON E.Dept_Number = D.Dept_NumberOR E.Employee_Number = D.Dept_Mgr_NumberORDER BY1, 2, 3, 4;EXPLAIN output on following page.Employee(26,000 rows)Department(1403 rows)Employee_Number La
78、st_Name First_Name Dept_NameDept_NumberEmployee_NumberDept_Mgr_NumberProduct Join (cont.)EXPLAIN EXPLANATION V2R6.1 EXPLAIN-1) First, we lock a distinct TFACT.pseudo table for read on a RowHash to prevent global deadlock for TFACT.E.2) Next, we lock a distinct TFACT.pseudo table for read on a RowHas
79、h to prevent global deadlock for TFACT.D. 3) We lock TFACT.E for read, and we lock TFACT.D for read. 4) We do an all-AMPs RETRIEVE step from TFACT.D by way of an all-rows scan with no residual conditions into Spool 2 (all_amps), which is duplicated on all AMPs. The size of Spool 2 is estimated with
80、high confidence to be 11,224 rows. The estimated time for this step is 0.03 seconds. 5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an all-rows scan, which is joined to TFACT.E by way of an all-rows scan with no residual conditions. Spool 2 and TFACT.E are joined using a product jo
81、in, with a join condition of (TFACT.E.Dept_Number = Dept_Number) OR (TFACT.E.Employee_Number = Dept_Mgr_Number). The input table TFACT.E will not be cached in memory, but it is eligible for synchronized scanning. The result goes into Spool 1 (group_amps), which is built locally on the AMPs. The resu
82、lt spool file will not be cached in memory. The size of Spool 1 is estimated with no confidence to be 973,873 rows. The estimated time for this step is 24.34 seconds.6) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.- The contents of Spool 1 are sent back
83、 to the user as the result of statement 1. The total estimated time is 24.38 seconds.n-Table JoinsAll n-Table joins are reduced to a series of two-table joins.The Optimizer attempts to determine the best join order.Collected Statistics on Join columns help the Optimizer choose wisely.SELECT . FROM T
84、able_A, Table_B, Table_C, Table_D WHERE . . . ;SPOOLFILETable_ATable_BTable_CTable_DSPOOLFILESPOOLFILESPOOLFILESPOOLFILESPOOLFILERESULTJoin Plan 1SPOOLFILETable_ATable_BTable_CTable_DSPOOLFILESPOOLFILESPOOLFILESPOOLFILESPOOLFILERESULTJoin Plan 2AgendaAgendaTeradata Client安装安装Teradata SQL Assistant &
85、 Teradata AdministratorDSQLTeradata数据库及数据库及ETL调度工具调度工具Procedure、Macro、View、TablesPI、SI、PPI机制机制数据重分布及数据重分布及JOIN机制机制SQL函数及开发经验函数及开发经验Teradata帮助系统帮助系统 HELP 命令命令 SHOW 命令命令 EXPLAIN 命令命令在在TYPE栏中，可能的输出说明如下：栏中，可能的输出说明如下：类型类型说明说明I INTEGERI1 BYTEINTI2 SMALLINTDA DATED DECIMALCV CHARACTER VARIABLE (VARCHAR)CF
86、CHARACTER FIXED (CHAR)逻辑表达式运算符逻辑表达式运算符标准的逻辑表达式运算符种类如下所示：标准的逻辑表达式运算符种类如下所示：运算符种类运算符种类符号符号含义含义比较运算符=NOT BETWEEN AND 等于不等于大于小于大于或等于小于或等于介于a和b之间或不介于a和b之间NOT IN IS NOT NULLNOT EXISTSLIKENOT IN IS NOT NULL NOT EXISTS LIKE属于或不属于某个集合一个数值是空值或不是空值一个查询至少返回一行或不返回任何行与某个数或值匹配LIKE中限定词的使用：中限定词的使用：利用一些限定词可以扩充利用一些限定词
87、可以扩充LIKE在字符串匹配方面的功能。可以使用的限定词包含：在字符串匹配方面的功能。可以使用的限定词包含：限定词限定词含义含义ANY 与一个或多个数值匹配SOME (ANY的同义词) 同上ALL 与列举的所有数值匹配例如：查询员工姓中任意位置有字母例如：查询员工姓中任意位置有字母E和和S的员工，可以使用下面的的员工，可以使用下面的SQL语句。语句。SELECT first_name,last_nameFROM employeeWHERE last_name LIKE ALL (%E%,%S%);如果改变一下上面的问题，要求查找员如果改变一下上面的问题，要求查找员工姓中任意位置有字母工姓中任意
88、位置有字母E或者或者S的员的员工，则应使用工，则应使用ANY或或SOME。SELECT first_name,last_nameFROM employeeWHERE last_name LIKE ANY (%E%,%S%);大小写敏感大小写敏感SELECT 1 WHERESc (CS)=sc;SELECT 1 WHERE(Sc(UPPERCASE)(CASESPECIFIC)=sc;SELECT 1 WHERE Sc(CASESPECIFIC)=sc;SELECT 1 WHERE Sc(NOT CASESPECIFIC)=sc;SELECT UPPER(sc);SELECT LOWER(Sc)
89、;SELECT Sc(UPPERCASE);Teradata / Oracle 常用语法比较常用语法比较 Oracle Oracle TeradataTeradatanvl(f1,f2,) nvl(f1,f2,) coalesce(f1,f2) coalesce(f1,f2) decode(AA,V1,R1,V2,R2)decode(AA,V1,R1,V2,R2) case when AA=V1 then R1 case when AA=V1 then R1 when AA=V2 then R2 when AA=V2 then R2 else NULL else NULL End Endto_
90、char(d1,YYYYMMDD) to_char(d1,YYYYMMDD) select cast (cast(dt1 as date format select cast (cast(dt1 as date format YYYYMMDD) as char(8)YYYYMMDD) as char(8)to_date(20040815,YYYYMMDD)to_date(20040815,YYYYMMDD)SELECT CAST(20100520-19000000) AS DATE FORMAT SELECT CAST(20100520-19000000) AS DATE FORMAT YYM
91、MDD)YYMMDD)to_date(20040815,YYYYMMDD)to_date(20040815,YYYYMMDD)select cast ( 20040815 as date format select cast ( 20040815 as date format YYYYMMDD)YYYYMMDD)select cast ( 20040815 212301 as timestamp(0) select cast ( 20040815 212301 as timestamp(0) format YYYYMMDDbHHMISS)format YYYYMMDDbHHMISS)nvl(f
92、1,0)nvl(f1,0)zeroifnull(f1)zeroifnull(f1)nvl(f1,0)nvl(f1,0)nullif(f1,0)nullif(f1,0)select from A, B where A.f1 = B.f2 (+)select from A, B where A.f1 = B.f2 (+)select from A left select from A left （outer outer ）join B on A.f1 join B on A.f1 = B.f2= B.f2Teradata / Oracle 常用语法比较（常用语法比较（cont.）INTEGERIN
93、TEGERINTEGERINTEGERCHARCHARCHARCHARVARCHAR2VARCHAR2VARCHARVARCHARNUMBER(M,N)NUMBER(M,N)DECIMAL(M+N,N)DECIMAL(M+N,N)DATEDATEDATE : DATE : 只存日子只存日子INTEGER : 200102INTEGER : 200102TIMESTAMP(6) : TIMESTAMP(6) : 存日子，时间，秒数到小数后六个存日子，时间，秒数到小数后六个位（极不常用，不推荐使用。）位（极不常用，不推荐使用。）TIMESTAMP : TIMESTAMP(6) TIMESTAMP
94、: TIMESTAMP(6) （如果不给小数位，（如果不给小数位，系统会自动给六个位）系统会自动给六个位）length()length()CHARACTERS()CHARACTERS()简写成简写成 CHARACTER()CHARACTER()、CHARS()CHARS()或者或者CHAR() CHAR() 不能统计数字型不能统计数字型trunc(to_date(20090323,YYYYMMDD),month) trunc(to_date(20090323,YYYYMMDD),month) trunc(23.53,1) trunc(23.53,1) ROUND(200392/ 10000),
95、2)ROUND(200392/ 10000),2)cast(200392/10000.00 as decimal(10,2) cast(200392/10000.00 as decimal(10,2) select months_between (date2010-5-1, select months_between (date2010-5-1, date2010-10-1) from dualdate2010-10-1) from dualSELECT (DATE2010-05-01)-(DATE2010-10-01) SELECT (DATE2010-05-01)-(DATE2010-10
96、-01) MONTH(4) AS mMONTH(4) AS mSELECT (DATE2010-05-01)-(DATE2010-10-01) SELECT (DATE2010-05-01)-(DATE2010-10-01) DAY(4) AS dDAY(4) AS dselect add_months (date2010-4-30,1) from dual select add_months (date2010-4-30,1) from dual 2009-05-312009-05-31select add_months (date2010-04-30,1) select add_month
97、s (date2010-04-30,1) 2010-05-302010-05-30TRIM函数函数语法语法 意义意义TRIM () 去除字符数据中前后端的空格或者二进制数据中前后头的零去除字符数据中前后端的空格或者二进制数据中前后头的零TRIM (BOTH FROM ) 同上同上TRIM (TRAILING FROM ) 去除后端的空格或二进制零去除后端的空格或二进制零TRIM (LEADING FROM ) 去除前端的空格或二进制零去除前端的空格或二进制零SQL:输出：输出：SELECT TRIM( abc ); abcSELECT TRIM (TRAILING FROM abc ) abc
98、SELECT TRIM (LEADING FROM abc ) abc Teradata没replace函数SQL变量变量SELECT DATABASE; 显示当前数据库显示当前数据库DBCSELECT USER; 显示当前显示当前Session登陆的用户名登陆的用户名DBCSELECT DATE, CURRENT_DATE ; 显示当前日期显示当前日期2010-05-21 , 2010-05-21定义格式定义格式: SELECT CAST(DATE AS DATE FORMAT YYYYMMDD)SELECT TIME, CURRENT_TIMESTAMP(0);显示当前时间显示当前时间08
99、:43:17, 2007-08-06 2010-05-21 08:43:54+00:00转换转换: SELECT CAST(CURRENT_TIMESTAMP(0) AS CHAR(19);2010-05-21 08:44:10SELECT CAST(CURRENT_TIMESTAMP(0) AS TIMESTAMP(0) FORMAT YYYY-MM-DDBHH:MI:SS)日期日期(DATE)的操作的操作取当前天：取当前天：SELECT CAST( CURRENT_DATE AS DATE FORMAT YYYYMMDD)取当前天的前一天，后一天取当前天的前一天，后一天SELECT CAS
100、T( CURRENT_DATE -1 AS DATE FORMAT YYYYMMDD)SELECT CAST( CURRENT_DATE + 1 AS DATE FORMAT YYYYMMDD)取前取前(后后)一个月的同一天一个月的同一天SELECT ADD_MONTHS(CURRENT_DATE , -1)SELECT ADD_MONTHS(CURRENT_DATE , 1)若若current_date为为 20100331,20100331,20090229结果是什么？结果是什么？SELECT ADD_MONTHS(DATE2009-02-28,-12)SELECT ADD_MONTHS(
101、DATE2009-02-28,1)取当前天所在月的第一天取当前天所在月的第一天SELECT SUBSTR(CAST(CURRENT_DATE AS DATE FORMAT YYYYMMDD),1,6) | 01;取当前天所在月的最后一天取当前天所在月的最后一天SELECT CAST( SUBSTR(CAST( ADD_MONTHS(CURRENT_DATE,1) AS DATE FORMAT YYYYMMDD),1,6) | 01AS DATE FORMAT YYYYMMDD) -1 日期相减日期相减SELECT ( DATE 2007-03-01 - DATE 2004-01-01) day
102、(4);SELECT (DATE2007-03-01- DATE2004-01-01) month(4) ; 时间相减时间相减SELECT (CAST(endtime AS TIMESTAMP) - CAST(starttime AS TIMESTAMP) ) MINUTE(4) AS m；SELECT (CAST(endtime AS TIMESTAMP) - CAST(starttime AS TIMESTAMP) ) SECOND(4) AS m；日期日期(DATE)的操作的操作(cont.)SELECT EXTRACT(YEAR FROM DATE);SELECT EXTRACT(MO
103、NTH FROM DATE + 30);SELECT EXTRACT(DAY FROM DATE + 2);SELECT EXTRACT(HOUR FROM TIME);SELECT EXTRACT(MINUTE FROM TIME);SELECT EXTRACT(SECOND FROM TIME);SELECT ADD_MONTHS(DATE,2);SELECT ADD_MONTHS(DATE,12*8);SELECT ADD_MONTHS(2002-03-19,12);系统日历：系统日历： sys_calendar.calendar 时间范围：时间范围：1900-01-01至至2100-1
104、2-31包括：包括： 年年/季季/度度/月月/周周/天的信息天的信息day_of_weekday_of_monthday_of_yearmonth_of_yearquarter_of_yearIN & EXISTS从逻辑上看，IN与EXISTS是一样的。IN子句在外部查询中比较子查询返回的值，并过滤掉行；EXISTS子句在子查询内部比较那些值并过滤掉行。在teradata数据库中，一个NULL值意味着未知，因此，对一个NULL值的任何比较或操作也都是无效的，而任何返回NULL的测试也都被忽略了。在出现NULL值的情况下， 使用IN与EXISTS查询的结果是一样的。在出现NULL值的情况下， 使
105、用NOT IN与NOT EXISTS查询的结果是不一样的。鉴于以上两种情况，为了保证结果集的完整性，在使用IN,EXISTS, NOT IN,NOT EXISTS的时候，建议使用coalesce函数。一般来讲，使用EXISTS的效率高于IN，使用NOT EXISTS的效率高于NOT IN。Examples using In & ExistsSQLresultsselect col1 from T1where col1 in(select col2 from T2)a, bselect col1 from T1where col1 in(select col3 from T3)a, bselec
106、t col1 from T1where exists(select * from T2where T2.col2 = T1.col1)a, bselect col1 from T1where exists(select * from T3where T3.col3 = T1.col1)a, bT1.col1SQLresultsselect col1 from T1where col1 not in(select col2 from T2)0, 1select col1 from T1where col1 not in(select col3 from T3)No resultsselect c
107、ol1 from T1where not exists(select * from T2where T2.col2 = T1.col1)0, 1 , nullselect col1 from T1where not exists(select * from T3where T3.col3 = T1.col1)0, 1 , nulla,b,0,1,nullT2.col2a,b,c,d,eT3.col3a,b,c,d,null select coalesce(f1,f2,f3) zeroifnull:时，返回空值时，返回空值 Select zeroifnull(brand_id) =select
108、coalesce(brand_id,0) f1 f2f3Result A B CA BCB CC NULLCoalesceRank & Row_Number()SELECT sales_person ,sales_region ,sales_amount ,RANK() OVER (PARTITION BY sales_region ORDER BY sales_amount DESC) AS rank_sales FROM sales_table QUALIFY rank_sales= 1;sales_personsales_regionsales_amountrank_salesGarab
109、aldi East 1001Baker East 992Fine East 893Adams East 754Edwards West 1001Connors West 992Davis West 992Rank & Row_Number()INSERT INTO wt_k( Currency_Cd ,Fx_Mid_Price)SELEC TRIM(Currency_Cd),CAST(Fx_Mid_Price/Unit AS DECIMAL(18,8)FROM $PDATADB.T99_EXCHANGE_RATE_PARAM_HWHER Record_Stat_Cd=0 AND Effecti
110、ve_Dt=CAST($TX_DATE AS DATE FORMAT YYYYMMDD)-1 AND (TRIM(Currency_Cd) ,Effective_Dt) IN (SELECT TRIM(Currency_Cd) AS Currency_Cd ,MAX(Effective_Dt) AS Effective_Dt FROM $PDATADB.T99_EXCHANGE_RATE_PARAM_HWHERE Record_Stat_Cd=0AND Effective_Dt=CAST($TX_DATE AS DATE FORMAT YYYYMMDD)-1 GROUP BY 1) GROUP
111、 BY 1 QUALIFY RANK(Posted_Price_Tm)=1;INSERT INTO wt_k( Currency_Cd ,Fx_Mid_Price)SELECT TRIM(Currency_Cd),CAST(Fx_Mid_Price/Unit AS DECIMAL(18,8) FROM $PDATADB.T99_EXCHANGE_RATE_PARAM_HWHERE Record_Stat_Cd=0AND Effective_Dt 20070701;但但Where tx_date like 200707%;不起作用不起作用PPI的使用（的使用（cont.）Partition上不要
112、使用表达式，否则上不要使用表达式，否则Partition不能被正确使用。不能被正确使用。T1. tx_date/100=CAST(20070917AS DATE FORMAT YYYYMMDD)/100Substring(T1. tx_date from 1 for 6) =200709应该修改为应该修改为 T1. tx_date=CAST(20070901 AS DATE FORMAT YYYYMMDD)PPI的使用（的使用（cont.） 脚本脚本：tb_030040270.pl/* 删除当月删除当月 */ 2小时小时 del BASS1.tb_03004 where proc_dt = 2
113、00709;insert into BASS1.tb_03004 7小时小时。 sel . from pview.vw_evt_cust_so cust where acpt_date=cast(200710|01 as date) cast(200710|01 as date)写法错误，PPI不起作用日期的正确写法日期的正确写法:Cast(20071001 as date format YYYYMMDD)在proc_dt建立PPIPPI字段从Load_Date调整为acpt_date数据类型数据类型注意非日期字段与日期字段注意非日期字段与日期字段char & date的转换与关的转换与关联：
114、联：如果数据类型一致可以直接使用；在CASE WHEN or COALESCE一定要使用显式的类型转换(CAST)CASE WHEN A = B THEN DATE1 ELSE 20061031 END应写成CASE WHEN A = B THEN DATE1 ELSE CAST(20061031 AS DATE) END数值运算时，确保运算过程中不丢失计算精度。数值运算时，确保运算过程中不丢失计算精度。CAST(100/3 AS DEC(5,2)应该写成CAST(100/3.00 AS DEC(5,2)目标列的选择目标列的选择减少目标列，可以少消耗减少目标列，可以少消耗SPOOL空间，从而提
115、高空间，从而提高SQL的效率的效率当系统任务繁忙，系统内存少的时候，效果尤为明显。举例：举例：Xx明细表(视图访问)， dwview.vw_xx_det共有73字段，以下SQL供返回1.6亿条记录左边的SQL，记录最长为：698字节，平均399字节右边的SQL，记录最长为：59字节， 平均30字节两者相差两者相差400多多GB的的SPOOL空间，空间，IO次数也随着相差甚大！次数也随着相差甚大！SPOOL空间估计：497 GBSPOOL空间估计：42 GBSELECT SUBS_ID ,MSISDN ,Begin_Date ,Begin_Time ,Call_DUR ,CHRG_DURFROM
116、 dwview.vw_xx_detWHERE PROC_DATE BETWEEN 20070701 AND 20070731 SELECT * FROM dwview.vw_xx_detWHERE PROC_DATE BETWEEN 20070701 AND 20070731Where条件的限定条件的限定根据根据Where条件先进行过滤数据集，再进行连接条件先进行过滤数据集，再进行连接(JOIN)等操作等操作这样，可以减少参与连接操作的数据集大小，从而提高效率好的查询引擎，可以自动优化；但有些复杂SQL，查询引擎优化得并不好。注意：系统的系统的SQL优化，只是避免最差的，选择相对优的，未必能够
117、得到最好的优化优化，只是避免最差的，选择相对优的，未必能够得到最好的优化结果。结果。SELECT A.TX_DATE, A.KPI_CODE ,B.SRC_NAME,A.KPI_VALUEFROM ( select * from qdata.tb_a where TX_DATE = 20070701 AND KPI_CODE = 65 ) ALEFT JOIN ( SELECT * FROM qdata.tb_b where KPI_CODE = 65 and N_TYPE = M) BON A.KPI_CODE = B.KPI_CODE SELECT A.TX_DATE, A.KPI_COD
118、E ,coalesce(B.SRC_NAME, no name) ,A.KPI_VALUEFROM qdata.tb_a ALEFT JOIN qdata.tb_b BON A.KPI_CODE = B.KPI_CODE WHERE A. TX_DATE = 20070701 AND A.KPI_CODE = 65 AND B.N_TYPE = M rewrite用用Case When替代替代UNION sel city_id,channel_id,cust_brand_id,sum(stat_values) as stat_valuesfrom ( . select t.city_id ,c
119、oalesce(v.channel_id,b.channel_id,-) as channel_id ,cust_brand_id ,sum(case when SMS_SVC_Type_Level_SECND = 017 and Call_Type_Code in (00,10,01,11) then sms_quan else 0 END) as stat_values from dwview.vw_xxxxxa t left join vt_xxxxxb v on t.subs_id=v.subs_id left join dwview.vw_xxxxxc b on t.city_id=
120、b.City_ID where cal_date=20070914 group by 1,2,3 union all select t.city_id ,coalesce(v.channel_id,b.channel_id,-) as channel_id ,cust_brand_id ,sum(sms_quan) as stat_values from dwview.vw_xxxxxa t left join vt_xxxxxb v on t.subs_id=v.subs_id left join dwview.vw_xxxxxc b on t.city_id=b.City_ID where
121、 cal_date=20070914 and SMS_SVC_Type_Level_SECND like 02% and SMS_SVC_Type_Level_SECND not in (021,022) group by 1,2,3 .)tmpGroup by 1,2,3两个子查询的表连接部分完全一样两个子查询除了取数据条件，其它都一样。Union all是多余的，它需要重复扫描数据，进行重复的JOIN可以用Case when替代union 用用Case When替代替代UNION (cont.)sel city_id,channel_id,cust_brand_id,sum(stat_va
122、lues) as stat_valuesfrom ( select t.city_id ,coalesce(v.channel_id,b.channel_id,-) as channel_id ,cust_brand_id ,sum(CASE WHEN SMS_SVC_Type_Level_SECND = 017 and Call_Type_Code in (00,10,01,11) THEN sms_quan WHEN SMS_SVC_Type_Level_SECND like 02% and SMS_SVC_Type_Level_SECND not in (021,022) THEN sm
123、s_quan ELSE 0 END ) as stat_values from dwview.vw_xxxxxa t left join vt_xxxxxb v on t.subs_id=v.subs_id left join dwview.vw_xxxxxc b on t.city_id=b.City_ID where cal_date=20070914.)tmpGroup by 1,2,3SQL优化重写优化重写用用OR替代替代UNION select t.city_id ,coalesce(v.channel_id,b.channel_id,-) as channel_id ,cust_b
124、rand_id ,sum( sms_quan) as stat_values from dwview.vw_xxxxxa t left join vt_xxxxxb v on t.subs_id=v.subs_id left join dwview.vw_xxxxxc b on t.city_id=b.City_ID where cal_date=20070914 and （ SMS_SVC_Type_Level_SECND = 017 and Call_Type_Code in (00,10,01,11) ） OR (SMS_SVC_Type_Level_SECND like 02% and
125、 SMS_SVC_Type_Level_SECND not in (021,022) ) )Group by 1,2,3SQL优化重写优化重写去掉多余的去掉多余的Distinct与与Group by sel t.operator ,t.acpt_channel_id ,t.acpt_city_id ,t.subs_id ,t.acpt_date as evt_date From ( sel operator, ACPT_Channel_ID, acpt_city_id,subs_id, acpt_date from pview.vw_cust_xxx cust where acpt_date
126、=20071007 and so_meth_code in(0,1,2) and PROC_STS_Code =-1 group by 1,2,3,4,5union all sel operator_num as operator, ACPT_Channel_ID, acpt_city_id, subs.subs_id, charge_date as acpt_date from pview.vw_xxx_a sub join pview.vw_xxx_b bus on subs.msisdn=bus.msisdn where charge_date =20071007 group by 1,
127、2,3,4,5 )t group by 1,2,3,4,5;既然t查询外层有group by操作去重，那么子查询内的Group by去重是多余的。而且，两个子查询group by后再用union all，就可能再产生重复记录，那么group by也失去意义了。解决方法： 把把t查询内部的两个查询内部的两个group by去掉即可去掉即可 类似的类似的Distinct问题，可效仿解决。问题，可效仿解决。去重去重去重Group by vs. DistinctDistinct是去除重复的操作是去除重复的操作Group by是聚集操作是聚集操作某些情况下，两者可以起到相同的作用。某些情况下，两者可以起
128、到相同的作用。两者的执行计划不一样，效率也不一样两者的执行计划不一样，效率也不一样建议：使用建议：使用Group byselect subs_id ,acct_idfrom PVIEW. VW_FIN_ACCT_SUBS_HISwhere efct_date 20070701 group by 1,2select DISTINCT subs_id ,acct_idfrom PVIEW. VW_FIN_ACCT_SUBS_HISwhere efct_date 20070701 Union vs. Union all Union与与Union all的作用是将多个的作用是将多个SQL的结果进行合并
129、。的结果进行合并。Union将自动剔除集合操作中的重复记录将自动剔除集合操作中的重复记录；需要耗更多资源。；需要耗更多资源。Union all则保留重复记录则保留重复记录，一般建议使用，一般建议使用Union all。第一个第一个SELECT语句，决定输出的字段名称，标题，格式等语句，决定输出的字段名称，标题，格式等要求所有的要求所有的SELECT语句：语句： 1) 必须要有同样多的表达式数目；必须要有同样多的表达式数目； 2) 相关表达式的域必须兼容相关表达式的域必须兼容select *from (select a) T1(col1)unionselect *from (select bc)
130、T2(col2)select *from (select bc)T3(col3)union allselect *from (select a) T1(col1)union allselect *from (select bc)T2(col2)col3-abcbccol1-ab先先Group by再再join运行时间运行时间 11小时小时Select case when b.CUST_Brand_ID is null then 5020 when b.CUST_Brand_ID in(2000,5010) then 5020 else b.CUST_Brand_ID end ,sum(COAL
131、ESCE(b.Bas_CHRG_DUR_Unit,0) as Thsy_Accum_New_SUBS_CHRG_DUR , sum(case when b.call_type_code =20 then b.Bas_CHRG_DUR_Unit else 0 END) from VT_t t inner join VT_b b on t.Subs_ID=b.Subs_ID left join PVIEW.vw_c c on b.Long_Type_Level_SECND= c.Long_Type_Level_SECND left join PVIEW.vw_d d on b.Roam_Type_
132、Level_SECND= d.Roam_Type_Level_SECND group by 1;记录数情况：记录数情况：t: 580万，万，b: 9400万万, c:8, d:8 主要问题：主要问题：假如连接顺序为： ( (b join c) join d) join t)则是( (9400万 join 8) join 8) join 580万)数据分布时间长数据分布时间长(IO多多)，连接次数多，连接次数多解决方法：解决方法： 先执行先执行(t join b)，然后，然后groupby，再，再join c，d先先Group by再再join (cont.)40秒秒Select case wh
133、en b.CUST_Brand_ID is null then 5020 when b.CUST_Brand_ID in(2000,5010) then 5020 else b.CUST_Brand_ID end ,sum(COALESCE(b.Bas_CHRG_DUR_Unit,0) as Thsy_Accum_New_SUBS_CHRG_DUR , sum(case when b.call_type_code =20 then b.Bas_CHRG_DUR_Unit else 0 END) from (select CUST_Brand_ID, call_type_code, Long_T
134、ype_Level_SECND, Roam_Type_Level_SECND, sum(Bas_CHRG_DUR_Unit) Bas_CHRG_DUR_Unit, count(*) quan from VT_t where subs_id in (select subs_id from VT_b) group by 1,2,3,4 ) b left join PVIEW.vw_c c on b.Long_Type_Level_SECND= c.Long_Type_Level_SECND left join PVIEW.vw_MID_CDE_ROAM_TYPE_LVL d on b.Roam_T
135、ype_Level_SECND= d.Roam_Type_Level_SECND group by 1;记录数情况：记录数情况：t: 580万，万，b: 9400万万, c:8, d:8 处理过程：处理过程： 先执行先执行(t join b)，然后，然后groupby，再，再join c，d结果：结果：1、 VT_T join VT_b PI相同，merge join，只需10秒2、经过、经过group by，b表只有表只有332记录记录3、b join c join d， 就是：就是： 332 8 84、最终结果：、最终结果：5记录，共记录，共40秒秒先先Group by再再join（con
136、t.）先汇总再连接，可以减少参与连接的数据集大小，减少比较次数，从而提先汇总再连接，可以减少参与连接的数据集大小，减少比较次数，从而提高效率。高效率。以下面以下面SQL为例，假设历史表（为例，假设历史表（ History ）有）有1亿条记录亿条记录左边的SQL，需要进行 1亿 90次比较右边的SQL，则只需要 1亿 2 次比较SELECT H.product_id ,sum(H.account_num)FROM History H , Calendar DTWHERE H.sale_date = DT.calendar_date AND DT.quarter = 3 GROUP BY 1;SE
137、LECT H.product_id, SUM(H.account_num)FROM History H , (SELECT min(calendar_date) min_date ,max(calendar_date) max_date FROM Calendar WHERE quarter = 3 ) DT WHERE H.sale_date BETWEEN DT.min_date and DT.max_dateGROUP BY 1;提取公共提取公共SQL形成临时表形成临时表出现以下出现以下SQL代码段，共代码段，共5次次，平均每次执行需，平均每次执行需10分钟分钟 。 FROM PVIEW
138、.VW_xx_MON a ,PVIEW.VW_MID_yy b ,vt_subs c WHERE a.CUST_Brand_ID=b.SUBS_Brand_Level_Third AND a.CAL_Month=200908 AND a.SUBS_ID=c.SUBS_ID 。整个脚本需要扫描以下整个脚本需要扫描以下SQL 14次次，平均每次执行需，平均每次执行需3分钟分钟PVIEW.VW_xx_MON where CAL_Month=200908提取公共提取公共SQL，形成临时表，较少扫描，形成临时表，较少扫描(IO)次数。次数。该脚本，经过优化之后，从该脚本，经过优化之后，从50分钟缩减至分
139、钟缩减至10分钟分钟关联条件关联条件 (1)Select A.a2, B.b2 from A join Bon substring(A.a1 from 1 for 7) = B.b1应该写为应该写为Select A.a2, B.b2 from (select substring(a1 from 1 for 7) as a1_new,a2 from A ) A_newjoin Bon a1_new = b1关联条件关联条件 (2)Select A.a2, B.b2 from A join Bon TRIM(A.a1 ) = TRIM(B.b1)应该写为应该写为Select A.a2, B.b2
140、from A join Bon A.a1 = B.b1SQL书写不当可能会引起笛卡儿积书写不当可能会引起笛卡儿积以下面两个以下面两个SQL为例，它们将进行笛卡儿积操作。为例，它们将进行笛卡儿积操作。例子例子1：Select employee.emp_no , employee.emp_nameFrom employee A例子例子2：SELECT A.EMP_Name, B.Dept_NameFROM employee A, Department BWhere a.dept_no = b.dept_no;表表Employee与表与表A进行笛卡儿积进行笛卡儿积表表A与表与表B进行笛卡儿积进行笛卡
141、儿积表表A与表与表B进行进行Inner Join改变查询计划的手段改变查询计划的手段在表上点右键在表上点右键 选择选择 Space Summary菜单菜单 ，查看，查看Skewfactor项，大于项，大于 20时需要调整表的时需要调整表的 PI收集统计信息收集统计信息关联字段上的统计信息Partition上的统计信息Where条件上的统计信息Group by 字段上的统计信息查看某个表的统计信息情况：查看某个表的统计信息情况：help stat DBName.TableName通过通过Explain查看，尚需统计哪些信息？查看，尚需统计哪些信息？diagnostic helpstats on
142、for session;总的来说尽量应当定时在系统不繁忙的时候做；执行频率应当和数据变化量成正比；优化示例优化示例1：数据分布：数据分布 与与 JOIN方法方法select 200709, a.City_ID, a.SUBS_ID, a.BELONG_DISTRICT from ( select City_ID, SUBS_ID, BELONG_DISTRICT from dwview.vw_a where CAL_Month=200612 union select City_ID, SUBS_ID, BELONG_DISTRICT from dwview.vw_b ) a inner joi
143、n dwview.vw_c b on a.subs_id = b.subs_id Where CAL_Month = 200709 And SUBS_STS_Code not In (10,11,12,13,20,30,60) and SUBS_STS_EFCT_Date between 200709 | 01 and 20070930 group by 1,2,3,4;主要问题：主要问题：1、把表b进行Duplicate 统计信息不齐全，认为表b经过条件过滤只有130条记录， 实际上有200万记录左右2、用Product Join连接算法 表A有1000万记录 Duplicate连接，共进行
144、比较次数： 1000万200万 3、最优的Join方法？4、解决办法： 对表b收集统计相应字段的信息 必要的话，固化表A，并统计字段subs_id优化示例优化示例2：数据分布：数据分布 与与 JOIN方法方法11小时小时Select case when b.CUST_Brand_ID is null then 5020 when b.CUST_Brand_ID in(2000,5010) then 5020 else b.CUST_Brand_ID end ,sum(COALESCE(b.Bas_CHRG_DUR_Unit,0) as Thsy_Accum_New_SUBS_CHRG_DUR
145、,。 from VT_t t inner join VT_b b on t.Subs_ID=b.Subs_ID left join PVIEW.vw_C c on b.Long_Type_Level_SECND= c.Long_Type_Level_SECND left join PVIEW.vw_D d on b.Roam_Type_Level_SECND= d.Roam_Type_Level_SECND group by 1;记录数情况：记录数情况：t: 580万，万，b: 9400万万, c:8, d:8都有统计信息都有统计信息主要问题：主要问题：1、连接顺序： ( (b Join c)
146、 join d ) join t2、对表b进行3次redistribute 3、连接算法：Merge Join 4、原因：b表经过汇总而得，虽然知有总记录数，但未知各个join字段的情况解决办法？ 固化b，并对b表连接字段进行统计 调整连接顺序？优化示例优化示例3固化临时表固化临时表脚本：脚本：rpt_mart_new_comm_mon0400.pl. . Drop table ttemp.mart_new_comm_mon0400_DUR_1;Create multiset table ttemp.mart_new_comm_mon0400_DUR_1 as ( ) with data pr
147、imary index(subs_id);Collect stat on ttemp.mart_new_comm_mon0400_DUR_1 column subs_id;Select case when b.CUST_Brand_ID is null then 5020 when b.CUST_Brand_ID in(2000,5010) then 5020 else b.CUST_Brand_ID end ,。 from VTNEW_SUBS_THISYEAR t inner join ttemp.mart_new_comm_mon0400_DUR_1 b on t.Subs_ID=b.S
148、ubs_ID left join . group by 1;Drop table ttemp.mart_new_comm_mon0400_DUR_1;固化临时表：固化临时表：注意命名的唯一性注意命名的唯一性并非所有临时表都需要固化并非所有临时表都需要固化!优化示例优化示例4强制多表强制多表JOIN的顺序的顺序select c.prov_title ,sum(.) AS fee from PVIEW.VW_STL_GSM_INTER a INNER JOIN pview.vw_CdE_TARRIF_dIST b ON a.OPPER_Home_Area = b.Long_Prefix INNER
149、 JOIN dwpview.vw_LOC_prov c ON b.prov_code =c.prov_id WHERE Self_Home_Area IN -主叫为本省用户主叫为本省用户 ( select city_Id from ttemp.vt_city_Id ) and trim(SETLMT_Area) in -结算地为内蒙结算地为内蒙 (select city_Id from ttemp.vt_city_Id ) and SELF_Telco_Operator_Code = 2 and PROC_Date between 20070601 and 20070630 group by
150、1418记录记录32记录记录12记录记录9亿亿记录记录12记录记录JOIN顺序：1. a join b: 比较次数9亿418 结果9亿条2、 (a join b) join c : 比较次数9亿 32 结果9亿条9亿418 + 9亿32次比较9亿450次比较优化示例优化示例4强制多表强制多表JOIN的顺序的顺序 (cont.)select c.prov_title ,sum(.) AS fee from ( select * from ttemp.TB_STL_GSM_INTER06 where SELF_Telco_Operator_Code = 2 and PROC_Date betwee
151、n 20070601 and 20070630 and Self_Home_Area IN -主叫为本省用户主叫为本省用户 ( select city_Id from ttemp.vt_city_Id ) and trim(SETLMT_Area) in -结算地为内蒙结算地为内蒙 (select city_Id from ttemp.vt_city_Id ) )a INNER join ( select b1.Long_Prefix, c1.prov_title FROM pview.vw_CdE_TARRIF_dIST b1 INNER join dwpview.vw_LOC_prov c
152、1 ON b1.prov_code =c1.prov_id ) b ON a.OPPER_Home_Area = b.Long_Prefixgroup by 1SQL重写JOIN顺序：1. b join c: 比较次数32418 结果: 418条2、 a join (b join c) : 比较次数9亿 418 结果9亿条9亿418次比较优化示例优化示例5强制多表强制多表JOIN的顺序的顺序 select a.subs_id ,a.Phone_NUM ,* ,sum(coalesce(c.last_arpu,0)last_arpu ,sum(coalesce(d.moths_arpu,0)mo
153、ths_arpu ,sum(coalesce(f.accum_intg,0)CUST_INTG FROM vt_a a left join ( select * from * ) b on a.subs_id=b.subs_id left join (select * from * ) c on a.subs_id=c.subs_id left join (select * from *) d on a.subs_id=d.subs_id left join (select cust_id,Contact_Person,Contact_Phone from PVIEW.vw_e where e
154、fct_date20070831 ) e on a.cust_id=e.cust_id left join (select subs_id,accum_intg from pview.vw_f_HIS where efct_date20070831 )f on a.subs_id=f.subs_idPI:subs_id除了表e, 其它表PI都是subs_ID除了表e，与其它表的join都是基于subs_idJoin 顺序为：顺序为： a * b * c * d * e * fJoin e之前，需要数据重分布Join e之后，join f 又要重分布解决方法：解决方法：把 left join e
155、 放在f之后:a * b * c * d * f * e只需一次的数据重分布PI:cust_idPI:subs_id并非所有并非所有SQL都可进行简单优化都可进行简单优化以下以下SQL限定了：限定了：数据的分布方式：只能用duplicateJOIN算法：只能用product join无法从SQL层面进行简单优化：共共2万亿次万亿次(”取长度取长度”，”字符串截取字符串截取”与与”比较比较”)16小时内无法完成SELECT A.Msisdn ,A.Opper_Num ,2 ,COUNT(*) AS Sms_QuanFROM PVIEW.VW_NET_GSM_SMS AINNER JOIN VT_
156、ORDER_USER BON SUBSTRING( A.Opper_Num FROM 1 FOR (CHAR_LENGTH(B.Msisdn) ) = B.MSISDNAND B.Serv_Id = A01WHERE A.Proc_Date .GROUP BY 1,2,3;表表A：PI(subs_ID)，30天共天共6亿记录；亿记录； 表表B：PI(SUBS_ID), 3千记录千记录请修改请修改”业务逻辑业务逻辑” JOIN问题的经验分析问题的经验分析运行速度慢的运行速度慢的SQL，绝大多数都是，绝大多数都是JOIN例外1：INSERT操作慢，可能是因为目标表为set类型，或者PI不对例外2：
157、数据读取慢，可能用like操作，或者数据本身就很大 JOIN的问题，主要在于：的问题，主要在于：1.数据分布方式不对：把大表进行duplicate，或者redistribute大表Redistribute有可能导致数据分布不均衡2.JOIN算法不对：例如，大表join小表，用merge join导致大表需要重新hash与sort例如，大表join大表不用merge joinJOIN问题的解决办法：问题的解决办法：对参与join的字段进行统计信息必要的时候，固化临时表，并统计信息一般情况下，不需要调整一般情况下，不需要调整SQL的业务逻辑的业务逻辑系统监控中发现的问题系统监控中发现的问题系统资源
158、在上班高峰时常常被大量耗尽未利用PI字段或Partition字段查询大表执行顺序避免对大表的UPDATE操作书写习惯历史数据取法设计考虑条件未加限制例如：例如：select subs_id from PVIEW.vw_mid_subs_info_daily where cust_type_id in (41,22,42,43,21,51,44) group by 1事件明细或中间表中未加条件限制充分利用大表中的Partition，避免进行全表扫描，减少IO充分理解中间表内容，优化设计思路执行顺序例如：例如：Select *FROM(SELECThome_area , subs_id , Bas
159、_Fee_CHRG_NUM , call_type_code , begin_time , proc_date , roam_type_code , cfee , lfee , chrg_dur , long_type AS long_type_code , opper_numFROMpview.vw_aWHEREproc_date = 20070501UNIONSELECThome_area , subs_id , CAST( ( chrg_dur + 59 ) AS INT ) / 60 AS Bas_Fee_CHRG_NUM , Comm_Type_Code AS call_type_c
160、ode , begin_time , proc_date , roam_type_code , cfee , lfee , chrg_dur , long_type_code , opper_numFROMpview.vw_bWHEREproc_date = 20070501 ) t1 INNER JOIN vt_subs_a t2ONt1.subs_id = t2.subs_idGROUPBY 1 , 2 ; 导出表先做关联，再做汇总？更改执行顺序，导出表先做汇总，再做关联，达到相同效果，可避免IO操作避免对大表的UPDATE操作例如：例如： UPDATE T1 FROM VT_COLOR_
161、SUBS_tmp AS T1,(SEL SUBS_ID,MMS_SVC_Type_Cod,SUM(Comm_NUM) AS Comm_NUM FROM VT_MS_INFO GROUP BY 1,2) AS T2 SET Total_QUAN= T2.Comm_NUM WHERE T1.SUBS_ID=T2.SUBS_ID AND T1.MMS_SVC_Type_Cod=T2.MMS_SVC_Type_CodUpdate TB set col1 = A where col2 = Bcreate multiset table TB1 as TBInsert into TB1 sel case w
162、hen col2 = B then A else col1 endDelete from TB where col1 = AInsert into TB sel * from TB1目标表数据量大，应尽量避免UPDATE操作UPDATE操作对性能影响较大，而且效率低如需对大表UPDATE，可考虑用INSERT+DELETE操作替代书写习惯例如：例如：cal_month = 200701 cal_month = 200701 (Integer)限制条件中未考虑字段类型考虑字段类型历史数据提取方法例如：例如：Where Efct_Date 20070701历史数据提取方法必须是半闭半开区间，否则历
163、史数据将会有问题书写仔细设计优化例如：例如：select home_area,subs_idfrom PVIEW.vw_XX_DET where proc_date between 2007|0101 and 20070331 and opper_num like 12593% and roam_type_code not in (2,3,7)设计时应该充分考虑系统性能设计时应该充分考虑系统性能合理设计利用现有中间表月任务加载时间过长，可考虑拆分成日作业执行优化前提优化前提保证数据正确性与一致性保证数据正确性与一致性对于后台脚本，优化时还要考虑作业间关系，不能为了优化破坏脚本对于后台脚本，优化
164、时还要考虑作业间关系，不能为了优化破坏脚本的逻辑及依赖关系的逻辑及依赖关系所有的优化不能脱离所有的优化不能脱离DDL及数据库本身的机制及数据库本身的机制数据分布设计策略数据分布设计策略PI & PPI（数据分布不均匀是大忌数据分布不均匀是大忌）在在Teradata中中,PI的选择十分重要的选择十分重要,选取不正确，则有如下的影响选取不正确，则有如下的影响：占用更大数据空间；占用更大数据空间；极大影响性能。极大影响性能。选择选择PI的大致原则的大致原则:ACCESSACCESS( (访问访问) )DISTRIBUTIONDISTRIBUTION( (分布分布) ) VOLATILITYVOLAT
165、ILITY( (可变性可变性) )即选择那些经常需要引用或用作关联的、能让表数据均匀分布、相对比较稳定即选择那些经常需要引用或用作关联的、能让表数据均匀分布、相对比较稳定(经常变化必然导致数据在经常变化必然导致数据在AMP间的迁移间的迁移)的字段做的字段做PI.表一定要有表一定要有PI,如果你没有指定如果你没有指定,Teradata会按如下原则指定会按如下原则指定PI:1)有有PK的话的话,用用PK字段作为字段作为UPI.2)没有没有PK的话的话,有有USI的话的话,将将USI的字段做的字段做UPI.3)没有没有PK,没有没有USI的话的话,将第一个字段作为将第一个字段作为NUPI. 所以所以
166、,尽量自己去指定尽量自己去指定PI!一般优化策略一般优化策略尽量利用分区尽量利用分区尽量创造条件使用分区，例如明细数据中的处理日期和统计月份表与表的关联，尽量通过两个表的表与表的关联，尽量通过两个表的PI字段进行关联，避免数据的重分字段进行关联，避免数据的重分布布若PI由多个字段组成，则要将多个字段列全，否则PI会不使用关联条件不可忽略，避免迪卡尔乘积关联条件不可忽略，避免迪卡尔乘积在在SQL中列全所需要的所有字段，避免中列全所需要的所有字段，避免select * from TableName，减少减少SPOOL空间开销空间开销;DDL上的优化上的优化DDL中用中用MULTISET，避免使用，
167、避免使用SET（SET可用指定可用指定UPI来实现）；来实现）；日期格式均指定为日期格式均指定为YYYYMMDD；考虑使用考虑使用NO FALLBACK 与与 FALLBACK以及以及NO JOURNAL 与与 JOURNAL；尽量少用尽量少用SI，只是有时在追求性能的情况下可以使用；，只是有时在追求性能的情况下可以使用；索引尽量选择后续索引尽量选择后续SQL可以用到的，并尽可能考虑唯一性；可以用到的，并尽可能考虑唯一性；字符字段评估是否该使用字符字段评估是否该使用CHAR类型，而不是类型，而不是VARCHAR类型，使用类型，使用CASESPECIFIC（大小写敏感）；（大小写敏感）；使用的字
168、段尽可能设置使用的字段尽可能设置Not Null；代码表中应将代码设置成代码表中应将代码设置成UPI；Partition字段一般为日期字段一般为日期(YYYYMMDD)或年月或年月(YYYYMM)；字段压缩：节省空间和提高速度；字段压缩：节省空间和提高速度；只读视图在创建时增加只读视图在创建时增加Locking Row for ACCESS选项选项其他优化方法其他优化方法定期用定期用COLLECT STATISTICS对表进行统计信息收集；对表进行统计信息收集；必要时使用临时表必要时使用临时表,简化简化JOIN语句的复杂度，便于人为控制；语句的复杂度，便于人为控制；INSERT INTO A
169、SELECT * FROM B;（若（若A,B主索引一致主索引一致,A为空表为空表,插入数插入数据极快）；据极快）；可变临时表不能提高速度，使用的目的是维护的方便性，缺陷也是显而易见可变临时表不能提高速度，使用的目的是维护的方便性，缺陷也是显而易见的：事后没法对中间过程数据进行检查；的：事后没法对中间过程数据进行检查；表之间的字段类型定义应保持一致，特别注意表之间的字段类型定义应保持一致，特别注意JOIN条件中的字段；条件中的字段；对于百万级以上的表，若对于百万级以上的表，若UPDATE的比例超过的比例超过5-10%，尽可能采取其它方法，尽可能采取其它方法处理；处理；尽可能多使用尽可能多使用EXPLAIN，查询数据库执行计划，查询数据库执行计划优化心得优化心得了解数据库原理及了解数据库原理及SQL解析、执行步骤，充分利用数据库本身的特性；解析、执行步骤，充分利用数据库本身的特性；没有最好，只有更好；没有最好，只有更好；优化的过程是平衡的过程优化的过程是平衡的过程-中庸；中庸；优化前后的优化前后的SQL要在相同数据库环境下进行比较，持续关注优化结果；要在相同数据库环境下进行比较，持续关注优化结果；灵活运用灵活运用SQL技巧，多实践技巧，多实践-实践出真知。实践出真知。部分资料从网络收集整理而来，供大家参考，感谢您的关注！
