oracle rowid and postgresql ctid

最新推荐文章于 2024-10-03 16:46:58 发布

iteye_14608

最新推荐文章于 2024-10-03 16:46:58 发布

阅读量218

点赞数

文章标签：数据库

首先介绍一下oracle rowid，数据库表行中的物理标识<div style=""> <div style="line-height:25px">SQL> select rowid from book_info where rownum<=1;</div> <div style="line-height:25px"> </div> <div style="line-height:25px">ROWID</div> <div style="line-height:25px">------------------</div> <div style="line-height:25px">AAAQTJAAaAAAAoaAAA</div> </div> <div style=""> </div> <div style=""> <p style="padding-bottom:0px; line-height:19px; text-indent:0px; margin:5px auto; padding-left:0px; padding-right:0px; font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; color:rgb(75,75,75); font-size:13px; padding-top:0px"> ROWID的格式如下： <p style="padding-bottom:0px; line-height:19px; text-indent:0px; margin:5px auto; padding-left:0px; padding-right:0px; font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; color:rgb(75,75,75); font-size:13px; padding-top:0px"> 数据对象编号文件编号块编号行编号 OOOOOO FFF BBBBBB RRR <p style="padding-bottom:0px; line-height:19px; text-indent:0px; margin:5px auto; padding-left:0px; padding-right:0px; font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; color:rgb(75,75,75); font-size:13px; padding-top:0px"> 由此看出，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) </div>