(oracle9i,windows,server2003)
update ch_flat_0113 a
set (a.floor,a.flarea,a.priv_flarea,a.co_flarea)
=(select b.ch,b.zmj,b.dymj,b.ftmj
from tb_flat b where a.house_id = b.house_id)
看plan_table,trace如下
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT ptimizer=CHOOSE
1 0 UPDATE OF 'CH_FLAT_0113'
2 1 TABLE ACCESS (FULL) OF 'CH_FLAT_0113'
3 1 TABLE ACCESS (FULL) OF 'TB_FLAT'
由于涉及的二个表都是10万左右数据,这样的话肯定是不行的。
后来发现问题的所在,两个表的house_id字段类型并不一样
一个是number 一个是varchar2,使用 函数 to_char或者to_number以后,
或者修改了 字段类型再加索引,得到的 执行计划如下 :
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT ptimizer=CHOOSE
1 0 UPDATE OF 'CH_FLAT_0113'
2 1 TABLE ACCESS (FULL) OF 'CH_FLAT_0113'
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_FLAT'
4 3 INDEX (UNIQUE SCAN) OF 'TN_FLAT_INDEX_1' (UNIQUE)
再修改一下语句,使用index full scan代替table access(full):
update /*+ index(a)*/ ch_flat_0113 a
set (a.floor,a.flarea,a.priv_flarea,a.co_flarea)
=(select b.ch,b.zmj,b.dymj,b.ftmj
from tb_flat b where a.house_id = b.house_id)
得到的执行计划:
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT ptimizer=CHOOSE (Cost=826 Card=66161 Bytes
=3903499)
1 0 UPDATE OF 'CH_FLAT_0113'
2 1 INDEX (FULL SCAN) OF 'CH_HOUSE_ID_2' (UNIQUE) (Cost=26 C
ard=66161 Bytes=3903499)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_FLAT' (Cost=2 Card=
1 Bytes=59)
4 3 INDEX (UNIQUE SCAN) OF 'TN_FLAT_INDEX_1' (UNIQUE) (Cos
t=1 Card=100)
进一步修改,使用hint选择rule方式:
update /*+ index(a) rule*/ ch_flat_0113 a
set (a.floor,a.flarea,a.priv_flarea,a.co_flarea)
=(select b.ch,b.zmj,b.dymj,b.ftmj
from tb_flat b where a.house_id = b.house_id)
实际上最后一种方式稍稍好点,不过后面2个区别不大。
update ch_flat_0113 a
set (a.floor,a.flarea,a.priv_flarea,a.co_flarea)
=(select b.ch,b.zmj,b.dymj,b.ftmj
from tb_flat b where a.house_id = b.house_id)
看plan_table,trace如下
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT ptimizer=CHOOSE
1 0 UPDATE OF 'CH_FLAT_0113'
2 1 TABLE ACCESS (FULL) OF 'CH_FLAT_0113'
3 1 TABLE ACCESS (FULL) OF 'TB_FLAT'
由于涉及的二个表都是10万左右数据,这样的话肯定是不行的。
后来发现问题的所在,两个表的house_id字段类型并不一样
一个是number 一个是varchar2,使用 函数 to_char或者to_number以后,
或者修改了 字段类型再加索引,得到的 执行计划如下 :
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT ptimizer=CHOOSE
1 0 UPDATE OF 'CH_FLAT_0113'
2 1 TABLE ACCESS (FULL) OF 'CH_FLAT_0113'
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_FLAT'
4 3 INDEX (UNIQUE SCAN) OF 'TN_FLAT_INDEX_1' (UNIQUE)
再修改一下语句,使用index full scan代替table access(full):
update /*+ index(a)*/ ch_flat_0113 a
set (a.floor,a.flarea,a.priv_flarea,a.co_flarea)
=(select b.ch,b.zmj,b.dymj,b.ftmj
from tb_flat b where a.house_id = b.house_id)
得到的执行计划:
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT ptimizer=CHOOSE (Cost=826 Card=66161 Bytes
=3903499)
1 0 UPDATE OF 'CH_FLAT_0113'
2 1 INDEX (FULL SCAN) OF 'CH_HOUSE_ID_2' (UNIQUE) (Cost=26 C
ard=66161 Bytes=3903499)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_FLAT' (Cost=2 Card=
1 Bytes=59)
4 3 INDEX (UNIQUE SCAN) OF 'TN_FLAT_INDEX_1' (UNIQUE) (Cos
t=1 Card=100)
进一步修改,使用hint选择rule方式:
update /*+ index(a) rule*/ ch_flat_0113 a
set (a.floor,a.flarea,a.priv_flarea,a.co_flarea)
=(select b.ch,b.zmj,b.dymj,b.ftmj
from tb_flat b where a.house_id = b.house_id)
实际上最后一种方式稍稍好点,不过后面2个区别不大。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16179598/viewspace-539595/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16179598/viewspace-539595/