个人DB2经验分享之入门篇

sher

　　草草写了一篇所学习到的一些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
　　declare val1 int default 0;
　　declare val2 int default 0;
　　declare val3 int default 0;
　　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;
　　以上的写法没有在业务逻辑上修改,但性能增加一倍以上