Oracle伪列ROWID和ROWNUM

最新推荐文章于 2024-12-10 11:22:25 发布

weixin_33766805

最新推荐文章于 2024-12-10 11:22:25 发布

阅读量92

点赞数

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/liuyuanyuangogo/blog/318648

本文详细解释了Oracle数据库中ROWID和ROWNUM的含义与使用方法，包括ROWID的组成、转换和作用，以及如何利用ROWNUM进行查询结果集的行号筛选。同时介绍了ROWID在非分区表并行更新与删除操作中的应用。

2019独角兽企业重金招聘Python工程师标准>>>

1.ROWID

ROWID数据类型介绍：

ROWID伪劣是BASE64编码的，oracle表的每行数据都有一个rowid值。与数据类型ROWID含义有所不同。

官网介绍：http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#i46148

ROWID类型组成详解：

ROWID伪劣值由18位组成，其中末三位，表示该行数据在表中的位置。如下所示：

ROWID结构变过过程：http://www.orafaq.com/wiki/ROWID

第一行数据的位置标记AAA，其中A在base64编码中表示0，即是0。所以说，表行数据的ROWID是从0（base64 表示为：AAA）开始的。

SQL> select rowid,substr(rowid, 1, 6) "#objct", substr(rowid, 7, 3) "#file",
  2  substr(rowid, 10, 6) "#block", substr(rowid, 16, 3) "#row"
  3  from clobtest where id =1;

ROWID              #objct       #file  #block       #row
------------------ ------------ ------ ------------ ------
AAANfCAAGAAAP3XAAA AAANfC       AAG    AAAP3X       AAA

rowid	AAANFC	AAG	AAAP3X	AAA
说明	数据对象号	相对文件号	数据块号	行号

base 64编码

索引	对应字符	索引	对应字符	索引	对应字符	索引	对应字符
0	A	17	R	34	i	51	z
1	B	18	S	35	l	52	0
2	C	19	T	36	k	53	1
3	D	20	U	37	l	54	2
4	E	21	V	38	m	55	3
5	F	22	W	39	n	56	4
6	G	23	X	40	o	57	5
7	H	24	Y	41	p	58	6
8	I	25	Z	42	q	59	7
9	J	26	a	43	r	60	8
10	K	27	b	44	s	61	9
11	L	28	c	45	t	62	+
12	M	29	d	46	u	63	/
13	N	30	e	47	v
14	O	31	f	48	w
15	P	32	g	49	x
16	Q	33	h	50	y

参考资料: http://blog.youkuaiyun.com/chiclewu/article/details/25782619

ROWID能作为行标记，不能作为顺序行号，其中一个原因是rowid实在行数据插入时确定的，如果中间出现删除，那么该行后面的行的rowid不能改变，如下：

SQL> create table bb(id int);

表已创建。

SQL> insert into bb values(1);

已创建 1 行。

SQL> insert into bb values(2);

已创建 1 行。

SQL> insert into bb values(3);

已创建 1 行。

SQL> insert into bb values(4);

已创建 1 行。

SQL> select rowid,id from bb;

ROWID                      ID
------------------ ----------
AAANe0AAGAAAHEoAAA          1
AAANe0AAGAAAHEoAAB          2
AAANe0AAGAAAHEoAAC          3
AAANe0AAGAAAHEoAAD          4

SQL> delete from bb where id=3
  2  ;

已删除 1 行。

SQL> select rowid,id from bb;

ROWID                      ID
------------------ ----------
AAANe0AAGAAAHEoAAA          1
AAANe0AAGAAAHEoAAB          2
AAANe0AAGAAAHEoAAD          4

SQL>

ROWID类型转换成十进制数字类型：

http://www.oracleonlinux.cn/2011/11/whats-oracle-rowid/

2.ROWNUM

rownum是查询结果集的行编号，但是结果集中必须包含rownum=1的情况，否则查询不到结果。

通过rownum伪列可以查询查询行号在某一区间的行数据，比如：查询出表tab1的【51,100】的行的数据：

方法一，通过结果集的minus操作：

select xx from tab1 where rownum<=100 minus select xx from tab1 where rownum<=50;

但是clob类型不支持minus，所以包含clob类型字段的表不能使用基于rownum结果集的minus操作。

方法二，通过重新查询子查询的结果集：

该方法的特点是必须知道表的所有列名字，否则会结果只能是多出一列rownum来。

SELECT t.* FROM (SELECT f.*, rownum num FROM tab1 f) t
where num >= 51 and num <= 100;

参考：http://blog.youkuaiyun.com/wushanyun1989/article/details/10353229

3.用rowid分块实现非分区表的并行update与delete

http://www.oracledatabase12g.com/archives/%E5%88%A9%E7%94%A8rowid%E5%88%86%E5%9D%97%E5%AE%9E%E7%8E%B0%E9%9D%9E%E5%88%86%E5%8C%BA%E8%A1%A8%E7%9A%84%E5%B9%B6%E8%A1%8Cupdate%E4%B8%8Edelete.html

转载于:https://my.oschina.net/liuyuanyuangogo/blog/318648