数据库优化

四、数据库优化
1.数据库物理结构优化
实例优化：sga+pga优化
数据文件优化：redo文件不能太小，不然频繁切换严重影响性能。
2.数据库逻辑结构优化
索引，分区，物化视图，统计信息。
3.SQL语句优化
Sql语句的执行顺序，从下向上，向外向内。
所以大小表关联，小表写后面。
所以有关联有筛选，先筛选后关联，减少关联的数据。
使用函数不能击中索引，导致全表扫描。
Oracle隐形数据类型转换（比如整数和字符），导致全表扫描。
使用exsit代替in
4.优化工具
使用EM中的2个sql调优工具：sql tuning advisor, sql access advisor。

5.其它优化方法
A：强制使用索引
SELECT /+ index(misywt12 MISYWT12_X)/ * FROM misywt12 where pssl <> 0 and zfbz = ‘0’ and sfrq0 is null and (substr(nvl(khbz, ’ '), 1, 2) <> ‘fp’ or (substr(khbz, 1, 2) = ‘fp’ and qrrq2 is not null)) and (psdjh>=66653250 or psdjh <=-66653250)

五、数据库开发
1.Sql语句
update使用方法
使用一个表A更新表B
UPDATE employee1 a
SET
(salary, name) =
(SELECT salary, name
FROM employee2 b
WHERE a. employee_id = b. employee_id)
WHERE employee_id IN
(SELECT employee_id
FROM employees2
);

2.Sql*loader导入csv文件
导入语句
sqlldr user1/user1@xxx control=C:\123\1.ctl log=C:\123\1.log
控制文件1.ctl
load data infile ‘c:\123\1.csv’ append into table wl2015k0.misywt01_0 fields terminated by ‘,’ (
spbm,
spmc,
jlrq timestamp “yyyy-mm-dd hh24:mi:ss”)

日期字段需要单独处理
jlrq timestamp “yyyy-mm-dd hh24:mi:ss”,
3.一个存储过程
–存储过程，发送采购订单到物流
create or replace procedure xg.p_t21 is
–定义游标c_cgdhd，需要发送的数据，加锁。
cursor c_cgdhd is select * from xg.tmp21
where fsckrq is null for update;
–定义游标变量变量v_cgdhd
v_cgdhd c_cgdhd%rowtype;
–定义其它变量
v_dhdjh xg.tmp21.dhdjh%type;
v_xh xg.tmp21.xh%type;
v_code number(6);
v_text varchar2(200);
–开始
begin
–使用for循环处理游标中的数据
for v_cgdhd in c_cgdhd loop
v_dhdjh:=v_cgdhd.dhdjh;
v_xh:=v_cgdhd.xh;
–更新发送日期，使用current of 游标c_cgdhd确定当前行数据。
update xg.tmp21 set fsckrq=sysdate
where current of c_cgdhd;
–把采购订单插入物流，使用行变量v_cgdhd直接insert
insert into xgck.tmp21 values v_cgdhd;
–写发送成功的日志
if sql%found then
insert into xg.tmp_fslog values(‘采购订货单’,v_cgdhd.dhdjh,v_cgdhd.xh,sysdate,‘发送成功’,‘’);
end if;
end loop;
–for循环结束了，游标也结束了。
commit ;
–commit要写在循环end loop外面，否则游标中加的锁会释放，导致更新失败。
–异常处理
exception
when others then
–如果有rollback,一条单据出错，所有单据都不能处理。
–如果没有rollback，一条单据出错，这条错误单据可以置上
–发送日期，重新执行存储过程，其它正常单据可以发送过去。
rollback ;
v_code:=SQLCODE;
v_text:=SQLERRM;
–写发送失败的日志
–异常处理中无法使用游标变量v_cgdhd，所以使用普通变量v_dhdjh，v_xh。
insert into xg.tmp_fslog values(‘采购订货单’,v_dhdjh,v_xh,sysdate,‘发送失败’,v_code||v_text);
commit ;
end p_t21;
–结束

解决dbms_output.put_line(‘hello’)不打印
解决dbms_output.put_line(‘hello’)不打印
为什麽dbms_output.put_line(‘hello’)不打印? 
sql>show all 
serveroutput OFF 
sql>set serveroutput on 
sql>exec

4.一个触发器
当第一次录入采购订单时，初始化库存信息
create or replace trigger xg01.misywt21_jz
before insert
on xg01.misywt21
for each row
declare
– local variables here
v_jjdj xg.misywt01.jjdj%type;
v1 number(1);
v_code NUMBER(6);
v_text VARCHAR2(200);

begin
select count(*) into v1 from xg01.misywt41 where spbm=:new.spbm and ckbm=:new.ckbm;
IF v1=0 THEN
select jjdj into v_jjdj from xg.misywt01 where spbm=:new.spbm;
insert into xg01.misywt41(ckbm,spbm,kcslsx,kcslxx,bzqxx, dj,qcrq,ckhwh, qcsl,jhsl,thsl,cksl,tksl,pdcdsl,dbsl)
values (:new.ckbm,:new.spbm,0,0,365, v_jjdj,sysdate,null, 0,0,0,0,0,0,0);
insert into xg01.misywt42 (ckbm,spbm,kcslsx,kcslxx,bzqxx,dj,qcrq,ckhwh,qcsl,qcje,jhsl,jhje,thsl,thje,cksl,ckje,tksl,tkje,pdcdsl,pdcdje,dbsl,dbje,dqjj)
values (:new.ckbm,:new.spbm,0,0,365,v_jjdj,sysdate,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,v_jjdj);
end if;
EXCEPTION
WHEN OTHERS THEN
v_code:= SQLCODE;
v_text:= SQLERRM;
insert into xg01.misywt21_trigger values(sysdate,:new.dhdjh,:new.spbm,:new.ckbm,v_code,v_text);
end misywt21_jz;