标签：00 ROWID -# OID rowid test KingbaseES

一，数据库中OID

每个表都有几个系统字段，其中有一个比较特殊的系统字段OID，使用\d并不会显示，且默认情况下创建也不会有OID。

---本文验证版本
test=# select version();
test-# /
 version
---------------------
 KingbaseES V008R006C007B0024 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat
4.8.5-28), 64-bit
(1 row)

1，一些基本概念

OID：行对象标识符（对象ID），KES中系统表/视图默认是有的OID，充当主键。

OID(Object Identifier) 就是KES内部用于标识数据库对象(即通常意义上的数据表，视图，存储过程之类)的一个长度为4字节的标识符。它是KES大部分系统

表的主键，KES中OID在系统表中是可以直接显示出来的，但是普通表默认并没有（非隐藏了），该字段只有在创建表时使用了“with oids”或配置参数

“default_with_oids”的值为真时出现。

2，OID对于系统表是全局的

如果系统表的OID是局部的话，理论上来说，同一个表内的数据应该是连续的OID数值，但是从下面来看明显不是连续的，甚至OID都不是从1开始的。

test=# select count(*)  from sys_type;
test-# /
 count
-------
  1208
(1 row)

test=# select count(*)  from sys_type where oid > 1208;
test-# /
 count
-------
  1120
(1 row)

test=# select oid  from sys_type where oid > 33990;
test-# /
  oid
-------
 33992
 33993
 33995
(3 rows)

一种简单且直白的方式，也可以证明系统表中的OID是连续且唯一的。不同对象新建之后，在不同的系统表中查看，可以看到OID是连续的。

---创建一个自定义集合类型，查看其在sys_type  中的OID是55136
cpbd_test=> create type aatyp is table of int;
cpbd_test-> /
CREATE TYPE
cpbd_test=> select oid,typname from sys_type  where typname = 'aatyp';
cpbd_test-> /
  OID  | TYPNAME
-------+---------
 55136 | aatyp
(1 row)
---创建一个自定义函数，查看其在sys_proc  中的OID是55137
cpbd_test=> create or replace function func_test05(i int)
cpbd_test-> return int
cpbd_test-> as
cpbd_test->   vv int;
cpbd_test-> begin
cpbd_test->  return 1;
cpbd_test-> end;
cpbd_test-> /
CREATE FUNCTION
cpbd_test=>
cpbd_test=> select oid,proname from sys_proc  where proname = 'func_test05';
cpbd_test-> /
  OID  |   PRONAME
-------+-------------
 55137 | func_test05
(1 row)

cpbd_test=>

3，OID对于普通表是局部的

在PG中即使是普通表也是全局的，但在KES中并不是，OID是局部的。

----然而，默认情况下我们自己去创建一个普通表，并没有OID这个字段
test=# create table tt5(id int);
test-# /
CREATE TABLE
test=# insert into tt5 values(10);
test-# /
INSERT 0 1
test=# select oid,id from tt5;
test-# /
ERROR:  column "oid" does not exist
LINE 1: select oid,id from tt5;
               ^
HINT:  Perhaps you meant to reference the column "tt5.id".
test=#

-----但是如果我们把default_with_oids设置为真时，可以看到创建的表将带有OID，但是默认是隐藏的
test=# set default_with_oids to true;
test-# /
SET
test=# create table tt6(id int);
test-# /
CREATE TABLE
test=# insert into tt6 values(10);
test-# /
INSERT 0 1
test=# select oid from tt6;
test-# /
 oid
-----
   1
(1 row)
test=# select * from tt6;
test-# /
 id
----
 10
(1 row)

----使用create table时指定OID
test=# set default_with_oids to false;
test-# /
SET
test=# create table tt7(id int) with oids;
test-# /
CREATE TABLE
test=# insert into tt7 values(10);
test-# /
INSERT 0 1
test=# select oid,id from tt7;
test-# /
 oid | id
-----+----
   1 | 10
(1 row)

test=#

通过上面的普通表测试验证（tt6和tt7）可以看出KES和PG并不一样，OID不是全局的，而是每个普通表独立的，也就是说是局部的。

4，OID的别名regclass

表（包括toast表）、索引、视图的对象标识符就是系统表“sys_class”的oid字段的值。

----查看首字母为t的表
test=#  select oid,relname from sys_class where relname like 't%';
test-# /
  OID  |         RELNAME
-------+--------------------------
 16700 | teachers
 16706 | teachers_pkey
 16708 | teachers_copy
 24903 | tt1
 16733 | ttab
 24909 | t2
 33139 | tt5
 33319 | ttype
 33349 | table_t1_pkey
 33346 | table_t1
 33664 | tbl_time
 33694 | tt_tab
 33718 | tt_log
 33726 | t1
 12201 | table_constraints
 12202 | table_privileges
 12204 | tables
 12205 | transforms
 12206 | triggered_update_columns
 12207 | triggers
 33985 | tdictionary_amlin
 33991 | tcontorler
(22 rows)

除oid这种通用的对象标识符类型外，其他的类型都提供一种把字符串转换成oid类型的操作符，这可以大大简化查询对象信息时的SQL语句，示例如下。

想要知道对象标识符为“16700”的表是哪一张，可以使用如下SQL命令查询

---查询标识符(OID)为16700的表名
test=# select 16700::regclass
test-# /
 REGCLASS
----------
 teachers
(1 row)

要查询普通表teachers”表有哪些字段，一般的SQL命令是需要先查询sys_attribute表再关联sys_class表的，完整语句示例如下

SELECT attrelid,attname,atttypid,attlen, attnum,attnotnull FROM sys_attribute WHERE attrelid = (SELECT oid FROM sys_class WHERE relname = 'teachers');

--查询“teachers”表有哪些字段
test=# SELECT attrelid,attname,atttypid,attlen, attnum,attnotnull FROM sys_attribute WHERE attrelid = (SELECT oid FROM sys_class WHERE relname = 'teac>
test-# /
 ATTRELID |   ATTNAME    | ATTTYPID | ATTLEN | ATTNUM | ATTNOTNULL
----------+--------------+----------+--------+--------+------------
    16700 | tableoid     |       26 |      4 |     -6 | t
    16700 | cmax         |       29 |      4 |     -5 | t
    16700 | xmax         |       28 |      4 |     -4 | t
    16700 | cmin         |       29 |      4 |     -3 | t
    16700 | xmin         |       28 |      4 |     -2 | t
    16700 | ctid         |       27 |      6 |     -1 | t
    16700 | teacher_id   |     1700 |     -1 |      1 | t
    16700 | teacher_name |     1043 |     -1 |      2 | t
    16700 | age          |     1700 |     -1 |      3 | f
    16700 | sal          |     1700 |     -1 |      4 | f
    16700 | gender       |     1043 |     -1 |      5 | f
    16700 | title        |     1043 |     -1 |      6 | f
    16700 | position     |     1043 |     -1 |      7 | f
    16700 | department   |     1043 |     -1 |      8 | t
(14 rows)

test=#

使用regclass类型的自动转换运算符就可以不关联查询sys_class了

SELECT attrelid,attname,atttypid,attlen, attnum,attnotnull FROM sys_attribute WHERE attrelid = 'teachers'::regclass;

test=# SELECT attrelid,attname,atttypid,attlen, attnum,attnotnull FROM sys_attribute WHERE attrelid =  'teachers'::regclass;
test-# /
 ATTRELID |   ATTNAME    | ATTTYPID | ATTLEN | ATTNUM | ATTNOTNULL
----------+--------------+----------+--------+--------+------------
    16700 | tableoid     |       26 |      4 |     -6 | t
    16700 | cmax         |       29 |      4 |     -5 | t
    16700 | xmax         |       28 |      4 |     -4 | t
    16700 | cmin         |       29 |      4 |     -3 | t
    16700 | xmin         |       28 |      4 |     -2 | t
    16700 | ctid         |       27 |      6 |     -1 | t
    16700 | teacher_id   |     1700 |     -1 |      1 | t
    16700 | teacher_name |     1043 |     -1 |      2 | t
    16700 | age          |     1700 |     -1 |      3 | f
    16700 | sal          |     1700 |     -1 |      4 | f
    16700 | gender       |     1043 |     -1 |      5 | f
    16700 | title        |     1043 |     -1 |      6 | f
    16700 | position     |     1043 |     -1 |      7 | f
    16700 | department   |     1043 |     -1 |      8 | t
(14 rows)

test=#

可以看出：其实，这个“ ’teachers’::regclass ”的作用实际就是：select oid from sys_class where relname=’teachers’; 即返回的是teachers表的oid，所以regclass叫做oid的别名。

5，一些小结

1），系统表中的OID是全局的，但是普通表OID是局部的。

2），如果使用WITH OIDS创建表，则每一行都包含一个OID伪列，该列将在INSERT期间自动填充。OID被顺序分配4字节整数。最初，它们在整个安装过程中都是唯一的。但是，OID计数器大约为42.9亿个，此后可能会重复OID。

3），通过在OID列上创建唯一索引，可以防止单个表中的OID重复（但请注意，WITH OIDS子句本身不会创建此类索引）。系统检查索引以查看是否已经存在新生成的OID，如果存在，则生成新的OID。重复该操作，直至满足唯一约束。因此，如果表内的数据已经很多，整个过程是非常低效的。

4），要对用户表中的行进行唯一编号，最好使用SERIAL而不是OID列，如果期望该表在其整个生命周期中拥有超过20亿个条目，则最好使用bigserial。

二，数据库中ROWID

ROWID是数据库的一个伪列，建立表的时候数据库会自动为每个表建立ROWID列，是数据库中每一条记录的唯一标识，对记录的访问是基于ROWID。但它

实际上并不存储在表中，可以从表中查询，但不支持插入、更新、删除他们的值。

KES的ROWID能力包括以下几个方面：

1，新增一个参数，开启后为每个表增加一个隐含列“ROWID”, ROWID 存储一个唯一确定的ID。

2，该参数开启之后，原default_with_oids 的参数就会失效。

3，创建的ROWID 隐含列，位于表的首列，是一个单调递增的序列。且为其建有索引，索引类型为唯约束的btree 索引，以提升查询性能。

ROWID创建的方式可以是：

1、打开default_with_rowid参数或者

2、CREATE建表时指定,比如create table trow(id int,name varchar) with rowid

3、对已有表可以通过以下方式增加rowid,比如alter table t set with rowid;

1，GUC参数default_with_rowid创建ROWID

开启后为系统上所有再创建的表增加一个rowid 名，类型为rowid type的隐含列。且所有插入的数据，都会将当前行的rowid 写入到该列之中。

注意：

该参数和default_with_oids的关系，两者面向不同的应用，不能同时生效，当两个参数都设置为true 时。（rowid 的参数优先级高于oids）,如下案例所示。

test=# show default_with_oids
test-# /
 DEFAULT_WITH_OIDS
-------------------
 on
(1 row)

test=# show default_with_rowid
test-# /
 DEFAULT_WITH_ROWID
--------------------
 on
(1 row)

test=# create table tt11(  id int );
test=# insert into tt11 values(10);
test=# select rowid,id from tt11;    ----默认为我们创建了rowid
test-# /
          ROWID          | ID
-------------------------+----
 AAAAAAAAADQCAAAAAAAAAAA | 10
(1 row)

test=# select oid,id from tt11;   ----没有为我们创建oid
test-# /
ERROR:  column "oid" does not exist
LINE 1: select oid,id from tt11;
               ^
HINT:  Perhaps you meant to reference the column "tt11.id".

2，CREATE语句创建ROWID

当不期望所有的表创建rowid 隐含列的时候，可以通过在DDL（create、alter）两类语句，进行表级ROWID列的创建或添加。

test=# create table student(sno int, name varchar(10), birthday date,
test(# department varchar(10), sex varchar(10))
test-# with rowid;
test-# /
CREATE TABLE
test=# insert into student values(1, 'li', '2018-1-1', 'physics', 'boy');
test-# insert into student values(5, 'lu', '2018-1-2', 'chinese', 'boy');
test-# insert into student values(3, 'wang', '2018-1-3', 'english', 'girl');
test-# insert into student values(4, 'zhang', '2018-1-4', 'history', 'boy');
test-# insert into student values(2, 'jack',  '2018-1-5', 'history', 'boy');
test=# select * from student;
test-# /
 SNO | NAME  |      BIRTHDAY       | DEPARTMENT | SEX
-----+-------+---------------------+------------+------
   1 | li    | 2018-01-01 00:00:00 | physics    | boy
   5 | lu    | 2018-01-02 00:00:00 | chinese    | boy
   3 | wang  | 2018-01-03 00:00:00 | english    | girl
   4 | zhang | 2018-01-04 00:00:00 | history    | boy
   2 | jack  | 2018-01-05 00:00:00 | history    | boy
(5 rows)

test=# select rowid, * from student;
test-# /
          ROWID          | SNO | NAME  |      BIRTHDAY       | DEPARTMENT | SEX
-------------------------+-----+-------+---------------------+------------+------
 AAAAAAAAADP5AAAAAAAAAAA |   1 | li    | 2018-01-01 00:00:00 | physics    | boy
 AAAAAAAAADP5AAAAAAAAAAB |   5 | lu    | 2018-01-02 00:00:00 | chinese    | boy
 AAAAAAAAADP5AAAAAAAAAAC |   3 | wang  | 2018-01-03 00:00:00 | english    | girl
 AAAAAAAAADP5AAAAAAAAAAD |   4 | zhang | 2018-01-04 00:00:00 | history    | boy
 AAAAAAAAADP5AAAAAAAAAAE |   2 | jack  | 2018-01-05 00:00:00 | history    | boy
(5 rows)

---同时可以看到，student这张表在创建了rowid后，默认就为我们创建了唯一约束和btree索引
test=# \d student
                          Table "public.student"
   Column   |            Type            | Collation | Nullable | Default
------------+----------------------------+-----------+----------+---------
 sno        | integer                    |           |          |
 name       | character varying(10 char) |           |          |
 birthday   | date                       |           |          |
 department | character varying(10 char) |           |          |
 sex        | character varying(10 char) |           |          |
Indexes:
    "student_rowid_key" UNIQUE CONSTRAINT, btree ("rowid")    --唯一约束的btree

值得一提的是，在default_with_oids 开启，default_with_rowid关闭时会报错，会提示用户不能创建带有rowid 隐含列的表（此参数配置下默认创建的是oid隐藏列）。

test=# set default_with_rowid to false;
test-# /
SET
test=# set default_with_oids to true;
test-# /
SET
test=# create table tt16(  id int ) with rowid;
test-# /
ERROR:  can not create table with rowid (default_with_rowid is false, and default_with_oids is true)!
test=#

3，ROWID数据类型

KES 的行记录(ROWS)在数据库中存在逻辑上的唯一标识（并非物理地址）通过ROWID 类型的数据进行表示，ROWID 一般的表示方法通过64进制的字符进行表示，总长度为23个字符（仅显示的字符数，并非实际存储数），但实际上的存储采用了一种变长的方式进行，其实际存储在4 ~ 18 个字节。

ROWID type类型的数据是通过内部的复合结构在数据库存储，也通过不同的成员标识不同信息。

ROWID type 的输入、输出都以64位编码格式，包含A-Z,a-z,0-9,+以及/符号。

ROWID 的范围如下：

存储	事务的回卷次数[0 ~ 5]	元组插入时的xid [6 ~ 11]	事务内已插入元组的个数 [12 ~ 22]
编码格式	32位	32位	64位
Min	AAAAAA	AAAAAB	AAAAAAAAAAA
Max	D//////	D//////	P//////////

超出以上范围的ROWID 都属于非法格式的ROWID。

注：每次开启一个新的事务（包含子事务），事务内已插入元组的个数都重新计算。

支持数据比较操作符

ROWID仅支持 =，>, >=, <, <=, != 以及同类型的比较操作符。可通过where 条件，表达式比较操作符可以指定rowid 类型，并且具有实际的逻辑意义。

=： rowid 上各个位置信息严格相等。
!=: rowid 上各个列位置信息严格不相等。
>= （类）: 先比较高位的事务回卷次数，在通过事务xid 进行逻辑大小比较，最后比较事务内插入的次数。

原则上，后插入的数据的rowid 要大于先插入的rowid，即rowid 属于单调递增的数据。

CREATE TABLE rowid_tt1(id rowid);
\echo `echo 'AAAAAAAAAAABAAAAAAAAAAA' > /tmp/rowid_tt1.txt`
COPY rowid_tt1 FROM '/tmp/rowid_tt1.txt';
select * from rowid_tt1;
copy rowid_tt1 to '/tmp/rowid_tt1_to.txt';
\echo `more /tmp/rowid_tt1_to.txt`

--实测如下：
test=# \echo `echo 'AAAAAAAAAAABAAAAAAAAAAA' > /tmp/rowid_tt1.txt`

test=# COPY rowid_tt1 FROM '/tmp/rowid_tt1.txt';
test-# /
COPY 1
test=# select * from rowid_tt1;
test-# /
           ID
-------------------------
 AAAAAAAAAAABAAAAAAAAAAA
(1 row)

test=# copy rowid_tt1 to '/tmp/rowid_tt1_to.txt';
test-# /
COPY 1
test=# \echo `more /tmp/rowid_tt1_to.txt`
AAAAAAAAAAABAAAAAAAAAAA
test=#

---目标目录下的文件
[root@localhost tmp]# cat rowid_tt1.txt
AAAAAAAAAAABAAAAAAAAAAA
[root@localhost tmp]# cat rowid_tt1_to.txt
AAAAAAAAAAABAAAAAAAAAAA
[root@localhost tmp]# pwd
/tmp
[root@localhost tmp]# ll
total 8
-rw-r--r--. 1 kingbase kingbase 24 Dec  5 05:39 rowid_tt1_to.txt
-rw-rw-r--. 1 kingbase kingbase 24 Dec  5 05:39 rowid_tt1.txt
drwx------. 3 root     root     17 Dec  4 05:40 systemd-private-6b7ca1c7075c448a8f7c9cad72bbf404-chronyd.service-RNqI49
drwx------. 3 root     root     17 Dec  1 03:05 systemd-private-c4eb155b75f843eb802e156a387665ce-chronyd.service-EJMbxT
drwx------. 2 root     root      6 Dec  4 04:45 vmware-root_693-4013395532
drwx------. 2 root     root      6 Dec  4 05:40 vmware-root_697-3988163015
drwx------. 2 root     root      6 Dec  1 03:05 vmware-root_698-2730496923
drwx------. 2 root     root      6 Dec  3 20:52 vmware-root_701-3979708482
drwx------. 2 root     root      6 Dec  4 04:01 vmware-root_702-2722304542
[root@localhost tmp]#

如果数据类型(字符数需要是23个)不匹配也是无法正常插入的

test=# insert into rowid_tt1 values('AAAAAAAAAAABAAAAAAAAAAA44444444');
test-# /
ERROR:  invalid input syntax for type rowid: "AAAAAAAAAAABAAAAAAAAAAA44444444"
LINE 1: insert into rowid_tt1 values('AAAAAAAAAAABAAAAAAAAAAA4444444...
                                     ^
test=# insert into rowid_tt1 values('AAAAAAAAAAABAAAAA');
test-# /
ERROR:  invalid input syntax for type rowid: "AAAAAAAAAAABAAAAA"
LINE 1: insert into rowid_tt1 values('AAAAAAAAAAABAAAAA');
                                     ^
test=#

操作符测试；

test=# select * from rowid_tt1;
test-# /
           ID
-------------------------
 AAAAAAAAAAABAAAAAAAAAAA
 AAAAAAAAAAABAAAAAAAAAAB
 AAAAAAAAAAABAAAAAAAAAAC
(3 rows)

test=# select * from rowid_tt1 where id >= 'AAAAAAAAAAABAAAAAAAAAAB';
test-# /
           ID
-------------------------
 AAAAAAAAAAABAAAAAAAAAAB
 AAAAAAAAAAABAAAAAAAAAAC
(2 rows)

test=#

4，其他说明

ROWID 使用时，需要注意以下事项：

1，支持在投影列中，使用ROWID伪列关键字，查询数据的当前行的逻辑 id。

2，支持在过滤条件（WHERE）中使用ROWID OP CONST（历史行版本）或者CONST OP ROWID的形式，用于条件过滤查询当前行最新的行逻辑 id。可以与其它过滤条件组成使用。OP只支持关系运算符，即>,<,>=,<=,<>等。

3，支持在ORDER BY和GROUP BY中使用ROWID。

4，可以在存储过程中，使用1，2，3或者是1，2，3的组合形式。

5，不支持ROWID与其它（2中OP）以外的运算符进行运算。

6，ROWID支持BTREE、HASH索引。

三，附录

1，双冒号(::)的含义

"::" 符号其实是一个强制类型转换符，作用等同于CAST。

在很多情况下，我们需要将一种数据类型的值转换为另一种数据类型。那么我们就可以使用它来进行转换。

::的语法格式为

expression::type

用法示例：

test=# select '25'::integer,sys_typeof('25'::integer), '12-oct-2023'::date,sys_typeof('12-oct-2023'::date);
test-# /
 INT4 | SYS_TYPEOF |        DATE         | SYS_TYPEOF
------+------------+---------------------+------------
   25 | integer    | 2023-10-12 00:00:00 | date
(1 row)

CAST的语法格式为

CAST ( expression AS target_type );

用法示例:

test=# select cast('25' as integer),sys_typeof( cast('25' as integer)),
test-# cast('12-oct-2023' as date),sys_typeof( cast('12-oct-2023' as date));
test-# /
 INT4 | SYS_TYPEOF |        DATE         | SYS_TYPEOF
------+------------+---------------------+------------
   25 | integer    | 2023-10-12 00:00:00 | date
(1 row)

标签：00,ROWID,-#,OID,rowid,test,KingbaseES
From： https://www.cnblogs.com/kingbase/p/17923221.html

KingbaseES中的OID和ROWID