HighGoDB插件之Oracle_fdw
1.简介
oracle_fdw 是 HighGoDB 的一个扩展插件，它提供了一个外部数据包装器，可以方便高效的访问oracle数据库，包括WHERE条件和所需列的下推以及全面的EXPLAIN支持。
2.加载oracle_fdw
语法：
CREATE EXTENSION [ IF NOT EXISTS ] extension_name(扩展名)
[ WITH ] [ SCHEMA 模式名称 ]
[ VERSION version(版本) ]
[ FROM 老版本 ]
[ CASCADE ]
3.参数配置
3.1.Foreign data wrapper 参数
- nls_lang (可选)
将 Oracle 的 NLSLANG 环境变量设置为此值。
NLSLANG 的格式为 “language_territory.charset”（例如 AMERICANAMERICA.AL32UTF8）。这必须与您的数据库编码匹配。当未设置此值时，如果可能的话，oracle_fdw 将自动执行正确的操作，并在无法执行时发出警告。只有在知道自己在做什么的情况下才设置此值。
3.2.Foreign server 参数
- dbserver (必填)
这是远程数据库的 Oracle 数据库连接字符串。
只要您的 Oracle 客户端进行了相应的配置，它可以采用 Oracle 支持的任何形式。对于本地（“BEQUEATH”）连接，请将其设置为空字符串。
- isolation_level (可选，默认值是serializable)
在 Oracle 数据库中使用的事务隔离级别。
该值可以是 serializable、readcommitted 或 readonly。
请注意，在单个 HighGoDB 语句中可以对 Oracle 表进行多次查询（例如，在嵌套循环连接期间）。为了确保不会发生由并发事务的竞争条件引起的不一致性，事务隔离级别必须保证读取的稳定性。
只有使用 Oracle 的 SERIALIZABLE 或 READ ONLY 事务隔离级别才能保证这一点。
很遗憾，Oracle 的 SERIALIZABLE 实现相当糟糕，会在意想不到的情况下（如向表中插入数据时）引发序列化错误（ORA-08177）。使用 READ COMMITTED 事务可以解决这个问题，但存在不一致性的风险。如果您想使用它，请检查执行计划，确保外部扫描不会执行多次。
- **nchar **(boolean，可选，默认值是off)
将此选项设置为 on 会在 Oracle 端选择更昂贵的字符转换。如果您正在使用单字节的 Oracle 数据库字符集，但是在包含无法表示在数据库字符集中的字符的 NCHAR 或 NVARCHAR2 列中，这是必需的。
将 nchar 设置为 on 会对性能产生明显影响，并且会导致 UPDATE 语句中设置超过2000字节的字符串（如果您使用 MAXSTRINGSIZE = EXTENDED，则是16383）出现 ORA-01461 错误。
这个错误似乎是一个 Oracle 的 bug。
- **set_timezone **(boolean, 可选, 默认值是off)
将这个选项设置为开启状态，将会在连接到 Oracle 数据库时将 Oracle 会话的时区设置为当前的 HighGoDB 参数中的时区值。只有在您计划使用 Oracle 的 TIMESTAMP WITH LOCAL TIME ZONE 类型列，并希望将它们转换为 HighGoDB 中的 timestamp without time zone 类型时，此选项才会有用。
请注意，如果在建立了与 Oracle 的连接之后更改时区，oracle_fdw 不会更改 Oracle 会话的时区。在这种情况下，您可以调用 oracle_close_connections() 方法，以便在下次访问外部表时打开一个新的连接。
如果 Oracle 不识别时区，连接将会失败，并出现类似的错误提示
ORA-01882: timezone region not found
在这种情况下，您可以选择使用不同的时区，或者将选项设置为关闭，并在 HighGoDB 服务器的环境中设置环境变量 ORA_SDTZ 为适当的值
3.3.User mapping 参数
- **user **(必填)
这是会话的 Oracle 用户名。
如果您不希望将 Oracle 凭据存储在 HighGoDB 数据库中（一种简单的方法是使用外部密码存储），可以将其设置为空字符串以进行外部身份验证。
- **password **(必填)
这是 Oracle 用户的密码。
3.4.Foreign table 参数
- **table **(必填)
这是 Oracle 表的名称。该名称必须与 Oracle 系统目录中的名称完全一致，通常只包含大写字母。要基于任意 Oracle 查询定义一个外部表，将此选项设置为括在括号中的查询，例如：
OPTIONS (table '(SELECT col FROM tab WHERE val = ''string'')')
在这种情况下不要设置 schema 选项。 对于基于简单查询定义的外部表，INSERT、UPDATE 和 DELETE 操作将正常工作；如果您想避免这种情况（或者对于更复杂的查询避免引起 Oracle 的错误信息混乱），请使用表选项 readonly 。
- dblink (可选)
访问表的 Oracle 数据库链接。此名称必须与 Oracle 的系统目录中出现的完全相同，通常只包含大写字母。
- **schema **(可选)
表的模式（或所有者）。这对于访问不属于连接的 Oracle 用户的表非常有用。此名称必须与 Oracle 的系统目录中出现的完全相同，通常只包含大写字母。
- **max_long **(可选，默认值是 32767 )
在Oracle表中，LONG、LONG RAW 和 XMLTYPE 列的最大长度。可能的值是介于1和1073741823之间的整数（ HighGoDB 中bytea的最大大小）。至少会分配这么多内存空间，因此较大的值会消耗大量内存。
如果 max_long 小于检索到的最长值的长度，您将收到错误消息 ORA-01406: fetched column value was truncated（检索的列值已截断）的错误提示。
- **readonly **(可选，默认值是false)
只有在未将此选项设置为yes/on/true的情况下，才允许对表进行INSERT、UPDATE和DELETE操作。
- sample_percent (可选，默认值是"100")
此选项仅影响ANALYZE处理，并且对于在合理的时间内分析非常大的表格非常有用。
该值必须介于0.000001和100之间，并定义将随机选择的Oracle表块的百分比，以计算 HighGoDB 表的统计信息。这是通过Oracle中的SAMPLE BLOCK (x)子句实现的。
对于使用Oracle查询定义的表，ANALYZE将失败并显示ORA-00933错误消息；对于使用复杂的Oracle视图定义的表，ANALYZE可能会失败并显示ORA-01446错误消息。
- **prefetch **(可选，默认值是 200)
该选项设置了在 HighGoDB 和 Oracle 之间进行外部表扫描时，通过单次往返获取的行数。这是通过使用Oracle的行预取功能实现的。该值必须介于0和10240之间，其中0表示禁用预取功能。
较高的值可以加快性能，但会在 HighGoDB 服务器上使用更多内存。
请注意，由于Oracle的限制，如果Oracle查询涉及到BLOB、CLOB或BFILE列，行预取功能将无法工作。因此，在外部表的这些列上进行查询时，如果获取大量行，性能会受到影响。
3.5.Column 参数
- key (可选，默认值是false)
如果将此选项设置为yes/on/true，则表示外部Oracle表上的相应列被视为主键列。为了使UPDATE和DELETE操作起作用，您必须在属于表主键的所有列上设置此选项。
- strip_zero (可选，默认值是false)
如果将此选项设置为yes/on/true，将在传输过程中从字符串中删除ASCII 0字符。在Oracle中，这些字符是有效的，但在 HighGoDB 中不是，因此在被oracle_fdw读取时会导致错误。此选项仅对于character、character varying和text列才有意义。
4.用法
4.1.Oracle权限
Oracle用户需要具有CREATE SESSION权限以及从特定表或视图中进行SELECT的权限。请注意，oracle_fdw在查询计划阶段访问Oracle表以获取其定义。这发生在对外部表的权限检查之前。因此，如果您尝试访问在 HighGoDB 中没有权限的外部表，则可能会收到Oracle错误。这是正常的，不会引起安全问题。
对于EXPLAIN VERBOSE，用户还需要在V$SQL和V$SQL_PLAN上具有SELECT权限。这些权限允许用户查看详细的查询执行计划信息。
4.2.连接
oracle_fdw会缓存Oracle连接，因为为每个单独的查询创建一个Oracle会话是代价昂贵的。所有连接在 HighGoDB 会话结束时会自动关闭。
函数oracle_close_connections()可用于关闭所有缓存的Oracle连接。这对于长时间运行的会话很有用，这些会话并不总是访问外部表，并且希望避免占用开放的Oracle连接所需的资源。
但是，在修改Oracle数据的事务内部不能调用此函数。这是因为在事务内部修改了Oracle数据后，关闭连接可能会导致未提交的更改丢失或事务无法回滚。因此，在修改Oracle数据的事务中，请避免调用oracle_close_connections()函数。
4.3.列
当您定义一个外部表时，Oracle表的列按其定义顺序映射到 HighGoDB 的列。
oracle_fdw只会在Oracle查询中包含 HighGoDB 查询实际所需的列。
HighGoDB 表可以比Oracle表拥有更多或更少的列。如果有更多的列，并且这些列被使用，您将收到一个警告，并返回NULL值。
如果您想要执行UPDATE或DELETE操作，请确保所有属于表主键的列上设置了key选项。如果未设置，将导致错误发生。
4.4.数据类型
在定义 HighGoDB 列时，您必须使用oracle_fdw可以转换的数据类型（请参阅下面的转换表）。只有在实际使用了该列时，才会执行此限制，因此您可以为无法转换的数据类型定义“虚拟”列，只要您不访问它们（此技巧仅适用于SELECT，而不能修改外部数据）。如果Oracle值超过了 HighGoDB 列的大小（例如，varchar列的长度或最大整数值），您将收到运行时错误。
这些转换由oracle_fdw自动处理：
| Oracle type | Possible HighGoDB types |
|---|---|
| CHAR | char, varchar, text |
| NCHAR | char, varchar, text |
| VARCHAR | char, varchar, text |
| VARCHAR2 | char, varchar, text, json |
| NVARCHAR2 | char, varchar, text |
| CLOB | char, varchar, text, json |
| LONG | char, varchar, text |
| RAW | uuid, bytea |
| BLOB | bytea |
| BFILE | bytea (read-only) |
| LONG RAW | bytea |
| NUMBER | numeric, float4, float8, char, varchar, text |
| NUMBER(n,m) with m<=0 | numeric, float4, float8, int2, int4, int8, boolean, char, varchar, text |
| FLOAT | numeric, float4, float8, char, varchar, text |
| BINARY_FLOAT | numeric, float4, float8, char, varchar, text |
| BINARY_DOUBLE | numeric, float4, float8, char, varchar, text |
| DATE | date, timestamp, timestamptz, char, varchar, text |
| TIMESTAMP | date, timestamp, timestamptz, char, varchar, text |
| TIMESTAMP WITH TIME ZONE | date, timestamp, timestamptz, char, varchar, text |
| TIMESTAMP WITH LOCAL TIME ZONE | date, timestamp, timestamptz, char, varchar, text |
| INTERVAL YEAR TO MONTH | interval, char, varchar, text |
| INTERVAL DAY TO SECOND | interval, char, varchar, text |
| XMLTYPE | xml, char, varchar, text |
| MDSYS.SDO_GEOMETRY | geometry |
如果将NUMBER转换为布尔值，0表示false，其他任何值都表示true。
插入或更新XMLTYPE只能使用不超过VARCHAR2数据类型的最大长度的值进行操作（最大长度为4000或32767，取决于MAX_STRING_SIZE参数设置）。
由于Oracle无法自动将NCLOB转换为客户端编码，目前不支持NCLOB类型。
如果您需要进行超过上述限制的转换，可以在Oracle或 HighGoDB 中定义一个适当的视图来进行转换。通过定义视图，您可以使用自定义逻辑来执行更复杂的数据转换操作。
4.5.WHERE 条件和 ORDER BY 子句
HighGoDB 将使用WHERE子句中适用的部分作为扫描的过滤条件。oracle_fdw构建的Oracle查询将包含与这些过滤条件相对应的WHERE子句，只要这些条件可以安全地转换为Oracle SQL。这个功能也被称为"WHERE子句的下推"，它可以大大减少从Oracle检索的行数，并且可以让Oracle的优化器选择一个好的访问所需表的计划。
类似地，ORDER BY子句将尽可能被下推到Oracle。但需要注意的是，任何按字符排序的ORDER BY条件都不会被下推，因为 HighGoDB 和Oracle中的排序顺序不能保证相同。
为了充分利用这一功能，请尽量在外部表中使用简单的条件。选择与Oracle类型相对应的 HighGoDB 列数据类型非常重要，因为如果数据类型不匹配，条件无法转换。
表达式now()、transaction_timestamp()、current_timestamp、current_date和localtimestamp将被正确转换。
EXPLAIN的输出将显示使用的Oracle查询，因此您可以了解哪些条件被转换为Oracle以及如何转换。这对于监视和优化查询执行过程非常有价值。
4.6.外部表之间的JOIN
oracle_fdw可以将连接操作下推到Oracle服务器，也就是说，两个外部表之间的连接将导致在Oracle端执行连接的单个查询。
在下推连接的情况下，存在一些限制：
-
两个表必须在同一个外部服务器上定义。
-
三个或更多表之间的JOIN不会被向下推。
-
JOIN必须在SELECT语句中进行。
-
oracle_fdw必须能够下推所有JOIN条件和WHERE子句。
-
没有JOIN条件的交叉连接不会被下推。
-
如果一个JOIN被下推，ORDER BY子句将不会被下推。
使用ANALYZE命令收集两个外部表的表统计信息来决定最佳的连接策略是非常重要的。这样 HighGoDB 能够更好地优化连接操作的执行计划。
4.7.修改外部数据
oracle_fdw在外部表上支持INSERT、UPDATE和DELETE操作。这在默认情况下是被允许的 ，并且可以通过设置readonly表选项来禁用该功能。
对于UPDATE和DELETE操作，与Oracle表的主键列相对应的列必须设置key列选项。这些列用于标识外部表的行，因此请确保该选项在所有属于主键的列上都已设置。
如果在INSERT操作中省略了外部表列，该列将被设置为 HighGoDB 外部表上DEFAULT子句中定义的值（如果没有DEFAULT子句，则为NULL）。不会使用对应的Oracle列上的DEFAULT子句。如果 HighGoDB 外部表没有包含Oracle表的所有列，则Oracle的DEFAULT子句将用于没有包含在外部表定义中的列。
INSERT、UPDATE和DELETE语句上的RETURNING子句是支持的，但不支持使用Oracle的LONG和LONG RAW数据类型的列（Oracle在RETURNING子句中不支持这些数据类型）。
使用AFTER和FOR EACH ROW定义的触发器要求外部表没有Oracle数据类型为LONG或LONG RAW的列。这是因为此类触发器使用了上述的RETURNING子句。
尽管修改外部数据是可以的，但性能通常不是特别好，特别是在涉及到大量行的情况下，这是因为（由于外部数据包装器的工作方式）每行都必须逐个处理。
事务会被转发到Oracle，因此BEGIN、COMMIT、ROLLBACK和SAVEPOINT按预期工作。不支持涉及Oracle的预编译语句。
由于oracle_fdw默认使用序列化事务，导致数据修改语句可能引起序列化失败。
ORA-08177: can't serialize access for this transaction
如果并发事务修改表，并且事务运行时间较长，就可能发生这种情况。这种错误可以通过其SQLSTATE（40001）进行识别。使用oracle_fdw的应用程序应该对失败并出现此错误的事务进行重试。
可以使用不同的事务隔离级别，详情请参阅Foreign server options进行讨论。
4.8.EXPLAIN
HighGoDB 的EXPLAIN命令可以显示实际发送到Oracle的查询语句。使用EXPLAIN VERBOSE会显示Oracle的执行计划（这在Oracle 9i或更早版本的服务器上不起作用）。
当使用oracle_fdw扩展执行查询时，可以使用EXPLAIN和EXPLAIN VERBOSE进行查询计划分析。
使用EXPLAIN命令可以查看由 HighGoDB 发送到Oracle的查询语句以及 HighGoDB 本身的查询计划信息。它提供了关于查询优化和执行的有用指导，但不会显示Oracle服务器的执行细节。
要获取Oracle服务器的详细执行计划，可以使用EXPLAIN VERBOSE命令。这将通过oracle_fdw扩展将查询发送到Oracle，并获取Oracle服务器返回的详细执行计划信息。但需要注意，在旧版本的Oracle服务器（9i或更早版本）中，这可能会出现问题，并可能不起作用。
因此，在使用EXPLAIN VERBOSE之前，需要确认所使用的Oracle服务器的版本和兼容性。如果使用的是较旧的Oracle服务器版本，它可能无法提供所需的执行计划细节。
总之，通过使用EXPLAIN和EXPLAIN VERBOSE命令，可以获得关于oracle_fdw查询的查询计划信息和Oracle服务器的执行计划信息，但在使用EXPLAIN VERBOSE时需注意与Oracle服务器的版本和兼容性。
4.9.ANALYZE
您可以使用ANALYZE命令对外部表进行统计信息收集，在oracle_fdw扩展中支持此功能。
如果没有统计信息，HighGoDB 无法估计对外部表的查询行数，这可能导致选择错误的执行计划。
HighGoDB 不会像对普通表那样，通过自动清理守护进程（autovacuum daemon）自动收集外部表的统计信息，因此在创建外部表后和远程表发生显著更改时，运行ANALYZE命令对外部表进行统计信息收集尤为重要。
需要注意的是，对Oracle外部表进行分析将导致全表连续扫描。您可以使用表选项中的sample_percent来加快分析速度，通过仅使用Oracle表的样本进行分析。
通过以下语法可以运行ANALYZE命令来收集外部表的统计信息：
ANALYZE [VERBOSE] foreign_table_name;
其中，foreign_table_name指定要进行统计信息收集的外部表的名称。使用VERBOSE选项可以输出收集统计信息的详细信息。
请确保在合适的时候运行ANALYZE命令，以确保外部表的执行计划基于准确的统计信息，从而提高查询性能。
4.10.PostGIS 支持
几何数据类型geometry只有在安装了PostGIS扩展后才可用。
oracle_fdw扩展仅支持以下几何类型：POINT、LINE、POLYGON、MULTIPOINT、MULTILINE和MULTIPOLYGON，二维和三维均可。不支持空的PostGIS几何对象，因为它们在Oracle Spatial中没有等效的表示方式。
对于Oracle SRID的NULL值，将被转换为0，反之亦然。对于Oracle SRID和PostGIS SRID之间的其他转换，请在 HighGoDB 的share目录中创建一个名为srid.map的文件。该文件的每一行应包含一个Oracle SRID和相应的PostGIS SRID，两者之间以空格分隔。为了获得良好的性能，请保持该文件的大小适中。
使用srid.map文件可以定义Oracle SRID和PostGIS SRID之间的转换关系，以便在查询中进行几何数据类型的转换。
请注意，要使用PostGIS的几何数据类型，必须确保已正确安装和配置PostGIS扩展，并在数据库中创建了相应的扩展。
总结而言，PostGIS扩展提供了丰富的几何数据类型和操作，允许在 HighGoDB 中处理空间数据。oracle_fdw扩展通过支持部分几何类型，使得在使用外部Oracle数据源时能够处理基本的空间数据操作。
4.11.IMPORT FOREIGN SCHEMA
IMPORT FOREIGN SCHEMA 可以用来批量导入 Oracle 模式中所有表的定义。
IMPORT FOREIGN SCHEMA 将会根据 ALL_TAB_COLUMNS 中的对象创建外表。这包括表、视图和物化视图，但不包括同义词。
下面是 IMPORT FOREIGN SCHEMA 支持的选项：
- case：控制导入过程中表和列名的大小写转换
可选的取值包括：
* keep：保留 Oracle 中的名称，通常是大写字母。
* lower：将所有表和列名转换为小写字母。
* smart：只转换在 Oracle 中全部是大写字母的名称（默认选项）。
- collation：大小写转换时所使用的排序规则，适用于 lower 和 smart 选项
默认值为 default，表示使用数据库的默认排序规则。仅支持 pg_catalog 模式中的排序规则。可以在 pg_collation 目录中查看 collname 字段的值以获取可能的取值列表。
- dblink：用于访问模式的 Oracle 数据库链接
链接名称必须与 Oracle 系统目录中出现的完全相同，通常只包含大写字母。
- readonly：在所有导入表上设置 readonly 选项。
- max_long：在所有导入表上设置 max_long 选项。
- sample_percent：在所有导入表上设置 sample_percent 选项。
- prefetch：在所有导入表上设置 prefetch 选项。
Oracle 模式名称必须与 Oracle 中的名称完全一致，通常是大写字母。如果模式名称中包含小写字母或特殊字符，则需要将其用双引号括起来（例如 “SCOTT”）。
LIMIT TO或EXCEPT子句中的表名必须按照上述大小写折叠后在 HighGoDB 中出现的方式编写。
使用 IMPORT FOREIGN SCHEMA 命令时，该命令将会在 HighGoDB 中为 Oracle 模式中的所有对象创建外部表，包括表、视图和物化视图。但是，同义词不会被包含在内。
请确保在使用 IMPORT FOREIGN SCHEMA 命令之前具备必要的权限，并正确配置了 HighGoDB 中的 Oracle 外部数据包装器扩展。
请注意，导入外部SCHEMA不适用于oracle server 8i。
5.示例
5.1.加载oracle_fdw插件
create extension oracle_fdw with schema <模式名>;
5.2.创建外部服务器 SERVER
创建Oracle数据库映射需要登录超级用户管理员操作。
需要注意的是 isolation_level 参数，指定在Oracle中的事务隔离级别，默认是serializable。会在意想不到的情况下（如向表中插入数据时）引发序列化错误（ORA-08177），配置为 read_committed 可解决此问题，详情可参考 3.2.Foreign server 参数
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.100.20:1521/orcl',isolation_level 'read_committed');
- oradb： 自定义一个 SERVER NAME
- OPTIONS: 有3个参数 dbserver、isolation_level、nchar
- dbserver(必需)：定义连接 Oracle 数据库的连接字符串。
- isolation_level(可选，默认为 serializable): 在 Oracle 数据库中使用的事务隔离级别，可设置的参数值 serializable、read_committed、read_only。
- nchar(可选，默认为 off): 是否开启 Oracle 端的字符转换，这个参数的开启对性能有很大影响。
将 SERVER 赋权给普通用户使用
GRANT USAGE ON FOREIGN SERVER oradb TO hguser;
列出已经创建的foreign server命令：\des+
hgdb=# \des+ 外部服务器列表
名称 | 拥有者 | 外部数据封装器 | 存取权限 | 类型 | 版本 | FDW选项 | 描述
-------+--------+----------------+-----------------+------+------+---------------------------------------------------------------------------+------
oradb | highgo | oracle_fdw | highgo=U/highgo+| | | (dbserver '//192.168.100.20:1521/orcl', isolation_level 'read_committed') |
| | | hguser=U/highgo | | | |
(1 行记录)
5.3.创建用户映射
创建用户映射。命令如下，此步一定要进入上一步赋权的用户进行操作，否则创建外部表失败。
CREATE USER MAPPING FOR hguser SERVER oradb OPTIONS (user 'orauser', password '111111');
- hguser ： HighGoDB 中已存在的用户
- oradb： 已创建的 SERVER NAME
- OPTIONS： 有2个参数 user、password
- user(必需)：Oracle 用户名
- password(必需)：Oracle 用户的密码
列出已经创建的USER MAPPING命令是：\deu+
hgdb=> \deu+
列出用户映射
服务器 | 用户名: | FDW选项
--------+----------+---------------------------------------
oradb | hguser | ("user" 'orauser', password '111111')
(1 行记录)
5.5.创建Oracle的外部表
想要在 HighGoDB 中操作 Oracle 的 emp_list 表，首先在 HighGoDB 中创建外部表，外部表指向 Oracle 的 emp_list 表，外部表有两种创建方式。
5.5.1.CREATE FOREIGN TABLE
需要注意：
- 外部表的字段名不需要与 Oracle 的表保持一致，但是字段顺序需要与 Oracle 的表保持一致
- 外部表的主键需要与 Oracle 的表保持一致
- OPTIONS 属性里的 schema 和 table 名称必须大写，不然 HighGoDB 端操作外部表会报 ‘ORA-00942: table or view does not exist’。
- OPTIONS 属性里的 schema 和 table 必须用单引号
- 外部表的结构需要和Oracle中的映射表结构保持一致
CREATE FOREIGN TABLE ora_emp_list
( empno VARCHAR(64) OPTIONS (key 'true') NOT NULL
, ename VARCHAR(10)
, job VARCHAR(9)
, mgr NUMERIC(4,0)
, hiredate TIMESTAMP
, sal NUMERIC(7,2)
, comm NUMERIC(7,2)
, deptno NUMERIC(2,0)
)
SERVER oradb OPTIONS (schema 'ORAUSER', table 'EMP_LIST');
- ora_emp_list： HighGoDB 自定义外部表名
- oradb： 已创建的 SERVER NAME
- 外部表 OPTIONS: 有7个参数 table、dblink、schema、max_long、readonly、sample_percent、prefetch
- table(必需)：Oracle 端的表名，必须大写，也可以自定义查询，后面有示例
- dblink(可选): 访问表所需的 Oracle 端的 database link，必须大写
- schema(可选): Oracle 端的表所属用户，适用于访问不属于连接 Oracle 用户的表，必须大写
- max_long(可选，默认是 ‘32767’): Oracle 表中 LONG、LONG RAW 和 XMLTYPE 列的最大长度。如果 max_long 小于检索到的最长值的长度，您将收到错误消息 ORA-01406: fetched column value was truncated。
- readonly(可选，默认是 ‘false’): 仅在此选项未设置为 yes/on/true 的表上才允许 DML 操作。
- sample_percent(可选，默认是 ‘100’): 此选项仅影响 ANALYZE 处理，可用于在合理的时间内 ANALYZE 非常大的表。
- prefetch(可选，默认是 ‘200’): 设置在外部表扫描期间通过 HighGoDB 和 Oracle 之间的单次往返获取的行数。该值必须介于 0 和 10240 之间，其中零值禁用预读。
- 列 OPTIONS: 有2个参数 key、strip_zeros
- key(可选，默认是 ‘false’): 如果设置为 yes/on/true，则外部 Oracle 表上的相应列被视为主键列。要使 UPDATE 和 DELETE 起作用，您必须在属于表主键的所有列上设置此选项。
- strip_zeros(可选，默认是 ‘false’): 如果设置为 yes/on/true，ASCII 0 字符将在传输过程中从字符串中删除。此类字符在 Oracle 中有效，但在 HighGoDB 中无效，因此在被 oracle_fdw 读取时会导致错误。此选项仅对character,character varying 和 text 列有意义。
5.5.2.IMPORT FOREIGN SCHEMA
需要注意：
- 这种方式不需要指定表结构，但是外部表名需要一致，也就是当前 HighGoDB 的 SCHEMA 下不能存在同名表，否则创建失败。
- IMPORT FOREIGN SCHEMA 将为在 ALL_TAB_COLUMNS 中找到的所有对象创建外部表。这包括表、视图和物化视图，但不包括同义词。
- Oracle SCHEMA 名称通常为大写。由于 HighGoDB 在处理之前将名称转换为小写，因此您必须用双引号保护 SCHEMA 名称（例如"SCOTT"）。
- LIMIT TO 导入括号内包含的表，多个表以逗号分隔，EXCEPT 导入不包含(排除)括号内的表，多个表以逗号分隔。
--导入所有
import FOREIGN SCHEMA "SCOTT" from server oradb into scott options(prefetch '10240');
--使用limit选择导入外部表
import FOREIGN SCHEMA "SCOTT" limit to (emp,dept) from server oradb into scott options(prefetch '10240');
--使用except排除相关表
import FOREIGN SCHEMA "SCOTT" except (emp,dept) from server oradb into scott options(prefetch '10240');
IMPORT FOREIGN SCHEMA 支持的选项：
- case(默认：smart)：控制导入期间表名和列名的大小写，参数值：
- keep: 保留 Oracle 中的名称，通常为大写。
- lower: 将所有表名和列名转换为小写。
- smart: 仅转换 Oracle 中全部大写的名称。
- collation(默认：default)：用于 case 选项的 lower 和 smart 选项的排序规则
- dblink
- readonly
- max_long
- sample_percent
- prefetch
5.5.3.查看已创建的外部表信息
hgdb=> \det+ ora_fdw.
引用表列表
架构模式 | 数据表 | 服务器 | FDW选项 | 描述
----------+----------+--------+---------------------------------------------------------------------------+------
ora_fdw | emp_list | oradb | (schema 'ORAUSER', "table" 'EMP_LIST', readonly 'true', prefetch '10240') |
(1 行记录)
5.6.删除创建的对象
drop foreign table oratab;
drop user mapping for test server oracle_91;
drop server oracle_91; #创建用户删除
DROP EXTENSION oracle_fdw; #创建用户删除
6.oracle_fdw函数
hgdb=> \df ora_fdw.
函数列表
架构模式 | 名称 | 结果数据类型 | 参数数据类型 | 类型
----------+--------------------------+--------------+-----------------------------+------
ora_fdw | oracle_close_connections | void | | 函数
ora_fdw | oracle_diag | text | name DEFAULT NULL::name | 函数
ora_fdw | oracle_execute | void | server name, statement text | 函数
ora_fdw | oracle_fdw_handler | fdw_handler | | 函数
ora_fdw | oracle_fdw_validator | void | text[], oid | 函数
(5 行记录)
函数 oracle_close_connections
oracle_fdw 会缓存 Oracle 连接，因为为每个单独的查询创建 Oracle 会话的成本很高。当 HighGoDB 会话结束时，所有连接都会自动关闭。
函数 oracle_close_connections() 可用于关闭所有缓存的 Oracle 连接，但是不能在修改 Oracle 数据的事务中调用此函数。
函数 oracle_diag
- 此函数仅用于诊断目的。
- 它将返回 oracle_fdw、HighGoDB 服务器和 Oracle 客户端的版本。如果调用时不带参数或者指定 NULL ，它将额外返回一些用于建立 Oracle 连接的环境变量的值。
- 如果调用时指定外部服务器的名称，它将额外返回 Oracle 服务器版本。
hgdb=> select ora_fdw.oracle_diag();
oracle_diag
--------------------------------------------------------------------------------------------------
oracle_fdw 2.5.0, PostgreSQL 12.7, Oracle client 11.2.0.4.0, ORACLE_HOME=/opt/instantclient_11_2
(1 行记录)
函数 oracle_execute
- 该函数可用于在远程 Oracle 服务器上执行任意 SQL 语句。这仅适用于不返回结果的语句（通常是 DDL 语句）。
- 使用该函数时要小心，因为它可能会干扰 oracle_fdw 的事务管理。请记住，在 Oracle 中运行 DDL 语句将发出隐式 COMMIT。最好建议您在多版本并发事务之外使用此功能。
-- 注意 SQL 语句结尾不要加分号
hgdb=# SELECT oracle_execute('oradb','drop table orauser.t111');
oracle_execute
----------------
(1 row)
7.问题优化
7.1.序列化错误（ORA-08177）
ERROR: error executing query: OCIStmtExecute failed to execute remote query
DETAIL: ORA-08177: can't serialize access for this transaction
--方式一：在创建 server 时，设置 isolation_level 'read_committed'
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.100.20:1521/orcl',isolation_level 'read_committed');
--方式二：更改外部服务器的定义
alter server oradb options (add isolation_level 'read_committed');
7.2.未指定默认值
外部表没有指定默认值，oracle_fdw 会将其替换为 NULL 并将其发送给 Oracle。
错误: error executing query: OCIStmtExecute failed to execute remote query
描述: ORA-01400: 无法将 NULL 插入 ("ORAUSER"."EMP"."DEPT")
--解决方案：在外部表对应的列添加默认值
alter foreign table ora_fdw.emp alter column dept set default 1;
hgdb=> \d+ ora_fdw.emp
引用的外部表 "ora_fdw.emp"
栏位 | 类型 | 校对规则 | 可空的 | 预设 | FDW选项 | 存储 | 统计目标 | 描述
------+-----------------------+----------+----------+------+--------------+----------+----------+------
id | character varying(64) | | not null | | (key 'true') | extended | |
name | character varying(32) | | not null | | | extended | |
dept | numeric | | not null | 1 | | main | |
服务器 oradb
FDW选项: (schema 'ORAUSER', "table" 'EMP')
7.3.修改外部表的数据
想要对外部表的数据进行修改操作，必须满足外部访问的表是有主键的情况，默认的列选项key设置的是false。需要手工设置对应的列名的key选项为true。
错误: no primary key column specified for foreign Oracle table
描述: For UPDATE or DELETE, at least one foreign table column must be marked as primary key column.
提示: Set the option "key" on the columns that belong to the primary key.
alter foreign table ora_fdw.emp alter column id options (add key 'true');
7.4.SQL性能
prefetch代表外部表扫描期间通过 HighGoDB 和Oracle之间的单次往返获取的行数，这是使用Oracle行预取实现的。该值必须介于0到10240之间，其中零值将禁用预取。设置较大的值可以提高性能，但是在 HighGoDB 服务器中会消耗更多的内存。
通过测试oracle_fdw的prefetch参数值大小对性能的影响，结论是建议把prefetch设置到最大值10240。这样速度上相比默认会有大约60%的提升。对于占用内存，发现在prefetch设置为10240后，对2000万表进行select*查询，进程内存和私有内存使用还是可控的，并没出现大幅增长。
--方式一：在创建或导入外部表时添加属性prefetch
--方式二：修改外部表
alter foreign table ora_fdw.emp options (add prefetch '10240');
407
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
