当前位置:  数据库>oracle

Oracle中如何更新一张大表记录

    来源: 互联网  发布时间:2017-06-04

    本文导语: SQL语句是一种方便的语言,同样也是一种“迷惑性”的语言。这个主要体现在它的集合操作特性上。无论数据表数据量是1条,还是1亿条,更新的语句都是完全相同。但是,实际执行结果(或者能否出现结果)却是有很大的差异...

SQL语句是一种方便的语言,同样也是一种“迷惑性”的语言。这个主要体现在它的集合操作特性上。无论数据表数据量是1条,还是1亿条,更新的语句都是完全相同。但是,实际执行结果(或者能否出现结果)却是有很大的差异。
 
笔者在开发DBA领域的一个理念是:作为开发人员,对数据库、对数据要有敬畏之心,一个语句发出之前,起码要考虑两个问题:目标数据表的总数据量是多少(投产之后)?你这个操作会涉及到多大的数据量?不同的回答,处理的方案其实是不同的。
 
更新大表数据,是我们在开发和运维,特别是在数据迁移领域经常遇到的一种场景。上面两个问题的回答是:目标数据表整体就很大,而且更新范围也很大。一个SQL从理论上可以处理。但是在实际中,这种方案会有很多问题。
 
本篇主要介绍几种常见的大表处理策略,并且分析出他们的优劣。作为我们开发人员和DBA,选取的标准也是灵活的:根据你的操作类型(运维操作还是系统日常作业)、程序运行环境(硬件环境是否支持并行)和程序设计环境(是否可以完全独占所有资源)来综合考量决定。
 
首先,我们需要准备出一张大表。

1、环境准备

我们选择Oracle 11.2版本进行试验。

SQL> select * from v$version;

 

BANNER

--------------------------------------------------------------------------------
 
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0    Production

 

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

 

 

准备一张大表。

 

 

SQL> create table t as select * from dba_objects;

Table created

 

SQL> insert into t select * from t;

72797 rows inserted

 

SQL> insert into t select * from t;

145594 rows inserted

 

(篇幅原因,中间过程略……)

SQL> commit;

Commit complete

 

 

SQL> select bytes/1024/1024/1024 from dba_segments where owner='SYS' and segment_name='T';
 
 

BYTES/1024/1024/1024

--------------------

 1.0673828125

 

SQL> select count(*) from t;

 

COUNT(*)

----------

 9318016

 

Executed in 14.711 seconds

 

 

数据表T作为数据来源,一共包括9百多万条记录,合计空间1G左右。笔者实验环境是在虚拟机上,一颗虚拟CPU,所以后面进行并行Parallel操作的方案就是示意性质,不具有代表性。
 
下面我们来看最简单的一种方法,直接update。

 

2、方法1:直接Update

 

最简单,也是最容易出问题的方法,就是“不管三七二十一”,直接update数据表。即使很多老程序员和DBA,也总是选择出这样的策略方法。其实,即使结果能出来,也有很大的侥幸成分在其中。
 
我们首先看笔者的实验,之后讨论其中的原因。先创建一张实验数据表t_target。

 

 

SQL> create table t_targettablespace users as select * from t;

Table created

 

 

SQL> update t_target set owner=to_char(length(owner));

(长时间等待……)

 

 

在等待期间,笔者发现如下几个现象:

ü  数据库服务器运行速度奇慢,很多连接操作速度减缓,一段时间甚至无法登陆;

ü  后台会话等待时间集中在数据读取、log space buffer、空间分配等事件上;

ü  长期等待,操作系统层面开始出现异常。Undo表空间膨胀;

ü  日志切换频繁;

此外,选择这样策略的朋友还可能遇到:前台错误抛出异常、客户端连接被断开等等现象。

笔者遇到这样的场景也是比较纠结,首先,长时间等待(甚至一夜)可能最终没有任何结果。最要命的是也不敢轻易的撤销操作,因为Oracle要进行update操作的回滚动作。一个小时之后,笔者放弃。
 
 

 

updatet_target set owner=to_char(length(owner))

ORA-01013: 用户请求取消当前的操作

(接近一小时未完成)

 

 

之后就是相同时间的rollback等待,通常是事务执行过多长时间,回滚进行多长时间。期间,可以通过x$ktuxe后台内部表来观察、测算回滚速度。这个过程中,我们只有“乖乖等待”。
 
 

 

SQL> select KTUXESIZ from x$ktuxe where KTUXESTA'INACTIVE';

 

  KTUXESIZ

----------

    62877

(……)

 

SQL> select KTUXESIZ from x$ktuxe where KTUXESTA'INACTIVE';

 

  KTUXESIZ

----------

      511

 

 

综合这种策略的结果通常是:同业抱怨(影响了他们的作业执行)、提心吊胆(不知道执行到哪里了)、资源耗尽(CPU、内存或者IO占到满)、劳而无功(最后还是被rollback)。如果是正式投产环境,还要承担影响业务生产的责任。
 
我们详细分析一下这种策略的问题:

首先,我们需要承认这种方式的优点,就是简单和片面的高效。相对于在本文中其他介绍的方法,这种方式代码量是最少的。而且,这种方法一次性的将所有的任务提交给数据库SQL引擎,可以最大程度的发挥系统一个方面(CPU、IO或者内存)的能力。
 
如果我们的数据表比较小,经验值在几万一下,这种方法是比较合适的。我们可以考虑使用。

另一方面,我们要看到Oracle Update的另一个方面,就是Undo、Redo和进程工作负载的问题。熟悉Oracle的朋友们知道,在DML操作的时候,Undo和Redo是非常重要的方面。当我们在Update和Delete数据的时候,数据块被修改之前的“前镜像”就会保存在Undo Tablespace里面。注意:Undo Tablespace是一种特殊的表空间,需要保存在磁盘上。Undo的存在主要是为了支持数据库其他会话的“一致读”操作。只要事务没有被commit或者rollback,Undo数据就会一直保留在数据库中,而且不能被“覆盖”。
 
Redo记录了进行DML操作的“后镜像”,Redo生成是和我们修改的数据量相关。现实问题要修改多少条记录,生成的Redo总量是不变的,除非我们尝试nologging选项。Redo单个日志成员如果比较小,Oracle应用生成Redo速度比较大。Redo Group切换频度高,系统中就面临着大量的日志切换或者Log Space Buffer相关的等待事件。
 
如果我们选择第一种方法,Undo表空间就是一个很大的瓶颈。大量的前镜像数据保存在Undo表空间中不能释放,继而不断的引起Undo文件膨胀。如果Undo文件不允许膨胀(autoextend=no),Oracle DML操作会在一定时候报错。即使允许进行膨胀,也会伴随大量的数据文件DBWR写入动作。这也就是我们在进行大量update的时候,在event等待事件中能看到很多的DBWR写入。因为,这些写入中,不一定都是更新你的数据表,里面很多都是Undo表空间写入。
 
同时,长时间的等待操作,触动Oracle和OS的负载上限,很多奇怪的事情也可能出现。比如进程僵死、连接被断开。

这种方式最大的问题在于rollback动作。如果我们在长时间的事务过程中,发生一些异常报错,通常是由于数据异常,整个数据需要回滚。回滚是Oracle自我保护,维持事务完整性的工具。当一个长期DML update动作发生,中断的时候,Oracle就会进入自我的rollback阶段,直至最后完成。这个过程中,系统是比较运行缓慢的。即使重启服务器,rollback过程也会完成。
 
所以,这种方法在处理大表的时候,一定要慎用!!起码要评估一下风险。

更多详情见请继续阅读下一页的精彩内容:


    
 
 

您可能感兴趣的文章:

  • 国内哪里可以下载oracle8.1.7或更新版本
  • Oracle 2010年4月更新修复Oracle协作套件安全漏洞
  • Oracle 2010年7月更新修复Oracle Fusion中间件安全漏洞
  • oracle更新xml节点问题的一些细节
  • Oracle 2010年7月更新修复多个Oracle Database安全漏洞
  • Oracle 2010年7月更新修复Transportation Manager安全漏洞
  • Oracle 2010年7月更新修复Enterprise Manager Grid Control安全漏洞
  • jsp如何选择更新oracle的date字段
  • Oracle 2010年7月更新修复多个PeopleSoft安全漏洞
  • Oracle 2010年7月更新修复多个E-Business Suite安全漏洞
  • Oracle 2010年4月更新修复多个E-Business Suite安全漏洞
  • Oracle多表级联更新详解
  • ORACLE学习笔记-添加更新数据函数篇
  • 如何用不算很熟练的jsp,oracle,javascript,html,css等建设动态网站,要网页打开速度快、易于日常维护更新?
  • 利用可更新ResultSet的updateBinaryStream()可以把图片存到mySql数据库,却不能存到Oracle数据库,怎么办?
  • 批量更新关联表(oracle、sql server)
  • 为什么我用javabean更新不了数据库?javabean,oracle高手请进!!!!!高分相赠!!!在线等待中。。。
  •  
    本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
    本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • 从jsp想oracle插入记录的顺序问题
  • 紧急求救:为什么oracle只能选择一定数据的记录
  • 记录Linux下一次oracle启动错误
  • ejb的bmp向oracle表插入图片记录的问题!
  • 请问怎么用jsp语句删除oracle中的一条记录?
  • 给200分:oracle的jdbc有BUG??为何在servlet中记录数不能超过120条?
  • 关于JDBC访问Oracle返回数据集的记录限制的问题
  • Oracle 如何快速查找和删除重复记录
  • Oracle 当前用户下所有表的记录总数
  • Oracle中用Rowid查找和删除重复记录
  • 如何确定Oracle数据库表重复的记录
  • shell向oracle插记录 小问题送分了,谢谢
  • 利用ASP来实现Oracle数据记录的分页显示
  • Oracle数据库设置任务计划备份一周的备份记录
  • Linux(Oracle系统在上面)系统无缘无故死机 , 可能是由于应用程序引起 , 可是由于重新启动查不到相关信息 , 不知道在哪里有记录系统CPU Lo
  • MySQL数据迁移到Oracle记录
  • Oracle中取固定记录数详细步骤
  • Oracle基本操作全记录
  • 编程语言 iis7站长之家
  • 在oracle下要在同一事务下插入多条记录,该怎么做??最好要有原代码
  • Oracle 12c发布简单介绍及官方下载地址
  • 在linux下安装oracle,如何设置让oracle自动启动!也就是让oracle那个服务自动启动,不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同?
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • 虚拟机装Oracle R12与Oracle10g
  • Oracle数据库(Oracle Database)体系结构及基本组成介绍
  • Oracle 数据库开发工具 Oracle SQL Developer
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • Oracle EBS R12 支持 Oracle Database 11g
  • Oracle 10g和Oracle 11g网格技术介绍


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,