select substr(sta.changetime, 0, 8) as riqi,sta.orderid
,(select p.facelistid from print_facelist p where p.orderid = sta.orderid) as faceid
,(select customername from customerinfo c where c.customerid =
(select customerid from ordersinfo o where o.orderid = sta.orderid
and o.customerid = c.customerid) and rownum = 1) as customername
,(select ordersum from ordersinfo o where o.orderid = sta.orderid) as ordersum
from orderstatuhisinfo sta
where 1 = 1
and sta.lststatu = '14'
and TO_DATE(SUBSTR(CHANGETIME, 0, 14), 'yyyymmddhh24miss') BETWEEN
TO_DATE('2009-01-01 16:01:09', 'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE('2009-08-29 16:01:09', 'yyyy-mm-dd hh24:mi:ss')
and exists (select 'x'
from ordersinfo ord
where ord.orderid = sta.orderid
and ord.expresstype = '0')
substr(sta.changetime, 0, 8) as riqi
, sta.orderid
, p.facelistid
, c.customername
, o.ordersum
from orderstatuhisinfo sta
, print_facelist p
, customerinfo c
, ordersinfo o
where sta.orderid = o.orderid
and sta.orderid = p.orderid
and o.customerid = c.customerid
and TO_DATE(SUBSTR(CHANGETIME, 0, 14), 'yyyymmddhh24miss')
BETWEEN TO_DATE('2009-01-01 16:01:09', 'yyyy-mm-dd hh24:mi:ss')
AND TO_DATE('2009-08-29 16:01:09', 'yyyy-mm-dd hh24:mi:ss')
and sta.lststatu = '14'
and o.expresstype = '0'表数据量不大的话试试这个,感觉customerinfo和ordersinfo是两个大表。sql优化的话下次记得把每个表的索引和表中数据量列出来
select /*+ parallel(sta,8) ordered use_hash(c,o) */