LightDB内置特性之访问oracle之oracle_fdw介绍

　　LightDB发行版内置了直接访问oracle的扩展oracle_fdw。只要lightdb所在服务器安装了oracle客户端/服务器或轻量客户端，并配置环境变量即可使用。如下：

export ORACLE_HOME=/home/zjh/instantclient_21_6
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
export TNS_ADMIN=/home/zjh/instantclient_21_6
export NLS_LANG=american_america.utf-8

　　oracle轻量客户端可直接从oracle官网https://www.oracle.com/database/technologies/instant-client/downloads.html下载。

create server oradb foreign data wrapper oracle_fdw options (dbserver '//10.20.45.214:1521/ora11g');
create user mapping for USER server oradb options (user 'fund60pub', password 'fund60pub');
GRANT USAGE ON FOREIGN DATA WRAPPER oracle_fdw TO CURRENT_USER;
GRANT USAGE ON FOREIGN SERVER oradb TO CURRENT_USER;

create foreign table EMPLOYEE 
   (    ID NUMBER(10,0), 
    SALARY NUMBER(20,0)
   )
SERVER oradb OPTIONS (schema 'FUND60PUB', table 'EMPLOYEE');

zjh@postgres=# \timing on
Timing is on.
zjh@postgres=# select * from EMPLOYEE;    -- 相比直接访问，走oracle fdw还是比较慢的。其他fdw如postgresql/mysql（需要注意关闭ssl，默认情况下走tcp协议postgresql会自动启用ssl，性能会损耗50%，lightdb则不会启用），也有该问题。
 id | salary 
----+--------
 11 |    300
  2 |    300
 33 |    300
  4 |    300
(4 rows)

Time: 2.508 ms

[zjh@hs-10-20-30-193 ~]$ ping 10.20.45.214     # 虽然有网络延时，但是尚可，网络不是大头
PING 10.20.45.214 (10.20.45.214) 56(84) bytes of data.
64 bytes from 10.20.45.214: icmp_seq=1 ttl=63 time=0.196 ms
64 bytes from 10.20.45.214: icmp_seq=2 ttl=63 time=0.194 ms
64 bytes from 10.20.45.214: icmp_seq=3 ttl=63 time=0.265 ms
64 bytes from 10.20.45.214: icmp_seq=4 ttl=63 time=0.222 ms
64 bytes from 10.20.45.214: icmp_seq=5 ttl=63 time=0.225 ms
^C
--- 10.20.45.214 ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4000ms
rtt min/avg/max/mdev = 0.194/0.220/0.265/0.028 ms

zjh@postgres=# select * from pg_foreign_table;
 ftrelid | ftserver |             ftoptions             
---------+----------+-----------------------------------
  319767 |   319762 | {schema=FUND60PUB,table=EMPLOYEE}
(1 row)

Time: 0.606 ms
zjh@postgres=# select * from pg_user_mapping;
  oid   | umuser | umserver |              umoptions              
--------+--------+----------+-------------------------------------
 319763 |     10 |   319762 | {user=fund60pub,password=fund60pub}
(1 row)

Time: 0.235 ms
zjh@postgres=# select * from pg_foreign_server;
  oid   | srvname | srvowner | srvfdw | srvtype | srvversion |   srvacl    |              srvoptions               
--------+---------+----------+--------+---------+------------+-------------+---------------------------------------
 319762 | oradb   |       10 | 319761 |         |            | {zjh=U/zjh} | {dbserver=//10.20.45.214:1521/ora11g}
(1 row)

Time: 0.229 ms
zjh@postgres=# select * from pg_foreign_data_wrapper;
  oid   |  fdwname   | fdwowner | fdwhandler | fdwvalidator |   fdwacl    | fdwoptions 
--------+------------+----------+------------+--------------+-------------+------------
 319761 | oracle_fdw |       10 |     319756 |       319757 | {zjh=U/zjh} | 
(1 row)

Time: 0.176 ms
zjh@postgres=# select * from pg_extension;
  oid   |  extname   | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
--------+------------+----------+--------------+----------------+------------+-----------+--------------
  13569 | plpgsql    |       10 |           11 | f              | 1.0        |           | 
 319755 | oracle_fdw |       10 |         2200 | t              | 1.2        |           | 
 319773 | canopy     |       10 |           11 | f              | 10.2-3     |           | 
(3 rows)

Time: 0.630 ms

zjh@postgres=# select * from pgbench_accounts limit 4;
 aid | bid | abalance |                                        filler                                        
-----+-----+----------+--------------------------------------------------------------------------------------
   1 |   1 |        0 |                                                                                     
   2 |   1 |        0 |                                                                                     
   3 |   1 |        0 |                                                                                     
   4 |   1 |        0 |                                                                                     
(4 rows)

Time: 0.254 ms
zjh@postgres=# select * from pgbench_accounts limit 4;
 aid | bid | abalance |                                        filler                                        
-----+-----+----------+--------------------------------------------------------------------------------------
   1 |   1 |        0 |                                                                                     
   2 |   1 |        0 |                                                                                     
   3 |   1 |        0 |                                                                                     
   4 |   1 |        0 |                                                                                     
(4 rows)

Time: 0.306 ms
zjh@postgres=# 

如果一个SQL语句中所有的表都是oracle外部表，则会整个SQL语句下推到oracle执行，见https://www.cnblogs.com/faxx/p/17547007.html。不管是-X，还是-A都支持。

https://www.hs.net/lightdb/docs/html/sql-alterusermapping.html

https://mrdrivingduck.github.io/blog/notes/PostgreSQL/PostgreSQL%20FDW%20Async%20Execution.html