這個函式執行查出的結果很慢，希望大神幫忙解決：
CREATE OR REPLACE FUNCTION FN_GETFAREBURULEUNITDATA(STARTPLACE VARCHAR2,
ENDPLACE VARCHAR2,
FIRSTTRAVALTIME VARCHAR2,
LASTTRAVALTIME VARCHAR2,
BERTH VARCHAR2,
FLIGHTNO VARCHAR2,
DATASTATUS NUMBER)
RETURN v_table_public
PIPELINED is
v_table_bsp v_table_public := v_table_public();
--FORCE
--指定金額代理費
v_avg_bsp_amt number;
v_avg_b2b_amt number;
v_avg_b2c_amt number;
--票面直減
v_pmzj_bsp_percent number;
v_pmzj_b2b_percent number;
--按Y直減
v_aYzj_bsp_amt number;
v_aYzj_b2b_amt number;
BEGIN
FOR ALLROUTE IN (SELECT DISTINCT (F.ORI_CODE || '-' || F.Dest_Code) ROUTESD, F.ORI_CODE, F.Dest_Code
FROM AIRFARE.MV_FAREBYRULE_REPORT_DATA F
WHERE 1=1
and ((F.FIRST_TRAVEL_DATE <=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE >=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd')) OR
(F.FIRST_TRAVEL_DATE <=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE >=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd')) OR
(F.FIRST_TRAVEL_DATE <=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE >=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd')) OR
(F.FIRST_TRAVEL_DATE >=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE <=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd')))) LOOP
FOR ALLBERTH IN (select DISTINCT REGEXP_SUBSTR(F_ChaiFen(f.rolecode) ,'[^,]+',1,l) booking_class
from (select booking_class rolecode,FIRST_TRAVEL_DATE,LAST_TRAVEL_DATE from MV_FAREBYRULE_REPORT_DATA) f,
(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100) b
WHERE 1=1
and l <=LENGTH(F_ChaiFen(f.rolecode)) - LENGTH(REPLACE(rolecode,','))+1
and ((F.FIRST_TRAVEL_DATE <=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE >=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd')) OR
(F.FIRST_TRAVEL_DATE <=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE >=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd')) OR
(F.FIRST_TRAVEL_DATE <=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE >=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd')) OR
(F.FIRST_TRAVEL_DATE >=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE <=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd')))
) LOOP
--BSP票面直減比例
SELECT ROUND(AVG(f.RSTF_PERCENT), 0) AS DISCOUNT
INTO v_pmzj_bsp_percent
FROM AIRFARE.MV_FAREBYRULE_REPORT_DATA f
WHERE f.ori_code = ALLROUTE.ORI_CODE
AND f.dest_code = ALLROUTE.Dest_Code
AND f.Rstf_Caculate_Type = 0
and ((F.FIRST_TRAVEL_DATE <=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE >=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd')) OR
(F.FIRST_TRAVEL_DATE <=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE >=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd')) OR
(F.FIRST_TRAVEL_DATE <=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE >=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd')) OR
(F.FIRST_TRAVEL_DATE >=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE <=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd')))
AND f.bspgroup IS NOT NULL
and instr(f.booking_class, ALLBERTH.BOOKING_CLASS) > 0;
--BSP按Y直減
SELECT ROUND(AVG(f.RSTF_PERCENT), 0) AS DISCOUNT
INTO v_aYzj_bsp_amt
FROM AIRFARE.MV_FAREBYRULE_REPORT_DATA f
WHERE f.ori_code = ALLROUTE.ORI_CODE
AND f.dest_code = ALLROUTE.Dest_Code
AND f.Rstf_Caculate_Type = 4
and ((F.FIRST_TRAVEL_DATE <=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE >=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd')) OR
(F.FIRST_TRAVEL_DATE <=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE >=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd')) OR
(F.FIRST_TRAVEL_DATE <=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE >=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd')) OR
(F.FIRST_TRAVEL_DATE >=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE <=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd')))
AND f.bspgroup IS NOT NULL
AND instr(f.booking_class, ALLBERTH.BOOKING_CLASS) > 0;
--b2b票面直減比例
SELECT ROUND(AVG(f.RSTF_PERCENT), 0) AS DISCOUNT
INTO v_pmzj_b2b_percent
FROM AIRFARE.MV_FAREBYRULE_REPORT_DATA f
WHERE f.ori_code = ALLROUTE.ORI_CODE
AND f.dest_code = ALLROUTE.Dest_Code
AND f.Rstf_Caculate_Type = 0
and ((F.FIRST_TRAVEL_DATE <=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE >=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd')) OR
(F.FIRST_TRAVEL_DATE <=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE >=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd')) OR
(F.FIRST_TRAVEL_DATE <=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE >=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd')) OR
(F.FIRST_TRAVEL_DATE >=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE <=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd')))
AND f.B2bgroup IS NOT NULL
AND instr(f.booking_class, ALLBERTH.BOOKING_CLASS) > 0;
--b2b按Y直減
SELECT ROUND(AVG(f.RSTF_PERCENT), 0) AS DISCOUNT
INTO v_aYzj_b2b_amt
FROM AIRFARE.MV_FAREBYRULE_REPORT_DATA f
WHERE f.ori_code = ALLROUTE.ORI_CODE
AND f.dest_code = ALLROUTE.Dest_Code
AND f.Rstf_Caculate_Type = 4
and ((F.FIRST_TRAVEL_DATE <=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE >=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd')) OR
(F.FIRST_TRAVEL_DATE <=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE >=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd')) OR
(F.FIRST_TRAVEL_DATE <=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE >=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd')) OR
(F.FIRST_TRAVEL_DATE >=
TO_DATE(FIRSTTRAVALTIME, 'yyyy-mm-dd') AND
F.LAST_TRAVEL_DATE <=
TO_DATE(LASTTRAVALTIME, 'yyyy-mm-dd')))
AND f.B2bgroup IS NOT NULL
AND instr(f.booking_class, ALLBERTH.BOOKING_CLASS) > 0;
IF (v_avg_bsp_amt IS NOT NULL) OR (v_avg_b2b_amt IS NOT NULL) OR
(v_avg_b2c_amt IS NOT NULL) or
(v_pmzj_bsp_percent IS NOT NULL) OR (v_aYzj_bsp_amt IS NOT NULL) or
(v_pmzj_b2b_percent IS NOT NULL) OR (v_aYzj_b2b_amt IS NOT NULL) THEN
PIPE ROW(table_tabletype_unit_public(ALLROUTE.ORI_CODE,
ALLROUTE.Dest_Code,
ALLBERTH.BOOKING_CLASS,
v_avg_bsp_amt,
v_avg_b2b_amt,
v_avg_b2c_amt,
v_pmzj_bsp_percent,
v_pmzj_b2b_percent,
v_aYzj_bsp_amt,
v_aYzj_b2b_amt));
END IF;
END LOOP;
END LOOP;
end;
uj5u.com熱心網友回復：
沒有你的業務規則，沒法優化。你可以嘗試一點點的除錯，全部注釋，一點一點的放開，看到哪個環節速度明顯慢了，再有針對性的提問
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/83821.html
標籤：高級技術
上一篇：oracle的備份檔案 dmp怎么在不經過oracle的情況下匯入到sqlserver
下一篇：linux下oracle監聽