61. oracle_fdw
61.1. 插件oracle_fdw简介
oracle_fdw是kingbaseES的一个扩展插件,它提供了一个外部数据包装器,可以方便高效的访问oracle数据库,包括WHERE条件和所需列的下推以及全面的EXPLAIN支持。
61.2. 插件oracle_fdw的加载方式
在ksql中运行:
create extension oracle_fdw
61.3. 插件oracle_fdw的参数配置
61.3.1. Foreign data wrapper 参数
-
nls_lang (可选)
将Oracle的NLS_LANG环境变量设置为此值。 NLS_LANG的格式为"language_territory.charset"(例如 AMERICAN_AMERICA.AL32UTF8)。这必须与你的数据库编码匹配。 如果未设置此值,oracle_fdw将自动做正确的事情,如果他不能,则发出警告。仅当你知道自己在做什么时才设置此选项。
61.3.2. Foreign server 参数
-
dbserver (必填)
远程数据库的Oracle数据库连接字符串. 这个可以是Oracle支持的任何形式,只要你的Oracle客户端进行了相应的配置。 将此设置为本地("BEQUEATH")连接的空字符串
-
isolation_level (可选,默认值是serializable)
在Oracle数据库中使用的事物隔离级别。 该值可以是serializable, read_committed, read_only
请注意,单个Kingbase语句期间可以多次查询Oracle表(例如,在一个nested loop join期间)。为了确保不会出现由并发事务的竞争条件引起的不一致,事务隔离级别必须保证事务读取稳定性。 只有Oraclede SERIALIZABLE或者READ ONLY隔离级别才能保证这一点
不行的是,oracle的SERIALIZABLE实现的相当糟糕,并在意外情况下导致序列化错误(ORA-08177),例如插入到表中。 使用READ COMMITTED事务可以解决此问题,但存在不一致的风险。如果你想使用它,请检查你的执行计划是否可以多次执行外部扫描。
-
nchar (boolean类型,可选,默认值是off)
将此选项设置为on会在Oracle端选择跟昂贵的字符转换。如果你使用单字节Oracle数据库字符集,但NCHAR或NVARCHAR2列包含数据库字符集中无法表示的字符,则这是必填的
将nchar设置为on会对性能产生显著的影响,并且会在Update语句中导致ORA-01461错误,当该语句设置的字符串超过2000字节时(如果你又MAX_STRING_SIZE=EXTENDED,则为16383)。此错误似乎是Oracle的错误。
61.3.3. User mapping 参数
-
user (必填)
会话的Oracle用户名 如果你不想在Kingbase数据库中存储Oracle凭据(一种简单的方法是使用外部密码存储),请将其设置为外部身份验证的空字符串
-
password (必填)
Oracle用户的密码
61.3.4. Foreign table 参数
-
table (必填)
Oracle表名。此名称必须与Oracle系统目录中出现的完全相同,因此通常只包含大写字母
要基于任意Oracle查询定义外部表,请将此选项设置为括号中的查询,例如
OPTIONS (table '(SELECT col FROM tab WHERE val = ''string'')')
在这汇总情况下不要设置scheam选项 INSERT、UPDATE和DELETE将作用于简单查询中定义的外部表;如果你想避免这种情况(或对更复杂的查询造成混淆Oracle错误消息),请使用table选项readonly
-
dblink (可选)
Oracle database link的名字。这个名称必须与Oracle系统目录中出现的完全相同,因此通常只包含大写字母
-
schema (可选)
表的scheam。对应访问不属于连接Oracle用户的表很有用。此名称必须要Oracle系统目录中出现的完全相同,因此通常只包含大写字母
-
max_long (可选,默认值是32767)
Oracle表中LONG, LONG RAW, XMLTYPE类型的列的最大长度。可能的值是1到1073741823之间的整数(kingbase中一个字节的最大大小)。此内存量至少分配两次,因此大量的值将消耗大量内存
-
readonly (可选,默认值是false)
INSERT, UPDATE, DELETE只允许操作这个选项没有被设置成yes/on/true的表
-
sample_percent (可选,默认值是"100")
此选项仅影响ANALYZE处理,可用于在合理的时间内ANALYZE非常大的表
该值必须介于0.000001和100之间,并定义将随机选择以计算kingbase表统计信息的Oracle表块的百分比。这是使用Oracle中SAMPLE BLOCK(x)子句完成的
对于使用Oracle查询定义的表,ANALYZE将失败并出现ORA-00933,而对于使用复杂Oracle视图定义的表,ANALYZE可能会失败并出现ORA-01446
-
prefetch (可选,默认值是200)
设置在外部表扫描期间通过kingbase与Oracle之间单次往返获取的行数。这个是使用Oracle行预取实现的。该值必须介于0和10240之间,其中零值禁用预取
较高的值可以提高性能,但是会在kingbase服务器上使用更多的内存
请注意,由于Oracle的限制,如果Oracle查询涉及BLOB, CLOB或BFILE列,行预取将不起作用。因此,如果你获取许多行,对外部表上此类列的查询将执行的很差
61.3.5. 列参数
-
key (可选,默认值是false)
如果被设置成了yes/on/true,Oracle表中对应的列被认为是主键列。 为了让UPDATE, DELETE工作,必须将所有的主键列都设置上这个选项
-
strip_zero (可选,默认值是false)
如果设置为yes/on/true,ASCII 0 字符将在传输过程中从字符串中删除。此类字符在Oracle中有效,但是在Kingbase中无效,因此再被oracle_fdw读取时会导致错误。此选项仅针对character,character varying和text类型的列
61.4. 插件oracle_fdw的使用方法
oracle_fdw需要依赖GLIBC 2.9及以上的版本才能运行。
61.4.1. Oracle权限
Oracle用户需要CREATE SESSION权限和对指定表或视图的SELECT权限。
对于EXPLAIN VERBOSE,用户还需要对V$SQL和V$SQL_PLAN的SELECT权限。
61.4.2. 连接
oracle_fdw会缓存与oracle的连接,因为每个查询创建oracle的成本很高。当Kingbase会话结束时,所有连接都将自动关闭。
函数oraclr_colse_connections()可用于关闭所有缓存的oracle连接。这对于长时间运行的不经常访问外部表的会话非常有用。可以避免阻塞并开放oracle所需的资源。 对于正在修改oracle数据的事务中不能使用此函数。
61.4.3. 列
定义外部表时,oracle表的列将按定义顺序映射到kingbase列。
oracle_fdw将只在oracle查询中包含kingbase查询实际需要的那些列。
kingbase表可以比oracle表有更多或更少的列。如果有更多的列,并且使用了这些列,将会收到警告并返回空值。
如果要更新或删除,请确保在属于表主键的所有列上都设置了key选项,否则将导致错误。
61.4.4. 数据类型
必须使用oracle_fdw可以转换的数据类型定义kingbase列(参考下面的类型转换表)。此限制仅在实际使用列时才强制执行,因此只要不访问不可转换的数据类型,就可以为它们定义伪列(此方法仅适用于SELECT,而不适用于修改外部数据)。如果oracle值超过kingbase列的大小(如varchar列的长度或者最大值),将会报错。
下面这些类型之间的转换将由oracle_fdw自动处理:
Oracle type | Possible Kingbase 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 |
如果一个数字被转换成布尔值,0意味着false,其他的都是true。
插入或更新XMLTYPE只适用于不超过VARCHAR2数据类型的最大长度(4000或32767,取决于MAX_STRING_SIZE参数)的值。
oracle_fdw目前不支持NCLOB,因为Oracle无法自动将其转换为客户端编码。
如果需要的转换的类型不在上述列表中,请在Oracle或Kingbase中定义适当的视图。
61.4.5. WHERE条件和ORDER BY子句
kingbase将使用WHERE子句的所有适用部分作为扫描的过滤器。Oracle_fdw构造的Oracle查询将包含与这些筛选条件相对应的WHERE子句,只要这样的条件可以安全地转换为Oracle SQL。这个特性,也称为WHERE子句的下推,可以极大地减少从Oracle检索到的行数,并使Oracle的优化器能够选择一个好的计划来访问所需的表。
同样,ORDERBY子句将尽可能下推到Oracle。请注意,按字符串排序的ORDER-BY条件不会被下推,因为Kingbase和Oracle中的排序顺序不能保证相同。
要利用它,请尝试对外部表使用简单的条件。选择与Oracle类型相对应的kingbase列数据类型,否则条件将无法转换。
表达式now()、transaction_timestamp()、current_timestamp、current_date和localtimestamp将被正确翻译。
EXPLAIN的输出将显示所使用的Oracle查询,因此可以看到哪些条件被转换为Oracle以及如何转换。
61.4.6. 外部表之间的JOIN
oracle_fdw可以向下推连接到oracle服务器,也就是说,两个外部表之间的连接将导致在oracle端执行连接的单个oracle查询。
发生这种情况时有一些限制:
-
两个表必须在同一外部服务器上定义。
-
三个或更多表之间的JOIN不会被向下推。
-
JOIN接必须在SELECT语句中。
-
oracle_fdw必须能够下推所有JOIN条件和WHERE子句。
-
没有JOIN条件的交叉JOIN不会向下推。
-
如果一个JOIN被下推,ORDER BY子句将不会被下推。
使用ANALYZE收集两个外部表的表统计信息以确定最佳JOIN策略非常重要。
61.4.7. 修改外部数据
oracle_fdw支持在外部表上插入、更新和删除。这在默认情况下是允许的,并且可以通过设置readonly table选项来禁用。
要使UPDATE和DELETE工作,必须设置与Oracle表的主键列对应的列的key column选项。这些列用于标识外部表行,因此请确保在属于主键的所有列上都设置了该选项。
如果在INSERT过程中省略外表列,则该列将设置为kingbase外表的DEFAULT子句中定义的值(如果没有DEFAULT子句,则设置为NULL)。不使用相应Oracle列上的默认子句。如果kingbase外部表不包含Oracle表的所有列,则Oracle DEFAULT子句将用于外部表定义中未包含的列。
INSERT、UPDATE和DELETE上的RETURNING子句受支持,但Oracle数据类型为LONG和LONG RAW的列除外(RETURNING子句中Oracle不支持这些数据类型)。
用AFTER和FOR为每行定义的触发器要求外表中没有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的应用程序应该重试由于此错误而失败的事务。
可以使用不同的事务隔离级别,有关讨论,请参阅外部服务器选项。
61.4.8. EXPLAIN
kingbase的EXPLAIN将显示实际发出给Oracle的查询。EXPLAIN VERBOSE将显示Oracle的执行计划(该计划不适用于Oracle server 9i或更早版本)。
61.4.9. ANALYZE
可以使用ANALYZE收集外部表的统计信息。这得到了oracle_fdw的支持。
如果没有统计信息,kingbase就无法估计外部表上查询的行数,这可能会导致选择错误的执行计划。
kingbase不会像对普通表那样使用autovacuum守护进程自动收集外部表的统计信息,因此在创建外部表之后以及远程表发生重大更改时,对外部表运行ANALYZE尤为重要。
分析Oracle外部表将导致完整的顺序表扫描。可以使用table选项sample_percent,通过只使用Oracle表的一个示例来加快速度。
61.4.10. PostGIS支持
数据类型geometry仅在安装PostGIS时可用。
仅支持二维和三维的POINT, LINE, POLYGON, MULTIPOINT, MULTILINE, MULTIPOLYGONT等几何体类型。不支持空的PostGIS几何图形,因为它们在Oracle Spatial中没有等效的几何图形。
Oracle SRID的空值将转换为0,反之亦然。对于Oracle SRID和PostGIS SRID之间的其他转换,请创建一个srid.map文件在kingbase的share目录中。该文件的每一行都应包含一个Oracle SRID和相应的PostGIS SRID,用空格隔开。保持文件小以获得良好的性能。
61.4.11. 支持 IMPORT FOREIGN SCHEMA
除了导入外部SCHEMA的文档外,还应考虑以下内容:
导入外部SCHEMA将为ALL_TAB_COLUMNS中的所有对象创建外部表。包括表、视图和物化视图,但不包括同义词。
以下是导入外部SCHEMA支持的选项:
-
case:控制导入期间表名和列名的大小写折叠
可能的值为:
-
keep:保留甲骨文中的名字,通常用大写。
-
lower:将所有表名和列名转换为小写。
-
smart:只翻译在Oracle中都是大写的名称(这是默认值)。
-
-
collation:用于case选项中的lower和smart值
默认值是default,这是数据库的默认排序规则。只支持sys_catalog SCHEMA中的排序规则。有关可能值的列表,请参sys_collation目录中的collname值。
-
dblink:访问SCHEMA的Oracle数据库链接
此名称必须完全按照Oracle系统目录中的名称书写,因此通常仅由大写字母组成。
-
readonly:在所有导入的表上设置readonly选项
-
max_long:在所有导入的表上设置max_long选项
-
sample_percent:在所有导入的表上设置sample_percent选项
-
prefetch:在所有导入的表上设置prefetch选项
Oracle SCHEMA名称必须与Oracle中的名称完全相同,因此通常使用大写。由于kingbase在处理之前将名称转换为小写,因此必须用双引号保护SCHEMA名称(例如“SCOTT”)。
LIMIT TO或EXCEPT子句中的表名必须按照上述大小写折叠后在kingbase中出现的方式编写。
请注意,导入外部SCHEMA不适用于oracle server 8i。
注意
oracle_fdw目前不支持批量导入大量数据,如需导入数据,请使用oracle数据导入工具进行操作。 在使用oracle_fdw时,其内部函数会将WHERE子句中的条件进行倒序处理,因此会导致WHERE条件的执行顺序与本地执行的WHERE条件顺序相反。如果WHERE子句中引用了序列,那么会因条件倒序引发结果与预期不符的情况,使用需要注意应避开此类情况。
61.4.12. 示例
CREATE EXTENSION oracle_fdw; -- TWO_TASK or ORACLE_HOME and ORACLE_SID must be set in the server's environment for this to work CREATE SERVER oracle FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//serverip:port/orcl', isolation_level 'read_committed'); CREATE USER MAPPING FOR PUBLIC SERVER oracle OPTIONS (user 'C##WQ', password '123'); -- drop the Oracle tables if they exist DO $$BEGIN SELECT oracle_execute('oracle', 'DROP TABLE C##WQ.typetest1 PURGE'); EXCEPTION WHEN OTHERS THEN NULL; END;$$; DO $$BEGIN SELECT oracle_execute('oracle', 'DROP TABLE C##WQ.gis PURGE'); EXCEPTION WHEN OTHERS THEN NULL; END;$$; SELECT oracle_execute( 'oracle', E'CREATE TABLE C##WQ.typetest1 (\n' ' id NUMBER(5)\n' ' CONSTRAINT typetest1_pkey PRIMARY KEY,\n' ' c CHAR(10 CHAR),\n' ' nc NCHAR(10),\n' ' vc VARCHAR2(10 CHAR),\n' ' nvc NVARCHAR2(10),\n' ' lc CLOB,\n' ' r RAW(10),\n' ' u RAW(16),\n' ' lb BLOB,\n' ' lr LONG RAW,\n' ' b NUMBER(1),\n' ' num NUMBER(7,5),\n' ' fl BINARY_FLOAT,\n' ' db BINARY_DOUBLE,\n' ' d DATE,\n' ' ts TIMESTAMP WITH TIME ZONE,\n' ' ids INTERVAL DAY TO SECOND,\n' ' iym INTERVAL YEAR TO MONTH\n' ') SEGMENT CREATION IMMEDIATE' ); oracle_execute ---------------- (1 row) SELECT oracle_execute( 'oracle', E'CREATE TABLE C##WQ.gis (\n' ' id NUMBER(5) PRIMARY KEY,\n' ' g MDSYS.SDO_GEOMETRY\n' ') SEGMENT CREATION IMMEDIATE' ); oracle_execute ---------------- (1 row) -- gather statistics SELECT oracle_execute( 'oracle', E'BEGIN\n' ' DBMS_STATS.GATHER_TABLE_STATS (''C##WQ'', ''TYPETEST1'', NULL, 100);\n' 'END;' ); oracle_execute ---------------- (1 row) SELECT oracle_execute( 'oracle', E'BEGIN\n' ' DBMS_STATS.GATHER_TABLE_STATS (''C##WQ'', ''GIS'', NULL, 100);\n' 'END;' ); oracle_execute ---------------- (1 row) -- create the foreign tables CREATE FOREIGN TABLE typetest1 ( id integer OPTIONS (key 'yes') NOT NULL, q double precision, c character(10), nc character(10), vc character varying(10), nvc character varying(10), lc text, r bytea, u uuid, lb bytea, lr bytea, b boolean, num numeric(7,5), fl float, db double precision, d date, ts timestamp with time zone, ids interval, iym interval ) SERVER oracle OPTIONS (table 'TYPETEST1'); ALTER FOREIGN TABLE typetest1 DROP q; -- a table that is missing some fields CREATE FOREIGN TABLE shorty ( id integer OPTIONS (key 'yes') NOT NULL, c character(10) ) SERVER oracle OPTIONS (table 'TYPETEST1'); -- a table that has some extra fields CREATE FOREIGN TABLE longy ( id integer OPTIONS (key 'yes') NOT NULL, c character(10), nc character(10), vc character varying(10), nvc character varying(10), lc text, r bytea, u uuid, lb bytea, lr bytea, b boolean, num numeric(7,5), fl float, db double precision, d date, ts timestamp with time zone, ids interval, iym interval, x integer ) SERVER oracle OPTIONS (table 'TYPETEST1'); INSERT INTO typetest1 (id, c, nc, vc, nvc, lc, r, u, lb, lr, b, num, fl, db, d, ts, ids, iym) VALUES ( 1, 'fixed char', 'nat''l char', 'varlena', 'nat''l var', 'character large object', bytea('\xDEADBEEF'), uuid('055e26fa-f1d8-771f-e053-1645990add93'), bytea('\xDEADBEEF'), bytea('\xDEADBEEF'), TRUE, 3.14159, 3.14159, 3.14159, '1968-10-20', '2009-01-26 15:02:54.893532 PST', '1 day 2 hours 30 seconds 1 microsecond', '-6 months' ); INSERT INTO shorty (id, c) VALUES (2, NULL); INSERT INTO typetest1 (id, c, nc, vc, nvc, lc, r, u, lb, lr, b, num, fl, db, d, ts, ids, iym) VALUES ( 3, E'a\u001B\u0007\u000D\u007Fb', E'a\u001B\u0007\u000D\u007Fb', E'a\u001B\u0007\u000D\u007Fb', E'a\u001B\u0007\u000D\u007Fb', E'a\u001B\u0007\u000D\u007Fb ABC' || repeat('X', 9000), bytea('\xDEADF00D'), uuid('055f3b32-a02c-4532-e053-1645990a6db2'), bytea('\xDEADF00DDEADF00DDEADF00D'), bytea('\xDEADF00DDEADF00DDEADF00D'), FALSE, -2.71828, -2.71828, -2.71828, '0044-03-15 BC', '0044-03-15 12:00:00 BC', '-2 days -12 hours -30 minutes', '-2 years -6 months' ); INSERT INTO typetest1 (id, c, nc, vc, nvc, lc, r, u, lb, lr, b, num, fl, db, d, ts, ids, iym) VALUES ( 4, 'short', 'short', 'short', 'short', 'short', bytea('\xDEADF00D'), uuid('0560ee34-2ef9-1137-e053-1645990ac874'), bytea('\xDEADF00D'), bytea('\xDEADF00D'), NULL, 0, 0, 0, NULL, NULL, '23:59:59.999999', '3 years' );
61.5. 插件oracle_fdw的卸载方法¶
在ksql中运行:
drop extension oracle_fdw
61.6. 插件oracle_fdw的升级方法
oracle_fdw扩展插件通常随着KingbaseES安装包一并升级。通常情况下用户无须单独升级些插件。
62. owa_util
62.1. 插件owa_util简介
插件owa_util是KingbaseES 的一个扩展插件。插件owa_util功能是提供owa_util系统包。
owa_util系统包提供了WHO_CALLED_ME过程。
-
插件名为 owa_util
-
插件版本 V1.0
62.2. 插件owa_util加载方式
该插件为初始化数据库实例时默认创建。
62.3. 插件owa_util的参数配置
owa_util扩展插件无需配置任何参数。
62.4. 插件owa_util使用方法
62.4.1. 返回过程调用者的信息
语法格式
OWA_UTIL.WHO_CALLED_ME( owner OUT VARCHAR2, name OUT VARCHAR2, lineno OUT NUMBER, caller_t OUT VARCHAR2);
功能描述
这个过程返回有关调用它的PL/SQL代码单元的信息(以输出参数的形式)。
参数说明
参数 | 描述 |
---|---|
owner | 该程序单元的拥有者 |
name | 程序单元的名称 |
lineno | 程序单元中发出调用的行号 |
caller_t | 进行调用的程序单元的类型。可能有:匿名块、触发器、包体、类型体、过程和函数。过程和函数仅适用于独立的过程和函数 |
注意
-
如果调用程序单元是在包中子程序,那么name就是包名。
-
如果调用程序单元是独立的过程或函数,那么name就是过程或函数的名称。
-
如果调用程序单元是匿名块的一部分,那么name则为NULL。
62.4.2. 示例
将who_called_me子程序作为proc1过程的一部分,只要有PL/SQL程序单元调用proc1,那么可以得到proc1的owner、name、lineno和caller_t。 若是匿名块调用proc1,那么owner和name则为NULL。
示例如下:
\set SQLTERM / create or replace procedure proc1(id number) as owner_name varchar2(100); caller_name varchar2(100); line_number number; caller_type varchar2(100); begin owa_util.who_called_me(owner_name,caller_name,line_number,caller_type); dbms_output.put_line('[id]:' || id || ' ' || '[caller_type]:' || caller_type || ' ' || '[owner_name]:' || owner_name || ' ' || '[caller_name]:' || caller_name || ' ' || '[line_number]:' || line_number || ' ' ); end; / set serverout on begin proc1(1); end; / [id]:1 [caller_type]:ANONYMOUS BLOCK [owner_name]: [caller_name]: [line_number]:2
62.5. 插件owa_util卸载方法
无法卸载。
62.6. 插件owa_util升级方法¶
若该插件有升级版本则通过 alter extension 来升级插件。
示例,由 1.0 升级到 1.1:
ALTER EXTENSION owa_util UPDATE TO '1.1';