DATABASE LINK
功能描述
当用户需要跨越本地数据库，访问远程数据库的数据时，可以通过DATABASE LINK像访问本地数据库一样访问远程数据库表中的数据。Vastbase在Oracle兼容模式下支持如下功能：
Vastbase G100支持使用
tablename@dblink_name语法进行表的增删改查功能。Vastbase支持postgres_fdw类型的dblink同义词（包含Vastbase到Vastbase和Vastbase到Oracle两种场景）。
Vastbase使用DATABASE LINK（jdbc_fdw）时，支持子查询下推功能，如果子查询能生成一个完整的ForeignScan，并且可以和上层的其他与之join的同server的ForeignScan，生成一个一起下推到远端做join的路径 ，则无需将子查询的结果拉到本地再进行join。该功能仅在数据库版本为V2.2 Build 15(Patch No.3)及以上版本支持。
DATABASE LINK 属性
可以创建 Public 与 Private 属性的DATABASE LINK。
指定为 Public 表示所创建的 DATABASE LINK 所有用户都可以使用；指定 Private 表示仅创建者可以使用。
不同的用户可以创建同名的 Private DATABASE LINK。
同一用户可以创建同名的 Private DATABASE LINK和 Public DATABASE LINK。在访问时优先访问 Private DATABASE LINK。
为支持不同用户创建同名DATABASE LINK，需禁用GRANT FOREIGN SERVER，不再允许将 SERVER 权限给予其他用户，以防一个用户能访问多个同名 SERVER，造成混乱。
支持的数据源
Vastbase 对 DATABASE LINK 的兼容建立在外部数据封装器（Foreign Data Wrapper，FDW）上，因此，在创建 DATABASE LINK时需指定使用的封装器。
要连接 Vastbase 或 PostgreSQL 数据源，使用 postgres_fdw。
postgres_fdw 连接串格式为
postgres_fdw(host 'remote_host',port 'reomte_port',dbname 'db_remote')。要连接 Oracle 数据源，使用 jdbc_fdw。
jdbc_fdw 连接串格式为
jdbc_fdw(url 'jdbc_url',jarfile 'jarfile')。
注意事项
该功能仅在数据库兼容模式为Oracle时支持（即数据库实例初始化时指定DBCOMPATIBILITY='A'）。
创建DATABASE LINK时，需要使用
CREATE EXTENSION创建 postgres_fdw 或 jdbc_fdw 扩展，并具备DATABASE LINK相关权限。不支持return为%rowtype行数据类型。
不支持二进制数据类型、二进制大对象类型、xml数据类型。
由于 Oracle与Vastbase函数的差异性，return值会以最后一个out参数形式存在，直接远程调用函数、存储过程时，数据库会自动补充return参数，不需要用户补充，而使用同义词时，无法确定此时是call调用还是select 调用，无法自动补全return参数，需要用户自行补充，在使用流程章节中将以用例再次详细说明。
Vastbase不支持在连接system的情况下访问system下的自定义类型。
语法格式
创建DATABASE LINK。
CREATE [PUBLIC] DATABASE LINK ${DBLINK_NAME} CONNECT TO ${USERNAME} IDENTIFIED BY '${PASSWORD}' USING { postgres_fdw(host 'remote_host',port 'reomte_port',dbname 'remote_database') | jdbc_fdw(url 'jdbc_url',jarfile 'jarfile') };
DBLINK加密时需要使用usermapping.key.cipher和usermapping.key.rand文件作为加密密码文件和加密因子。首次使用前需要在本地通过如下命令创建这两个文件，并将这两个文件放入各节点目录 $GAUSSHOME/bin，且确保具有读权限。
vb_guc generate -o usermapping -S default -D $GAUSSHOME/bin删除DATABASE LINK。
DROP [PUBLIC] DATABASE LINK ${DBLINK_NAME}通过DATABASE LINK进行远端表的增删改查操作。
INSERT INTO [${SCHEMA_NAME}].${TABLE_NAME}@${DBLINK_NAME} --插入 UPDATE [${SCHEMA_NAME}].${TABLE_NAME}@${DBLINK_NAME} SET... --更新 DELETE FROM [${SCHEMA_NAME}].${TABLE_NAME}@${DBLINK_NAME} WHERE... --删除 SELECT ... FROM [${SCHEMA_NAME}].${TABLE_NAME}@${DBLINK_NAME} --查询创建同义词。
CREATE [OR REPLACE] SYNONYM synonym_name FOR [${SCHEMA_NAME}].${TABLE_NAME}@${DBLINK_NAME};
参数说明
PUBLIC
表示DATABASE LINK支持跨用户访问。
${DBLINK_NAME}
连接名称，可自定义。
${USERNAME}
远端数据库用户名。
${PASSWORD}
远端数据库用户的密码。
remote_host
远端 Vastbase 数据源 IP 地址。
remote_host
远端 Vastbase 数据源端口号。
remote_database
远端 Vastbase 数据源库名。
jdbc_url
jdbc 连接串的URL。
jarfile
jdbc使用的jar包名称。
${SCHEMA_NAME}
目标函数或存储过程所在的模式名（或者package名称）。可不指定，不指定时默认访问database link中Oracle用户对应的模式。
${TABLE_NAME}
远程访问的数据库表名。
synonym_name
自定义的同义词名称。
示例
示例1： 使用DATABASE LINK连接远端vastbase数据库并执行DML操作。
1、在远端数据库创建用户testlink_user并授权。
CREATE USER testlink_user WITH sysadmin password '123456Aa';
grant all on database vastbase to testlink_user;
2、在远端数据库创建表emp_fdw，并插入数据。
create table emp_fdw(empno int primary key,ename varchar(30));
insert into emp_fdw values(1,'foo');
insert into emp_fdw values(2,'bar');
3、在本地数据库创建 postgres_fdw扩展。
create extension postgres_fdw;
4、在本地数据库创建用户user_1 并授权。
CREATE user user_1 password 'Aa123456';
GRANT all on database vastbase to user_1;
GRANT usage on foreign data wrapper postgres_fdw to user_1;
5、关闭远端数据库和本地数据库的强制修改密码功能。
ALTER SYSTEM SET password_force_alter=off;
6、在本地数据库，把当前会话里的会话用户标识和当前用户标识都设置为用户user_1 。
SET SESSION session authorization user_1 password 'Aa123456';
7、在本地数据库生成加密密码文件和加密因子。
vb_guc generate -o usermapping -S default -D $GAUSSHOME/bin
8、在本地数据库创建DATABASE LINK。
CREATE DATABASE LINK dblink_84 CONNECT TO testlink_user IDENTIFIED BY '123456Aa' USING postgres_fdw(host '172.16.105.57',port '10929',dbname 'vastbase');
9、在本地数据库通过dblink_84查询表emp_fdw的数据。
SELECT * FROM emp_fdw@dblink_84;
返回结果为：
empno | ename
-------+-------
1 | foo
2 | bar
(2 rows)
10、清理环境。
--远端数据库
DROP USER testlink_user CASCADE;
DROP table emp_fdw;
--本地数据库
DROP extension postgres_fdw;
DROP USER user user_1 CASCADE;
DROP DATABASE LINK dblink_84;
示例2： 基于DATABASE LINK创建同义词并查询表数据（G100到G100的DATABASE LINK）。
1、在远端数据库创建db_remote数据库。
create database db_remote;
\c db_remote
2、在远端数据库创建用户并授权。
create user user_remote with sysadmin password 'Aa123456';
grant all on database vastbase to user_remote;
3、切换用户（远端数据库）。
set role user_remote password 'Aa123456';
4、在远端数据库创建表并插入数据。
CREATE TABLE table_remote_1173268(col1 int primary key,col2 varchar(30));
INSERT INTO table_remote_1173268 VALUES(1,'clouds');
INSERT INTO table_remote_1173268 VALUES(2,'break');
INSERT INTO table_remote_1173268 VALUES(4,'hhhhh');
5、在本地数据库生成加密密码文件和加密因子。
vb_guc generate -o usermapping -S default -D $GAUSSHOME/bin
6、在本地创建数据库db_local。
create database db_local;
\c db_local;
7、在本地数据库db_local创建用户。
create user user_local with sysadmin password 'Aa123456';
grant all on database vastbase to user_local;
set role user_local password 'Aa123456';
8、切换用户（本地数据库）。
set role user_local password 'Aa123456';
9、在本地数据库创建插件。
create extension postgres_fdw;
10、在本地数据库创建DATABASE LINK。
CREATE DATABASE LINK dblink_84 CONNECT TO user_remote IDENTIFIED BY 'Aa123456' USING postgres_fdw(host '172.16.103.84',port '11001',dbname 'db_remote');
11、关闭远端数据库和本地数据库的强制修改密码功能。
alter system set password_force_alter=off;
12、在本地数据库创建表。
create table table_remote_1173268_2(col1 int,col2 varchar(30));
insert into table_remote_1173268_2(col1,col2) select col1,col2 from table_remote_1173268@dblink_84;
13、通过dblink查询远端数据插入本地表。
insert into table_remote_1173268_2(col1,col2) select col1,col2 from table_remote_1173268@dblink_84;
14、在本地数据库查询表数据。
select * from table_remote_1173268_2;
返回结果为：
col1 | col2
------+--------
1 | clouds
2 | break
4 | hhhhh
(3 rows)
15、在本地数据库创建同义词。
CREATE SYNONYM syn_table_1173268 for table_remote_1173268@dblink_84;
16、在本地数据库使用创建的DATABASE LINK同义词插入数据。
insert into table_remote_1173268_2(col1,col2) select col1,col2 from syn_table_1173268;
17、在本地数据库 再次执行查询。
select * from table_remote_1173268_2;
返回结果为：
col1 | col2
------+--------
1 | clouds
2 | break
4 | hhhhh
1 | clouds
2 | break
4 | hhhhh
(6 rows)
18、清理环境。
--远端数据库
DROP DATABASE db_remote;
DROP USER user_remote cascade;
DROP TABLE table_remote_1173268;
--本地数据库
DROP DATABASE db_local;
DROP USER user_local cascade;
DROP extension postgres_fdw;
DROP DATABASE LINK dblink_84;
DROP TABLE table_remote_1173268_2;
DROP synonym syn_table_1173268;
示例3: 创建私有或公有DATABASE LINK。
1、修改postgresql.conf参数，将jdbc_fdw配置到shared_preload_libraries参数中，该参数修改完成后需要重启数据库。
shared_preload_libraries = 'jdbc_fdw'
2、重启数据库实例。
3、创建插件。
CREATE EXTENSION jdbc_fdw;
4、生成加密密码文件和加密因子。
vb_guc generate -o usermapping -S default -D $GAUSSHOME/bin
5、创建DATABASE LINK。
（1）创建私有private DATABASE LINK。
CREATE DATABASE LINK oradb1 connect to SYSTEM identified by 'root'
using jdbc_fdw(
url 'jdbc:oracle:thin:@//172.16.103.120:1521/utf8',
jarfile '/home/vastbase2/ojdbc7.jar'
);
（2）创建公共public DATABASE LINK。
CREATE PUBLIC DATABASE link oradb2 connect to SYSTEM identified by 'root'
using jdbc_fdw(
url 'jdbc:oracle:thin:@//172.16.103.104:1521/orcl',
jarfile '/home/vastbase2/ojdbc7.jar'
);
6、使用DATABASE LINK查询。
select * from fdw_t1@oradb1;
select * from fdw_t1@oradb2;
返回结果为：
c1 | c2
----+----
1 | a1
2 | a1
3 | a1
(3 rows)
c1 | c2
----+----
1 | a1
2 | a1
3 | a1
(3 rows)
7、在本地执行ALTER SERVER命令。
ALTER SERVER oradb1 VERSION '8.4';
ALTER PUBLIC SERVER oradb2 VERSION '8.5';
8、清理环境，删除DATABASE LINK。
DROP DATABASE LINK oradb1;
DROP PUBLIC DATABASE LINK oradb2;
示例4: 基于DATABASE LINK（jdbc_fdw）的子查询下推功能。
1、获取驱动包ojdbc7.jar，并将其放置到$GAUSSHOME/lib/postgresql/下。
2、修改postgresql.conf参数，将jdbc_fdw配置到shared_preload_libraries参数中，该参数修改完成后需要重启数据库。
shared_preload_libraries = 'jdbc_fdw'
3、重启数据库实例。
4、在本地创建jdbc_fdw。
create extension jdbc_fdw;
5、Oracle远端创建表并插入数据。
CREATE TABLE t1(id int,a1 date,a2 varchar(30),a3 char(30));
INSERT INTO t1 VALUES(1,date'2023-1-26','a','a');
INSERT INTO t1 VALUES(2,date'2022-1-26','b','b');
INSERT INTO t1 VALUES(3,date'2021-1-26','c','c');
INSERT INTO t1 VALUES(4,date'2020-1-26','d','d');
insert into t1 VALUES(5,date'2019-1-26','e','e');
CREATE TABLE t2(id int,a1 date,a2 varchar(30),a3 char(30));
INSERT INTO t2 VALUES(1,date'2023-1-26','a','a');
INSERT INTO t2 VALUES(2,date'2022-1-26','b','b');
INSERT INTO t2 VALUES(3,date'2021-1-26','c','c');
INSERT INTO t2 VALUES(4,date'2020-1-26','d','d');
INSERT INTO t2 VALUES(5,date'2019-1-26','e','e');
6、生成加密密码文件和加密因子。
vb_guc generate -o usermapping -S default -D $GAUSSHOME/bin
7、在本地创建DATABASE LINK。
CREATE DATABASE LINK dblink_1193065 CONNECT TO wzr IDENTIFIED BY 'Aa123456' USING jdbc_fdw(
url 'jdbc:oracle:thin:@//172.16.103.104:1521/orcl',
jarfile '/home/vb2216_danny_18432/local/vastbase/lib/postgresql/ojdbc7.jar'
);
8、在本地执行查询。
select * from t1@dblink_1193065 aa, (select a.id,a.a1,b.a2 from t1@dblink_1193065 a left join t2@dblink_1193065 b on a.id=b.id ) bb where aa.id=bb.id;
返回结果为：
id | a1 | a2 | a3 | id | a1 | a2
----+---------------------+----+--------------------------------+----+---------------------+----
1 | 2023-01-26 00:00:00 | a | a | 1 | 2023-01-26 00:00:00 | a
2 | 2022-01-26 00:00:00 | b | b | 2 | 2022-01-26 00:00:00 | b
3 | 2021-01-26 00:00:00 | c | c | 3 | 2021-01-26 00:00:00 | c
(3 rows)
9、查看步骤8查询语句的执行计划。
explain (costs off) select * from t1@dblink_1193065 aa, (select a.id,a.a1,b.a2 from t1@dblink_1193065 a left join t2@dblink_1193065 b on a.id=b.id ) bb where aa.id=bb.id limit 3;
返回结果为：
QUERY PLAN
-----------------------------------------------------------
-> Foreign Scan
Relations:((tl aa)INNER JOIN(t1 a))LEFT JOIN(t2 b)
(2 rows)
10、在本地创建同义词并查询。
create synonym s1 for t1@dblink_1193065;
create synonym s2 for t2@dblink_1193065;
11、在本地通过同义词查询表数据。
select * from s1;
select * from s2;
返回结果均为：
id | a1 | a2 | a3
----+---------------------+----+--------------------------------
1 | 2023-01-26 00:00:00 | a | a
2 | 2022-01-26 00:00:00 | b | b
3 | 2021-01-26 00:00:00 | c | c
4 | 2020-01-26 00:00:00 | d | d
5 | 2019-01-26 00:00:00 | e | e
(5 rows)
