关于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,该视图...
9. 数据伪列的使用:数据伪列是指 Oracle 中的一种特殊的列,例如 ROWNUM 伪列、ROWID 伪列等。这些伪列可以用来对数据进行操作和查询。 10. SQL Developer 工具的使用:SQL Developer 是 Oracle 的一个开发工具,...
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...
本人在工作中,从测试库往正式库中复制记录,用select for update 报错:ORA-01480:STR 绑定值的结尾 Null字符缺失 ,遂上网查找,都说是有非法字符(半个引号) 或者长度超长造成的。根据本人分析,应该不是上述原因...
很多时候,一般的ORACLE用户不知道如何有效地利用它。 dictionary 全部数据字典表的名称和解释,它有一个同义词dict dict_column 全部数据字典表里字段名称和解释 如果我们想查询跟索引有关的数据字典时,...
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...