优化Oracle中的Update语句

发布网友发布时间：2022-04-08 03:47

共3个回答

懂视网时间：2022-04-08 08:08

1.     语法单表：UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值如：update t_join_situation set join_state=‘1‘whereyear=‘2011‘ 更新年度为“2011”的数据的join_state字段为“1”。如果更新的字段加了索引，更新时会重建索引，更新效率会慢。    多表关联，并把一个表的字段值更新到另一个表中的字段去： update 表a set a.字段1 = (select b.字段1 from 表b where a.字段2=b.字段2) where exists(select 1 from 表b where a.字段2=b.字段2) oracle的更新语句不通MSSQL那么简单易写，就算写出来了，但执行时可能会报这是由于set哪里的子查询查出了多行数据值，oracle规定一对一更新数据，所以提示出错。要解决这样必须保证查出来的值一一对应。 2.     原理 Update语句的原理是先根据where条件查到数据后，如果set中有子查询，则执行子查询把值查出来赋给更新的字段，执行更新。如：update 表a set a.字段1 = (select b.字段1 from 表b where a.字段2=b.字段2) where exists(select 1 from 表b where a.字段2=b.字段2)。查表a的所有数据，循环每条数据，验证该条数据是否符合exists(select 1 from 表b where a.字段2=b.字段2)条件，如果是则执行(select b.字段1 from 表b where a.字段2=b.字段2)查询，查到对应的值更新a.字段1中。关联表更新时一定要有exists(select 1 from 表b where a.字段2=b.字段2)这样的条件，否则将表a的其他数据的字段1更新为null值。二、         提高oracle更新效率的各种解决方案 1.     标准update语法当你需要更新的表是单个或者被更新的字段不需要关联其他表带过来，则最后选择标准的update语句，速度最快，稳定性最好，并返回影响条数。如果where条件中的字段加上索引，那么更新效率就更高。但对需要关联表更新字段时，update的效率就非常差。 2.     inline view更新法 inline view更新法就是更新一个临时建立的视图。如：update (select a.join_state asjoin_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‘) setjoin_state_a=join_state_b 括号里通过关联两表建立一个视图，set中设置好更新的字段。这个解决方法比写法较直观且执行速度快。但表B的主键一定要在where条件中，并且是以“=”来关联被更新表，否则报一下错误： 3.merge更新法 merge是oracle特有的语句，语法如下： MERGE INTO table_name alias1
USING (table|view|sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
    UPDATE table_name
    SET col1 = col_val1,
        col2     = col2_val
WHEN NOT MATCHED THEN
    INSERT (column_list) VALUES (column_values); 它的原理是在alias2中Select出来的数据，每一条都跟alias1进行 ON (join condition)的比较，如果匹配，就进行更新的操作(Update),如果不匹配，就进行插入操作(Insert)。执行merge不会返回影响的行数。Merge语句的写法比较繁琐，并且最多只能两个表关联，复杂的语句用merge更新法将力不从心且效率差。 4.快速游标更新法语法如： begin for cr in (查询语句) loop –-循环    --更新语句（根据查询出来的结果集合） endloop; --结束循环 end; oracle支持快速游标，不需要定义直接把游标写到for循环中，这样就方便了我们批量更新数据。再加上oracle的rowid物理字段（oracle默认给每个表都有rowid这个字段，并且是唯一索引），可以快速定位到要更新的记录上。例子如下： begin for cr in (select a.rowid,b.join_state 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‘) loop update t_join_situation set join_state=cr.join_state where rowid = cr.rowid; endloop; end; 使用快速游标的好处很多，可以支持复杂的查询语句，更新准确，无论数据多大更新效率仍然高，但执行后不返回影响行数。三、结论

方案	建议
标准update语法	单表更新或较简单的语句采用使用此方案更优。
inline view更新法	两表关联且被更新表通过关联表主键关联的，采用此方案更优。
merge更新法	两表关联且被更新表不是通过关联表主键关联的，采用此方案更优。
快速游标更新法	多表关联且逻辑复杂的，采用此方案更优。

实时测试的速度： --48466条数据 --1.297 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 --7.156 update t_join_situation a set a.join_state=(select b.join_state from 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‘) whereexists (select1from 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‘) --3.281 begin for cr in (select a.rowid,b.join_state 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‘) loop update t_join_situation set join_state=cr.join_state where rowid = cr.rowid; endloop; end; --1.641 mergeinto t_join_situation a using t_people_info b on (a.people_number=b.people_number and a.year=‘2011‘and a.city_number=‘M00000‘and a.town_number=‘M51000‘) whenmatchedthenupdateset a.join_state=b.join_state

Oracle的update语句优化研究

标签：

热心网友时间：2022-04-08 05:16

一.SQL语言的使用

　　1.IN 操作符

　　用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。

　　但是用IN的SQL性能总是比较低的，从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：

　　ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。

　　由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。

　　推荐方案：在业务密集的SQL当中尽量不采用IN操作符

　　2.NOT IN操作符

　　此操作是强列推荐不使用的，因为它不能应用表的索引。

　　推荐方案：用NOT EXISTS 或(外连接+判断为空)方案代替

　　3.<> 操作符(不等于)

　　不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。

　　推荐方案：用其它相同功能的操作运算代替，如

　　a<>0 改为 a>0 or a<0

　　a<>'' 改为 a>''

更多内容见中国软件网，ORACLE SQL语句优化推荐方案

热心网友时间：2022-04-08 06:34

wa_data的主键是什么?