KingbaseES中的OID和ROWID
一，数据库中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)
