几个小问题.

[WEIWEILI]

1.
select * from
(select * from tgmy.c_hwda a
where a.hwid not in (select hwid from tgmy.ht_d_hwfp) ) b
where b.hwid not in (select hwid from tgmy.ht_ruk_hwls c where c.zt='预分配')
and Length(trim(b.hwcode))>=8 and substr(b.hwcode,1,2) >= '01' and substr(b.hwname,1,1) <> 'X'
order by substr(b.hwcode,1,2),substr(b.hwcode,8,1) ,substr(b.hwcode,1,7) desc
或
select * from
(select * from tgmy.c_hwda a
where a.hwid not in (select hwid from tgmy.ht_d_hwfp) ) b
where not exists (select 1 from tgmy.ht_ruk_hwls c where c.zt='预分配' and b.hwid = c.hwid)
and Length(trim(b.hwcode))>=8 and substr(b.hwcode,1,2) >= '01' and substr(b.hwname,1,1) <> 'X'
order by substr(b.hwcode,1,2),substr(b.hwcode,8,1) ,substr(b.hwcode,1,7) desc
执行时，有时速度快，有时速度会慢上好几倍。（相同条件下,数据量都不超过3000条）
2.
select * from
(select * from tgmy.c_hwda a
where a.hwid not in (select hwid from tgmy.ht_d_hwfp) ) b
where b.hwid not in (select hwid from tgmy.ht_ruk_hwls c where c.zt='预分配')
and Length(trim(b.hwcode))>=8 and substr(b.hwcode,1,2) >='01' and substr(b.hwname,1,1) <> 'X'
order by substr(b.hwcode,1,2), substr(b.hwcode,8,1),substr(b.hwcode,1,7) desc;
如何取出第一条。
3.
在存储过程中，建立临时表；多个人同时运行这个存储过程，会不会有影响。

[ern]

支持楼上
速度快慢也有可能有其他人在用数据库做大数据量操作

[WEIWEILI]

to : ern(学习Oracle)
不是，现在只有一个人用，是不是和设置有关

[WEIWEILI]

还有，有时还会报超时。
select * from
(select * from tgmy.c_hwda a
where a.hwid not in (select hwid from tgmy.ht_d_hwfp) ) b
where
Length(trim(b.hwcode))>=8 and substr(b.hwcode,1,2) >= '01' and substr(b.hwname,1,1) <> 'X'
order by substr(b.hwcode,1,2),substr(b.hwcode,8,1) ,substr(b.hwcode,1,7) desc
如果这么改，速度就不会慢了。但是也达不到我要的效果了。
not in 和 not exists

not in 要进行全表扫描
而not exists 不会
可时实际使用，执行时间是一样的啊。
有优化的方法吗

[beckhambobo]

还不如利用执行计划查看

[jiezhi]

1.正常现象。
2.where rownum=1
3.建立的临时表是全局的，每个session互相不影响。

[beckhambobo]

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a90842/ch9.htm#1006527

[WEIWEILI]

嘿嘿
怎么看啊？

[WEIWEILI]

没什么好办法？
结了。

[welyngj]

建立临时表会造成碎片

[shuipipi]

I 服了 YOUR SQL！

[WEIWEILI]

ok

[WEIWEILI]

tg