sql语句的优化案例分析

今天一测试人员提供过来一sql语句，结构如下：

SELECT t.* FROM v_service t,(SELECT DISTINCT icpcode,icpservid
FROM t_servcate2serv t1,(SELECT cateid FROM t_servcate
START WITH cateid=1 and showflag=1 CONNECT BY PRIOR cateid = parentid
and showflag=1) t2 WHERE t1.cateid=t2.cateid) t3
WHERE t.icpcode=t3.icpcode AND t.icpservid=t3.icpservid and (t.ServStatus='A' OR t.servstatus ='P' or t.servStatus='B' ) and (t.OnDemandFlag !=1 and t.OnDemandFlag!=2) and (t.accessmodeid=2 or t.accessmodeid=5 or ((t.accessmodeid=3 or t.accessmodeid=4) and (t.SERVTYPE=2 or t.SERVTYPE=3 or t.SERVTYPE=9 or t.SERVTYPE=0) ));

其中v_service是一视图，内容如下：

create or replace view v_service
(icpcode, spname, spshortname, servidalias, icpservid, servname, servstatus, servattr, umflag, servtype, usagedesc, wwwurl, introurl, chargetype, price, chargedesc, starttime, endtime, servdesc, csrtel, spurl, accessmodeid, freeusetype, offlinedesc, offlinestate, offlinetime, freeusecount, ondemandflag, servicelogo, grouptype, servgroupid, type, specorderflag, specorderurl, brand, demourl, servcatid, freeurl)
as
select
t1.icpcode,t3.spname,t3.spshortname spshortname,T3.SERVIDALIAS,t1.icpservid,t1.servname, t1.servstatus,t1.ServAttr,
t1.umflag,T1.servtype,t1.UsageDesc,t1.wwwurl,t1.introURL, t1.ChargeType,t1.Price,t1.ChargeDesc,
t1.StartTime starttime,t1.EndTime,nvl(t2.servdesc,t1.servdesc) servdesc ,t3.csrtel,T3.CSRURL,
t4.ACCESSMODEID,t1.FREEUSETYPE,t1.OFFLINEDESC,t1.OFFLINESTATE,t1.OFFLINETIME,t1.FREEUSECOUNT,t1.ONDEMANDFLAG,
t1.SERVICELOGO,t7.GROUPTYPE,t7.SERVGROUPID,t7.TYPE,t1.SPECORDERFLAG,t1.SPECORDERURL,t2.brand,t2.demoURL,t1.SERVCATID,
w.freeurl
from service t1,t_servicemod t2,spinfo t3 ,SERVICE_ACCESS_MODE t4,
(select t6.ICPCODE,t6.ICPSERVID,t5.SERVGROUPID,t5.GROUPTYPE,t6.TYPE
from SERVICEGROUP t5,GROUP_SERVICE t6 where t5.SERVGROUPID=t6.SERVGROUPID) t7,
wap_service w
where t1.icpcode=t2.icpcode(+) and t1.icpservid=t2.icpservid(+) and
t1.icpcode=t3.spid(+) and t1.icpcode=t4.icpcode(+) and t1.icpservid=t4.icpservid(+) and
t1.ICPCODE=t7.icpcode(+) and t1.ICPSERVID=t7.icpservid(+) and
t1.ICPCODE=w.icpcode(+) and t1.ICPSERVID=w.icpservid(+) and
(t1.ServStatus='A' OR t1.servstatus ='P' or t1.servStatus='B') and
(t1.OnDemandFlag !=1 and t1.OnDemandFlag!=2) and
(t4.accessmodeid=2 or t4.accessmodeid=5 or (
(t4.accessmodeid=3 or t4.accessmodeid=4) and
(t1.SERVTYPE=2 or t1.SERVTYPE=3 or t1.SERVTYPE=9 or t1.servtype=0)
)
);

我晕，这么复杂的sql语句也敢放在现网上执行，非得把数据库搞塌掉！赶紧优化之！

这个查询语句的性能消耗主要在视图v_service上！

将视图v_service改为一个物化视图(materialized view)，然后每天定时刷新同步一次。

基于代码的改动量最小化，可直接取物化视图名为v_service，将原视图drop掉。

步骤如下：
1、建物化视图（建之前需dba赋create materialized view的系统权限给pas用户）
create materialized view v_service
as
select 。。。。。。（同建视图的select语句）

--建索引
create index indx_mv_service on v_service(icpcode,icpservid);

--定时刷新，可一天一次(根据需要来定)，放在job中定时调用
exec dbms_mview.refresh(list => 'v_service');

如下的对比结果供参考：
--v_service是视图时
SQL> select count(*) from v_service;

COUNT(*)
----------
25794

已用时间: 00: 00: 01.03
--v_service是物化视图时
SQL> select count(*) from v_service;

COUNT(*)
----------
25794

已用时间: 00: 00: 00.047
当然除了物化视图外，其他的索引、临时表和表关联等都是要考虑的，在此就不一一阐述。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/38542/viewspace-882821/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/38542/viewspace-882821/