try:
-------------------------------------------------------------------------------
SELECT count(*)
FROM group grp,
(SELECT *
FROM custom_hist a
WHERE exists (
SELECT 1
FROM custom_hist c
WHERE c.start_num <= '20050131' || '235959999'
AND c.end_num >= '20050131' || '235959000'
AND c.stop_stat<> '4'
AND a.person_cd = c.person_cd
AND a.danmn_start_num = c.danmn_start_num)) custom,
(SELECT *
FROM custom_Address_hist a
WHERE exists(
SELECT 1
FROM custom_Address_hist c
WHERE c.start_num <= '20050131' || '235959999'
AND c.end_num >= '20050131' || '235959000'
AND c.adrClassify = '1'
AND a.person_cd = c.person_cd
AND a.addr_set_classfiy = c.addr_set_classfiy
AND a.danmn_start_num = c.danmn_start_num)) customAddr
WHERE grp.grpCd = custom.grpCd
AND grp.start_ymd <= '20050131'
AND grp.end_ymd >= '20050131'
AND custom.person_cd = customAddr.person_cd(+)
AND customAddr.adrClassify(+) = '1'
AND custom.stop_stat<> '4'
-------------------------------------------------------------------------------
SELECT count(*)
FROM group grp,
(SELECT *
FROM custom_hist a
WHERE exists (
SELECT 1
FROM custom_hist c
WHERE c.start_num <= '20050131' || '235959999'
AND c.end_num >= '20050131' || '235959000'
AND c.stop_stat<> '4'
AND a.person_cd = c.person_cd
AND a.danmn_start_num = c.danmn_start_num)) custom,
(SELECT *
FROM custom_Address_hist a
WHERE exists(
SELECT 1
FROM custom_Address_hist c
WHERE c.start_num <= '20050131' || '235959999'
AND c.end_num >= '20050131' || '235959000'
AND c.adrClassify = '1'
AND a.person_cd = c.person_cd
AND a.addr_set_classfiy = c.addr_set_classfiy
AND a.danmn_start_num = c.danmn_start_num)) customAddr
WHERE grp.grpCd = custom.grpCd
AND grp.start_ymd <= '20050131'
AND grp.end_ymd >= '20050131'
AND custom.person_cd = customAddr.person_cd(+)
AND customAddr.adrClassify(+) = '1'
AND custom.stop_stat<> '4'
解决方案 »
- 如何合并列
- 求助,win2003serv+ora9i2,agent服务不能启动,没改过计算机名
- 求一SQL,二级序号的问题
- SQL语句写法(有难度)
- 我想把两个表的查询结果通过一个共同的时间字段连接在一起,请问怎么做?
- 请教,怎么去掉数据中的换行符?
- 在Linux9下总算把Oracle9i软件给装好了,又出现创建数据库问题(不要小瞧噢)?
- 如何查找一个用户所使用的表空间
- 我建的DBLINK为什么总是报”ORA-12154: TNS: 无法处理服务名“的错呢?代码如下:
- 在其他的机子上能够安装oracle,但在我的笔记本按同样方法去无法安装,每次到64%时就抱错,那位大虾帮我解决一下吧
- 没有用过触发器!
- 监听器错误:没有监听器!
看看执行计划
如 select * from ...
where id=(select max(id) from .. where .... )
最多可能只需10秒.
from xgroup grp
, (select *
from custom_hist a
where start_num <= '20050131' || '235959999'
and end_num >= '20050131' || '235959000'
and stop_stat <> 4
and custom.danmn_start_num in (
select max (danmn_start_num)
from custom_hist
where person_cd = a.person_cd
and start_num <= '20050131' || '235959999'
and end_num >= '20050131' || '235959000'
and stop_stat <> 4)) custom
, (select *
from custom_address_hist a
where start_num <= '20050131' || '235959999'
and end_num >= '20050131' || '235959000'
and adrclassify = '1'
and danmn_start_num in (
select max (damnm_start_num)
from custom_address_hist
where person_cd = a.person_cd
and addr_set_classfiy = a.addr_set_classfiy
and start_num <= '20050131' || '235959999'
and end_num >= '20050131' || '235959000'
and adrclassify = '1')) customaddr
where grp.grpcd = custom.grpcd
and grp.start_ymd <= '20050131'
and grp.end_ymd >= '20050131'
and custom.person_cd = customaddr.person_cd(+)
and customaddr.adrclassify(+) = '1';