臨時被安排來優化sql，但是功力尚淺，找不到可行的方法，希望各位給出點建議，下面是我從程序里摳出來的代表性代碼，由于代碼較長，分開貼
declare
V_S_TIME DATE;
V_E_TIME DATE;
V_ERRMESSAGE VARCHAR2(100);
I_NUMBER INTEGER;
CURSOR CURSOR_PRPCINSURED_D IS SELECT (SELECT T.VEHICLEID FROM
T_CMCS_S_POLICY_CAR T
WHERE G.POLICYNO=T.POLICYNO) AS VEHICLEID,
G.INSUREDTYPE,
G.INSUREDCODE,
G.INSUREDNAME,
G.INSUREDADDRESS,
G.INSUREDNATURE,
G.INSUREDFLAG,
G.INSUREDIDENTITY,
G.IDENTIFYTYPE,
G.IDENTIFYNUMBER,
G.ACCOUNT,
G.DEPARTUREDATE,
G.DEPARTUREHOUR,
G.POLICYNO,
G.PHONENUMBER,
G.MOBILE,
G.INTG_DATASTATE,
G.SERIALNO
FROM ODSSEND.T_CMCS_PRPCINSURED G WHERE INTG_DATASTATE = 'D';
TYPE T_POLICYINSURED IS TABLE OF T_CMCS_S_VEHICLE_CONTACTS%ROWTYPE INDEX BY BINARY_INTEGER;
V_POLICYINSURED T_POLICYINSURED;
TYPE T_PRPCINSURED IS TABLE OF T_PRPCINSURED_RECORD INDEX BY BINARY_INTEGER;
V_PRPCINSURED T_PRPCINSURED;
uj5u.com熱心網友回復：
begin
V_S_TIME := SYSDATE;
LOOP
FETCH CURSOR_PRPCINSURED BULK COLLECT
INTO V_PRPCINSURED LIMIT 1000;
FOR I_INDEX IN 1 .. V_PRPCINSURED.COUNT LOOP
BEGIN
V_PRPCINSUREDCODE := V_PRPCINSURED(I_INDEX);
V_POLICYINSURED(I_INDEX).ID := SEQ_S_VEHICLE_CONTACTS.NEXTVAL;
V_POLICYINSURED(I_INDEX).VEHICLEID := V_PRPCINSUREDCODE.V_VEHICLEID;
V_POLICYINSURED(I_INDEX).INSUREDTYPE := V_PRPCINSUREDCODE.V_INSUREDTYPE;
V_POLICYINSURED(I_INDEX).INSUREDCODE := V_PRPCINSUREDCODE.V_INSUREDCODE;
V_POLICYINSURED(I_INDEX).INSUREDNAME := V_PRPCINSUREDCODE.V_INSUREDNAME;
V_POLICYINSURED(I_INDEX).INSUREDADDRESS := V_PRPCINSUREDCODE.V_INSUREDADDRESS;
V_POLICYINSURED(I_INDEX).INSUREDNATURE := V_PRPCINSUREDCODE.V_INSUREDNATURE;
V_POLICYINSURED(I_INDEX).INSUREDFLAG := V_PRPCINSUREDCODE.V_INSUREDFLAG;
V_POLICYINSURED(I_INDEX).INSUREDIDENTITY := V_PRPCINSUREDCODE.V_INSUREDIDENTITY;
V_POLICYINSURED(I_INDEX).IDENTIFYTYPE := V_PRPCINSUREDCODE.V_IDENTIFYTYPE;
V_POLICYINSURED(I_INDEX).IDENTIFYNUMBER := V_PRPCINSUREDCODE.V_IDENTIFYNUMBER;
V_POLICYINSURED(I_INDEX).ACCOUNT := V_PRPCINSUREDCODE.V_ACCOUNT;
V_POLICYINSURED(I_INDEX).DEPARTUREDATE := V_PRPCINSUREDCODE.V_DEPARTUREDATE;
V_POLICYINSURED(I_INDEX).DEPARTUREHOUR := V_PRPCINSUREDCODE.V_DEPARTUREHOUR;
V_POLICYINSURED(I_INDEX).INSUREDSEX := '';
V_POLICYINSURED(I_INDEX).INSUREDBIRTHDAY := '';
V_POLICYINSURED(I_INDEX).IDENTIFYCOM := '';
V_POLICYINSURED(I_INDEX).BUSIPHASE := '2';
V_POLICYINSURED(I_INDEX).BUSINO := V_PRPCINSUREDCODE.V_BUSINO;
V_POLICYINSURED(I_INDEX).CONTACTSINDEX := '0';
V_POLICYINSURED(I_INDEX).PHONENUMBER := V_PRPCINSUREDCODE.V_PHONENUMBER;
V_POLICYINSURED(I_INDEX).MOBILE := V_PRPCINSUREDCODE.V_MOBILE;
V_POLICYINSURED(I_INDEX).SERIALNO := V_PRPCINSUREDCODE.V_SERIALNO;
--下發狀態為 D
IF V_PRPCINSUREDCODE.V_DATASTATE = 'D' THEN
DELETE FROM T_CMCS_S_VEHICLE_CONTACTS G
WHERE G.BUSINO = V_POLICYINSURED(I_INDEX).BUSINO
AND G.SERIALNO = V_PRPCINSUREDCODE.V_SERIALNO;
--下發狀態不為 D
ELSE
/*SELECT COUNT(1)
INTO I_NUMBER
FROM T_CMCS_S_VEHICLE_CONTACTS G
WHERE G.BUSINO = V_POLICYINSURED(I_INDEX).BUSINO
AND G.SERIALNO = V_PRPCINSUREDCODE.V_SERIALNO;*/
SELECT CASE WHEN EXISTS (SELECT 1 FROM T_CMCS_S_VEHICLE_CONTACTS A
WHERE A.BUSINO = V_POLICYINSURED(I_INDEX).BUSINO AND A.SERIALNO = V_PRPCINSUREDCODE.V_SERIALNO)
THEN 1 ELSE 0 END INTO I_NUMBER FROM DUAL;
--存盤區關系人表無該資料
IF I_NUMBER = 0 THEN
INSERT INTO T_CMCS_S_VEHICLE_CONTACTS
VALUES V_POLICYINSURED
(I_INDEX);
ELSE
UPDATE T_CMCS_S_VEHICLE_CONTACTS G
SET G.INSUREDTYPE = V_PRPCINSUREDCODE.V_INSUREDTYPE,
G.INSUREDCODE = V_PRPCINSUREDCODE.V_INSUREDCODE,
G.INSUREDNAME = V_PRPCINSUREDCODE.V_INSUREDNAME,
G.INSUREDADDRESS = V_PRPCINSUREDCODE.V_INSUREDADDRESS,
G.INSUREDNATURE = V_PRPCINSUREDCODE.V_INSUREDNATURE,
G.INSUREDFLAG = V_PRPCINSUREDCODE.V_INSUREDFLAG,
G.INSUREDIDENTITY = V_PRPCINSUREDCODE.V_INSUREDIDENTITY,
G.IDENTIFYTYPE = V_PRPCINSUREDCODE.V_IDENTIFYTYPE,
G.IDENTIFYNUMBER = V_PRPCINSUREDCODE.V_IDENTIFYNUMBER,
G.ACCOUNT = V_PRPCINSUREDCODE.V_ACCOUNT,
G.DEPARTUREDATE = V_PRPCINSUREDCODE.V_DEPARTUREDATE,
G.DEPARTUREHOUR = V_PRPCINSUREDCODE.V_DEPARTUREHOUR,
G.PHONENUMBER = V_PRPCINSUREDCODE.V_PHONENUMBER,
G.MOBILE = V_PRPCINSUREDCODE.V_MOBILE
WHERE G.BUSINO = V_PRPCINSUREDCODE.V_BUSINO
AND G.SERIALNO = V_PRPCINSUREDCODE.V_SERIALNO;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO T_CMCS_PRPCINSURED_ERR
(POLICYNO,
RISKCODE,
SERIALNO,
LANGUAGE,
INSUREDTYPE,
INSUREDCODE,
INSUREDNAME,
INSUREDADDRESS,
INSUREDNATURE,
INSUREDFLAG,
INSUREDIDENTITY,
RELATESERIALNO,
IDENTIFYTYPE,
IDENTIFYNUMBER,
CREDITLEVEL,
POSSESSNATURE,
BUSINESSSOURCE,
BUSINESSSORT,
OCCUPATIONCODE,
EDUCATIONCODE,
BANK,
ACCOUNTNAME,
ACCOUNT,
LINKERNAME,
POSTADDRESS,
POSTCODE,
PHONENUMBER,
MOBILE,
EMAIL,
BENEFITRATE,
BENEFITFLAG,
FLAG,
WORKTYPE,
REMARK,
APPLYQUANTITY,
ACCOUNTNATURE,
BANKAGENTNAME,
BANKBRANCHESCODE,
BANKBRANCHESNAME,
NATIONALITY,
ACCEPTSMFLAG,
TRAINNUMBER,
SEATNUMBER,
DEPARTURESTATION,
ARRIVALSTATION,
DEPARTUREDATE,
DEPARTUREHOUR,
TICKETNUMBER,
INSUREDVISASERIALNO,
INSUREDVERIFYNO,
IDENTIFYVALIDDATE,
CUSTNO,
MAJORFLAG,
MAJORLEVEL,
ISPUBLICKIND,
DISABILITYEVALSTD,
CLSLEVEL,
INTG_DATASOURCE,
INTG_DATASTATE,
INTG_U_CHANGEDATE,
INTG_ETL_SYSTIME,
HANDLETIME,
HANDLETYPE,
HANDLESTATUS,
ORGCODE)
SELECT POLICYNO,
RISKCODE,
SERIALNO,
LANGUAGE,
INSUREDTYPE,
INSUREDCODE,
INSUREDNAME,
INSUREDADDRESS,
INSUREDNATURE,
INSUREDFLAG,
INSUREDIDENTITY,
RELATESERIALNO,
IDENTIFYTYPE,
IDENTIFYNUMBER,
CREDITLEVEL,
POSSESSNATURE,
BUSINESSSOURCE,
BUSINESSSORT,
OCCUPATIONCODE,
EDUCATIONCODE,
BANK,
ACCOUNTNAME,
ACCOUNT,
LINKERNAME,
POSTADDRESS,
POSTCODE,
PHONENUMBER,
MOBILE,
EMAIL,
BENEFITRATE,
BENEFITFLAG,
FLAG,
WORKTYPE,
REMARK,
APPLYQUANTITY,
ACCOUNTNATURE,
BANKAGENTNAME,
BANKBRANCHESCODE,
BANKBRANCHESNAME,
NATIONALITY,
ACCEPTSMFLAG,
TRAINNUMBER,
SEATNUMBER,
DEPARTURESTATION,
ARRIVALSTATION,
DEPARTUREDATE,
DEPARTUREHOUR,
TICKETNUMBER,
INSUREDVISASERIALNO,
INSUREDVERIFYNO,
IDENTIFYVALIDDATE,
CUSTNO,
MAJORFLAG,
MAJORLEVEL,
ISPUBLICKIND,
DISABILITYEVALSTD,
CLSLEVEL,
INTG_DATASOURCE,
INTG_DATASTATE,
INTG_U_CHANGEDATE,
INTG_ETL_SYSTIME,
SYSDATE,
'',
'0',
ORGCODE
FROM ODSSEND.T_CMCS_PRPCINSURED T
WHERE T.POLICYNO = V_POLICYINSURED(I_INDEX).BUSINO
AND T.SERIALNO = V_PRPCINSUREDCODE.V_SERIALNO;
DELETE FROM ODSSEND.T_CMCS_PRPCINSURED T
WHERE T.POLICYNO = V_POLICYINSURED(I_INDEX).BUSINO
AND T.SERIALNO = V_PRPCINSUREDCODE.V_SERIALNO;
V_ERRMESSAGE := SUBSTR(SQLERRM, 1, 100); --回傳錯誤資訊
SP_DOLOG('P_I_POLICY-T_CMCS_S_VEHICLE_CONTACTS',
V_POLICYINSURED(I_INDEX)
.BUSINO || V_PRPCINSUREDCODE.V_SERIALNO,
V_ERRMESSAGE);
COMMIT;
END;
END LOOP;
COMMIT;
EXIT WHEN CURSOR_PRPCINSURED%NOTFOUND;
END LOOP;
CLOSE CURSOR_PRPCINSURED;
V_E_TIME := SYSDATE;
SP_DOHLOG('T_CMCS_PRPCINSURED', V_S_TIME, V_E_TIME, '1');
end;
uj5u.com熱心網友回復：
這個標量子查詢(SELECT T.VEHICLEID FROMT_CMCS_S_POLICY_CAR T
WHERE G.POLICYNO=T.POLICYNO) AS VEHICLEID,
改成表關聯查詢
uj5u.com熱心網友回復：
這個表量子查詢我改成left join了 但是效果沒多大uj5u.com熱心網友回復：
T_CMCS_S_POLICY_CAR 需要有POLICYNO欄位索引uj5u.com熱心網友回復：
這個索引是有的uj5u.com熱心網友回復：
把所有SQL都拿出來單獨執行看看哪個慢，再優化
uj5u.com熱心網友回復：
我嘗試過使用merge的方式來做，但是例外處理部分中要拿到錯誤資料，我無法實作額uj5u.com熱心網友回復：
我嘗試過使用merge的方式來做uj5u.com熱心網友回復：
看樓上uj5u.com熱心網友回復：
這是一整個回圈，要怎么拆出來單個執行額，我不是太懂唉
uj5u.com熱心網友回復：
這是一整個回圈，要怎么拆出來單個執行額，我不是太懂唉
uj5u.com熱心網友回復：
這個有啥不懂的，把SQL拉出來啊，條件里的引數隨便給個值看看執行效率啊
uj5u.com熱心網友回復：
昨天測驗了下各操作的效率問題：
資料量：22698條
插入：61.152s
更新：63.242s
洗掉：4.025s
uj5u.com熱心網友回復：
優化后的代碼如下：
-- MERGE關系人表
declare
V_S_TIME DATE;
V_E_TIME DATE;
V_ERRMESSAGE VARCHAR2(100);
I_NUMBER INTEGER;
TYPE T_I_PRPCINSURED IS REF CURSOR;
CURSOR_PRPCINSURED T_I_PRPCINSURED;
TYPE T_PRPCINSURED_RECORD IS RECORD(
V_BUSINO ODSSEND.T_CMCS_PRPCINSURED.POLICYNO%TYPE,
V_SERIALNO ODSSEND.T_CMCS_PRPCINSURED.SERIALNO%TYPE);
V_PRPCINSUREDCODE T_PRPCINSURED_RECORD;
TYPE T_PRPCINSURED IS TABLE OF T_PRPCINSURED_RECORD INDEX BY BINARY_INTEGER;
V_PRPCINSURED T_PRPCINSURED;
begin
-- Test statements here
V_S_TIME := SYSDATE;
--下發狀態為 D
OPEN CURSOR_PRPCINSURED FOR 'SELECT POLICYNO,SERIALNO FROM ODSSEND.T_CMCS_PRPCINSURED WHERE INTG_DATASTATE = ''D''';
LOOP
FETCH CURSOR_PRPCINSURED BULK COLLECT INTO V_PRPCINSURED LIMIT 1000;
FORALL I IN 1..V_PRPCINSURED.COUNT
DELETE FROM T_CMCS_S_VEHICLE_CONTACTS G
WHERE G.BUSINO = V_PRPCINSURED(I).V_BUSINO
AND G.SERIALNO = V_PRPCINSURED(I).V_SERIALNO;
EXIT WHEN CURSOR_PRPCINSURED%NOTFOUND;
COMMIT;
END LOOP;
CLOSE CURSOR_PRPCINSURED;
--下發狀態不為 D
MERGE INTO T_CMCS_S_VEHICLE_CONTACTS A
USING ( SELECT T.VEHICLEID,
G.INSUREDTYPE,
G.INSUREDCODE,
G.INSUREDNAME,
G.INSUREDADDRESS,
G.INSUREDNATURE,
G.INSUREDFLAG,
G.INSUREDIDENTITY,
G.IDENTIFYTYPE,
G.IDENTIFYNUMBER,
G.ACCOUNT,
G.DEPARTUREDATE,
G.DEPARTUREHOUR,
G.POLICYNO,
G.PHONENUMBER,
G.MOBILE,
G.INTG_DATASTATE,
G.SERIALNO
FROM ODSSEND.T_CMCS_PRPCINSURED G
LEFT JOIN T_CMCS_S_POLICY_CAR T
ON G.POLICYNO=T.POLICYNO
WHERE G.INTG_DATASTATE <> 'D') B
ON ( A.BUSINO = B.POLICYNO AND A.SERIALNO = B.SERIALNO)
WHEN MATCHED THEN
UPDATE SET A.INSUREDTYPE = B.INSUREDTYPE,
A.INSUREDCODE = B.INSUREDCODE,
A.INSUREDNAME = B.INSUREDNAME,
A.INSUREDADDRESS = B.INSUREDADDRESS,
A.INSUREDNATURE = B.INSUREDNATURE,
A.INSUREDFLAG = B.INSUREDFLAG,
A.INSUREDIDENTITY = B.INSUREDIDENTITY,
A.IDENTIFYTYPE = B.IDENTIFYTYPE,
A.IDENTIFYNUMBER = B.IDENTIFYNUMBER,
A.ACCOUNT = B.ACCOUNT,
A.DEPARTUREDATE = B.DEPARTUREDATE,
A.DEPARTUREHOUR = B.DEPARTUREHOUR,
A.PHONENUMBER = B.PHONENUMBER,
A.MOBILE = B.MOBILE
WHEN NOT MATCHED THEN
INSERT VALUES (
SEQ_S_VEHICLE_CONTACTS.NEXTVAL,
B.VEHICLEID,
B.INSUREDTYPE,
B.INSUREDCODE,
B.INSUREDNAME,
B.INSUREDADDRESS,
B.INSUREDNATURE,
B.INSUREDFLAG,
B.INSUREDIDENTITY,
B.IDENTIFYTYPE,
B.IDENTIFYNUMBER,
B.ACCOUNT,
B.DEPARTUREDATE,
B.DEPARTUREHOUR,
'',
'',
'',
'2',
B.POLICYNO,
'0',
B.PHONENUMBER,
B.MOBILE,
B.SERIALNO);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
V_ERRMESSAGE := SUBSTR(SQLERRM, 1, 100); --回傳錯誤資訊
SP_DOLOG('P_I_POLICY-T_CMCS_S_VEHICLE_CONTACTS','',V_ERRMESSAGE);
V_E_TIME := SYSDATE;
SP_DOHLOG('T_CMCS_PRPCINSURED', V_S_TIME, V_E_TIME, '1');
end;
優化后的執行效率很顯著
插入：0.827s
更新：0.718s
洗掉：3.027s
但是這樣做，原程序例外部分對例外資料的操作，在優化后的程序里我不會操作，各位大大有什么好的建議？
但是這樣做的話，一場部分
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/107691.html
標籤：開發
上一篇：Navicat激活