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/，如需轉載，請註明出處，否則將追究法律責任。