oracle rowid and postgresql ctid
首先介绍一下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
is
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
1 | apple
1 | apple
2 | orange
2 | orange
2 | orange
2 | orange
3 | banana
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)
