我有一个这样的执行计划
inf_acct inf_subscriber_all inf_acct_relation
这三个表中的数据有几百万麻烦各位牛人帮忙看看哪里需要改进!!!update dc_acct_ccg
set (group_id, update_date) = (select c2, sysdate
from (select distinct t2.acct_id c1, 1 c2
from inf_acct t2,
inf_subscriber_all t3,
inf_acct_relation t8
where (t2.acct_type = '1' and
t3.sub_state <> 'B02')
and t2.acct_id = t8.acct_id
and '*' = t8.fee_item_code
and 0 = t8.status
and t3.sub_id = t8.sub_id
and t2.acct_id > ?
and t2.acct_id <= ?)
where dc_acct_ccg.acct_id = c1)
where exists (select 'x'
from (select distinct t2.acct_id c1, 1 c2
from inf_acct t2,
inf_subscriber_all t3,
inf_acct_relation t8
where (t2.acct_type = '1' and t3.sub_state <> 'B02')
and t2.acct_id = t8.acct_id
and '*' = t8.fee_item_code
and 0 = t8.status
and t3.sub_id = t8.sub_id
and t2.acct_id > ?
and t2.acct_id <= ?)
where dc_acct_ccg.acct_id = c1
and group_id <> c2)
and acct_id > ?
and acct_id <= ?;
inf_acct inf_subscriber_all inf_acct_relation
这三个表中的数据有几百万麻烦各位牛人帮忙看看哪里需要改进!!!update dc_acct_ccg
set (group_id, update_date) = (select c2, sysdate
from (select distinct t2.acct_id c1, 1 c2
from inf_acct t2,
inf_subscriber_all t3,
inf_acct_relation t8
where (t2.acct_type = '1' and
t3.sub_state <> 'B02')
and t2.acct_id = t8.acct_id
and '*' = t8.fee_item_code
and 0 = t8.status
and t3.sub_id = t8.sub_id
and t2.acct_id > ?
and t2.acct_id <= ?)
where dc_acct_ccg.acct_id = c1)
where exists (select 'x'
from (select distinct t2.acct_id c1, 1 c2
from inf_acct t2,
inf_subscriber_all t3,
inf_acct_relation t8
where (t2.acct_type = '1' and t3.sub_state <> 'B02')
and t2.acct_id = t8.acct_id
and '*' = t8.fee_item_code
and 0 = t8.status
and t3.sub_id = t8.sub_id
and t2.acct_id > ?
and t2.acct_id <= ?)
where dc_acct_ccg.acct_id = c1
and group_id <> c2)
and acct_id > ?
and acct_id <= ?;
SET (group_id, update_date) = (1, SYSDATE)
WHERE EXISTS (SELECT 1
FROM inf_acct t2,
inf_subscriber_all t3,
inf_acct_relation t8
WHERE t2.acct_id = t8.acct_id
AND t3.sub_id = t8.sub_id
AND dc_acct_ccg.acct_id = t2.acct_id
AND t2.acct_id > ?
AND t2.acct_id <= ?
AND group_id <> 1
AND t2.acct_type = '1' AND t3.sub_state <> 'B02'
AND '*' = t8.fee_item_code
AND 0 = t8.status);
支持,用exists 替换distinct
UPDATE DC_ACCT_CCG
SET (GROUP_ID, UPDATE_DATE) =
(1, SYSDATE)
WHERE EXISTS
(SELECT 'x'
FROM INF_ACCT T2, INF_SUBSCRIBER_ALL T3, INF_ACCT_RELATION T8
WHERE (T2.ACCT_TYPE = '1' AND T3.SUB_STATE <> 'B02')
AND T2.ACCT_ID = T8.ACCT_ID
AND '*' = T8.FEE_ITEM_CODE
AND 0 = T8.STATUS
AND T3.SUB_ID = T8.SUB_ID
AND T2.ACCT_ID > ?
AND T2.ACCT_ID <= ?
AND DC_ACCT_CCG.ACCT_ID = C1
AND GROUP_ID <> 1)
AND ACCT_ID > ?
AND ACCT_ID <= ?;
Database access error. The SQL statement is null. The error message is java.sql.SQLException: addSync: caught Exception.util.Toolkit.getGeneryException(Toolkit.java(Compiled Code))
util.db.ConnectionMgr.getConnection(ConnectionMgr.java(Compiled Code))
util.Toolkit.getConnection(Toolkit.java(Compiled Code))
util.formula.SliceMgr.initSliceCalcMarks(SliceMgr.java(Compiled Code))
util.formula.SliceMgr.instance(SliceMgr.java(Inlined Compiled Code))
util.formula.SysCalcParamStatement.executeNext(SysCalcParamStatement.java(Compiled Code))
util.formula.SysParamCalcMgr.calcParam(SysParamCalcMgr.java(Compiled Code))
util.formula.SysParamCalcMgr.calcParam(SysParamCalcMgr.java(Inlined Compiled Code))
util.formula.SysParamCalcMgr.calcParam(SysParamCalcMgr.java(Compiled Code))
util.formula.SysParamCalcMgr.eventTriggerCalcParam(SysParamCalcMgr.java(Compiled Code))
util.formula.SysParamCalcMgr.eventTriggerCalcParam(SysParamCalcMgr.java(Inlined Compiled Code))