oracle update 效率低 怎么办
发布网友
发布时间:2022-04-08 03:47
我来回答
共2个回答
懂视网
时间: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
一种,看执行计划,是不是全表扫描什么的。对更新语句进行优化;
再一种,可以用merge into 来代替update ,作为oracle独有的一个关键词,挺好用的
如何提高Oracle大数据表Update效率
后来尝试过的改进办法有:1.把表上的LOGGING取消2.把表上的INDEX取消但是依然很慢,无奈下找到这个:?p=100:11:0:::P11_QUESTION_ID:6407993912330在这个主题问答里,ORA官方提了一种处理的办法:1.利用CREATE table as select xxxxx的办法来生成一新表T12.在T1上创建与目标表一样的索引3.把目标表...
oracleselect很快update很慢
解决方案1: 这种比较没什么意义。需要注意的一点是,select 涉及的是共享锁, 在nolock提示下可以保证不死锁。update涉及的是更新锁和排他锁,没有锁 提示可以用,在一定条件下会等待很长时间,甚至死锁。解决方案2: 可以自己测试一下,根据主键查询的话,会从数据库里找到匹配该主键的一条数据,update的话,...
oracle update 多个字段更新性能
1.使用单条 SQL 语句更新多个字段:通过一条 SQL 语句同时更新多个字段可以减少数据库服务器和客户端之间的通信量,从而提高性能。示例语句如下:UPDATE table_name SET column1=value1,column2=value2,column3=value3 WHERE condition;2.使用索引:在更新多个字段时,如果表中存在索引,可以加快更新的速...
oracle存储过程update执行很慢(50多万条需要更新),求大神指点
哥们,使用中间表试试. 也许能快一些 -- 1.先把需要修改的数据查询出来 ,放入中间表1 DROP TABLE T_TABLE1;CREATE TABLE T_TABLE1 AS SELECT * FROM T_TABLE WHERE ...;DELETE TABLE T_TABLE1 WHERE ...;-- fun_c(C) 代表C 字段更新逻辑的函数.DROP TABLE T_TABLE2;CREATE TABLE T_...
想update一个大表,数据量四千万左右,如何通过调整Oracle数据库的某些...
1.如果是在线事务系统,那么建立合适的索引非常重要,其次通过分区技术设立合理的分区键也可以大大提升更新前的查找定位效率。2.如果是后台批量数据分析系统,一次需要更新表中数据的大部分,那么可以考虑使用临时表过度,采用insert到临时表再替换原表的方式代替update操作,这样效率的提升非常可观,对系统的...
在oracle数据库中用循环动态执行update语句很慢,如何解决
你数据有那么大,更新肯定是很慢的,你可以从下面两个方面考虑:一、你要在3千万的数据里面查询,一定要走索引,就是对查询条件的字段建立索引 二、插入到的表和更新的表如果太大,如果插入非常频繁,可以插入取消部分索引。
ORACLE优化SQL语句,提高效率(2)
使用索引同样能提高效率 虽然使用索引能得到查询效率的提高 但是我们也必须注意到他的代价 索引需要空间来存储 也需要定期维护 每当有记录在表中增减或索引列被修改时 索引本身也会被修改 这意味着每条记录的INSERT DELETE UPDATE将为此多付出 次的磁盘I/O 因为索引需要额外的存储空间和处理 那些不必要的...
如何加速Oracle大批量数据处理?
3、如果你的服务器有多个cpu,采用parellel hint,可以大幅度的提高效率 另外,建表的参数非常重要,对于更新非常频繁的表,建议加大PCTFREE的值,以保证数据块中有足够的空间用于UPDATE, 从而降低CHAINED_ROWS。二、 各种批量DML操作:(1)、oracle批量拷贝:set arraysize 20 set copycommit 5000 c...
Oracle Update执行效率的一个小问题.我有1000条数据flag有等于1 也有...
1. 看数据量和索引情况:2. 如果=0的极少,而flag又有索引的话,那么用 update xx set flag =1 where flag = 0 会很快。3. 如果=0的极多,不过有没有索引,update xx set flag = 1 和 update xx set flag =1 where flag = 0 的效率应该一样,都是全部扫描。
oracle update 效率问题
在使用implict update table 时,发现其中一个表一定要有唯一约束,否则会报错!但是oracle可以使用hints:/*+ BYPASS_UJVC*/ 屏蔽掉队唯一性的检查。这个应该对性能影响不大。对于 /*+ USE_HASH(TABLE) */ use_hash提示对指定的标志性一个散列连接。将指定的表与其他行源通过哈希连接方式连接起来.从...