This function performs detected results is slow, hope god help solve:
The 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
- the fees specified amount
V_avg_bsp_amt number;
V_avg_b2b_amt number;
V_avg_b2c_amt number;
- par lapse
V_pmzj_bsp_percent number;
V_pmzj_b2b_percent number;
- press Y lapse
V_aYzj_bsp_amt number;
V_aYzj_b2b_amt number;
The BEGIN
FOR ALLROUTE IN (SELECT DISTINCT (F.O RI_CODE | | '-' | | F.D est_Code) ROUTESD, F.O RI_CODE, F.D est_Code
The FROM AIRFARE. MV_FAREBYRULE_REPORT_DATA F
WHERE 1=1
And ((F.F IRST_TRAVEL_DATE & lt;=
TO_DATE (FIRSTTRAVALTIME, '- dd yyyy - mm) AND
F.L AST_TRAVEL_DATE & gt;=
TO_DATE (FIRSTTRAVALTIME, 'yyyy - mm - dd)) OR
(F.F IRST_TRAVEL_DATE & lt;=
TO_DATE (LASTTRAVALTIME, '- dd yyyy - mm) AND
F.L AST_TRAVEL_DATE & gt;=
TO_DATE (LASTTRAVALTIME, 'yyyy - mm - dd)) OR
(F.F IRST_TRAVEL_DATE & lt;=
TO_DATE (FIRSTTRAVALTIME, '- dd yyyy - mm) AND
F.L AST_TRAVEL_DATE & gt;=
TO_DATE (LASTTRAVALTIME, 'yyyy - mm - dd)) OR
(F.F IRST_TRAVEL_DATE & gt;=
TO_DATE (FIRSTTRAVALTIME, '- dd yyyy - mm) AND
F.L AST_TRAVEL_DATE & lt;=
TO_DATE (LASTTRAVALTIME, 'yyyy - mm - dd)))) LOOP


FOR ALLBERTH IN (select DISTINCT REGEXP_SUBSTR (F_ChaiFen (f.r olecode), '[^,] +', 1, l) booking_class
The from (select booking_class rolecode, FIRST_TRAVEL_DATE LAST_TRAVEL_DATE from MV_FAREBYRULE_REPORT_DATA) f,
(SELECT LEVEL l FROM DUAL CONNECT BY LEVELWHERE 1=1
And l & lt;=LENGTH (F_ChaiFen (f.r olecode)) - LENGTH (the REPLACE (rolecode, ', ')) + 1
And ((F.F IRST_TRAVEL_DATE & lt;=
TO_DATE (FIRSTTRAVALTIME, '- dd yyyy - mm) AND
F.L AST_TRAVEL_DATE & gt;=
TO_DATE (FIRSTTRAVALTIME, 'yyyy - mm - dd)) OR
(F.F IRST_TRAVEL_DATE & lt;=
TO_DATE (LASTTRAVALTIME, '- dd yyyy - mm) AND
F.L AST_TRAVEL_DATE & gt;=
TO_DATE (LASTTRAVALTIME, 'yyyy - mm - dd)) OR
(F.F IRST_TRAVEL_DATE & lt;=
TO_DATE (FIRSTTRAVALTIME, '- dd yyyy - mm) AND
F.L AST_TRAVEL_DATE & gt;=
TO_DATE (LASTTRAVALTIME, 'yyyy - mm - dd)) OR
(F.F IRST_TRAVEL_DATE & gt;=
TO_DATE (FIRSTTRAVALTIME, '- dd yyyy - mm) AND
F.L AST_TRAVEL_DATE & lt;=
TO_DATE (LASTTRAVALTIME, 'yyyy - mm - dd)))
) LOOP

- BSP par lapse rate
SELECT ROUND (AVG (f.R STF_PERCENT), 0) AS DISCOUNT
INTO v_pmzj_bsp_percent
The FROM AIRFARE. MV_FAREBYRULE_REPORT_DATA f
WHERE f.o ri_code=ALLROUTE. ORI_CODE
AND f.d est_code=ALLROUTE. Dest_Code
AND f.R stf_Caculate_Type=0
And ((F.F IRST_TRAVEL_DATE & lt;=
TO_DATE (FIRSTTRAVALTIME, '- dd yyyy - mm) AND
F.L AST_TRAVEL_DATE & gt;=
TO_DATE (FIRSTTRAVALTIME, 'yyyy - mm - dd)) OR
(F.F IRST_TRAVEL_DATE & lt;=
TO_DATE (LASTTRAVALTIME, '- dd yyyy - mm) AND
F.L AST_TRAVEL_DATE & gt;=
TO_DATE (LASTTRAVALTIME, 'yyyy - mm - dd)) OR
(F.F IRST_TRAVEL_DATE & lt;=
TO_DATE (FIRSTTRAVALTIME, '- dd yyyy - mm) AND
F.L AST_TRAVEL_DATE & gt;=
TO_DATE (LASTTRAVALTIME, 'yyyy - mm - dd)) OR
(F.F IRST_TRAVEL_DATE & gt;=
TO_DATE (FIRSTTRAVALTIME, '- dd yyyy - mm) AND
F.L AST_TRAVEL_DATE & lt;=
TO_DATE (LASTTRAVALTIME, 'yyyy - mm - dd)))
AND f.b spgroup IS NOT NULL
And instr (f.b ooking_class, ALLBERTH BOOKING_CLASS) & gt; 0;
Pressing Y - BSP lapse
SELECT ROUND (AVG (f.R STF_PERCENT), 0) AS DISCOUNT
INTO v_aYzj_bsp_amt
The FROM AIRFARE. MV_FAREBYRULE_REPORT_DATA f
WHERE f.o ri_code=ALLROUTE. ORI_CODE
AND f.d est_code=ALLROUTE. Dest_Code
AND f.R stf_Caculate_Type=4
And ((F.F IRST_TRAVEL_DATE & lt;=
TO_DATE (FIRSTTRAVALTIME, '- dd yyyy - mm) AND
F.L AST_TRAVEL_DATE & gt;=
TO_DATE (FIRSTTRAVALTIME, 'yyyy - mm - dd)) OR
(F.F IRST_TRAVEL_DATE & lt;=
TO_DATE (LASTTRAVALTIME, '- dd yyyy - mm) AND
F.L AST_TRAVEL_DATE & gt;=
TO_DATE (LASTTRAVALTIME, 'yyyy - mm - dd)) OR
(F.F IRST_TRAVEL_DATE & lt;=
TO_DATE (FIRSTTRAVALTIME, '- dd yyyy - mm) AND
F.L AST_TRAVEL_DATE & gt;=
TO_DATE (LASTTRAVALTIME, 'yyyy - mm - dd)) OR
(F.F IRST_TRAVEL_DATE & gt;=
TO_DATE (FIRSTTRAVALTIME, '- dd yyyy - mm) AND
F.L AST_TRAVEL_DATE & lt;=
TO_DATE (LASTTRAVALTIME, 'yyyy - mm - dd)))
AND f.b spgroup IS NOT NULL
AND instr (f.b ooking_class, ALLBERTH BOOKING_CLASS) & gt; 0;
- b2b par lapse rate
SELECT ROUND (AVG (f.R STF_PERCENT), 0) AS DISCOUNT
INTO v_pmzj_b2b_percent
The FROM AIRFARE. MV_FAREBYRULE_REPORT_DATA f
WHERE f.o ri_code=ALLROUTE. ORI_CODE
AND f.d est_code=ALLROUTE. Dest_Code
AND f.R stf_Caculate_Type=0
And ((F.F IRST_TRAVEL_DATE & lt;=
TO_DATE (FIRSTTRAVALTIME, '- dd yyyy - mm) AND
F.L AST_TRAVEL_DATE & gt;=
TO_DATE (FIRSTTRAVALTIME, 'yyyy - mm - dd)) OR
(F.F IRST_TRAVEL_DATE & lt;=
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull