oracle rowid and postgresql ctid

来源：互联网发布：c语言中temp 编辑：程序博客网时间：2024/05/29 18:05

首先介绍一下oracle rowid，数据库表行中的物理标识

SQL> select rowid from book_info where rownum<=1;

ROWID

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

AAAQTJAAaAAAAoaAAA

ROWID的格式如下：

数据对象编号文件编号块编号行编号
OOOOOO FFF BBBBBB RRR

由此看出，AAAQTJ是数据对象编号，AAa是文件编号，AAAAoa是块编号，AAA是行编号

select rowid ,

substr(rowid,1,6) "OBJECT",

substr(rowid,7,3) "FILE",

substr(rowid,10,6) "BLOCK",

substr(rowid,16,3) "ROW"

from book_info where rownum<=5;

查出64位编码的值：

ROWID OBJECT FILE BLOCK ROW

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

AAAQTJAAaAAAAoCAAB AAAQTJ AAa AAAAoC AAB

AAAQTJAAaAAAAoCAAC AAAQTJ AAa AAAAoC AAC

AAAQTJAAaAAAAoCAAD AAAQTJ AAa AAAAoC AAD

AAAQTJAAaAAAAoCAAE AAAQTJ AAa AAAAoC AAE

AAAQTJAAaAAAAoCAAF AAAQTJ AAa AAAAoC AAF

查出10进制的值：

SQL> select dbms_rowid.rowid_object(rowid) object_id, dbms_rowid.rowid_relative_fno(rowid) file_id,

dbms_rowid.rowid_block_number(rowid) block_id ,dbms_rowid.rowid_row_number(rowid) num from book_info where rownum<=5;

OBJECT_ID FILE_ID BLOCK_ID NUM

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

66761 26 2568 0

66761 26 2568 1

66761 26 2568 2

66761 26 2568 3

66761 26 2568 4

获取rowid函数：

create or replace function get_rowid

(l_rowid in varchar2)

return varchar2

ls_my_rowid varchar2(200);

rowid_type number;

object_number number;

relative_fno number;

block_number number;

row_number number;

begin

dbms_rowid.rowid_info(l_rowid,rowid_type,object_number,relative_fno, block_number, row_number);

ls_my_rowid := 'Object# is :'||to_char(object_number)||chr(10)||

'Relative_fno is :'||to_char(relative_fno)||chr(10)||

'Block number is :'||to_char(block_number)||chr(10)||

'Row number is :'||to_char(row_number);

return ls_my_rowid ;

end;

select get_rowid(rowid) from book_info where rownum<=1;

Object# is :66761

Relative_fno is :26

Block number is :2586

Row number is :0

当然最常用的是用rowid去除重复：

查出重复数据：

select a.rowid,a.* from 表名 a

where a.rowid !=

(

select max(b.rowid) from 表名 b

where a.字段1 = b.字段1 and

a.字段2 = b.字段2

)

删除重复数据：

delete from 表名 a

where a.rowid !=

(

select max(b.rowid) from 表名 b

where a.字段1 = b.字段1 and

a.字段2 = b.字段2

)

对于整行都重复的那么，可以使用distinct函数。

以下介绍下postgresql的ctid

testuser=# select ctid,* from t1 limit 1;

ctid | a

-------+-----------

(0,1) | 100000000

和oracle rowid类似也是一个物理字段，自动生成，不过结构和oracle rowid不一样，可以看到是(blockid，itemid)

ctid在数据更改后也会变化。

利用ctid去除重复数据：

建立测试表，插入数据：

testuser=# create table t2 (id int,name varchar(20));

CREATE TABLE

testuser=# insert into t2 values (1,'apple');

INSERT 0 1

testuser=# insert into t2 values (1,'apple');

INSERT 0 1

testuser=# insert into t2 values (1,'apple');

INSERT 0 1

testuser=# insert into t2 values (2,'orange');

INSERT 0 1

testuser=# insert into t2 values (2,'orange');

INSERT 0 1

testuser=# insert into t2 values (2,'orange');

INSERT 0 1

testuser=# insert into t2 values (2,'orange');

INSERT 0 1

testuser=# insert into t2 values (3,'banana');

INSERT 0 1

testuser=# insert into t2 values (3,'banana');

INSERT 0 1

testuser=# select * from t2;

id | name

----+--------

1 | apple

2 | orange

3 | banana

查询重复的数据：

testuser=# select ctid,* from t2 where ctid in (select min(ctid) from t2 group by id);

ctid | id | name

-------+----+--------

(0,1) | 1 | apple

(0,4) | 2 | orange

(0,8) | 3 | banana

删除重复数据并查看结果：

testuser=# delete from t2 where ctid not in (select min(ctid) from t2 group by id);

DELETE 6

testuser=# select * from t2;

id | name

----+--------

1 | apple

2 | orange

3 | banana

(3 rows)