`
koreyoshi
  • 浏览: 241915 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类
最新评论

Oracle RowID的应用(转)

 
阅读更多

转自:http://blog.csdn.net/mudalu626/article/details/8016569

RowID的概念请参考以下文章:http://blog.csdn.net/mudalu626/article/details/8014383


知道了RowID的概念,那么在什么场景下使用RowID呢。


1.分页
oracle中rowid高速分页解析

--rowid分页,第一步
select rowid rid,sal from emp order by sal desc;
--rowid分页,第二步
select rownum rn,rid from(select rowid rid,sal from emp order by sal desc) where rownum<10;
--rowid分页,第三步
select rid from(select rownum rn,rid from(select rowid rid,sal from emp order by sal desc) where rownum<10) where rn>5;
--rowid分页,第四步
select * from emp where rowid in(select rid from(select rownum rn,rid from(select rowid rid,sal from emp order by sal desc) where rownum<10) where rn>5);

****************************************************
第一层:获取数据物理地址
第二层:取得最大页数
第三层:取得最小页数
第四层:因为取得的页数都是物理地址,再根据物理地址,插叙出具体数据


但是这种方法的高效性是有前提的,具体请参考阿里数据库团队的这篇文章:
http://www.taobaodba.com/html/134_oracle_page_compare.html


2.海量数据场景下提高update性能
最近一直在折腾大表的更新问题,今天终于有了突破。兴奋之余发个帖子跟大家分享一下心得,并且讨论一下是否还可能进一步提高处理速度。
问题是这样的:一张5亿条记录的表,没有分区。由于增加了一个冗余字段,需要根据另外一张表(4.8亿条)更新这个大表。下面是具体的描述:
环境:HP-UX 11i+Oracle9.2.0.8+RAID
要更新的表:T1 (id1 number, id2 number, curr_count number,.....) --id1唯一 5亿条记录 >60GB
更新数据来源:T2 (id2 number, curr_count number) --id2唯一 4.8亿
更新逻辑:T2中的每一条记录,都到T1中找到对应的记录(T2.id2=T1.id2),更新T1.curr_count=T2.curr_count
限制条件:只能在线更新(应用程序一直在访问这个表,所以不能用INSERT SELECT),不能占用太多系统资源,要求3天之内更新完毕。
原来的做法:
declare
cursor cur_t2 is
select /*+ use_hash(T1,T2) parallel(T1,16) parallel_index(IX_T1_id2,16) */
T2.id2, T2.curr_count, T1.rowid row_id
from T1, T2
where T1.id2=T2.id2;
v_counter number;
begin
v_counter := 0;
for row_t2 in cur_t2 loop
update T1 set curr_count=row_t2.curr_count
where rowid=row_t2.row_id;
v_counter := v_counter + 1;
if (v_counter>=1000) then
commit;
v_counter := 0;
end if;
end loop;
commit;
end;
/
问题:更新太慢,260 rows/s,全部更新完毕需要22天!
经过调查发现是UPDATE语句执行的效率太低,进一步的跟踪发现,UPDATE至少90%的时间是在等待db file sequential read这个事件。按说都ROWID了,为什么还有这么多磁盘等待?再看disk reads,明白了,原来UPDATE语句产生了大量的物理读,当然慢了。想必T1表太大了,Data Buffer装不下,并且有其他的表跟它竞争,所以刚更新一条数据,从磁盘读取了一个数据块到内存,很快就被挤去出了,下次更新这个块上的其他数据时,还得再从磁盘读取。这样Data Buffer Cache的效率就很低,基本没有利用上。
怎么解决呢?最好是能按数据块的顺序更新,这样某个数据块里的第一行数据更新后,数据块内的其他行就不用再从磁盘里读取了(不太可能那么快就被挤出内存),物理读降低了,速度肯定能加快。可是怎样按数据块的顺序更新呢?我想到了ROWID的结构是data object number(6位字符串)+relative file number(3位字符串)+block number(6位字符串)+row number(3位字符串),那么ROWID的顺序应该就是数据块的顺序了。于是我修改了PLSQL:
alter table T1 storage(buffer_pool keep); -- keep buffer pool size = 6GB
declare
cursor cur_t2 is
select /*+ use_hash(T1,T2) parallel(T1,16) parallel_index(IX_T1_id2,16) */
T2.id2, T2.curr_count, T1.rowid row_id
from T1, T2
where T1.id2=T2.id2
order by T1.rowid;
v_counter number;
begin
v_counter := 0;
for row_t2 in cur_t2 loop
update T1 set curr_count=row_t2.curr_count
where rowid=row_t2.row_id;
v_counter := v_counter + 1;
if (v_counter>=1000) then
commit;
v_counter := 0;
end if;
end loop;
commit;
end;
/
alter table T1 storage(buffer_pool default);




这回更新的速度大为加快:10000 rows/s。分析跟踪文件表明db file sequential reads和磁盘读取变的很少。按照这个速度20个小时之内就能全部更新完了。




心得:处理的数据量并没有减少,只是改变一下处理的顺序,也可以极大地提高性能。








order by rowid导致大量的查询物理读。其实在本文第二个测试中也是这样的——CURSOR的打开时间比不ORDER BY ROWID时间要长,因为多了SORT。可是这样是值得的,因为后续有大量的UPDATE,节省的物理读是很客观的。
*后记:修改了几处错误:
where T1.id1=T2.id2 => where T1.id2=T2.id2
parallel_index(IX_T2_id2,16) => parallel_index(IX_T1_id2,16)


3.查找和删除重复数据
表CZ的结构如下:
SQL> desc cz
Name Null? Type
----------------------------------------- -------- ------------------
C1 NUMBER(10)
C10 NUMBER(5)
C20 VARCHAR2(3)
删除重复记录的方法原理:
(1).在Oracle中,每一条记录都有一个rowid,rowid在整个数据库中是唯一的,rowid确定了每条记录是在Oracle中的哪一个数据文件、块、行上。
(2).在重复的记录中,可能所有列的内容都相同,但rowid不会相同,所以只要确定出重复记录中那些具有最大rowid的就可以了,其余全部删除。
重复记录判断的标准是:
C1,C10和C20这三列的值都相同才算是重复记录。
经查看表CZ总共有16条记录:
SQL>set pagesize 100
SQL>select * from cz;
C1 C10 C20
---------- ---------- ---
1 2 dsf
1 2 dsf
1 2 dsf
1 2 dsf
2 3 che
1 2 dsf
1 2 dsf
1 2 dsf
1 2 dsf
2 3 che
2 3 che
2 3 che
2 3 che
3 4 dff
3 4 dff
3 4 dff
4 5 err
5 3 dar
6 1 wee
7 2 zxc
20 rows selected.
1.查找重复记录的几种方法:
(1).SQL>select * from cz group by c1,c10,c20 having count(*) >1;
C1 C10 C20
---------- ---------- ---
1 2 dsf
2 3 che
3 4 dff
(2).SQL>select distinct * from cz;
C1 C10 C20
---------- ---------- ---
1 2 dsf
2 3 che
3 4 dff
(3).SQL>select * from cz a where rowid=(select max(rowid) from cz where c1=a.c1 and c10=a.c10 and c20=a.c20);
C1 C10 C20
---------- ---------- ---
1 2 dsf
2 3 che
3 4 dff
2.删除重复记录的几种方法:
(1).适用于有大量重复记录的情况(在C1,C10和C20列上建有索引的时候,用以下语句效率会很高):
SQL>delete cz where (c1,c10,c20) in (select c1,c10,c20 from cz group by c1,c10,c20 having count(*)>1) and rowid not in
(select min(rowid) from cz group by c1,c10,c20 having count(*)>1);
SQL>delete cz where rowid not in(select min(rowid) from cz group by c1,c10,c20);
(2).适用于有少量重复记录的情况(注意,对于有大量重复记录的情况,用以下语句效率会很低):
SQL>delete from cz a where a.rowid!=(select max(rowid) from cz b where a.c1=b.c1 and a.c10=b.c10 and a.c20=b.c20);
SQL>delete from cz a where a.rowid<(select max(rowid) from cz b where a.c1=b.c1 and a.c10=b.c10 and a.c20=b.c20);
SQL>delete from cz a where rowid <(select max(rowid) from cz where c1=a.c1 and c10=a.c10 and c20=a.c20);
(3).适用于有少量重复记录的情况(临时表法):
SQL>create table test as select distinct * from cz; (建一个临时表test用来存放重复的记录)
SQL>truncate table cz; (清空cz表的数据,但保留cz表的结构)
SQL>insert into cz select * from test; (再将临时表test里的内容反插回来)
(4).适用于有大量重复记录的情况(Exception into 子句法):
采用alter table 命令中的 Exception into 子句也可以确定出库表中重复的记录。这种方法稍微麻烦一些,为了使用“excepeion into ”子句,必须首先创建 EXCEPTIONS 表。创建该表的 SQL 脚本文件为 utlexcpt.sql 。对于win2000系统和 UNIX 系统, Oracle 存放该文件的位置稍有不同,在win2000系统下,该脚本文件存放在$ORACLE_HOMEOra90rdbmsadmin 目录下;而对于 UNIX 系统,该脚本文件存放在$ORACLE_HOME/rdbms/admin 目录下。
具体步骤如下:
SQL>@?/rdbms/admin/utlexcpt.sql
Table created.
SQL>desc exceptions
Name Null? Type
----------------------------------------- -------- --------------
ROW_ID ROWID
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
CONSTRAINT VARCHAR2(30)
SQL>alter table cz add constraint cz_unique unique(c1,c10,c20) exceptions into exceptions;
*
ERROR at line 1:
ORA-02299: cannot validate (TEST.CZ_UNIQUE) - duplicate keys found
SQL>create table dups as select * from cz where rowid in (select row_id from exceptions);
Table created.
SQL>select * from dups;
C1 C10 C20
---------- ---------- ---
1 2 dsf
1 2 dsf
1 2 dsf
1 2 dsf
2 3 che
1 2 dsf
1 2 dsf
1 2 dsf
1 2 dsf
2 3 che
2 3 che
2 3 che
2 3 che
3 4 dff
3 4 dff
3 4 dff
16 rows selected.
SQL>select row_id from exceptions;
ROW_ID
------------------
AAAHD/AAIAAAADSAAA
AAAHD/AAIAAAADSAAB
AAAHD/AAIAAAADSAAC
AAAHD/AAIAAAADSAAF
AAAHD/AAIAAAADSAAH
AAAHD/AAIAAAADSAAI
AAAHD/AAIAAAADSAAG
AAAHD/AAIAAAADSAAD
AAAHD/AAIAAAADSAAE
AAAHD/AAIAAAADSAAJ
AAAHD/AAIAAAADSAAK
AAAHD/AAIAAAADSAAL
AAAHD/AAIAAAADSAAM
AAAHD/AAIAAAADSAAN
AAAHD/AAIAAAADSAAO
AAAHD/AAIAAAADSAAP
16 rows selected.
SQL>delete from cz where rowid in ( select row_id from exceptions);
16 rows deleted.
SQL>insert into cz select distinct * from dups;
3 rows created.
SQL>select *from cz;
C1 C10 C20
---------- ---------- ---
1 2 dsf
2 3 che
3 4 dff
4 5 err
5 3 dar
6 1 wee
7 2 zxc
7 rows selected.
从结果里可以看到重复记录已经删除。

分享到:
评论

相关推荐

    Oracle数据库rowid深入探析.pdf

    Oracle数据库rowid的应用 在实际应用中,rowid可以用于很多方面,例如: (1) 唯一标识表中的每一行 (2) 优化查询性能 (3) 实现高效的数据检索 (4) 提高数据库的安全性 Oracle数据库rowid是一个非常重要的概念...

    基于Oracle RowID实现批量数据的分页下载.pdf

    本文重点探讨了一种基于Oracle RowID实现批量数据分页下载的方法,这种方法可以有效提高效率并减少对数据库的压力。 RowID是Oracle数据库中用于唯一标识每一行数据的物理地址,它不是用户可见的列,但可以在SQL查询...

    利用rowid快速在线更新海量数据

    **ROWID** 是 Oracle 数据库中一个非常重要的概念,它是一种特殊类型的列,用于唯一标识表中的一行数据。ROWID 的格式通常包含数据对象号 (Data Object Number)、相对文件号 (Relative File Number)、块号 (Block ...

    试论Rowid在Oracle数据库中的应用.pdf

    Rowid在Oracle数据库中的另一个重要应用是恢复已删除的数据。在数据被删除后,尽管表内看不到数据,但是对应的Rowid还在Oracle的回收站中保留一段时间。利用这些Rowid,可以恢复被删除的数据行。 除了使用Rowid的...

    浅析Rowid在Oracle数据库中的应用.pdf

    在Oracle中,Rowid扮演着关键角色,它是一种伪列,用于唯一标识数据库中的每一行记录。Rowid包含了记录的物理存储位置信息,使得数据库能够快速定位和访问数据。 Rowid分为不同类型:Foreign Rowid、Logical Rowid...

    rowid与rownumber

    rowid 与 rownumber 的介绍和应用 rowid 和 rownum 是 Oracle 数据库中的两个重要概念,对于...了解 rowid 和 rownum 的概念、结构和应用场景,可以帮助开发人员更好地使用 Oracle 数据库,提高数据库的性能和效率。

    oracle_SQL中rowid与rownum的使用

    五、ROWID应用示例 `ROWID`可以直接用于查询,例如: ```sql SELECT * FROM table WHERE ROWID = 'AAEAAQAAAAAAAABfAAAAJGQxZmRjYzYtMzQ4Mi00NjY1LThiZjEtNzI5ODg5MzU3MzYy'; ``` 这将直接定位并返回具有指定`ROWID`...

    Oracle中的rowid

    在数据库操作中,ROWID被广泛应用于数据检索,特别是通过索引进行查询时。B-Tree索引的每个索引条目由两部分组成:键值和对应的ROWID。键值用于匹配查询条件,ROWID则用于快速定位到实际的数据行。因此,索引极大地...

    oracle_SQL中ROWID与ROWNUM的使用

    ### Oracle SQL中ROWID与ROWNUM的使用 #### ROWNUM 的使用——TOP-N 分析 在 Oracle SQL 中,`ROWNUM` 是一种特殊的伪列,用于标识查询结果集中的行编号。它常用于实现 TOP-N 查询,即返回结果集中满足特定条件的...

    oracle到sqlserver存储过程语法转换

    - **Oracle**: 使用`ROWID`和`ROWNUM`进行行标识和计数。 ```sql SELECT * FROM table WHERE ROWNUM ; ``` - **SQL Server**: 使用`TOP`来限制返回的行数。 ```sql SELECT TOP 10 * FROM table; ``` **5. ...

    oracle_SQL-rowid--rownum.zip_oracle

    在Oracle SQL中,`ROWID`和`ROWNUM`是两个非常重要的概念,它们在数据库查询和操作中扮演着关键角色。本篇文章将详细解析这两个概念及其应用。 **ROWID** `ROWID`是Oracle数据库中一个特殊的伪列,它为每一行数据...

    在oracle中灵活使用Rownum和rowId

    综上所述,`ROWNUM` 和 `ROWID` 在Oracle数据库中都有其独特的应用场景。`ROWNUM` 主要用于限制查询结果的数量或实现分页查询等功能;而 `ROWID` 则主要用于快速定位和稳定查询特定记录。在实际开发过程中,根据需求...

    Oracle与DB2数据类型分类对应说明

    Oracle 中的 ROWID 虚拟列用于对表中的某一列进行唯一标示,DB2/400 中也有这样的数据类型 ROWID,它与 Oracle 中的 ROWID 的功能相似。DB2/400 中的 ROWID 可以存放 40 byte 的数据用来唯一标示表中的每一行。 ...

    ORACLE与DB2的区别和转换

    - **解析**: Oracle中的ROWID是一种特殊的数据类型,可以唯一标识表中的每一行。DB2在v8版本中增加了对类似ROWID的支持,允许用户更方便地进行行级别的操作。 ##### 7. 数字转换 **Oracle**: `select to_number('...

    Oracle.DataAccess.Client_dll

    总的来说,Oracle.DataAccess.Client.dll是C#开发Oracle应用的重要工具,它提供了全面的类库来支持各种数据库操作,并且支持高级特性如查询通知,大大增强了.NET开发者在Oracle环境下的编程能力。在实际项目中,理解...

    SQL_Server_vs_Oracle_存储过程语法转换1.30.doc

    在数据库管理领域,SQL Server 和 Oracle 是两种广泛应用的关系型数据库管理系统。它们在语法上存在一些显著的差异,尤其是在处理存储过程和函数时。以下是一些关键的语法转换点,可以帮助理解如何从 Oracle 转换到 ...

    ORACLE到DB2应用移植方法探讨.pdf

    Oracle 特色的数据和功能 (ROWID,ROWNumber,%TYPE,%ROWTYPE) 以及集合类型 (联合数组,嵌套表,varrays) DB2 不支持。 四、移植方法探讨 方法 1:对 ORACLE 和 DB2 中各应用对象进行分析,找出对照的规则,设计...

Global site tag (gtag.js) - Google Analytics