草草写了一篇所学习到的一些DB2技术和经验的日志,当然这些只是其中小部分.会继续分享的
----------------------------------------
表设计时需要注意的:
1. 默认值
create table tbname(col1 int not null with default 1) 或者
create table tbname(col1 int not null default 1)
使用了该默认值可以
2. Char和varchar
字段数据类型尽量使用char来代替字符，char类型性能稍比varchar好点。因为varchar是变长类型，所以是先读取数据长度再读取数据，分了两个步骤，而char是定长类型，读取数据长度和数据只用了一个步骤
3. 字段顺序
如果该表常常更新且字段中含有varchar数据类型，请把该字段放到最后。
(1)create table tbname(col1 int not null with default 1,col3 varchar(10),col2 int)【不推荐】
(2)create table tbname(col1 int not null with default 1,col2 int,col3 varchar(10))【推荐】
原因是varchar类型是变长的，而char是定长的，所以常常见到在读char类型的时候，不足所指定长度的数据则补充空格.因为是变长的类型所以字段在CREATE表的时候列col3会在按CREATE顺序来创建，但在(1)中列col3不会直接是varchar，而是DB2把列col3的值移到最后面，而在col3只保存一个定长的指针指向到最后的值而已.在(2)中只要直接把变长的类型字段放置最后面即可避免DB2额外生成多一个指针.
4. 合适使用约束也可以增强查询性能
Create table tbname(col1 int ,col2 int,col3 int generated always as(col1 + col2),col4 int generated always as( case when col1 > col2 then col1 else col2 end))
查询1 取col1和col2之和的时候
Select sum(col1+col2) from tbname;
可以改成:
Select sum(col3) from tbname;
查询2 取col1和col2其中最大的一个
Select case when col1 > col2 then col1 else col2 end as col from tbname
可以改成
Select col4 as col from tbname;
一些常用的技巧
1．删除大量数据的时候
删除表中数据：delete from tablename
不记 log 删除表中数据： alter table tabname activate not logged initially;delete from tablename
修改表不记录 log 方式：alter table tabname activate not logged with empty table
LOAD 的 replace 方式：load from empfile of del replace into tabname
======
以上四种用法参考了袁春光在DW上发布的的文章[LOAD的使用技巧].
注:对于存放敏感数据的表不推荐这样使用,因为把日志删除后,数据就很难恢复回来了.
2．变量赋值
(1)在存储过程或者函数(function)中常见如下赋值:
Begin atomic
default
default 0;
default
declare val4 char;
set val1 = 1;
set val2 = 1;
set val3 = 1;
set val4 = ‘1’;
End;(2)可以改写成以下形式:
Begin atomic
declare val1 int default 0;
declare val2 int default 0;
declare val3 int default 0;
declare val4 char(2);
declare val5 char(2) default null;--含变量赋值的时候使用如下
-- values(1,1,1,coalesce(val5,’1’)) into val1,val2,val3,val4; //假设在设val4的是变量最好加上一个避免空值的coalesce来控制,因为values(…) into(…) 的时候有空值会报错
--只含常量的时候推荐使用如下
values(1,1,1,coalesce(val5,’1’)) into val1,val2,val3,val4;
End;
这样的赋值的好处有提高性能,减低代码量:因为按照(1)形式来编写的时候每set一次,DB2都会去访问一次内存,其实是很影响性能的,而values 一次就能把所有需要赋的值的指针全部找出来,理论说(2)的形式能提高(1)的形式的n-1次set的倍性能
3．count(*) 和exists的用法
在判断数据是否存在的时候尽量避免使用count(1)，因为这样会对全表遍历可以使用exists来代替，exists的好处是它一旦遇到数据就立刻终止查询下去
例：
select case when count(1) >0 then '存在' else '不存在' end as isFlag from test fetch first 1 row only;
可以改写成
select case when exists(select 1 from test fetch first 1 row only) then '存在' else '不存在' end as isFlag from test fetch first1 row only;
--该方式写得比较复杂,但是主要为了举例说明exists的用法
4．DB2的编写风格建议
根据本人实践了牛新庄的经验总结,了解到DB2中的SQL优化器对复杂的SQL优化得比较强大(相对于oracle来说),建议尽量的编写一条复杂的SQL来代替多条的简单SQL.
例:要求你把客户ID为123的客户名称 张三改成李四,并且把他更改前和更改后的数据查询出来
1:一般情况下会有如下步骤:
(1) 查询更改前的客户信息
Select cid,cname from customer where cid=123;
(2) 更改客户姓名
Update customer set cname=’李四’ where cid=123;
(3) 查询更改后的客户信息
Select cid,cname from customer where cid=123;
2:可以更改成该用法
Select cid,cname from new table(Update customer set cname=’李四’ where cid=123);
with t1 as (Select cid,cname from new table(Update customer set cname=’李四’ where cid=123))
select * from t1 where cid=123 union all select * from customer where cid=123 with ur;
以上的写法没有在业务逻辑上修改,但性能增加一倍以上
