关于oracle的更新效率问题，select时很快，但update就很慢

[zhiquan911]

以下两条语句，一条查询的，一条是把想把查询的东西update到对应行中，但update的语句效率很慢，
两条语句条件的写法上大致一样，究竟oracle的update是怎样的原理，导致效率这么慢，求助一下各位

--更新的语句（速度很慢，基本不动）
update  t_join_situation a 
set (a.special_clinic_compen_money,a.special_clinic_compen_count)=
(
select a.special_clinic_compen_money+compensation_sum as a1,a.special_clinic_compen_count+compensation_count as a2 from
(
select sum(this_year_compensation_cost) as compensation_sum,count(1) as compensation_count,people_number from t_compensate_proof where 
type='3' and state='0' and year='2010' and city_number='P00000'
 group by people_number 
) b
where a.city_number='P00000' and a.year='2010' and a.people_number=b.people_number
) 
where exists (select 1 from t_compensate_proof b where 
a.people_number=b.people_number and a.city_number='P00000' and a.city_number=b.city_number
)



--查询速度很快
select a.people_number from t_join_situation a,
(
select sum(this_year_compensation_cost) as compensation_sum,count(1) as compensation_count,people_number from t_compensate_proof where 
type='2' and state='0' and year='2010' and city_number='U00000' 
 group by people_number 
) b
where a.city_number='U00000' and a.year='2010' and a.people_number=b.people_number

[csuxp2008]

可能更索引有关系，看看t_compensate_proof这张表上的索引

[心中的彩虹]

[Quote=引用楼主 zhiquan911 的回复:]
以下两条语句，一条查询的，一条是把想把查询的东西update到对应行中，但update的语句效率很慢，
两条语句条件的写法上大致一样，究竟oracle的update是怎样的原理，导致效率这么慢，求助一下各位

SQL code

--更新的语句（速度很慢，基本不动）
update t_join_situation a
set (a.special_clinic_compen_mo……
[/Quote]

-- 试下这样



update (select a.special_clinic_compen_money t1,a.special_clinic_compen_count t2,compensation_sum,compensation_count
from t_join_situation a,
(select sum(this_year_compensation_cost) as compensation_sum,count(1) as compensation_count,people_number,city_number
from t_compensate_proof where type='3' and state='0' and year='2010' and city_number='P00000'
group by city_number,people_number) b
where a.people_number=b.people_number and a.city_number=b.city_number)
set t1=compensation_sum,t2=compensation_count

[Diza1986]

对表建立索引或分区可以大幅提高查询的效率，但如果在该列数据上作更新操作则效率会非常差

另外对于这种条件

where a.city_number='P00000' and a.year='2010' and a.people_number=b.people_number

建议这样写

where a.people_number=b.people_number and a.year='2010' and a.city_number='P00000'

oracle条件语句是从后开始执行的，这样效率会好一些

[minitoy]

恩,应该是索引的原因.索引是典型的以空间换时间.select时使用索引可以大大减少检索时间.但是update的时候得维护索引,所以导致update变慢.

[zhiquan911]

t_join_situation 表 按city_number字段分区，并且people_number和year是索引字段，
但我更新的字段不是索引字段

[minitoy]

那看看执行计划呢