现在执行时间已经超过了一分钟,SRV_orderhistory_F表现有数据量已经达到了几百万了.多谢了!
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') as sysdate1,
dealerCode,
orderNo,
registerNO,
vinNo,
vhcMakerType,
vhcCode,
vhcName,
model,
customerID,
customerName,
customerZipCode,
customerAddr,
tel1,
tel2,
mileAge,
nvl(rezID, '') as rezID,
nvl(basRezID, '') as basRezID,
to_char(orderCancelDate, 'YYYY-MM-DD HH24:MI:SS') as orderCancelDate,
to_char(updateTime, 'YYYY-MM-DD HH24:MI:SS') as updateTime,
to_char(creDate, 'YYYY-MM-DD HH24:MI:SS') as creDate
from SRV_ORDER_F
where nvl(updateTime, creDate) >=
to_date('2008-08-01 12:42:05', 'YYYY-MM-DD HH24:MI:SS')
and nvl(updateTime, creDate) <
to_date('2008-08-01 12:45:09', 'YYYY-MM-DD HH24:MI:SS')
and ((rezID is null and basRezID is not null) or
(orderCancelDate is not null and rezID is not null))
union all
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') as sysdate1,
dealerCode,
orderNo,
registerNO,
vinNo,
vhcMakerType,
vhcCode,
vhcName,
model,
customerID,
customerName,
customerZipCode,
customerAddr,
tel1,
tel2,
mileAge,
nvl(rezID, '') as rezID,
nvl(basRezID, '') as basRezID,
to_char(orderCancelDate, 'YYYY-MM-DD HH24:MI:SS') as orderCancelDate,
to_char(updateTime, 'YYYY-MM-DD HH24:MI:SS') as updateTime,
to_char(creDate, 'YYYY-MM-DD HH24:MI:SS') as creDate
from SRV_orderhistory_F
where nvl(updateTime, creDate) >=
to_date('2008-08-01 12:42:05', 'YYYY-MM-DD HH24:MI:SS')
and nvl(updateTime, creDate) <
to_date('2008-08-01 12:45:09', 'YYYY-MM-DD HH24:MI:SS')
and ((rezID is null and basRezID is not null) or
(orderCancelDate is not null and rezID is not null))
order by orderNo
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') as sysdate1,
dealerCode,
orderNo,
registerNO,
vinNo,
vhcMakerType,
vhcCode,
vhcName,
model,
customerID,
customerName,
customerZipCode,
customerAddr,
tel1,
tel2,
mileAge,
nvl(rezID, '') as rezID,
nvl(basRezID, '') as basRezID,
to_char(orderCancelDate, 'YYYY-MM-DD HH24:MI:SS') as orderCancelDate,
to_char(updateTime, 'YYYY-MM-DD HH24:MI:SS') as updateTime,
to_char(creDate, 'YYYY-MM-DD HH24:MI:SS') as creDate
from SRV_ORDER_F
where nvl(updateTime, creDate) >=
to_date('2008-08-01 12:42:05', 'YYYY-MM-DD HH24:MI:SS')
and nvl(updateTime, creDate) <
to_date('2008-08-01 12:45:09', 'YYYY-MM-DD HH24:MI:SS')
and ((rezID is null and basRezID is not null) or
(orderCancelDate is not null and rezID is not null))
union all
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') as sysdate1,
dealerCode,
orderNo,
registerNO,
vinNo,
vhcMakerType,
vhcCode,
vhcName,
model,
customerID,
customerName,
customerZipCode,
customerAddr,
tel1,
tel2,
mileAge,
nvl(rezID, '') as rezID,
nvl(basRezID, '') as basRezID,
to_char(orderCancelDate, 'YYYY-MM-DD HH24:MI:SS') as orderCancelDate,
to_char(updateTime, 'YYYY-MM-DD HH24:MI:SS') as updateTime,
to_char(creDate, 'YYYY-MM-DD HH24:MI:SS') as creDate
from SRV_orderhistory_F
where nvl(updateTime, creDate) >=
to_date('2008-08-01 12:42:05', 'YYYY-MM-DD HH24:MI:SS')
and nvl(updateTime, creDate) <
to_date('2008-08-01 12:45:09', 'YYYY-MM-DD HH24:MI:SS')
and ((rezID is null and basRezID is not null) or
(orderCancelDate is not null and rezID is not null))
order by orderNo
sql本身没有什么可以优化的。
主要看看是否建立了index:
create index i_SRV_orderhistory_F_1 on SRV_orderhistory_F(nvl(updateTime, creDate));
create index i_SRV_ORDER_F_1 on SRV_ORDER_F(nvl(updateTime, creDate));
(orderCancelDate is not null and rezID is not null)) 这个条件用不到索引了。。
create index i_SRV_orderhistory_F_1 on SRV_orderhistory_F(nvl(updateTime, creDate));
create index i_SRV_ORDER_F_1 on SRV_ORDER_F(nvl(updateTime, creDate));