这个语句该怎么改

显示全部楼层 · 2005-10-30 17:05:33

select a.*,b.*
from letter a,sign b
where a.letter_id=b.letter_id(+)
AND b.signinfo_id IN (SELECT max(signinfo_id) FROM sign GROUP BY letter_id)
AND history_flag = '0'
AND a.add_type = '分户'
AND a.state_now = '正常'
ORDER BY a.letter_id DESC
这个语句运行较慢,想通过将 IN 改为 exists 提高效率 ,但是我在子查询中使用了max(signinfo_id) 聚集函数,不知道怎么使用exists了,帮我看看该如何改

千问 · 2005-10-30 17:05:33

select a.*,b.*
from letter a,sign b
where a.letter_id=b.letter_id(+)
AND exists (SELECT 'x' from (selectmax(signinfo_id)signinfo_idFROM sign GROUP BY letter_id)
whereb.signinfo_id = signinfo_id )
AND history_flag = '0'
AND a.add_type = '分户'
AND a.state_now = '正常'

千问 · 2005-10-30 17:05:33

我在执行时速度很慢,看计划是这句 select max(signinfo_id) signinfo_id FROM sign GROUP BY letter_id 是全表扫描呢
已用时间:00: 00: 07.02
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE
10 FILTER
21 NESTED LOOPS (OUTER)
32 TABLE ACCESS (BY INDEX ROWID) OF 'LETTER'
43 INDEX (FULL SCAN DESCENDING) OF 'PK_LETTER' (UNIQUE)
52 TABLE ACCESS (BY INDEX ROWID) OF 'SIGN'
65 INDEX (RANGE SCAN) OF 'RELATIONSHIP_7_FK' (NON-UNIQU

E)
71 FILTER
87 SORT (GROUP BY)
98 TABLE ACCESS (FULL) OF 'SIGN'
Statistics
----------------------------------------------------

0recursive calls
26db block gets
7473consistent gets
5095physical reads

0redo size
2916bytes sent via SQL*Net to client
503bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

3sorts (disk)

1rows processed
我在letter表上有索引阿

INDEX_NAME
TABLE_NAME
COLUMN_NAME
------------------------------ ------------------------------ ----------------
RELATIONSHIP_7_FK
SIGN
LETTER_ID
两个表都是十几万记录.

千问 · 2005-10-30 17:05:33

select max(signinfo_id) signinfo_id FROM sign GROUP BY letter_id
不能加GROUP BY letter_id ！！

你的SQL语句不用改成exists，因为in里面没有用到过滤语句，
另外， a.letter_id和b.letter_id要建立索引增加连接速度

千问 · 2005-10-30 17:05:33

select a.*,b.*
from letter a,sign b
where a.letter_id=b.letter_id(+)
AND b.signinfo_id=(SELECT max(c.signinfo_id) FROM sign c where c.letter_id=b.letter_id)
AND history_flag = '0'
AND a.add_type = '分户'
AND a.state_now = '正常'
ORDER BY a.letter_id DESC

千问 · 2005-10-30 17:05:33

没明白 wenyp兄的意思 ,为什么不要group by
dreamonce兄说法好像不对

千问 · 2005-10-30 17:05:33

顶,帮我看看

千问 · 2005-10-30 17:05:33

最初由 wenyp 发布
[B]select max(signinfo_id) signinfo_id FROM sign GROUP BY letter_id
不能加GROUP BY letter_id ！！

你的SQL语句不用改成exists，因为in里面没有用到过滤语句，
另外， a.letter_id和b.letter_id要建立索引增加连接速度 [/B]
加不加 “不能加GROUP BY letter_id”得到的结果大不一样。
不知这位老兄考虑清楚了没有。

千问 · 2005-10-30 17:05:33

哦，是的，去掉group by后结果不一样，会少掉很多值，
上面我的一个回复有错误

千问 · 2005-10-30 17:05:33

我的问题没有解决呢,我该怎么办阿~~~等了两天了