记录一次亲身经历数据库查询优化：Oracle 分页 排序 rownum 优化
原始 【查询出需要14秒，去掉order by后只需1秒，但是时间排序必须有】：
select rn, t2.* from (
select rownum rn, t1.* from (
select b.* from ( select o.id,o.code,o.merchant_id,o.merchant_name,o.order_status,o.total_price,o.order_time order_time2,to_char(o.order_time, 'yyyy-MM-dd HH24:mi:ss') order_time,o.payment_method,o.type,o.remark,to_char(o.receipt_time, 'yyyy-MM-dd HH24:mi:ss') receipt_time, to_char(o.pay_time, 'yyyy-MM-dd HH24:mi:ss') pay_time,to_char(o.send_time, 'yyyy-MM-dd HH24:mi:ss') send_time,to_char(o.confirm_time, 'yyyy-MM-dd HH24:mi:ss') confirm_time,o.user_phone,o.user_account,o.user_mail,o.user_name,o.contact_name,o.contact_phone, o.contact_address,o.receipt_postcode, o.pay_code,o.sms_num,o.sms_status,o.order_source,o.valid_time,os.id osid,os.name osname,p.id pid,p.name pname,oss.id ossid, oss.name ossname, m.id mid, m.shop_name shop_name,mo.id moid, mo.order_id order_id, mo.count count,f.id fid,f.name fname,mfo.id mfoid, mfo.seat_id seat_id,mfo.password mfopassword,mfo.sectionid mfosectionid,fs.id fsid,to_char(fs.film_date, 'yyyy-MM-dd') film_date,fs.film_time film_time,fs.video_hall video_hall,fs.standard_price standard_price,fs.clearing_price clearing_price,fs.promotion_price promotion_price,DECODE(em.fee, null, fs.fee, '', fs.fee, em.fee) fee,c.id cid,c.name cname,c.code ccode, c.zone czone,c.city ccity, c.sources,vh.id vhid,vh.hall_id hall_id,vh.name vhname
from tc_order o
left join tc_order_status os on os.id = o.order_status left join tc_payment p on p.id = o.payment_method
left join tc_order_source oss on oss.id = o.order_source left join tc_merchant m on m.id = o.merchant_id
left join tc_ext_merchant em on em.id = m.id left join tc_merchandise_order mo on mo.order_id = o.id
left join tc_film f on f.id = mo.merchandise_id left join tc_merchandise_film_order mfo on mfo.order_id = mo.id
left join tc_film_schedule fs on fs.id = mfo.schedule_id left join tc_cinema c on c.id = fs.cinema left join tc_vedio_hall vh on vh.id = fs.video_hall
where o.type = 3 and c.sources in (2, 4, 5) and os.id = 7
) b order by b.order_time2 desc
) t1 WHERE 10>= rownum
) t2
进化1【优化】：
select rn, t2.* from (
select nei rn, t1.* from (
select rownum nei,ne.* from (
select b.* from ( select o.id,o.code,o.merchant_id,o.merchant_name,o.order_status,o.total_price,o.order_time order_time2,to_char(o.order_time, 'yyyy-MM-dd HH24:mi:ss') order_time,o.payment_method,o.type,o.remark,to_char(o.receipt_time, 'yyyy-MM-dd HH24:mi:ss') receipt_time, to_char(o.pay_time, 'yyyy-MM-dd HH24:mi:ss') pay_time,to_char(o.send_time, 'yyyy-MM-dd HH24:mi:ss') send_time,to_char(o.confirm_time, 'yyyy-MM-dd HH24:mi:ss') confirm_time,o.user_phone,o.user_account,o.user_mail,o.user_name,o.contact_name,o.contact_phone, o.contact_address,o.receipt_postcode, o.pay_code,o.sms_num,o.sms_status,o.order_source,o.valid_time,os.id osid,os.name osname,p.id pid,p.name pname,oss.id ossid, oss.name ossname, m.id mid, m.shop_name shop_name,mo.id moid, mo.order_id order_id, mo.count count,f.id fid,f.name fname,mfo.id mfoid, mfo.seat_id seat_id,mfo.password mfopassword,mfo.sectionid mfosectionid,fs.id fsid,to_char(fs.film_date, 'yyyy-MM-dd') film_date,fs.film_time film_time,fs.video_hall video_hall,fs.standard_price standard_price,fs.clearing_price clearing_price,fs.promotion_price promotion_price,DECODE(em.fee, null, fs.fee, '', fs.fee, em.fee) fee,c.id cid,c.name cname,c.code ccode, c.zone czone,c.city ccity, c.sources,vh.id vhid,vh.hall_id hall_id,vh.name vhname
from tc_order o
left join tc_order_status os on os.id = o.order_status left join tc_payment p on p.id = o.payment_method
left join tc_order_source oss on oss.id = o.order_source left join tc_merchant m on m.id = o.merchant_id
left join tc_ext_merchant em on em.id = m.id left join tc_merchandise_order mo on mo.order_id = o.id
left join tc_film f on f.id = mo.merchandise_id left join tc_merchandise_film_order mfo on mfo.order_id = mo.id
left join tc_film_schedule fs on fs.id = mfo.schedule_id left join tc_cinema c on c.id = fs.cinema left join tc_vedio_hall vh on vh.id = fs.video_hall
where o.type = 3 and c.sources in (2, 4, 5) and os.id = 7
) b order by b.order_time2 desc
) ne
) t1 WHERE 10>= t1.nei
) t2
进化2【优化】：
select t2.* from (
select nei rn, t1.* from (
select rownum nei,ne.* from (
select b.* from ( select o.id,o.code,o.merchant_id,o.merchant_name,o.order_status,o.total_price,o.order_time order_time2,to_char(o.order_time, 'yyyy-MM-dd HH24:mi:ss') order_time,o.payment_method,o.type,o.remark,to_char(o.receipt_time, 'yyyy-MM-dd HH24:mi:ss') receipt_time, to_char(o.pay_time, 'yyyy-MM-dd HH24:mi:ss') pay_time,to_char(o.send_time, 'yyyy-MM-dd HH24:mi:ss') send_time,to_char(o.confirm_time, 'yyyy-MM-dd HH24:mi:ss') confirm_time,o.user_phone,o.user_account,o.user_mail,o.user_name,o.contact_name,o.contact_phone, o.contact_address,o.receipt_postcode, o.pay_code,o.sms_num,o.sms_status,o.order_source,o.valid_time,os.id osid,os.name osname,p.id pid,p.name pname,oss.id ossid, oss.name ossname, m.id mid, m.shop_name shop_name,mo.id moid, mo.order_id order_id, mo.count count,f.id fid,f.name fname,mfo.id mfoid, mfo.seat_id seat_id,mfo.password mfopassword,mfo.sectionid mfosectionid,fs.id fsid,to_char(fs.film_date, 'yyyy-MM-dd') film_date,fs.film_time film_time,fs.video_hall video_hall,fs.standard_price standard_price,fs.clearing_price clearing_price,fs.promotion_price promotion_price,DECODE(em.fee, null, fs.fee, '', fs.fee, em.fee) fee,c.id cid,c.name cname,c.code ccode, c.zone czone,c.city ccity, c.sources,vh.id vhid,vh.hall_id hall_id,vh.name vhname
from tc_order o
left join tc_order_status os on os.id = o.order_status left join tc_payment p on p.id = o.payment_method
left join tc_order_source oss on oss.id = o.order_source left join tc_merchant m on m.id = o.merchant_id
left join tc_ext_merchant em on em.id = m.id left join tc_merchandise_order mo on mo.order_id = o.id
left join tc_film f on f.id = mo.merchandise_id left join tc_merchandise_film_order mfo on mfo.order_id = mo.id
left join tc_film_schedule fs on fs.id = mfo.schedule_id left join tc_cinema c on c.id = fs.cinema left join tc_vedio_hall vh on vh.id = fs.video_hall
where o.type = 3 and c.sources in (2, 4, 5) and os.id = 7
) b order by b.order_time2 desc
) ne
) t1 WHERE 10>= nei
) t2
