澡堂子: TUNING

Showing posts with label TUNING. Show all posts

2013/02/21

USI ON PI in PPI table

normally, create a SI on PI will cause a error:

create table tbl1
( col1 char(10),
col2 char(10),
col3 char(10)
)
primary index(col1, col2)
index(col1,col2)

CREATE TABLE Failed. 3519: Two indexes with the same columns.

here is some special circumstances:

create table tbl1
( col1 char(10),
col2 DATE
)
primary index(col1)
PARTITION BY RANGE_N(col2 BETWEEN DATE '2000-01-01' AND DATE '2020-12-31' EACH INTERVAL '1' DAY)
unique index(col1)

the reason is performance.

2013/01/28

Abort Sessions in teradata

i used to kill sessions in teradata by pmon

there is another way : by udf SYSLIB.AbortSessions()

from

http://dadawarehousingwithtd.blogspot.com/2012/10/kill-session-in-teradata.html
http://www.teradataforum.com/teradata/20111122_082726.htm

1. Find the session info as:

select * from dbc.SessionInfo;

2. Execute abortsession function:

SELECT SYSLIB.AbortSessions(1,'MyUser',0,'Y','Y');

Param1: Hostno

Param2:UserName

Param3: SessionNo

Param4: LogoffSessions

Param5: UserOverride

Where 0 in Param3 means all session of user will be aborted,you can specify sessionno to kill a specific session.

Param4=Y means logoff the session first.

2013/01/21

query logging on/off

a user need execute privilage on DBC.DBQLAccessMacro to setting query on or off
so at first we neet to give execute right to the user who neeed to manage query logging

1.GRANT EXECUTE ON DBC.DBQLAccessMacro TO USER_A;

and then logon with USER_A to set logging on

2.begin query logging on USER_B;

3.end query logging on USER_B;

4.revoke EXECUTE ON DBC.DBQLAccessMacro from USER_A;

2012/08/09

Join index is so restricted

CREATE TABLE TBLA
(
bank_shikib_shi CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
tengun CHAR(2) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
tenban CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL
）

CREATE TABLE TBLB
(
bnkptn CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
tengun CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
tenban CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
)

we got above two tables and we need a join index

so the DDL is..

create join index testjoinindex as
select
...
...
...
from
TBLA t1
left outer join
TBLB t2
on
t1.bank_shikib_shi=t2.bnkptn
and t1.daihyosha_tenban=t2.tenban
primary index (bank_shikib_shi, tenban, cif_no)
;

we got a error:
CREATE INDEX Failed. [5464] Error in Join Index DDL, A name specified for an index was not found in the select list.

the reason is:
TBLA IS CASESPECIFIC BUT TBLB IS NOT NOT CASESPECIFIC

WTF

2012/01/03

TERADATA SQL TO VIEW SKEW DATA

usally we can use the dbc.TABLESIZE table to see perm size of each AMP on a table to find that if there is any significant data skew exist

SELECT
VPROC, SUM(CURRENTPERM)
FROM
DBC.TABLESIZE
WHERE
DATABASENAME = 'CVDTST'
AND TABLENAME = 'K_CALENDER'
GROUP BY
VPROC

Vproc Sum(CurrentPerm)
0 5,632.00
1 5,632.00
2 4,608.00
3 4,608.00
4 5,120.00
5 5,632.00
.
.
.
.

81 5,120.00
82 6,144.00
83 5,632.00
84 5,120.00
85 5,120.00
86 5,120.00
87 5,120.00

More accurate method is, count table's row count on each amp but not perm size.
it is more accurate, but much more costly

SELECT
HASHAMP (HASHBUCKET(HASHROW ( BOT_CD ,KAISYA_CD ,REKI_YMD))) AS "AMP NO", COUNT(*)
FROM
CVDTST.K_CALENDER
GROUP BY
1

AMP NO Count(*)
0 57
1 60
2 45
3 43
4 51
5 55
.
.
.
.
.
80 50
81 49
82 62
83 56
84 48
85 52
86 54
87 52

一般来说，我们可以通过查看DBC.Tablesize中某个表在各个AMP上占用的PERM空间来判定是否有显著的SKEW。
更精确的方法是利用PI字段依次计算出row hash,bucket,amp，通过统计每个amp上分配的行数来精确判定SKEW。
这个方法更精确，当然也需要更大的代价

2011/12/26

PERFORMANCE IMPACT WHEN TERADATA DEAL WITH "NOT IN" CLAUSE

THERE IS A NOTABLE PERFORMANCE IMPACT WHEN TERADATA DEAL WITH "NOT IN" CLAUSE
WITHOUT NOT NULL PROPERTY SPECIFIED ON THE COLUMN

HERE IS THE SQL SCRIPT AND explain IN TERDATA 13:

explain SELECT
BOT_CD
,KAISYA_CD
,TKS_CD
,LOCA_CD
,COL_NO
,NHIN_BSHO_CD
,MAC_CD
,SHO_CD
,JSC_SHO_CD
,JSC_SHO_CD_EDA
,HEN_ZAIKO_SU
,KINAI_ZAIKO_SU
,HOTCOLD_FLG
FROM
cvdtst.S_KINAIZAIKO_COL1221OU/* S_KINAIZAIKO_COL */
WHERE
(
BOT_CD
,KAISYA_CD
,TKS_CD
,LOCA_CD
,NHIN_BSHO_CD
,SHO_CD
,HOTCOLD_FLG
)
NOT IN
(
SELECT
BOT_CD
,KAISYA_CD
,TKS_CD
,LOCA_CD
,NHIN_BSHO_CD
,SHO_CD
,BUNRUI_CD
FROM
cvdtst.S_SLIP1_SHO1221OU /* S_SLIP1_SHO */
WHERE
SURYOU > 0
)
;

1) First, we lock a distinct cvdtst."pseudo table" for read on a
RowHash to prevent global deadlock for cvdtst.S_SLIP1_SHO1221OU.
2) Next, we lock a distinct cvdtst."pseudo table" for read on a
RowHash to prevent global deadlock for
cvdtst.S_KINAIZAIKO_COL1221OU.
3) We lock cvdtst.S_SLIP1_SHO1221OU for read, and we lock
cvdtst.S_KINAIZAIKO_COL1221OU for read.
4) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from cvdtst.S_SLIP1_SHO1221OU
by way of an all-rows scan with a condition of (
"cvdtst.S_SLIP1_SHO1221OU.SURYOU > 0.0") into Spool 7
(all_amps), which is redistributed by the hash code of (
cvdtst.S_SLIP1_SHO1221OU.BUNRUI_CD,
cvdtst.S_SLIP1_SHO1221OU.SHO_CD,
cvdtst.S_SLIP1_SHO1221OU.NHIN_BSHO_CD,
cvdtst.S_SLIP1_SHO1221OU.LOCA_CD,
cvdtst.S_SLIP1_SHO1221OU.TKS_CD,
cvdtst.S_SLIP1_SHO1221OU.KAISYA_CD,
cvdtst.S_SLIP1_SHO1221OU.BOT_CD) to all AMPs. Then we do a
SORT to order Spool 7 by row hash and the sort key in spool
field1 eliminating duplicate rows. The size of Spool 7 is
estimated with no confidence to be 11,078,203 rows (
1,074,585,691 bytes). The estimated time for this step is
7.68 seconds.
2) We do an all-AMPs SUM step to aggregate from
cvdtst.S_KINAIZAIKO_COL1221OU by way of an all-rows scan with
no residual conditions. Aggregate Intermediate Results are
computed globally, then placed in Spool 4.
5) We do an all-AMPs SUM step to aggregate from Spool 7 by way of an
all-rows scan. Aggregate Intermediate Results are computed
globally, then placed in Spool 8.
6) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from Spool 8 (Last Use) by
way of an all-rows scan into Spool 2 (all_amps), which is
duplicated on all AMPs.
2) We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by
way of an all-rows scan into Spool 3 (all_amps), which is
duplicated on all AMPs.
7) We do an all-AMPs RETRIEVE step from cvdtst.S_KINAIZAIKO_COL1221OU
by way of an all-rows scan with no residual conditions into Spool
6 (all_amps), which is redistributed by the hash code of (
cvdtst.S_KINAIZAIKO_COL1221OU.BOT_CD,
cvdtst.S_KINAIZAIKO_COL1221OU.KAISYA_CD,
cvdtst.S_KINAIZAIKO_COL1221OU.TKS_CD,
cvdtst.S_KINAIZAIKO_COL1221OU.LOCA_CD,
cvdtst.S_KINAIZAIKO_COL1221OU.NHIN_BSHO_CD,
cvdtst.S_KINAIZAIKO_COL1221OU.SHO_CD,
cvdtst.S_KINAIZAIKO_COL1221OU.HOTCOLD_FLG) to all AMPs. Then we
do a SORT to order Spool 6 by row hash, and null value information
in Spool 3 and Spool 2. Skip this retrieve step if null exists.
The size of Spool 6 is estimated with low confidence to be
3,257,496 rows (218,252,232 bytes). The estimated time for this
step is 1.91 seconds.
8) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of
an all-rows scan, which is joined to Spool 7 by way of an
all-rows scan. Spool 6 and Spool 7 are joined using an
exclusion merge join, with a join condition of ("(BOT_CD =
BOT_CD) AND ((KAISYA_CD = KAISYA_CD) AND ((TKS_CD = TKS_CD)
AND ((LOCA_CD = LOCA_CD) AND ((NHIN_BSHO_CD = NHIN_BSHO_CD)
AND ((SHO_CD = SHO_CD) AND (HOTCOLD_FLG = BUNRUI_CD ))))))"),
and null value information in Spool 3 and Spool 2. Skip this
join step if null exists. The result goes into Spool 1
(group_amps), which is built locally on the AMPs. The size
of Spool 1 is estimated with no confidence to be 3,257,496
rows (244,312,200 bytes). The estimated time for this step
is 1.02 seconds.
2) We do an all-AMPs RETRIEVE step from
cvdtst.S_KINAIZAIKO_COL1221OU by way of an all-rows scan with
no residual conditions into Spool 10 (all_amps), which is
redistributed by the hash code of (
cvdtst.S_KINAIZAIKO_COL1221OU.BOT_CD) to all AMPs. Then we
do a SORT to order Spool 10 by row hash, and null value
information in Spool 3 and Spool 2. Skip this retrieve step
if there is no null. The size of Spool 10 is estimated with
low confidence to be 3,257,496 rows (218,252,232 bytes). The
estimated time for this step is 1.91 seconds.
9) We do an all-AMPs RETRIEVE step from Spool 7 (Last Use) by way of
an all-rows scan into Spool 11 (all_amps), which is redistributed
by the hash code of (cvdtst.S_SLIP1_SHO1221OU.BOT_CD) to all AMPs.
Then we do a SORT to order Spool 11 by row hash, and null value
information in Spool 3 and Spool 2. Skip this retrieve step if
there is no null. The size of Spool 11 is estimated with no
confidence to be 11,078,203 rows (1,074,585,691 bytes). The
estimated time for this step is 7.68 seconds.
10) We do an all-AMPs JOIN step from Spool 10 (Last Use) by way of an
all-rows scan, which is joined to Spool 11 (Last Use) by way of an
all-rows scan. Spool 10 and Spool 11 are joined using an
exclusion merge join, with a join condition of ("(BOT_CD = BOT_CD)
AND ((KAISYA_CD = KAISYA_CD) AND ((TKS_CD = TKS_CD) AND ((LOCA_CD
= LOCA_CD) AND ((NHIN_BSHO_CD = NHIN_BSHO_CD) AND ((SHO_CD =
SHO_CD) AND (HOTCOLD_FLG = BUNRUI_CD ))))))"), and null value
information in Spool 3 (Last Use) and Spool 2 (Last Use). Skip
this join step if there is no null. The result goes into Spool 1
(group_amps), which is built locally on the AMPs. The size of
Spool 1 is estimated with no confidence to be 3,257,496 rows (
244,312,200 bytes). The estimated time for this step is 1.02
seconds.
11) 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.

TERADATA HAVE TO CONFIRM IF NULL VALUE EXISTS ON EVERY "NOT IN" COLUMN,
AND IT IS VERY COSTLY AND WILL GREATLY IMPACT THE SYSTEM'S PERFORMANCE.
WE CAN AVOID IT BY SPECIFIING "NOT NULL" PROPERTY ON THE "NOT IN" COLUMN

HERE IS A ANOTHER SAMPLE IN JAPANESE
http://d.hatena.ne.jp/tgk/20100913

-------------------------------------------------------------------------------
THE FOLLOWING IS SQL AND EXPLAIN IN TERADATA V2R6

explain SELECT
BOT_CD
,KAISYA_CD
,LOCA_CD
,COL_NO
,MAC_CD
,SHO_CD
,HEN_ZAIKO_SU
,KINAI_ZAIKO_SU
,HOTCOLD_FLG
FROM
S_KINAIZAIKO_COL/* S_KINAIZAIKO_COL */
WHERE
(
BOT_CD
,KAISYA_CD
,LOCA_CD
,MAC_CD
,SHO_CD
,HOTCOLD_FLG
)
NOT IN
(
SELECT
BOT_CD
,KAISYA_CD
,LOCA_CD
,MAC_CD
,SHO_CD
,BUNRUI_CD
FROM
S_SLIP1_SHO/* S_SLIP1_SHO */
WHERE
SURYOU > 0
)

1) First, we lock a distinct HWKRUN34."pseudo table" for read on a
RowHash to prevent global deadlock for HWKRUN34.S_SLIP1_SHO.
2) Next, we lock a distinct HWKRUN34."pseudo table" for read on a
RowHash to prevent global deadlock for HWKRUN34.S_KINAIZAIKO_COL.
3) We lock HWKRUN34.S_SLIP1_SHO for read, and we lock
HWKRUN34.S_KINAIZAIKO_COL for read.
4) We do an all-AMPs SUM step to aggregate from
HWKRUN34.S_KINAIZAIKO_COL by way of an all-rows scan with no
residual conditions. Aggregate Intermediate Results are computed
globally, then placed in Spool 3.
5) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
an all-rows scan into Spool 2 (all_amps) (compressed columns
allowed), which is duplicated on all AMPs.
6) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from
HWKRUN34.S_KINAIZAIKO_COL by way of an all-rows scan with no
residual conditions into Spool 5 (all_amps) (compressed
columns allowed), which is redistributed by hash code to all
AMPs. Then we do a SORT to order Spool 5 by row hash, and
null value information in Spool 2. Skip this retrieve step
if null exists. The size of Spool 5 is estimated with low
confidence to be 3,663,600 rows. The estimated time for this
step is 4.55 seconds.
2) We do an all-AMPs RETRIEVE step from HWKRUN34.S_SLIP1_SHO by
way of an all-rows scan with a condition of (
"HWKRUN34.S_SLIP1_SHO.SURYOU > 0.0") into Spool 6 (all_amps),
which is redistributed by hash code to all AMPs. Then we do
a SORT to order Spool 6 by row hash and the sort key in spool
field1 eliminating duplicate rows. The input table will not
be cached in memory, but it is eligible for synchronized
scanning. The size of Spool 6 is estimated with no
confidence to be 11,532,920 rows. The estimated time for
this step is 18.35 seconds.
7) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of
an all-rows scan, which is joined to Spool 6 by way of an
all-rows scan. Spool 5 and Spool 6 are joined using an
exclusion merge join, with a join condition of ("(BOT_CD =
BOT_CD) AND ((KAISYA_CD = KAISYA_CD) AND ((LOCA_CD = LOCA_CD)
AND ((MAC_CD = MAC_CD) AND ((SHO_CD = SHO_CD) AND
(HOTCOLD_FLG = BUNRUI_CD )))))"), and null value information
in Spool 2. Skip this join step if null exists. The result
goes into Spool 1 (group_amps), which is built locally on the
AMPs. The size of Spool 1 is estimated with index join
confidence to be 3,663,600 rows. The estimated time for this
step is 1.47 seconds.
2) We do an all-AMPs RETRIEVE step from
HWKRUN34.S_KINAIZAIKO_COL by way of an all-rows scan with no
residual conditions into Spool 7 (all_amps) (compressed
columns allowed), which is redistributed by hash code to all
AMPs. Then we do a SORT to order Spool 7 by row hash, and
null value information in Spool 2. Skip this retrieve step
if there is no null. The size of Spool 7 is estimated with
low confidence to be 3,663,600 rows. The estimated time for
this step is 4.55 seconds.
8) We do an all-AMPs RETRIEVE step from Spool 6 (Last Use) by way of
an all-rows scan into Spool 8 (all_amps) (compressed columns
allowed), which is redistributed by hash code to all AMPs. Then
we do a SORT to order Spool 8 by row hash, and null value
information in Spool 2. Skip this retrieve step if there is no
null. The size of Spool 8 is estimated with no confidence to be
11,532,920 rows. The estimated time for this step is 18.35
seconds.
9) We do an all-AMPs JOIN step from Spool 7 (Last Use) by way of an
all-rows scan, which is joined to Spool 8 (Last Use) by way of an
all-rows scan. Spool 7 and Spool 8 are joined using an exclusion
merge join, with a join condition of ("(BOT_CD = BOT_CD) AND
((KAISYA_CD = KAISYA_CD) AND ((LOCA_CD = LOCA_CD) AND ((MAC_CD =
MAC_CD) AND ((SHO_CD = SHO_CD) AND (HOTCOLD_FLG = BUNRUI_CD )))))"),
and null value information in Spool 2 (Last Use). Skip this join
step if there is no null. The result goes into Spool 1
(group_amps), which is built locally on the AMPs. The size of
Spool 1 is estimated with index join confidence to be 3,663,600
rows. The estimated time for this step is 1.47 seconds.
10) 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.

Teradata13在处理NOT IN查询的时候，如果NOT IN字段没有NOT NULL定义的话，TERADATA会确认每一个字段是否为NULL值，
这会导致数次FTS，极大的影响性能。解决方式是，给NOT IN项目定义NOT IN，或者把NOT IN改为左连接

不过在老版本的Teradata中（比如说V2R6），似乎并不存在这样的问题，目前无法完全确定，留待以后研究

2010/06/12

TD性能调优和物理实现培训总结 2

原帖地址
http://blog.chinaunix.net/u3/103331/showart_2050758.html

总结 2
1、Parcel
Client to Server(Request Parcel(1)、Data Parcel(0 or N)、Response Parcel)

   Parcel步骤：REQUEST PARCEL -> SYNTAXER -> RESOLVER -> SECURITY -> OPTIMIZER -> GENERATOR -> GNCApply -> AMP STEPS

   使用Data Parcel的好处，是可以让PE缓存执行计划，重复执行，提高效率。
   使用意见：对于那种重复执行的动作，比如重复插入一堆的数据的时候，最好将数据弄成文件的形式，采用Using 的方式在BTEQ中提交，能提高很多效率。
   如何查看执行的sql是否被cache呢？
   Answer:通过DBQLogTbl表，里面的字段CacheFlag可以查看该语句在执行的时候是否有被cache（PE的解析Cache是采用LRU算法，4个小时最长生命周期，既4个小时候会把该sql语句给unmarked）

Parcel的RESOLVER过程，会使用系统字典表来检查sql语句是否正确，那么是否会锁系统表呢？
Answer:Parcel的RESOLVER也是使用缓存来存系统字典表信息，不会锁系统表。Data Dictionary Cache

2、Join Strategies
Teradata的设计思想是：Share Nothing，也就是说各个AMP之间是独立，每个AMP都不知道其他AMP到底存储有什么样的数据，所以对于Join、比较等，都需要在本AMP进行了，也就有了所有的Redistribution和Duplicate to all amp。

   Product Join：Duplicate smaller table to all amp,(not sort)
   Merge Join：Duplicate smaller table or redistribute table which don't join with pi. Sort on join column row hash.
   merge join 由于Sort column row hash，所以可以减少比较的次数（不用像Product Join那样每个都需要比较）。

   Join如果非正常的慢可能有以下几个情况：
   which is redistributed by hash code to all AMPs ： Skew表被Redistribute了
   which is duplicated on all AMPs ：大表被Duplicate了。
   这就涉及到一个统计信息的问题。如果收集的统计信息没有及时的更新，比如说建完一个表，收集了统计信息，然后统计信息里面这个表是小表（因为是空表）。而当这个表数据量变大时，没有更新统计信息，则优化器还是认为他是小表，可能就会duplicate这张表，那就是个大工作量了。

   Where条件中，关于OR操作的优化，可以采用UNION的方式，放到2个SQL语句中执行，而不做product join。

SELECT Department_Name, Last_Name, First_Name
FROM     Employee INNER JOIN Department
ON           Employee.Department_Number = Department.Department_Number
OR           Employee.Employee_Number = Department.Manager_Employee_Number;
变为
SELECT Department_Name, Last_Name, First_Name
FROM     Employee INNER JOIN Department
ON          Employee.Department_Number = Department.Department_Number
UNION UNION
SELECT Department_Name, Last_Name, First_Name
FROM     Employee INNER JOIN Department
ON          Employee.Employee_Number = Department.Manager_Employee_Number;

Nested Join(最简单的join)：join的一个表而且有确定的where条件（UPI或者USI），只能选择出一条记录

3、PPI
PPI的好处：特殊查询能力的增强，比如说按日期查询（设置按日期的PPI），避免全表扫描
PPI可能出现的问题：多了2个字节的空间浪费（partition号），对PI的查询可能会比没有定义PPI的慢(先确定了partition在访问里面的row hash)。
如果partition 不是PI的一部分的话，PI不能定义为UPI，而为了保证唯一性，可以定义USI在字段上。
思考？为什么partition不是PI的一部分的话，PI不能定义为UPI
Answer：UPI能确保唯一性，而如果partition不是PI的一部分的话，如果使用PI来Access的话，会扫描每个partition在定位，已经失去了作为UPI的意义了。

DBC.IndexConstraints中ConstraintsType标志位Q的表示PPI。

PPI的适用情形：
1、大表
2、partition字段经常被Access
3、partition的数量是有限制的（65535）

4、Aggregation
ARSA算法：
Aggregate - Local on the AMPs
Redistribute - The Local Aggregations
Sort - The Redistribution Aggregations
Aggregate - Globally for the final aggregation

Group by VS Distinct
1、适用情况：
     Group by 先在本AMP上分组，然后在在Globally上分组
     Distinct 是先把数据分布到All AMP上在去重。
     所以，在重复值比较多的情况下，Group by 能提高点性能，其他情况下，Distinct比较快点。（不是绝对的）
2、关于Eliminate
     group by 没有进行eliminate操作，distinct有进行这样的操作。
     （in、not in、unique、distinct操作会去重）

5、Date
TD中，日期以数字来存储。如2009-09-09在数据库中被转换为20090909，在数据库中存储过数字10090909（20090909-19000000）
如果时间小于19000000则在数据库中存储为负值。

        SELECT EXTRACT(YEAR FROM DATE '2004-03-31') ||
        EXTRACT(MONTH FROM DATE '2004-03-31')
        result:       2004          3
        整数转换为11位字符在||

TD性能调优和物理实现培训总结 1

原帖地址

http://blog.chinaunix.net/u3/103331/showart_2045801.html

1、几种索引的判定：
Join Index 预先进行关联的表，类似于“物化视图”，需要占用表空间，Join Index可以自动更新。
Global Index 在选择出来的字段中有rowid
Sparse Index 在Index中使用了Where条件
Aggregate Join Index 预先在index中进行Aggregate操作(同summary table的区别：summary table需要手动更新，Aggregate Join index可以自动更新)

2、Store Procedure和Macro的区别：
Macro和Stored Produre同样能使用变量，调用sql语句。其中Macro一般比Stored Procedure 简单，同时Stored Procedure支持SPL操作，既IF..ELSE...THEN等。

3、TDWM：Teradata Dynamic Workload Manager，混合负载管理，动态的管理资源分布和优先级。（目前正在做试验）

4、关于视图
加视图的好处：
1、SQL语句---保存复杂的SQL语句来直接运行
2、保密---可以在sql语句中使用一定的方式来加密字段
3、权限和索机制---最主要的，通过视图来限制对基表的访问，同时对基本的访问时候可以加Access锁

基于视图的视图访问是极其没有效率的：
可以试下语句create table tmp as (select * from t03_agreement)，可以发现tmp的主键已经不是t03_agreement的主键了，嵌套视图同样的，当访问嵌套视图时候，第二个视图的索引已经没用了，会造成很大的SKEW，影响数据的选取。（根据这段时间的工作，发现在生产上还是有一些使用到了嵌套视图的。）

5、事务和锁
   事务处理系统的典型特点是具备ACID特征。ACID指的是Atomic(原子的)、Consistent(一致的)、Isolated(隔离的)以及Durable(持续的)，它们代表着事务处理应该具备的四个特征。
   TD的锁这种级别：Access、Read、Write、Exclude锁。其中，DDL语句一般是Exclude锁，Insert、update一般是Write锁，Select一般是read锁。同级的锁可以互相叠加，Exclude锁除外。Access锁可以获得除Exclude锁以外的全部锁，但是可能会造成脏读的现象，视图就是以这种方式为考虑的。（问题分析，DW允许脏读的产生，因为DW多以大数据量的查询为主，比较少进行update的操作，除非是在数据批处理的时候，不像OLTP系统，需要对数据的一致性有很高的要求。）
   TD基于MPP的架构，每个AMP之间是独立的，所以对于同样的一个请求，可能会有不同的执行顺序，没有一个同步的策略，这个时候TD采用的是一个Pesudo表，这个表是每个AMP都公用的，以此来保证同步，每次需要加锁某个对象的时候，就先加Pessudo表，而不是对每个AMP进行加锁。（可以通过Explain来查看得到）
   BT ET跟Muti-statement加锁的不一样：
   1、BT ET是一个语句一个语句加锁，锁进行累积，直到遇到ET的时候才释放锁
   2、muti-statement 是一次性全部加锁
TD对于死锁机制的处理：
   在TD数据库中，每4分钟会检查一次死锁，如果出现死锁的话，就roll back 最后的那个Session

Presudo的思考---是不是每次访问都需要使用到Presudo表呢？
   answer:
      2个语句explain select * from table where pi字段 = ? 和explain select * from table where notpi字段= ?
      你会发现，使用pi字段的时候就不会所presudo表，这是因为TD的Presudo表是针对MPP架构设计的，如果是单个AMP操作的时候，其实就没有所谓的同步的现象，所以就不用使用Presodu。
      如上，也就是说单AMP操作不需要presodu表。

对于提交Multi-Statement Requests，将“；”号放在sql语句的前面，多个sql语句同时提交，如下
                  Update Dept
                        set Salary_Change_Date = Date
                 ; Update Manager
                        set Salary_Amt = Salary_Amt *1.08
                 ; Update Employee
                        set Salary_Amt = Salary_Amt * 1.04;
    上述语句被放到同一个Session中提交，但是在数据库处理的时候并不是按照顺序一个个下来的，而是随机执行的，同时优化器可能根据判断，来并行的执行语句，所以不能妄图使用Multi-Statement来达到顺序执行的目的。

6、关于DBQLogtbl表
   DBQLogTbl表，是DBC的系统表，FallBack的，双倍存储空间。一般来说如果在数据库中开启了日志记录的话，这个表的空间会增长很快。
   DBQLogTbl里面有很多非常有用的信息。
   比如字段CacheFlag可以得出本sql是否使用了cache，比如说里面有CPU时间，语句执行时间等，可以由此来判断多长时间是等待，多长时间是真正执行，同时也可以日志下对于数据库的sql语句，可以由此来进行调优。
   DBQLogTbl表可以使用的策略如下：
     1、开启数据库的日志记录
     2、每天清理DBQLogTbl表，把数据库传输到一个没有fallback的表中（可以适当的删除些不需要的字段）
     3、分析导出的数据

2010/06/11

TUNING TIPS

BETWEEN比IN有效
IN比EXIST有效
LIKE条件的开头如果不是通配符则优化器使用索引的可能。否则进行全表扫描
DISTINCT很耗费资源（但是和GROUP BY相比如何呢？）
如果可以的话使用UNION ALL而不是UNION
使用多个SQL语句计算不同范围的合计值会导致同一个表的多次扫描，如果将
这些SQL合并，并用CASE区分范围，可以减少扫描次数。这会极大的提高性能
DISTINCT会创建临时表，可以更改SQL的结构避免DISTINCT的使用。
使用派生表而非临时表。减少磁盘IO
避免使用混合类型的表达式。例如字符串和数值比较，TERADATA会将字符串
转换成数值。这会使索引失效并导致全表扫描。结论：混合类型比较时做显示转换
SI
JI
GLOBAL JI
PPI