oracle rowid转为列,PostgreSQL实现oracle中rowid

oracle中可以通过rowid定位到一条数据。索引扫描就是先根据查询条件的到对应数据的rowid，然后通过rowid得到数据，也可以直接使用rowid来查询数据，比如select * from tbl where rowid=xxx; 在没有行迁移的情况下，rowid是固定不变的。

在pg中索引扫描是先查询到数据的ctid，然后根据ctid去得到相应的数据。但是ctid和oracle中的rowid并不完全相同，因为pg中多版本的原因，ctid是会发生变化的,例如：https://www.cndba.cn/foucus/article/3749

bill=# select ctid,* from t1 limit 5;

ctid | id

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

(0,1) | 1

(0,2) | 2

(0,3) | 3

(0,4) | 4

(0,5) | 5

(5 rows)

bill=# update t1 set id=111 where id =1;

UPDATE 1

bill=# vacuum ANALYZE t1;

VACUUM

bill=# select ctid,* from t1 limit 5;

ctid | id

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

(0,2) | 2

(0,3) | 3

(0,4) | 4

(0,5) | 5

(0,6) | 6

(5 rows)

—方法一：sequence 唯一标识

https://www.cndba.cn/foucus/article/3749

bill=# create table tbl (rowid serial8 not null, c1 int, c2 int);

CREATE TABLE

bill=# create unique index idx_tbl_1 on tbl(rowid);

CREATE INDEX

bill=# insert into tbl (c1,c2) values (1,2);

INSERT 0 1

bill=# insert into tbl (c1,c2) values (1,2);

INSERT 0 1

bill=# insert into tbl (c1,c2) values (1,2);

INSERT 0 1

bill=# select * from tbl;

rowid | c1 | c2

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

1 | 1 | 2

2 | 1 | 2

3 | 1 | 2

(3 rows)

—方法二：identify列

https://www.cndba.cn/foucus/article/3749

bill=# create table tbl (rowid int8 GENERATED ALWAYS AS IDENTITY not null, c1 int, c2 int);

create unique index idx_tbl_1 on tbl(rowid); CREATE TABLE

bill=# create unique index idx_tbl_1 on tbl(rowid);

CREATE INDEX

bill=# insert into tbl (c1,c2) values (1,2);

INSERT 0 1

bill=# insert into tbl (c1,c2) values (1,2);

INSERT 0 1

bill=# insert into tbl (c1,c2) values (1,2);

INSERT 0 1

bill=# select * from tbl;

rowid | c1 | c2

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

1 | 1 | 2

2 | 1 | 2

3 | 1 | 2

(3 rows)

—方法三：oidhttps://www.cndba.cn/foucus/article/3749https://www.cndba.cn/foucus/article/3749

bill=# /dT oid

List of data types

Schema | Name | Description

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

pg_catalog | oid | object identifier(oid), maximum 4 billion

(1 row)

不过oid不适合存储超过40亿条记录的表。

https://www.cndba.cn/foucus/article/3749https://www.cndba.cn/foucus/article/3749

postgres=# create table tbl (c1 int, c2 int) with oids;

CREATE TABLE

postgres=# create unique index idx_tbl_oid on tbl(oid);

CREATE INDEX

postgres=# insert into tbl (c1,c2) values (1,2);

INSERT 168528 1

postgres=# insert into tbl (c1,c2) values (1,2);

INSERT 168529 1

postgres=# insert into tbl (c1,c2) values (1,2);

INSERT 168530 1

postgres=# select oid,* from tbl;

oid | c1 | c2

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

168528 | 1 | 2

168529 | 1 | 2

168530 | 1 | 2

(3 rows)

bill=# create table tbl (c1 int, c2 int) with oids;

psql: ERROR: syntax error at or near "oids"

LINE 1: create table tbl (c1 int, c2 int) with oids;

pg12中是将oid作为一种数据类型来使用：

bill=# create table tbl(oid oid,c1 int,c2 int);

CREATE TABLE

版权声明：本文为博主原创文章，未经博主允许不得转载。