有一张表,名为表A,字段order_seq为主键,现在有一条语句:
select *
from 表A where order_seq = (Select min(order_seq) From 表A Where down_flag = 'N' and cancel_flag = 'N')当表A的数据量很大时,此条语句的速度会迅速降低,请教高手有没有办法将上面的语句进行优化,提高查询的速度.
select *
from 表A where order_seq = (Select min(order_seq) From 表A Where down_flag = 'N' and cancel_flag = 'N')当表A的数据量很大时,此条语句的速度会迅速降低,请教高手有没有办法将上面的语句进行优化,提高查询的速度.
down_flag = 'N' and cancel_flag = 'N'
如果选择性低,并且不需要频繁更新表A
可考虑建立一个位置索引
或者试试这个语句
SELECT *
FROM 表A a
WHERE NOT EXISTS (SELECT NULL
FROM 表A b
WHERE down_flag = 'N' AND
cancel_flag = 'N' AND
b.order_seq < a.order_seq)
from (
select t.*, row_number()over(order by order_seq asc) rn
from table_a t
where down_flag = 'N' and cancel_flag = 'N'
)
where rn =1
有一张表,名为表A,字段order_seq为主键其实是楼主是要这个:select *
from (
select t.*, rownum rn
from table_a t
where down_flag = 'N' and cancel_flag = 'N'
order by order_seq asc
)
where rn = 1
Select * From 表A
Where down_flag = 'N'
and cancel_flag = 'N'如果你的是不同的表,表A和表B,使用exists会比你那个好,比in也好一点。表B有时间加上时间会更好。可参考如下:
select * from 表A n
where exists (
Select * From 表B m
Where m.down_flag = 'N'
and m.cancel_flag = 'N'
and m.order_seq=n.order_seq
// time between to_date() and to_date()
)
from (
select t.*
from table_a t
where down_flag = 'N' and cancel_flag = 'N'
order by order_seq asc
)
where rownum = 1