select *
from (select a.*, rownum RESULT_INDEX
from (select b.*
from vw_order_xj_list b
where b.ORDER_ID in
(select dwo1.order_id
from dsr_work_order dwo1
where (dwo1.org_id in
(1, 654208, 655362, 655379) or
dwo1.belong_job_id in
(10000000, 10000001, 10000002, 10000003)))
order by b.SEND_DATE desc) a)
where RESULT_INDEX between 1 and 100
from (select a.*, rownum RESULT_INDEX
from (select b.*
from vw_order_xj_list b
where b.ORDER_ID in
(select dwo1.order_id
from dsr_work_order dwo1
where (dwo1.org_id in
(1, 654208, 655362, 655379) or
dwo1.belong_job_id in
(10000000, 10000001, 10000002, 10000003)))
order by b.SEND_DATE desc) a)
where RESULT_INDEX between 1 and 100
子查询还用order by
当然慢了
SELECT *
FROM (SELECT B.*, ROW_NUMBER() OVER(ORDER BY B.SEND_DATE DESC) AS RESULT_INDEX
FROM VW_ORDER_XJ_LIST B,
(SELECT DWO1.ORDER_ID
FROM DSR_WORK_ORDER DWO1
WHERE (DWO1.ORG_ID IN (1, 654208, 655362, 655379) OR
DWO1.BELONG_JOB_ID IN
(10000000, 10000001, 10000002, 10000003))) C
WHERE B.ORDER_ID = C.ORDER_ID) A
WHERE RESULT_INDEX BETWEEN 1 AND 100
vw_order_xj_list(ORDER_ID)上面两表中条件字段是什么类型?是否有索引?
数据量如何?如果建立索引,选择性怎样?如果是全表扫描,根据条件列的选择性建立索引另外,可以考虑将in改为exists试试,或者使用4楼的表连接方式测试一下效率
SELECT *
FROM (SELECT a.*, rownum RESULT_INDEX
FROM (SELECT b.*
FROM vw_order_xj_list b
WHERE EXISTS
(SELECT 1
FROM dsr_work_order dwo1
WHERE (dwo1.org_id IN (1, 654208, 655362, 655379) OR
dwo1.belong_job_id IN
(10000000, 10000001, 10000002, 10000003))
AND dwo1.order_id = b.order_id)
ORDER BY b.SEND_DATE DESC) a)
WHERE RESULT_INDEX BETWEEN 1 AND 100;
dsr_work_order(org_id,belong_job_id) --- 数据量达到百万
vw_order_xj_list(ORDER_ID) ---数据量达到几十万
order_id是主键索引 org_id,belong_job_id都有组合索引
但是我看执行计划是好像都没有用到
上面都是全表扫表但是执行计划还可以,下面用到索引了
select b.*
from vw_order_xj_list b
where b.ORDER_ID in
(select dwo1.order_id
from dsr_work_order dwo1
where dwo1.org_id in
(1, 654208, 655362))
union
select b.*
from vw_order_xj_list b
where b.ORDER_ID in
(select dwo1.order_id
from dsr_work_order dwo1
where dwo1.belong_job_id in
(10000000, 10000001, 10000002))
但是效率却不如上面的,不知道是啥原因
如果选择出来记录很多,占b表30%以上,这样优化余地就不是很大了。如果记录较多,使用4楼表连接方式,执行计划使用hash join应该是比较好的。csdn图片只能发链接。复制文本贴
试下将org_id,belong_job_id组合索引改为独立的两个索引,
再将SEND_DATE改为降序索引