达梦数据库创建了视图，并且查询很快，然后用@view接收查询很慢

视图如下，具体字段省略了，然后通过数据库管理工具查询视图下的数据非常快，变更查询参数也很快。放到代码里用@View查询就很慢，数据表是千万级别。

/***Manager***/CREATE OR REPLACE VIEW "VIEW_LIST_OBJ"
AS
SELECT
--具体数据，省略
	jzBussPersonId,
	eventId,
	regionId
FROM
	(
		SELECT
		--具体的字段
			P .*,
			E .*
		FROM
			GOOD_HAB01 E,
			GOOD_HAB06 P
		WHERE
			E .BHAX0011 = P .BHAX0011
		AND P .BHAX0001 = E .BHAX0001
	) A,
	GOOD_HAB06 b,
	(
		SELECT
		--具体的字段，这几张表数据比较大，千万级别
			E . ID
		FROM
			CC_WORKITEM W,
			CC_TASKINSTANCE T,
			DB_EVENT E
		WHERE
			W.TASKINSTANCE_ID = T . ID 
		AND T .PROCESSINSTANCE_ID = E .PROCESSINSTANCE_ID 
		AND (W.STATE = 1 OR W.STATE = 0)
	) x
WHERE
	1 = 1
AND A .BIZ_EVENT_ID = x. ID (+)
AND A .eventId = b.bhax0001
and b.ahax0017='01' 
ORDER BY
	A .updateTime;

wx_iliujcepgaiqkpbsb1jr 1楼•30天前

Java代码里的查询打印脚本，通过这个脚本在数据库管理工具中查询很快。放到代码里就很慢

SELECT
        *
FROM
        (
                SELECT
                        T.*,
                        ROWNUM RN
                FROM
                        (
                                SELECT
                                        *
                                FROM
                                        VIEW_LIST_OBJ
                                WHERE
                                        1            =1
                                    AND regionId     ='310104003000'
                                    AND bizNo        ='b001'
                                    AND jzEventType  ='r003'
                                    AND eventStatus IN ('06', '03', '07', '08', '10', '11', '12', '13', '14', '15', '16', '17', '20', '21')
                                    AND actor_id     ='3'
                        )
                        T
                WHERE
                        ROWNUM <= 10
        )
WHERE
        RN > 0;

请先登陆