TERADATA数据库

TERADATA数据库

1.表属性: Set / Multiset

●Set Table 不允许记录重复

●MultiSet Table 允许记录重复

●默认值：Set Table

> Create Table... AS ... 生成的目标表属性默

●对SET Table 进行INSERT 操作，需要检查是否存在重复记录

> 相当的耗资源

> 若真要限定唯一性，可以通过UPI 或USI 实现

●建议：Teradata中都用MultiSet

2.主索引(PI) 设置

●PI 影响数据的存储与访问，其选择标准：

> 不同值尽量多的字段(More Unique Values)

> 使用频繁的字段：包括值访问和连接访问

> 少更新

> PI 字段不宜太多

> 最好是手动指定PI

3.分区索引(PPI) 设置

●PPI （Partition Primary Index ，分区索引），把具有相同分区值的数据聚簇

存放在一起；类似于SQL Server 的聚簇索引（Cluster Index ），Oracle 的聚簇表（Cluster Table ）。

●利用PPI ，可以快速插入/ 访问同一个Partition （分区）的数据。

●Partition 上不要使用表达式，否则Partition 不能被正确使用。

> Substring(T1. tx_date from 1 for 6) ='200709'

> cast( '200710' || '01' as date) 写法错误，PPI 不起作用

4.临时表

1)可变临时表

●在spool缓冲区中物化。

●不使用数据字典和交易锁。

●在cache中保留表的定义。

●在一个会话中，能够被多个查询使用。

●可以随时被手动删除，会话结束时自动删除。

●使用CREATE VOLATILE TABLE语句创建。

●使用ON COMMIT PRESERVE ROWS，允许会话中的其他查

询使用这个可变临时表。

●缺省是ON COMMIT DELETE ROWS，意味着查询提交

后，数据被删除。

●LOG指示维护交易日志，NO LOG的性能更好。缺省是LOG。

●一个会话中，最多有64个可变临时表。

●每个可变临时表必须有唯一的名称。

●不同会话可以使用同样的可变临时表名称

●表名不能与此用户已有的对象重名

●DELETE DATABASE/USER (不能删除可变临时表)

例子:

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;

2)全局临时表

●全局临时表与可变临时表有不同的地方：

z 基础定义是永久的，保存在数据字典中。

z 要物化表，要有相应SQL的权限。

z 空间要占用用户的“临时空间(temporary space)”。

z 每个会话最多可以物化32全局临时表。

z 系统重启动后，还存在。

●全局临时表与可变临时表有相似的地方：

z 对会话而言，每个实例是本地的。

z 会话结束后，物化的表被自动删除。(但基础定义仍然存储在数据字典中)

z 都有LOG 和ON COMMIT PRESERVE/DELETE选项。

z 物化表中内容与其他会话不共享。

z 在会话开始时，表被清空。

●使用CREATE GLOBAL TEMPORARY命令创建

5.数据类型

●非日期字段与日期字段char & date 的转换与关联

●数值运算时，确保运算过程中不丢失计算精度。

例: CAST(100/3.00 AS DEC(5,2))

●字符( 串) 与数字相比较

比较规则: 1) 比较两个值( 字段) ，它们的类型必须一样！

2) 当字符( 串) 与数字相比较时，先把字符( 串) 转换成数字，再进行比较。

6.SQL语句性能优化

●减少目标列，可以少消耗SPOOL 空间，从而提高SQL 的效率

●根据Where 条件先进行过滤数据集，再进行连接(JOIN) 等操作

●用Case When 替代UNION

●用OR 替代UNION

●去掉多余的Distinct 与Group by

> Distinct 是去除重复的操作

> Group by 是聚集操作

> 某些情况下，两者可以起到相同的作用

> 两者的执行计划不一样，效率也不一样

> 建议：使用Group by

●Union vs. Union all

> Union 与Union all 的作用是将多个SQL 的结果进行合并

> Union 将自动剔除集合操作中的重复记录，需要耗更多资源

> Union all 则保留重复记录，一般建议使用Union all 。

> 第一个SELECT 语句，决定输出的字段名称，标题，格式

> 要求所有的SELECT 语句：

1) 必须要有同样多的表达式数目；

2) 相关表达式的域必须兼容

●先Group by 再join

> 先汇总再连接，可以减少参与连接的数据集大小，减少比较次数，从而提高效率。

例如：假设历史表（History ）有 1 亿条记录

1）以下SQL ，需要进行 1 亿×90 次比较

SELECT H.product_id,sum(H.account_num)

FROM History H , Calendar DT

WHERE H.sale_date = DT.calendar_date AND DT.quarter = '3'

GROUP BY 1;

2）以下SQL ，则只需要 1 亿× 1 次比较

SELECT 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_date

GROUP BY 1;

●提取公共SQL 形成临时表

●多表关联条件

7.连接条件过于复杂

8.缺乏统计信息