-- a子句 0.05秒
select * from mtl_system_items_b msi
where msi.segment1 in ('180201080017','180201080018','180201080019','180201090056')
-- b 子句 10 秒
select * from mtl_system_items_b msi
where msi.segment1 in ( select distinct itemno from t2 where category_id=874 )注:单独运行select distinct itemno from t2 where category_id=874 需时0.5秒
,select distinct itemno from t2 where category_id=874 的结果是如下4条纪录
'180201080017'
'180201080018'
'180201080019'
'180201090056'如果在sql server中我会以这样提升效率
如:
declare @t1 table (code varchar(30))
insert into @t1 (code) select distinct itemno from t2 where category_id=874select * from mtl_system_items_b msi
where msi.segment1 in ( select code from @t1 )在oracle 中,对于b 子句如何优化?
select * from mtl_system_items_b msi
where msi.segment1 in ('180201080017','180201080018','180201080019','180201090056')
-- b 子句 10 秒
select * from mtl_system_items_b msi
where msi.segment1 in ( select distinct itemno from t2 where category_id=874 )注:单独运行select distinct itemno from t2 where category_id=874 需时0.5秒
,select distinct itemno from t2 where category_id=874 的结果是如下4条纪录
'180201080017'
'180201080018'
'180201080019'
'180201090056'如果在sql server中我会以这样提升效率
如:
declare @t1 table (code varchar(30))
insert into @t1 (code) select distinct itemno from t2 where category_id=874select * from mtl_system_items_b msi
where msi.segment1 in ( select code from @t1 )在oracle 中,对于b 子句如何优化?
--如果表的数据较大,最好用exists替换in
select * from mtl_system_items_b msi
where exists ( select 1 from t2 where category_id=874 and msi.segment1=itemno)
是否和你预料的不一样?--或者直接关联试一下写可能效率更好
SELECT DISTINCT msi.*
FROM mtl_system_items_b msi, t2
WHERE msi.segment1 = t2.itemno
AND t2.category_id = 874;
(select distinct itemno from t2 where category_id=874 )
select * from mtl_system_items_b msi
where msi.segment1 in ( select * from temp)
用exists 比in好
这个网站的内容比较好 看看
http://www.360doc.com/content/10/0908/09/3212287_52030258.shtml
--试试
select msi.* from mtl_system_items_b msi,t2
where t2.itemno =msi.segment1 and t2.category_id=874
---你那种写法是临时表的写法 也可以在ORACLE 中采用with tb as
(select distinct itemno from t2 where category_id=874)
msi.* from mtl_system_items_b msi,tb
where tb.itemno =msi.segment1