--几张表的数量如下:
--代码表:63条
select count(*) from etranscodeinfo; --机构表:4887条
select count(*) from bankdept;--账号表:75712 条
select count(*) from eaccount;--客户表:67363 条
select count(*) from ebankcif;--交易表:7280737 条
select count(*) from ejnl;select t2.deptseq,count(*) as accamount from
(
select acseq,ea.deptseq from eaccount ea,
(
select t1.cifseq from ebankcif t1
where t1.edition='1' and not exists(select 1 from ebankcif where t1.cifseq=cifseq and opendate>t1.opendate)
) eb
where ea.cifseq=eb.cifseq
and ea.authflag !=1 and
(
(ea.acstate='0' and ea.opendate <to_date(#nextEndDate#,'YYYY-MM-DD') )
or
(ea.acstate='1' and ea.closedate>=to_date(#startDate#,'YYYY-MM-DD') and ea.closedate < to_date(#nextEndDate#,'YYYY-MM-DD'))
)
and ea.deptseq like #likeDeptSeq#||'%'
) t2
where not exists
(
select 1 from ejnl ej,etranscodeinfo et
where ej.transcode=et.transcode and host='01' and acctype='0' and ej.acseq is not null and
ej.transdate>=to_date(#startDate#,'yyyy-mm-dd') and ej.transdate<=to_date(#endDate#,'yyyy-mm-dd')
and ej.acseq=t2.acseq
) group by t2.deptseq别名t2是上半部分,查询不存在ejnl交易表中的记录。如何优化。
--代码表:63条
select count(*) from etranscodeinfo; --机构表:4887条
select count(*) from bankdept;--账号表:75712 条
select count(*) from eaccount;--客户表:67363 条
select count(*) from ebankcif;--交易表:7280737 条
select count(*) from ejnl;select t2.deptseq,count(*) as accamount from
(
select acseq,ea.deptseq from eaccount ea,
(
select t1.cifseq from ebankcif t1
where t1.edition='1' and not exists(select 1 from ebankcif where t1.cifseq=cifseq and opendate>t1.opendate)
) eb
where ea.cifseq=eb.cifseq
and ea.authflag !=1 and
(
(ea.acstate='0' and ea.opendate <to_date(#nextEndDate#,'YYYY-MM-DD') )
or
(ea.acstate='1' and ea.closedate>=to_date(#startDate#,'YYYY-MM-DD') and ea.closedate < to_date(#nextEndDate#,'YYYY-MM-DD'))
)
and ea.deptseq like #likeDeptSeq#||'%'
) t2
where not exists
(
select 1 from ejnl ej,etranscodeinfo et
where ej.transcode=et.transcode and host='01' and acctype='0' and ej.acseq is not null and
ej.transdate>=to_date(#startDate#,'yyyy-mm-dd') and ej.transdate<=to_date(#endDate#,'yyyy-mm-dd')
and ej.acseq=t2.acseq
) group by t2.deptseq别名t2是上半部分,查询不存在ejnl交易表中的记录。如何优化。
解决方案 »
- oracle 数据统计的问题
- number类型的数据是不是可以插入到char或varchar2类型的字段中
- sql优化的问题:(not)exists 比 (not)in 好吗?
- 向oracle中一次插入多条记录
- 大侠们,怎样给oracle存储过程传递数组参数啊,
- oracle9i居然使用的两个监听端口???
- 谁帮我解答一下
- Oracle用户请注意了,好消息要与大家一同分享!千万不要错过!
- 如何将自增字段(序列流)设为主关键字?
- asp中设置连接Oracle数据库时,遇到这样的情况,麻烦大家看一下:
- 求救两台可以互相ping通的机器 一台机器无法登陆另一台机器的oracle
- 请Proc 下SQL 语句能用 oracle 函数ROW_NUMBER()和 rank()吗
你好,这个问题你解决了吗?
http://bbs.csdn.net/topics/310009185#new_post
ea.closedate
ea.deptseq
ebankcif.cifseq
ej.acseq
et.transcode这些字段若有索引,且xxxseq列重复率低的话,应不致太慢。