oracle sql 优化之update多表

执行较慢的待优化的SQL：

update business_new
           set fare1_balance_ratio = (select BALANCE_RATIO             from bfare2
                   where bfare2.exchange_type = business_new.exchange_type and
                         bfare2.stock_type = business_new.stock_type and
                         (bfare2.entrust_way = business_new.entrust_way) and
                         (bfare2.entrust_type = business_new.entrust_type) 
        and bfare2.fare_type = '0')

从执行计划可以看出，走的就是nl关联，所以慢是正常的。

于是将其改写为merge，如下：

merge into business_new using bfare2
on (bfare2.exchange_type = business_new.exchange_type and
                         bfare2.stock_type = business_new.stock_type and
                         (bfare2.entrust_way = business_new.entrust_way) and
                         (bfare2.entrust_type = business_new.entrust_type) 
        and bfare2.fare_type = '4')
        when matched then update
           set business_new.farex_balance_ratio = bfare2.BALANCE_RATIO

很快就跑出来了。需要注意的是，update语句本身是通过hint让两表强制走hash join的。

除了用merge改写让两表关联走hash join外，还有一种更优、但有条件的做法。如下：

update (select fare1_balance_ratio,BALANCE_RATIO from business_new,bfare2
 where bfare2.exchange_type = business_new.exchange_type and
                         bfare2.stock_type = business_new.stock_type and
                         (bfare2.entrust_way = business_new.entrust_way) and
                         (bfare2.entrust_type = business_new.entrust_type) 
        and bfare2.fare_type = '0')
           set fare1_balance_ratio = BALANCE_RATIO ;

这也称为inline view更新法，性能是最好的，但相比merge并不明显。但表B的主键一定要在where条件中，并且是以“=”来关联被更新表，否则会遇到ORA-01779: 无法修改与非键值保存表对应的列。造成这个错误的原因是更新的列不是事实表的列，而是维度表的列。换句话说，如果两张表关联，其中一张表的关联列是主键，那么另一张表就是事实表，也就是说另一张表中的列就是可更新的；除非另一张表的关联列也是主键，否则这张表就是不可更新的，如果更新语句涉及到了这张表，就会出现ORA-1799错误。也就是，要么两张表都通过PK关联，要么只有非PK这张表可更新。

至于for循环，除非逻辑特别复杂，用for bulk collect，否则不要考虑。

参考2个批量update语句且有多表关联的改进写法：

update (select YP.DEALLOCATE_BUDGET_,YP.REMAIN_BUDGET_ from   YP where YP.PLAN_ID in ('3a', '3b')) set DEALLOCATE_BUDGET_ = REMAIN_BUDGET_；

(1)in, not 的写法可以改进为exists ,  not exists的写法;

(2)update 支持多个字段列表方式如 update  tab1  set  (a,b,c )  = (select v1, v2, v3 from  tab2 where tab2.uid=tab1.uid);

(3)涉及历史数据的大表（如数十亿记录），应考虑分区表带来的改进；或者先禁用索引，更新后再重建索引。

update (select A.JOIN_STATE as JOIN_STATE_A, B.JOIN_STATE as JOIN_STATE_B            from T_JOIN_SITUATION A, T_PEOPLE_INFO B           where A.PEOPLE_NUMBER = B.PEOPLE_NUMBER             and A.YEAR = '2011'             and A.CITY_NUMBER = 'M00000'             and A.TOWN_NUMBER = 'M51000')    set JOIN_STATE_A = JOIN_STATE_B；