oracle sql 优化之update多表

stevenberg

已于 2024-03-16 21:12:58 修改

阅读量1.9k

点赞数 24

CC 4.0 BY-SA版权

分类专栏： ORACLE优化文章标签： oracle sql 数据库性能优化

于 2024-03-16 13:25:23 首次发布

本文链接：https://blog.youkuaiyun.com/stevenberg/article/details/136760713

执行较慢的待优化的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