抢个沙发,呵呵 lz可以试试下面的语句: select t2.esc_Orderid, t1.payment_id from payment t1,ctoc_order t2 where t2.order_id = t1.service_order and t1.payment_id between '820120528094530833273' and '820120528122552834900'; 关于sql性能分析器,这个我也不是太清楚了!
一个优化技巧,楼主应该避免使用IN 或者是NOT IN 语句,在使用IN和not in时会导致全表扫描速度肯定就慢了瑟可以尝试一下修改看看能不能提升性能: 原语句: select t2.esc_Orderid, t1.payment_id from payment t1 left join ctoc_order t2 on t2.order_id = t1.service_order where t1.payment_id in( '820120528094530833273', '820120528122552834900') 更改后语句 先根据payment_id 查询t1表中的server_code然后在进行连接 select aa.payment_id,bb.esc_orderid from (select payment_id,server_order from payment where payment_id='820120528094530833273' union all select payment_id,server_order from payment where payment_id='820120528122552834900') aa left outer join ctoc_order bb on aa.server_order=bb.order_id;
in 或者not in的范围不大的时候还是会走索引的。优化器会自己转换的。 如果你那个in 语句能极大的缩小结果集那应该先做in查询然后再关联。比如100w条,你left join 完了,再一个in 要期中一万条,肯定不如先从100w条里取出1w条再管连快。当然优化器会做优化,但是你不能保证他一定会优化。with aa as ( select t1.payment_id,t1.service_order from t1 where t1.payment_id in ('820120528094530833273','820120528122552834900') ) select t2.esc_orderid,aa.payment_id from aa left join t2 on t2.order_id = t1.service_order如果还嫌慢可以再t1上建 payment_id,service_order联合索引。
create table s41071030 ( sno int , sname varchar2(10), ssex char(1), sage int, sdept varchar2(10) ); / create table c41071030( cno int, cname varchar2(10), cpno int, ccredit varchar2(10) ); / create table sc41071030 ( sno int, cno int, grade int ); / insert into sc41071030 select 1001,1,40 from dual union all select 1001,2,45 from dual union all select 1001,3,50 from dual union all select 1002,1,44 from dual union all select 1002,2,40 from dual union all --select 1002,3,50 from dual union all select 1003,1,60 from dual; /insert into s41071030(sno,sdept) select 1001,'CS' from dual union all select 1002,'CS' from dual union all select 1003,'CS' from dual union all select 1004,'dd' from dual; / with cte as ( select a.sno ,count(1) as v_count from sc41071030 a inner join s41071030 b on a.sno=b.sno where b.sdept='CS' group by a.sno order by count(1) desc ) select a.* from cte a inner join ( select max(v_count) as v_count from cte )b on a.v_count=b.v_count /*drop table s41071030; drop table c41071030; drop table sc41071030;*/
避免用in, not in .这样效率比较慢,可以用exist。不抽象了吧!
with cte as ( select a.sno ,count(1) as v_count from sc41071030 a inner join s41071030 b on a.sno=b.sno where b.sdept='CS' group by a.sno order by count(1) desc ) select a.* from cte a inner join ( select max(v_count) as v_count from cte )b on a.v_count=b.v_count 我想问这个.
lz可以试试下面的语句:
select t2.esc_Orderid, t1.payment_id from
payment t1,ctoc_order t2
where t2.order_id = t1.service_order and t1.payment_id
between '820120528094530833273' and '820120528122552834900';
关于sql性能分析器,这个我也不是太清楚了!
原语句:
select t2.esc_Orderid, t1.payment_id from
payment t1 left join ctoc_order t2
on t2.order_id = t1.service_order
where t1.payment_id
in(
'820120528094530833273',
'820120528122552834900')
更改后语句 先根据payment_id 查询t1表中的server_code然后在进行连接
select aa.payment_id,bb.esc_orderid from
(select payment_id,server_order from payment where payment_id='820120528094530833273'
union all
select payment_id,server_order from payment where payment_id='820120528122552834900') aa
left outer join ctoc_order bb on aa.server_order=bb.order_id;
如果你那个in 语句能极大的缩小结果集那应该先做in查询然后再关联。比如100w条,你left join 完了,再一个in 要期中一万条,肯定不如先从100w条里取出1w条再管连快。当然优化器会做优化,但是你不能保证他一定会优化。with aa as
(
select t1.payment_id,t1.service_order
from t1
where t1.payment_id in ('820120528094530833273','820120528122552834900')
)
select t2.esc_orderid,aa.payment_id
from aa left join t2 on t2.order_id = t1.service_order如果还嫌慢可以再t1上建 payment_id,service_order联合索引。
create table s41071030
(
sno int ,
sname varchar2(10),
ssex char(1),
sage int,
sdept varchar2(10)
);
/
create table c41071030(
cno int,
cname varchar2(10),
cpno int,
ccredit varchar2(10)
);
/
create table sc41071030
(
sno int,
cno int,
grade int
);
/
insert into sc41071030
select 1001,1,40 from dual union all
select 1001,2,45 from dual union all
select 1001,3,50 from dual union all
select 1002,1,44 from dual union all
select 1002,2,40 from dual union all
--select 1002,3,50 from dual union all
select 1003,1,60 from dual;
/insert into s41071030(sno,sdept)
select 1001,'CS' from dual union all
select 1002,'CS' from dual union all
select 1003,'CS' from dual union all
select 1004,'dd' from dual;
/
with cte as
(
select a.sno ,count(1) as v_count from sc41071030 a
inner join s41071030 b on a.sno=b.sno
where b.sdept='CS'
group by a.sno
order by count(1) desc
)
select a.* from cte a
inner join
(
select max(v_count) as v_count from cte
)b on a.v_count=b.v_count
/*drop table s41071030;
drop table c41071030;
drop table sc41071030;*/
with cte as
(
select a.sno ,count(1) as v_count from sc41071030 a
inner join s41071030 b on a.sno=b.sno
where b.sdept='CS'
group by a.sno
order by count(1) desc
)
select a.* from cte a
inner join
(
select max(v_count) as v_count from cte
)b on a.v_count=b.v_count
我想问这个.