Details
A reader, August 29, 2018 - 6:57 am UTC
According to your suggestion I have remaped my query below ,could u pls help me check if the remap query is ok? or need to change somewhere,thanks a lot!
before query:
select *
from ufzsswxt.T_PM_N_ORDER odr
join ufzsswxt.T_PM_N_ORDER_TRADE trade on odr.ORDER_ID = trade.ORDER_ID
join ufzsswxt.T_PM_N_ORDER_DETAIL dtl on trade.TRADE_ID = dtl.TRADE_ID
LEFT join ufzsswxt.T_PM_N_ORDER_PAY pay ON trade.trade_id = pay.trade_id
left join ufzsswxt.T_PM_N_ORDER_SNAPSHOT odr_snap ON dtl.DETAIL_ID = odr_snap.order_detail_id and dtl.SKU_ID = odr_snap.sku_id
where odr.business_status in ('TO_BE_PAY','TO_BE_SHIP', 'SHIPPING','REFUNDOK','FINISH','AFTER_SALE','CLOSE','SHIPOK')
and odr.CHANNEL_ID in('GDJSYH1','GDJSYH','KHD_QD','XYKJ1','XYKJ2','ZYHLWYK','10085MALL','HD_LTDH')
and odr.create_time >=to_date('2018-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and odr.create_time <to_date('2018-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss');
remap query:
select * from ufzsswxt.T_PM_N_ORDER odr where odr.ORDER_ID in (select trade.ORDER_ID from ufzsswxt.T_PM_N_ORDER_TRADE trade) and odr.business_status in ('TO_BE_PAY','TO_BE_SHIP', 'SHIPPING','REFUNDOK','FINISH','AFTER_SALE','CLOSE','SHIPOK')
and odr.CHANNEL_ID in('GDJSYH1','GDJSYH','KHD_QD','XYKJ1','XYKJ2','ZYHLWYK','10085MALL','HD_LTDH')
and odr.create_time >=to_date('2018-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and odr.create_time <to_date('2018-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss');
select * from ufzsswxt.T_PM_N_ORDER_TRADE trade where trade.ORDER_ID in (select odr.ORDER_ID from ufzsswxt.T_PM_N_ORDER odr where odr.business_status in ('TO_BE_PAY','TO_BE_SHIP', 'SHIPPING','REFUNDOK','FINISH','AFTER_SALE','CLOSE','SHIPOK')
and odr.CHANNEL_ID in('GDJSYH1','GDJSYH','KHD_QD','XYKJ1','XYKJ2','ZYHLWYK','10085MALL','HD_LTDH')
and odr.create_time >=to_date('2018-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and odr.create_time <to_date('2018-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss'));
select * from ufzsswxt.T_PM_N_ORDER_DETAIL dtl where dtl.TRADE_ID in (select trade.TRADE_ID from ufzsswxt.T_PM_N_ORDER odr,ufzsswxt.T_PM_N_ORDER_TRADE trade where odr.ORDER_ID = trade.ORDER_ID and odr.business_status in ('TO_BE_PAY','TO_BE_SHIP', 'SHIPPING','REFUNDOK','FINISH','AFTER_SALE','CLOSE','SHIPOK')
and odr.CHANNEL_ID in('GDJSYH1','GDJSYH','KHD_QD','XYKJ1','XYKJ2','ZYHLWYK','10085MALL','HD_LTDH')
and odr.create_time >=to_date('2018-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and odr.create_time <to_date('2018-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss'));
select * from ufzsswxt.T_PM_N_ORDER_PAY pay where pay.trade_id in (select trade.TRADE_ID from ufzsswxt.T_PM_N_ORDER odr,ufzsswxt.T_PM_N_ORDER_TRADE trade,ufzsswxt.T_PM_N_ORDER_DETAIL dtl where odr.ORDER_ID = trade.ORDER_ID and trade.TRADE_ID = dtl.TRADE_ID and odr.business_status in ('TO_BE_PAY','TO_BE_SHIP', 'SHIPPING','REFUNDOK','FINISH','AFTER_SALE','CLOSE','SHIPOK')
and odr.CHANNEL_ID in('GDJSYH1','GDJSYH','KHD_QD','XYKJ1','XYKJ2','ZYHLWYK','10085MALL','HD_LTDH')
and odr.create_time >=to_date('2018-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and odr.create_time <to_date('2018-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss'));
select * from ufzsswxt.T_PM_N_ORDER_SNAPSHOT odr_snap where (odr_snap.order_detail_id,odr_snap.SKU_ID) in (select dtl.DETAIL_ID,dtl.SKU_ID from ufzsswxt.T_PM_N_ORDER odr
join ufzsswxt.T_PM_N_ORDER_TRADE trade on odr.ORDER_ID = trade.ORDER_ID
join ufzsswxt.T_PM_N_ORDER_DETAIL dtl on trade.TRADE_ID = dtl.TRADE_ID
LEFT join ufzsswxt.T_PM_N_ORDER_PAY pay ON trade.trade_id = pay.trade_id
where odr.business_status in ('TO_BE_PAY','TO_BE_SHIP', 'SHIPPING','REFUNDOK','FINISH','AFTER_SALE','CLOSE','SHIPOK')
and odr.CHANNEL_ID in('GDJSYH1','GDJSYH','KHD_QD','XYKJ1','XYKJ2','ZYHLWYK','10085MALL','HD_LTDH')
and odr.create_time >=to_date('2018-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and odr.create_time <to_date('2018-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss'));
September 04, 2018 - 5:04 am UTC
Um.........maybe.
We really can't tell just by looking at the SQL - you need to run before and after tests. Make sure it returns the right results, and is giving the performance boost you are after etc.