关于ROWID的介绍参考我的Blog:
Oracle Rowid 介绍
http://blog.csdn.net/tianlesoftware/archive/2009/12/16/5020718.aspx
关于大表Update 的一个讨论,参考itpub:
http://www.itpub.net/viewthread.php?tid=1052077
一. 在虚拟机上 使用rowid 进行update 测试
使用rowid 进行update能提高速度,是因为通过rowid 能够迅速的进行定位,不用全表进行扫描。
-- 查看表dave 记录数
SYS@dave2(db2)> select count(*) from dave;
COUNT(*)
----------
3080115 --300万数据
-- 创建测试表dba
SYS@dave2(db2)> create table dba as select * from dave;
Table created.
--用dave 表去更新DBA表
SYS@dave2(db2)> update dba ta set prov_code=(select area_code from dave tb where ta.id=tb.id);
3080115 rows updated.
Elapsed: 00:16:12.81 -- 整个更新花了16分钟
--update 期间查看session 执行时间:
SQL>select sid,target,time_remaining,elapsed_seconds,message,sql_id from v$session_longops where sid=138;
select * from v$lock where sid=138;
select * from v$session_wait where sid=138;
-- 使用rowid 进行更新
DECLARE
CURSOR cur IS
SELECT
a.area_code, b.ROWID ROW_ID
FROM dave a, dba b
WHERE a.id = b.id
ORDER BY b.ROWID; ---如果表的数据量不是很大,可以不用 order by rowid
V_COUNTER NUMBER;
BEGIN
V_COUNTER := 0;
FOR row IN cur LOOP
UPDATE dba
SET prov_code = row.area_code
WHERE ROWID = row.ROW_ID;
V_COUNTER := V_COUNTER + 1;
IF (V_COUNTER >= 1000) THEN
COMMIT;
V_COUNTER := 0;
END IF;
END LOOP;
COMMIT;
END;
PL/SQL procedure successfully completed.
Elapsed: 00:14:54.07 -- 执行花了14分钟,速度提高不是很多。
在这个更新中,使用了ORDER BY b.ROWID 进行了排序,每个数据块里面都有多条记录,这样按rowid 进行排序,那么这样每次访问数据块的时候就会相同,就会减小block 在调用的次数,从而提高效率。
因为我这是虚拟机上的测试环境,所以内存分配的并不合适,I/O 也不行。
--我们把order by 去掉,在更新看看
DECLARE
CURSOR cur IS
SELECT
a.area_code, b.ROWID ROW_ID
FROM dave a, dba b
WHERE a.id = b.id;
V_COUNTER NUMBER;
BEGIN
V_COUNTER := 0;
FOR row IN cur LOOP
UPDATE dba
SET prov_code = row.area_code
WHERE ROWID = row.ROW_ID;
V_COUNTER := V_COUNTER + 1;
IF (V_COUNTER >= 1000) THEN
COMMIT;
V_COUNTER := 0;
END IF;
END LOOP;
COMMIT;
END;
PL/SQL procedure successfully completed.
Elapsed: 00:20:24.43
-- 居然用了21分钟,看来对大表还是很有必要进行order by rowid的。
二. 在测试服务器上测试
折腾了半天没有折腾出效果来。将数据dump 出来,在imp 到测试服务器,300w的数据,dump 文件有300M。
--在测试服务器上直接update
SQL> update dba ta set prov_code=(select area_code from dave tb where ta.id=tb.id);
update dba ta set prov_code=(select area_code from dave tb where ta.id=tb.id)
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
Elapsed: 00:20:45.04
一直的处理中. 被迫取消。 查看了一下session的状态:
SQL>select sid,target,time_remaining,elapsed_seconds,message,sql_id from v$session_longops where sid=197;
等了20分钟,才8个blocks,要处理到39521个blocks,不知道要到那个猴年马月了。 居然比我虚拟机上测试的还慢。
-- 在测试服务器上使用rowid + order by
DECLARE
CURSOR cur IS
SELECT
a.area_code, b.ROWID ROW_ID
FROM dave a, dba b
WHERE a.id = b.id
ORDER BY b.ROWID; ---如果表的数据量不是很大,可以不用 order by rowid
V_COUNTER NUMBER;
BEGIN
V_COUNTER := 0;
FOR row IN cur LOOP
UPDATE dba
SET prov_code = row.area_code
WHERE ROWID = row.ROW_ID;
V_COUNTER := V_COUNTER + 1;
IF (V_COUNTER >= 1000) THEN
COMMIT;
V_COUNTER := 0;
END IF;
END LOOP;
COMMIT;
END;
PL/SQL procedure successfully completed.
Elapsed: 00:04:45.98
-- 总算看到效果了,4分多钟搞定,如果在生产库上,这个操作应该还会快一点。
-- 在测试服务器上使用rowid
DECLARE
CURSOR cur IS
SELECT
a.area_code, b.ROWID ROW_ID
FROM dave a, dba b
WHERE a.id = b.id;
V_COUNTER NUMBER;
BEGIN
V_COUNTER := 0;
FOR row IN cur LOOP
UPDATE dba
SET prov_code = row.area_code
WHERE ROWID = row.ROW_ID;
V_COUNTER := V_COUNTER + 1;
IF (V_COUNTER >= 1000) THEN
COMMIT;
V_COUNTER := 0;
END IF;
END LOOP;
COMMIT;
END;
PL/SQL procedure successfully completed.
Elapsed: 00:09:06.73 -- 花了9分钟
通过以上测试,验证了对于大表的update,除了使用rowid,还需要根据rowid 排序一下。
-------------------------------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
Email: dvd.dba@gmail.com
DBA1 群:62697716(满); DBA2 群:62697977(满) DBA3 群:62697850(满)
DBA 超级群:63306533(满); DBA4 群: 83829929 DBA5群: 142216823
DBA6 群:158654907 聊天 群:40132017 聊天2群:69087192
--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请
分享到:
相关推荐
对一个大表进行UPDATE,DELETE,如果在一个SESSION里面运行SQL,很容易引发undo不够, 或者由于一些原因,导致回滚,这个是灾难
oracle动态性能表 学习动态性能表 第一篇--v$sysstat 2007.5.23 按照OracleDocument中的描述,v$sysstat存储自数据库实例运行那刻起就开始累计全实例(instance-wide)的资源使用情况。 类似于v$sesstat,该视图...
oracle 甲骨文 获得最高认证级别的ISO标准安全认证,性能最高, 保持开放平台下的TPC-D和TPC-C的世界记录。但价格不菲 大型企业 db2 IBM DB2在企业级的应用最为广泛, 在全球的500家最大的企业中,几乎85%以上用DB2...
很多时候,一般的ORACLE用户不知道如何有效地利用它。 dictionary 全部数据字典表的名称和解释,它有一个同义词dict dict_column 全部数据字典表里字段名称和解释 如果我们想查询跟索引有关的数据字典时,...
导致索引失效: 1、表上频繁发生update,delete操作; 2、表上发生了alter table ..move操作(move操作导致了rowid变化)。
1、表上频繁发生update,delete操作; 2、表上发生了alter table ..move操作(move操作导致了rowid变化)。 二、重建索引的标准 1、索引重建是否有必要,一般看索引是否倾斜的严重,是否浪费了空间, ...
第一部分 Oracle SQL*PLUS基础 23 第一章 Oracle数据库基础 23 §1.1 理解关系数据库系统(RDBMS) 23 §1.1.1 关系模型 23 §1.1.2 Codd十二法则 24 §1.2 关系数据库系统(RDBMS)的组成 24 §1.2.1 RDBMS 内核 24...
ORACLE函数大全 ________________________________________ 作者:[本站编辑] 来源:[CSDN] 浏览:[ ] SQL中的单记录函数 1.ASCII 返回与指定的字符对应的十进制数; SQL> select ascii('A') A,ascii('a') a,...
select * from student for update student表需要操作人修改完commit之后才可以做其他的操作,否则该表会被锁住。 二、方式二 select t.*,t.rowid from student t 在pl/sql developer中右击某表,显示的就是该...
SQL> select instr('oracle traning','ra',1,2) instring from dual; INSTRING --------- 9 6.LENGTH 返回字符串的长度; SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from ....
sql> alter index xay_id allocate extent(size 200k datafile \'c:/oracle/index.dbf\'); <8>.alter index xay_id deallocate unused; 、查看索引 SQL>select index_name,index_type,table_name from user...
很多时候,一般的ORACLE用户不知道如何有效地利用它。 dictionary 全部数据字典表的名称和解释,它有一个同义词dict dict_column 全部数据字典表里字段名称和解释 如果我们想查询跟索引有关的数据字典时,...
本人在工作中,从测试库往正式库中复制记录,用select for update 报错:ORA-01480:STR 绑定值的结尾 Null字符缺失 ,遂上网查找,都说是有非法字符(半个引号) 或者长度超长造成的。根据本人分析,应该不是上述原因...
UPDATE语句163 DELETE 语句166 从外部数据源中导入和导出数据169 Microsoft Access 170 Microsoft and Sybase SQL Server 171 Personal Oracle7171 总结172 问与答172 校练场173 练习173 第九天创建和操作表174 目标...
64位版本的 PLSQL 正式版,只能运行在64位系统中,需要你安装 64 位的 Oracle11g 或 Oracle12c 客户端。 安装中文包时请注意安装路径是否为PLSQL程序的路径。 备注:新版本Ribbon启用了 Ribbon 界面,改动较大,不...
64位版本的 PLSQL 正式版,只能运行在64位系统中,需要你安装 64 位的 Oracle11g 或 Oracle12c 客户端。 安装中文包时请注意安装路径是否为PLSQL程序的路径。 备注:新版本Ribbon启用了 Ribbon 界面,改动较大,不...
64位版本的 PLSQL 正式版,只能运行在64位系统中,需要你安装 64 位的 Oracle11g 或 Oracle12c 客户端。 安装中文包时请注意安装路径是否为PLSQL程序的路径。 备注:新版本Ribbon启用了 Ribbon 界面,改动较大,不...
The SQL Window will now navigate to the offending cell in the result set after an insert or update with a column-specific error. The rowid column is now omitted when exporting a result set grid in SQL...
The SQL Window will now navigate to the offending cell in the result set after an insert or update with a column-specific error. The rowid column is now omitted when exporting a result set grid in SQL...
The SQL Window will now navigate to the offending cell in the result set after an insert or update with a column-specific error. The rowid column is now omitted when exporting a result set grid in SQL...