Oracle:搞过Oracle的一定对rowid比较熟悉啦，由下面基本含义组成
SELECT ROWID, DBMS_ROWID.ROWID_OBJECT(ROWID) AS OBJECT, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) AS FILENUM, DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS BLOCK, DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) AS ROWN FROM emp; ROWID OBJECT FILENUM BLOCK ROWN ------------------ ---------- ---------- ---------- ---------- AAAUIRAABAAAg8pAAA 82449 1 134953 0 AAAUIRAABAAAg8pAAB 82449 1 134953 1 AAAUIRAABAAAg8pAAC 82449 1 134953 2 AAAUIRAABAAAg8pAAD 82449 1 134953 3 AAAUIRAABAAAg8pAAE 82449 1 134953 4 AAAUIRAABAAAg8pAAF 82449 1 134953 5 AAAUIRAABAAAg8pAAG 82449 1 134953 6 AAAUIRAABAAAg8pAAH 82449 1 134953 7 AAAUIRAABAAAg8pAAI 82449 1 134953 8 AAAUIRAABAAAg8pAAJ 82449 1 134953 9 AAAUIRAABAAAg8pAAK 82449 1 134953 10 AAAUIRAABAAAg8pAAL 82449 1 134953 11 AAAUIRAABAAAg8pAAM 82449 1 134953 12 AAAUIRAABAAAg8pAAN 82449 1 134953 13 SQL>select OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID from dba_objects where OBJECT_NAME='EMP'; OBJECT_NAME OBJECT_ID DATA_OBJECT_ID -------------------- ---------- -------------- EMP 82449 82449 SQL>alter table emp move; SQL>select OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID from dba_objects where OBJECT_NAME='EMP'; OBJECT_NAME OBJECT_ID DATA_OBJECT_ID -------------------- ---------- -------------- EMP 82449 84675 ROWID OBJECT FILENUM BLOCK ROWN ------------------ ---------- ---------- ---------- ---------- AAAUrDAABAAAhSpAAA 84675 1 136361 0 AAAUrDAABAAAhSpAAB 84675 1 136361 1 AAAUrDAABAAAhSpAAC 84675 1 136361 2 AAAUrDAABAAAhSpAAD 84675 1 136361 3 AAAUrDAABAAAhSpAAE 84675 1 136361 4 AAAUrDAABAAAhSpAAF 84675 1 136361 5 AAAUrDAABAAAhSpAAG 84675 1 136361 6 AAAUrDAABAAAhSpAAH 84675 1 136361 7 AAAUrDAABAAAhSpAAI 84675 1 136361 8 AAAUrDAABAAAhSpAAJ 84675 1 136361 9 AAAUrDAABAAAhSpAAK 84675 1 136361 10 AAAUrDAABAAAhSpAAL 84675 1 136361 11 AAAUrDAABAAAhSpAAM 84675 1 136361 12 AAAUrDAABAAAhSpAAN 84675 1 136361 13 14 rows selected.
可以看到data object number和块号变了，如果move到另外的tablespace那么 file number也会变 PostgreSQL：
lightdb@postgres=# select relname,oid,relfilenode from pg_class where relname = 'emp'; relname | oid | relfilenode ---------+-------+------------- emp | 31396 | 31396 (1 row) lightdb@postgres=# truncate table emp; TRUNCATE TABLE lightdb@postgres=# select relname,oid,relfilenode from pg_class where relname = 'emp'; relname | oid | relfilenode ---------+-------+------------- emp | 31396 | 31407 (1 row)
可以看到PostgreSQL的行为和Oracle很类似呀，oid对应Oracle的object_id，relfilenode对应Oracle的data_object_id，前者是逻辑对象，后者是实体对象 那么看下PostgreSQL的ctidvacuum full之后，数据行在块内的物理位置就会移动，即ctid会发生变化，所以ctid不能作为长期的行标识符，应该使用主键来标识一个逻辑行。ctid由两个数字组成，第一个数字表示物理块号，第二个数字表示在物理块中的行号，所以说 PostgreSQL的ctid是表级别唯一的行，而Oracle中是整个实例中唯一。
lightdb@postgres=# select ctid,empno from emp; ctid | empno --------+------- (0,1) | 7369 (0,2) | 7499 (0,3) | 7521 (0,4) | 7566
Oracle可以用rowid 删除表中重复的数据，那么PostgreSQL同样也是可以的
lightdb@postgres=# select * from t_test; id ---- 1 2 2 3 (4 rows) delete from t_test where ctid in ( select ctid from (SELECT ctid, id, count(*) OVER (PARTITION BY id ORDER BY ctid) as cnt FROM t_test ) where cnt > 1) returning *;
