ROWID类型

ROWID数据类型用于标识表中的数据行，包含定位该行所需的关键信息，可实现对表中数据行的快速定位。

ROWID数据类型由29个BASE64编码的字符组成，各个字符串的含义如表1所示：

表1 ROWID类型字段含义
字段	含义	字符长度	取值范围（BASE64编码）
reserve_flag	保留位。	6	当前版本0（AAAAAA）。
node_slice	节点的分片编号。	3	最小值0（AAA），最大值65535（P//），实际最大值为集群分片数量。
bucket_id	hash bucket表的bucket编号。	3	最小值0（AAA），最大值32767（H//），实际最大值为表的最大bucket编号。对于非hash bucket表而言，该字段值固定为65535（P//）。
table_oid	表的oid。	6	最小值0（AAAAAA），最大值4294967295（D/////）。
row_no	行的编号。	11	最小值0（AAAAAAAAAAA），最大值9223372036854775807（H//////////）。

ROWID类型不支持以下操作：

ROWID类型不支持作为表的分区键、分布键。
ROWID类型不支持HTAP。
ROWID类型仅支持ORA兼容性数据库。
ROWID类型不支持数据脱敏策略。
ROWID类型不可作为自定义范围类型的subtype。详细可见范围类型章节内容。
ROWID类型仅支持JDBC驱动。

示例

gaussdb=# CREATE DATABASE ora_test DBCOMPATIBILITY='ORA';
CREATE DATABASE
gaussdb=# \c ora_test
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "ora_test" as user "$user".

-- 创建表
ora_test=# CREATE TABLE rowid_test(id INT, rwd ROWID) DISTRIBUTE BY HASH(id);
CREATE TABLE

-- 插入数据，值为NULL
ora_test=# INSERT INTO rowid_test VALUES(1, NULL);
INSERT 0 1

-- 插入数据，满足ROWID格式
ora_test=# INSERT INTO rowid_test VALUES(2, 'AAAAAAAAAP//BBBBBAAAAAAAAAAAC');
INSERT 0 1
ora_test=# INSERT INTO rowid_test VALUES(3, 'AAAAAAAAAP//BBBd+AAAAAAAA901/');
INSERT 0 1
ora_test=# INSERT INTO rowid_test VALUES(4, 'AAAAAAAAAP//A12i+AAAAAAAAItn/');
INSERT 0 1
ora_test=# INSERT INTO rowid_test VALUES(5, 'AAAAAAAAAP//CC+uJAAAAAAGASIi9');
INSERT 0 1

-- 插入数据，不满足ROWID格式
ora_test=# INSERT INTO rowid_test VALUES(6, 'AAAAAAAAAP//BBBd+AAAAAAAA@@@@');
ERROR:  Invalid rowid.
LINE 1: INSERT INTO rowid_test VALUES(6, 'AAAAAAAAAP//BBBd+AAAAAAAA@...
                                         ^
CONTEXT:  referenced column: rwd

-- 插入数据，不满足ROWID值范围
ora_test=# INSERT INTO rowid_test VALUES(6, 'AAAAAAAAAP/////d+AAAAAAAA901/');
ERROR:  Invalid rowid.
LINE 1: INSERT INTO rowid_test VALUES(6, 'AAAAAAAAAP/////d+AAAAAAAA9...
                                         ^
CONTEXT:  referenced column: rwd

-- 查看数据
ora_test=# SELECT * FROM rowid_test;
 id |              rwd              
----+-------------------------------
  1 | 
  2 | AAAAAAAAAP//BBBBBAAAAAAAAAAAC
  3 | AAAAAAAAAP//BBBd+AAAAAAAA901/
  4 | AAAAAAAAAP//A12i+AAAAAAAAItn/
  5 | AAAAAAAAAP//CC+uJAAAAAAGASIi9
(5 rows)

-- 删除表
ora_test=# DROP TABLE IF EXISTS rowid_test;
DROP TABLE
ora_test=# \c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "$user".
gaussdb=# DROP DATABASE ora_test;
DROP DATABASE

操作符

ROWID类型支持比较操作符（=、<>、<、>、<=、>=），当与不支持隐式转换成ROWID的数据类型进行比较操作时，报错“Inconsistent datatypes”。比较操作符详情请参见比较操作符章节。

ROWID类型的比较规则是按照node_slice、table_oid、bucket_id、row_no字段数值依次进行比较。

示例

gaussdb=# CREATE DATABASE ora_test DBCOMPATIBILITY='ORA';
CREATE DATABASE
gaussdb=# \c ora_test
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "ora_test" as user "$user".

-- ROWID类型比较
ora_test=# CREATE OR REPLACE FUNCTION func1 RETURN ROWID AS
ora_test$# DECLARE
ora_test$#     a ROWID;
ora_test$#     b ROWID;
ora_test$# BEGIN
ora_test$#     a := 'AAAAAAAAAP//ABPH//AAAAAAAACAB';
ora_test$#     b := 'AAAAAAAAAP//ABPH//AAAAAAAACAA';
ora_test$#     DBE_OUTPUT.PRINT_LINE((a = b)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a <> b)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a > b)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a < b)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a <= b)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a >= b)::text);
ora_test$#     RETURN a;
ora_test$# END;
ora_test$# /
CREATE FUNCTION
ora_test=# SELECT func1();
false
true
true
false
false
true
             func1
-------------------------------
 AAAAAAAAAP//ABPH//AAAAAAAACAB
(1 row)

-- ROWID类型与其他可隐式转换类型的比较
ora_test=# CREATE OR REPLACE FUNCTION func1 RETURN ROWID AS
ora_test$# DECLARE
ora_test$#     a ROWID;
ora_test$#     b TEXT;
ora_test$#     c VARCHAR(30);
ora_test$#     d VARCHAR2;
ora_test$#     e CHAR(29);
ora_test$#     F NVARCHAR2;
ora_test$# BEGIN
ora_test$#     --数据准备
ora_test$#     a:='AAAAAAAAAP//ABPH//AAAAAAAACAB';
ora_test$#     b:='AAAAAAAAAP//ABPH//AAAAAAAACAA';
ora_test$#     c:='AAAAAAAAAP//ABPH//AAAAAAAACAA';
ora_test$#     d:='AAAAAAAAAP//ABPH//AAAAAAAACAA';
ora_test$#     e:='AAAAAAAAAP//ABPH//AAAAAAAACAA';
ora_test$#     F:='AAAAAAAAAP//ABPH//AAAAAAAACAA';
ora_test$# 
ora_test$#     --与text比较
ora_test$#     DBE_OUTPUT.PRINT_LINE('text:');
ora_test$#     DBE_OUTPUT.PRINT_LINE((a = b)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a <> b)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a > b)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a < b)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a <= b)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a >= b)::text);
ora_test$# 
ora_test$#     --与varchar比较
ora_test$#     DBE_OUTPUT.PRINT_LINE('varchar:');
ora_test$#     DBE_OUTPUT.PRINT_LINE((a = c)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a <> c)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a > c)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a < c)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a <= c)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a >= c)::text);
ora_test$# 
ora_test$#     --与varchar2比较
ora_test$#     DBE_OUTPUT.PRINT_LINE('varchar2:');
ora_test$#     DBE_OUTPUT.PRINT_LINE((a = d)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a <> d)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a > d)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a < d)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a <= d)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a >= d)::text);
ora_test$#     --与char比较
ora_test$#     DBE_OUTPUT.PRINT_LINE('char:');
ora_test$#     DBE_OUTPUT.PRINT_LINE((a = e)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a <> e)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a > e)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a < e)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a <= e)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a >= e)::text);
ora_test$# 
ora_test$#     --与nvarchar2比较
ora_test$#     DBE_OUTPUT.PRINT_LINE('nvarchar2:');
ora_test$#     DBE_OUTPUT.PRINT_LINE((a = f)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a <> f)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a > f)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a < f)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a <= f)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a >= f)::text);
ora_test$# 
ora_test$#     RETURN a;
ora_test$# END;
ora_test$# /
CREATE FUNCTION
ora_test=# SELECT func1();
text:
false
true
true
false
false
true
varchar:
false
true
true
false
false
true
varchar2:
false
true
true
false
false
true
char:
false
true
true
false
false
true
nvarchar2:
false
true
true
false
false
true
             func1
-------------------------------
 AAAAAAAAAP//ABPH//AAAAAAAACAB
(1 row)

-- ROWID类型与其他不可隐式转换类型的比较
ora_test=# CREATE OR REPLACE FUNCTION func1 RETURN ROWID AS
ora_test$# DECLARE
ora_test$#     a ROWID;
ora_test$#     b INT;
ora_test$# BEGIN
ora_test$#     --数据准备
ora_test$#     a := 'AAAAAAAAAP//ABPH//AAAAAAAACAB';
ora_test$#     b := 1;
ora_test$# 
ora_test$#     --与int比较
ora_test$#     DBE_OUTPUT.PRINT_LINE((a = b)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a <> b)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a > b)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a < b)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a <= b)::text);
ora_test$#     DBE_OUTPUT.PRINT_LINE((a >= b)::text);
ora_test$#     RETURN a;
ora_testb$# END;
ora_test$# /
CREATE FUNCTION

ora_test=# SELECT func1();
ERROR:  Inconsistent datatypes: expected rowid got integer
CONTEXT:  SQL statement "CALL dbe_output.print_line((a = b)::text)"
PL/pgSQL function func1() line 11 at PERFORM
referenced column: func1

ora_test=# DROP FUNCTION IF EXISTS func1();
DROP FUNCTION
ora_test=# \c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "$user".
gaussdb=# DROP DATABASE ora_test;
DROP DATABASE

索引

ROWID类型普通列支持BTREE/UBTREE索引，详情请参见CREATE INDEX章节内容。在使用ROWID类型普通列进行JOIN操作时，支持HASH JOIN算子，详情请参见连接查询章节内容。

示例

gaussdb=# CREATE DATABASE ora_test DBCOMPATIBILITY='ORA';
CREATE DATABASE
gaussdb=# \c ora_test
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "ora_test" as user "$user".

ora_test=# CREATE TABLE t1(b ROWID, c INT) WITH (storage_type=ustore);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'c' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
ora_test=# CREATE TABLE t2(b ROWID, c INT) WITH (storage_type=astore);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'c' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
ora_test=# INSERT INTO t1 VALUES('AAAAAAAAAP//BBBBBAAAAAAAAAAAC', generate_series(1,100));
INSERT 0 100
ora_test=# INSERT INTO t2 VALUES('AAAAAAAAAP//BBBBBAAAAACdAAAAC', generate_series(1,100));
INSERT 0 100
ora_test=# --BTREE索引
ora_test=# CREATE INDEX t1_rowid_index ON t1 USING BTREE (b);
NOTICE:  Index type btree is converted to index type ubtree.
CREATE INDEX
ora_test=# CREATE INDEX t2_rowid_index ON t2 USING BTREE (b rowid_ops);
CREATE INDEX
ora_test=# \d t1
      Table "public.t1"
 Column |  Type   | Modifiers
--------+---------+-----------
 b      | rowid   |
 c      | integer |
Indexes:
    "t1_rowid_index" ubtree (b) WITH (storage_type=USTORE) TABLESPACE pg_default

ora_test=# \d t2
      Table "public.t2"
 Column |  Type   | Modifiers
--------+---------+-----------
 b      | rowid   |
 c      | integer |
Indexes:
    "t2_rowid_index" btree (b) TABLESPACE pg_default

ora_test=# DROP INDEX t1_rowid_index;
DROP INDEX
ora_test=# DROP INDEX t2_rowid_index;
DROP INDEX
ora_test=# CREATE INDEX t1_rowid_index ON t1 USING UBTREE (b);
CREATE INDEX
ora_test=# CREATE INDEX t2_rowid_index on t2 USING UBTREE (b rowid_ops);
NOTICE:  Index type ubtree is converted to index type btree.
CREATE INDEX
ora_test=# \d t1
      Table "public.t1"
 Column |  Type   | Modifiers
--------+---------+-----------
 b      | rowid   |
 c      | integer |
Indexes:
    "t1_rowid_index" ubtree (b) WITH (storage_type=USTORE) TABLESPACE pg_default

ora_test=# \d t2
      Table "public.t2"
 Column |  Type   | Modifiers
--------+---------+-----------
 b      | rowid   |
 c      | integer |
Indexes:
    "t2_rowid_index" btree (b) TABLESPACE pg_default

ora_test=# DROP INDEX t1_rowid_index;
DROP INDEX
ora_test=# DROP INDEX t2_rowid_index;
DROP INDEX
ora_test=# ANALYZE t1;
ANALYZE
ora_test=# ANALYZE t2;
ANALYZE
--HASH JOIN 算子
ora_test=# EXPLAIN VERBOSE SELECT * FROM t1 JOIN t2 ON t1.b = t2.b;
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=11.69..14.48 rows=2 width=56)
   Output: t1.b, t1.c, t2.b, t2.c
   Node/s: All datanodes
   ->  Hash Join  (cost=11.62..14.38 rows=2 distinct=[200, 1] width=56)
         Output: t1.b, t1.c, t2.b, t2.c, (Expression Flatten Optimized)
         Hash Cond: (t1.b = t2.b), (Expression Flatten Optimized)
         ->  Seq Scan on public.t1  (cost=0.00..2.50 rows=100 width=28)
               Output: t1.b, t1.c
               Distribute Key: t1.c
         ->  Hash  (cost=10.37..10.37 rows=200 width=28)
               Output: t2.b, t2.c
               ->  Streaming(type: BROADCAST)  (cost=0.00..10.37 rows=200 width=28)
                     Output: t2.b, t2.c
                     Spawn on: All datanodes
                     Consumer Nodes: All datanodes
                     ->  Seq Scan on public.t2  (cost=0.00..2.50 rows=100 width=28)
                           Output: t2.b, t2.c
                           Distribute Key: t2.c
(18 rows)
ora_test=# DROP TABLE IF EXISTS t1;
DROP TABLE
ora_test=# DROP TABLE IF EXISTS t2;
DROP TABLE
ora_test=# \c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "$user".
gaussdb=# DROP DATABASE ora_test;
DROP DATABASE

类型转换

ROWID类型和字符串类型允许进行隐式类型转换。

示例

gaussdb=# CREATE DATABASE ora_test DBCOMPATIBILITY='ORA';
CREATE DATABASE
gaussdb=# \c ora_test
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "ora_test" as user "$user".

ora_test=# CREATE TABLE t1(b ROWID, c int);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'c' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
ora_test=# INSERT INTO t1 VALUES('AAAAAAAAAP//BBBBBAAAAAAAAAAAC'::BPCHAR(29));
INSERT 0 1
ora_test=# INSERT INTO t1 VALUES('AAAAAAAAAP//BBBBBAAAAAAAAAAAC'::VARCHAR(29));
INSERT 0 1
ora_test=# INSERT INTO t1 VALUES('AAAAAAAAAP//BBBBBAAAAAAAAAAAC'::NVARCHAR2(29));
INSERT 0 1
ora_test=# INSERT INTO t1 VALUES('AAAAAAAAAP//BBBBBAAAAAAAAAAAC'::TEXT);
INSERT 0 1
ora_test=# CREATE TABLE t2(b BPCHAR(29));
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'b' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
ora_test=# INSERT INTO t2 VALUES('AAAAAAAAAP//BBBBBAAAAAAAAAAAC'::ROWID);
INSERT 0 1
ora_test=# CREATE TABLE t3(b VARCHAR(29));
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'b' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
ora_test=# INSERT INTO t3 VALUES('AAAAAAAAAP//BBBBBAAAAAAAAAAAC'::ROWID);
INSERT 0 1
ora_test=# CREATE TABLE t4(b NVARCHAR2(29));
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'b' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
ora_test=# INSERT INTO t4 VALUES('AAAAAAAAAP//BBBBBAAAAAAAAAAAC'::ROWID);
INSERT 0 1
ora_test=# CREATE TABLE t5(b TEXT);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'b' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
ora_test=# INSERT INTO t5 VALUES('AAAAAAAAAP//BBBBBAAAAAAAAAAAC'::ROWID);
INSERT 0 1
gaussdb=# DROP TABLE IF EXISTS t1;
DROP TABLE
gaussdb=# DROP TABLE IF EXISTS t2;
DROP TABLE
gaussdb=# DROP TABLE IF EXISTS t3;
DROP TABLE
gaussdb=# DROP TABLE IF EXISTS t4;
DROP TABLE
gaussdb=# DROP TABLE IF EXISTS t5;
DROP TABLE
ora_test=# \c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "$user".
gaussdb=# DROP DATABASE ora_test;
DROP DATABASE

父主题： 数据类型

上一篇：向量化引擎支持的数据类型

下一篇：向量数据类型

ROWID类型

示例

操作符

索引

类型转换

相关文档

您对此文档页面的整体评价？