视图查询速度很慢

[progsky]

视图如下“
CREATE OR REPLACE VIEW V_FEE_RECEIPT AS
SELECT RECEIPT.RECEIPTINDEXID, --实收款主键
RECEIPT.Receiptflownumber, --实收款编号
RECEIPT.CHECKNO, --支票号
RECEIPT.CASHTOTAL, --现金金额
FEs.f_Receiptbookingno(RECEIPT.RECEIPTINDEXID) AS RECEIPTEXESMEMO--提单号
FROM T_FEE_RECEIPT RECEIPT
LEFT JOIN T_FEE_INVOICE INVOICE ON
INVOICE.INVOICEINDEXID = RECEIPT.INVOICEINDEXID
函数如下：
CREATE OR REPLACE FUNCTION F_RECEIPTBOOKINGNO(L_RECEIPTID IN VARCHAR2)
RETURN VARCHAR2 AS
RESULTVALUE VARCHAR2(5000);
i INTEGER;
BEGIN
DECLARE
L_TEMPRETURNVALUE NVARCHAR2(200);
begin
RESULTVALUE := '';
L_TEMPRETURNVALUE := L_RECEIPTID;
FOR MAIN_CURSOR IN (SELECT ER.Bookingno
FROM T_FEE_EXESRECEIPT ER
WHERE ER.RECEIPTID = L_TEMPRETURNVALUE
) LOOP
IF MAIN_CURSOR.Bookingno IS NOT NULL THEN
IF RESULTVALUE IS NULL THEN
RESULTVALUE := RESULTVALUE || MAIN_CURSOR.Bookingno;
ELSE
SELECT INSTR(RESULTVALUE, MAIN_CURSOR.Bookingno) INTO i FROM DUAL;
IF i = 0 THEN
RESULTVALUE := RESULTVALUE || '|' || MAIN_CURSOR.Bookingno ;
END IF;
END IF;
END IF;
END LOOP;
END;
RETURN(RESULTVALUE);
END F_RECEIPTBOOKINGNO;
函数本身已测试，查询很快。我如果根据视图里的RECEIPTEXESMEMO字段查询很慢。不怎么太懂oracle，请高手指导！

[meta1983]

RECEIPTEXESMEMO 是通过调用存储过程FEs.f_Receiptbookingno(RECEIPT.RECEIPTINDEXID)得出来的，导致全表扫描,而存储过程本身也比较复杂，导致全表扫描速度慢

[gelyon]

我也在寻求原因，我前几天遇到类似的情况，
视图是基于4个基表联合查询创建的
通过视图查询，不同的公司，执行效率差别非常之大！查询条件都是一摸一样的

后来我追查的初步原因是：基表统计信息过旧

处理：删除基表的统计信息，重新收集
analyze table A delete statistics;

analyze table A compute statistics ;

楼主你可以试试。

[progsky]

1楼的太厉害了！谢谢二楼的，我试一下！