SQL 優化請教

Question

select * from table_a where sn='1001';select sn from table_b 
where test_time between to_date('2003080109:30','yyyymmddhh24:mi')
and   to_date('2003080209:30','yyyymmddhh24:mi')select * from table_a a,(select sn from table_b 
where test_time between to_date('2003080109:30','yyyymmddhh24:mi')
and   to_date('2003080209:30','yyyymmddhh24:mi')b
where a.sn=b.sn另外需要對進行優化。
http://www.csdn.net/develop/read_article.asp?id=20414
http://www.csdn.net/develop/read_article.asp?id=20266

Answer 1

楼上的：select * from table_a a,(select sn from table_b 中是否要该为
select a.* from table_a a,(select sn from table_b
否则是否会产生迪卡尔乘积？select a.* from table_a a,table_b b
where a.sn='1001'
and   a.sn=b.sn
and   b.test_time>=to_date('2003080109:30','yyyymmddhh24:mi')
and   b.test_time<=to_date('2003080209:30','yyyymmddhh24:mi')

Answer 2

select * from table_a 
 where exists( select 1 from table_b where a.sn = b.sn  
   and test_time>=to_date('2003080109:30','yyyymmddhh24:mi')
   and test_time<=to_date('2003080209:30','yyyymmddhh24:mi'))

Answer 3

To: jiezhi(浪子) 
   你所說的方法不可行，table_b中如果數據量大的話，照樣很慢。To: David1289(David)
   where a.sn='1001' 查詢之前我根本不知道sn的值。更不可能。To: onejune4450(中文字符) 
   是否能說得更詳細些，好像不能運行哦。---------------------------------------------------------------
很感謝大家的幫忙!!!!!

Answer 4

select * from table_a a
 where exists( select 1 from table_b b where a.sn = b.sn  
   and test_time>=to_date('2003080109:30','yyyymmddhh24:mi')
   and test_time<=to_date('2003080209:30','yyyymmddhh24:mi'))

Answer 5

select /*+ table_a_index */* from table_a where sn in(select /*+ table_b_index */sn from table_b 
where test_time>=to_date('2003080109:30','yyyymmddhh24:mi')
and   test_time<=to_date('2003080209:30','yyyymmddhh24:mi')
);