# ROWID类型 ROWID数据类型用于标识表中的数据行，包含定位该行所需的关键信息，可实现对表中数据行的快速定位。 ROWID数据类型由29个BASE64编码的字符组成，各个字符串的含义如[表1]所示： 表1ROWID类型字段含义 | 字段 | 含义 | 字符长度 | 取值范围（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//////////）。 | ![](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/public_sys-resources/note_3.0-zh-cn.png) ROWID类型不支持以下操作： - ROWID类型不支持作为表的分区键、分布键。 - ROWID类型不支持HTAP。 - ROWID类型仅支持ORA兼容性数据库。 - ROWID类型不支持数据脱敏策略。 - ROWID类型不可作为自定义范围类型的subtype。详细可见[范围类型](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/gaussdb-08-0018.html)章节内容。 - 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"。比较操作符详情请参见[比较操作符](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/gaussdb-08-0040.html)章节。 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](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/gaussdb-08-0253.html)章节内容。在使用ROWID类型普通列进行JOIN操作时，支持HASH JOIN算子，详情请参见[连接查询](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/gaussdb-08-0387.html)章节内容。 示例 ``` 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 ```