项目要分页查询科目的父科目,我用递归后,查询要等15秒以上,谁有好办法优化一下,或递归的替代办法,
这个实在太慢了,客户肯定受不了。
代码如下:SELECT * FROM
(SELECT aa.*,ROWNUM r FROM
( SELECT *
FROM ( select distinct a.id, a.ssegmentcode2 m_strAccount,
a.ssegmentname2 m_strName,a.nSubjectType m_lSubjectType,
a.nparentsubjectid nParentSubjectId,a.nBalanceDirection
from sett_glsubjectdefinition a
start with ssegmentcode2 in
( select c.sSubjectCode from Sett_GlEntry c
where c.nCurrencyID = 1
and c.nOfficeID = 1
and c.nStatusID > 0
and c.mAmount != 0
and c.dtExecute between to_date('2010-02-22','yyyy-MM-dd')
and to_date('2010-02-22','yyyy-MM-dd')
)
connect by prior nparentsubjectId = id
order by a.ssegmentcode2
)
)
aa ) WHERE r BETWEEN 1 AND 10
这个实在太慢了,客户肯定受不了。
代码如下:SELECT * FROM
(SELECT aa.*,ROWNUM r FROM
( SELECT *
FROM ( select distinct a.id, a.ssegmentcode2 m_strAccount,
a.ssegmentname2 m_strName,a.nSubjectType m_lSubjectType,
a.nparentsubjectid nParentSubjectId,a.nBalanceDirection
from sett_glsubjectdefinition a
start with ssegmentcode2 in
( select c.sSubjectCode from Sett_GlEntry c
where c.nCurrencyID = 1
and c.nOfficeID = 1
and c.nStatusID > 0
and c.mAmount != 0
and c.dtExecute between to_date('2010-02-22','yyyy-MM-dd')
and to_date('2010-02-22','yyyy-MM-dd')
)
connect by prior nparentsubjectId = id
order by a.ssegmentcode2
)
)
aa ) WHERE r BETWEEN 1 AND 10
connect by
这些语法我第一次看到,我只会使用非递归的查询,如果你可以简要说明下查询需求,我倒是可以重写一个不带递归的查询。
回复2楼:要求出父科目号,可能必须要用递归,其它方法还没听说过,请高手指教!
SELECT *
FROM ( select distinct a.id, a.ssegmentcode2 m_strAccount,
a.ssegmentname2 m_strName, a.nparentsubjectid nParentSubjectId from sett_glsubjectdefinition a
start with ssegmentcode2 in
( select c.sSubjectCode from Sett_GlEntry c
where c.mAmount != 0
and c.dtExecute between to_date('2010-02-22','yyyy-MM-dd')
and to_date('2010-02-22','yyyy-MM-dd')
)
connect by priornparentsubjectId = id
order by a.ssegmentcode2
)
FROM ( select distinct a.id, a.ssegmentcode2 m_strAccount,
a.ssegmentname2 m_strName, a.nparentsubjectid nParentSubjectId from sett_glsubjectdefinition a
start with ssegmentcode2 in
( select c.sSubjectCode from Sett_GlEntry c
where c.mAmount != 0
and c.dtExecute between to_date('2010-02-22','yyyy-MM-dd')
and to_date('2010-02-22','yyyy-MM-dd')
)
connect by priornparentsubjectId = id
order by a.ssegmentcode2
)
红色这段慢?
order by a.ssegmentcode2 排序也耗时间..
你是原始数据和现在只想结果贴出来看看那