sql - oracle插入很慢

Question

我有以下 Oracle 查询，执行速度很快(几秒钟):

select contract_id_fk as ct,
       max(trip_id) as tid,
       max(consumed_mileage) as cum
from trip
where to_date > to_date('20-12-2016','DD-MM-YYYY')
and contract_id_fk is not null
and vehicle_id_fk is not null
and trip_stop_status is null
group by contract_id_fk

'trip' 表有大量行(超过 2000 万)
现在，我想将此查询的结果插入表中，使用:

INSERT INTO lst
select contract_id_fk as ct, 
       max(trip_id) as tid, 
       max(consumed_mileage) as cum 
from trip
where to_date > to_date('20-12-2016','DD-MM-YYYY') 
and contract_id_fk is not null 
and vehicle_id_fk is not null 
and trip_stop_status is null 
group by contract_id_fk

这是非常缓慢的。太慢以至于交易超时(在我的例子中超过 30 秒)。知道为什么这这么慢，以及如何对其进行优化？

Answer 1

很难说是什么原因，因为可能有多种原因，例如:

内存不足，或重做日志大小

次优表设置(PCTFREE 等)

硬件性能问题

我推荐两件事:

检查等待事件是什么

尝试使用 APPEND 提示插入

INSERT /*+ APPEND */ INTO lst
select contract_id_fk as ct,
max(trip_id) as tid, max(consumed_mileage) as cum
from trip where to_date > to_date('20-12-2016','DD-MM-YYYY')
and contract_id_fk is not null and vehicle_id_fk is not null and
trip_stop_status is null group by contract_id_fk