LightDB-A支持创建外部表,外部数据源可以是oracle, postgresql等.
在LightDB-A创建oracle server和用户映射:
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//x.x.x.x:1521/test');
GRANT USAGE ON FOREIGN SERVER oradb TO fj;
CREATE USER MAPPING FOR fj SERVER oradb OPTIONS (user 'TEST', password '.............');
在Oracle创建表:
CREATE TABLE local_tbl (c1 int NOT NULL, c2 int NOT NULL, c3 varchar2(32), CONSTRAINT local_tbl_pkey PRIMARY KEY (c1));
CREATE TABLE local_tbl2 (c1 int NOT NULL, c2 int NOT NULL, c3 varchar2(32), CONSTRAINT local2_tbl_pkey PRIMARY KEY (c1));
在LightDB-A创建外部表, 映射到Oracle:
create FOREIGN table oft (c1 int NOT NULL, c2 int NOT NULL, c3 text)
server oradb options (table 'LOCAL_TBL');
create FOREIGN table oft2 (c1 int NOT NULL, c2 int NOT NULL, c3 text)
server oradb options (table 'LOCAL_TBL2');
-- insert into oft(c1, c2) select i, i from generate_series(0, 10000, 1) i;
-- insert into oft2(c1, c2) select i, i from generate_series(0, 10000, 1) i;
ERROR: error executing query: OCIStmtExecute failed to execute remote query
DETAIL: ORA-08177: can't serialize access for this transaction
带where条件的语句
lightdb=# explain (analyze) select * from oft2 where c1 < 10000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on oft2 (cost=10000.00..20000.00 rows=1000 width=40) (actual time=2.114..2.116 rows=0 loops=1)
Oracle query: SELECT /*8ac7e7f3946af2d338038bbf2b1f2a53*/ r1."C1", r1."C2", r1."C3" FROM "LOCAL_TBL2" r1 WHERE (r1."C1" < 10000)
Optimizer: Postgres query optimizer
Planning Time: 0.826 ms
(slice0) Executor memory: 7K bytes.
Memory used: 128000kB
Execution Time: 2.156 ms
(7 rows)
带where,及聚合函数的语句
lightdb=# explain (analyze) select sum(c1) from oft2 where c1 < 10000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=20002.50..20002.51 rows=1 width=8) (actual time=1.620..1.622 rows=1 loops=1)
-> Foreign Scan on oft2 (cost=10000.00..20000.00 rows=1000 width=4) (actual time=1.607..1.608 rows=0 loops=1)
Oracle query: SELECT /*df3081a4ef375caa252041bf257243af*/ r1."C1" FROM "LOCAL_TBL2" r1 WHERE (r1."C1" < 10000)
Optimizer: Postgres query optimizer
Planning Time: 0.758 ms
(slice0) Executor memory: 14K bytes.
Memory used: 128000kB
Execution Time: 1.670 ms
(8 rows)
在LightDB-A上join: 两个外部表均在oracle上
lightdb=# explain (analyze) select * from oft full join oft2 on oft.c1 = oft2.c1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------
Foreign Scan (cost=10000.00..20000.00 rows=1000 width=80) (actual time=0.863..0.864 rows=0 loops=1)
Oracle query: SELECT /*1724e7408dbc2aa1e960df0f0bdbb192*/ r1."C1", r1."C2", r1."C3", r2."C1", r2."C2", r2."C3" FROM ("LOCAL_TBL" r1 FULL JOIN "LOCAL_TBL2
" r2 ON (r1."C1" = r2."C1"))
Optimizer: Postgres query optimizer
Planning Time: 1.339 ms
(slice0) Executor memory: 9K bytes.
Memory used: 128000kB
Execution Time: 0.894 ms
(7 rows)
在LightDB-A上join: 两个外部表均在oracle上, 带聚合函数, 聚合函数目前不支持下推
lightdb=# explain (analyze) select sum(oft.c1) from oft full join oft2 on oft.c1 = oft2.c1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=20002.50..20002.51 rows=1 width=8) (actual time=2.600..2.601 rows=1 loops=1)
-> Foreign Scan (cost=10000.00..20000.00 rows=1000 width=4) (actual time=2.596..2.597 rows=0 loops=1)
Oracle query: SELECT /*af432403390fd19477c69e9f26d614a9*/ r1."C1" FROM ("LOCAL_TBL" r1 FULL JOIN "LOCAL_TBL2" r2 ON (r1."C1" = r2."C1"))
Optimizer: Postgres query optimizer
Planning Time: 1.064 ms
(slice0) Executor memory: 12K bytes.
Memory used: 128000kB
Execution Time: 2.651 ms
(8 rows)
在LightDB-A上join: 一个外部表,一个是非外部表
lightdb=# create table dist_tbl(c1 int) distributed by (c1);
CREATE TABLE
lightdb=# explain (analyze) select * from oft full join dist_tbl on oft.c1 = dist_tbl.c1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=20017.50..23395.25 rows=96300 width=44) (actual time=68.468..68.474 rows=0 loops=1)
-> Hash Full Join (cost=20017.50..22111.25 rows=32100 width=44) (actual time=0.000..68.141 rows=0 loops=1)
Hash Cond: (dist_tbl.c1 = oft.c1)
-> Seq Scan on dist_tbl (cost=0.00..355.00 rows=32100 width=4) (actual time=0.000..0.023 rows=0 loops=1)
-> Hash (cost=20013.33..20013.33 rows=333 width=40) (actual time=0.000..64.721 rows=0 loops=1)
Buckets: 262144 Batches: 1 Memory Usage: 2048kB
-> Redistribute Motion 1:3 (slice2) (cost=10000.00..20013.33 rows=333 width=40) (actual time=0.000..64.717 rows=0 loops=1)
Hash Key: oft.c1
-> Foreign Scan on oft (cost=10000.00..20000.00 rows=1000 width=40) (actual time=0.000..0.705 rows=0 loops=1)
Oracle query: SELECT /*cd8c22c22d0a386d03594f6eacd5ac7d*/ r1."C1", r1."C2", r1."C3" FROM "LOCAL_TBL" r1
Optimizer: Postgres query optimizer
Memory used: 128000kB
Execution Time: 74.052 ms
(17 rows)
当启用ORCA优化器后, join不会下推:
lightdb=# set optimizer=on;
SET
lightdb=# explain (analyze) select * from oft full join oft2 on oft.c1 = oft2.c1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Merge Full Join (cost=0.00..862.00 rows=3 width=32) (actual time=1.869..1.872 rows=0 loops=1)
Merge Cond: (oft.c1 = oft2.c1)
-> Sort (cost=0.00..431.00 rows=1 width=16) (actual time=0.490..0.491 rows=0 loops=1)
Sort Key: oft.c1
Sort Method: quicksort Memory: 25kB
Executor Memory: 26kB Segments: 1 Max: 26kB (segment -1)
-> Foreign Scan on oft (cost=0.00..431.00 rows=1 width=16) (actual time=0.475..0.475 rows=0 loops=1)
Oracle query: SELECT /*cd8c22c22d0a386d03594f6eacd5ac7d*/ r1."C1", r1."C2", r1."C3" FROM "LOCAL_TBL" r1
-> Sort (cost=0.00..431.00 rows=1 width=16) (actual time=1.370..1.371 rows=0 loops=1)
Sort Key: oft2.c1
Sort Method: quicksort Memory: 25kB
Executor Memory: 26kB Segments: 1 Max: 26kB (segment -1)
-> Foreign Scan on oft2 (cost=0.00..431.00 rows=1 width=16) (actual time=1.367..1.367 rows=0 loops=1)
Oracle query: SELECT /*bd17a8d3ec77b6f27cb09595116840b9*/ r2."C1", r2."C2", r2."C3" FROM "LOCAL_TBL2" r2
Optimizer: Pivotal Optimizer (GPORCA)
Planning Time: 13.831 ms
(slice0) Executor memory: 81K bytes. Work_mem: 26K bytes max.
Memory used: 128000kB
Execution Time: 1.949 ms
(19 rows)
lightdb=# explain (analyze) select sum(oft.c1) from oft full join oft2 on oft.c1 = oft2.c1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Aggregate (cost=0.00..862.00 rows=1 width=8) (actual time=2.556..2.559 rows=1 loops=1)
-> Merge Full Join (cost=0.00..862.00 rows=3 width=4) (actual time=2.550..2.552 rows=0 loops=1)
Merge Cond: (oft.c1 = oft2.c1)
-> Sort (cost=0.00..431.00 rows=1 width=4) (actual time=1.400..1.401 rows=0 loops=1)
Sort Key: oft.c1
Sort Method: quicksort Memory: 25kB
Executor Memory: 26kB Segments: 1 Max: 26kB (segment -1)
-> Foreign Scan on oft (cost=0.00..431.00 rows=1 width=4) (actual time=1.390..1.390 rows=0 loops=1)
Oracle query: SELECT /*ae2a7d620bfefbdfff5f19cd521175ca*/ r1."C1" FROM "LOCAL_TBL" r1
-> Sort (cost=0.00..431.00 rows=1 width=4) (actual time=1.146..1.147 rows=0 loops=1)
Sort Key: oft2.c1
Sort Method: quicksort Memory: 25kB
Executor Memory: 26kB Segments: 1 Max: 26kB (segment -1)
-> Foreign Scan on oft2 (cost=0.00..431.00 rows=1 width=4) (actual time=1.135..1.136 rows=0 loops=1)
Oracle query: SELECT /*d5d1f33ed6d5ea2f04f5479ffb4fb4ba*/ r2."C1" FROM "LOCAL_TBL2" r2
Optimizer: Pivotal Optimizer (GPORCA)
Planning Time: 12.468 ms
(slice0) Executor memory: 57K bytes. Work_mem: 26K bytes max.
Memory used: 128000kB
Execution Time: 2.640 ms
(20 rows)
lightdb=# explain (analyze) select count(oft.c1) from oft full join oft2 on oft.c1 = oft2.c1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Aggregate (cost=0.00..862.00 rows=1 width=8) (actual time=0.818..0.820 rows=1 loops=1)
-> Merge Full Join (cost=0.00..862.00 rows=3 width=4) (actual time=0.802..0.803 rows=0 loops=1)
Merge Cond: (oft.c1 = oft2.c1)
-> Sort (cost=0.00..431.00 rows=1 width=4) (actual time=0.492..0.492 rows=0 loops=1)
Sort Key: oft.c1
Sort Method: quicksort Memory: 25kB
Executor Memory: 26kB Segments: 1 Max: 26kB (segment -1)
-> Foreign Scan on oft (cost=0.00..431.00 rows=1 width=4) (actual time=0.483..0.483 rows=0 loops=1)
Oracle query: SELECT /*ae2a7d620bfefbdfff5f19cd521175ca*/ r1."C1" FROM "LOCAL_TBL" r1
-> Sort (cost=0.00..431.00 rows=1 width=4) (actual time=0.307..0.308 rows=0 loops=1)
Sort Key: oft2.c1
Sort Method: quicksort Memory: 25kB
Executor Memory: 26kB Segments: 1 Max: 26kB (segment -1)
-> Foreign Scan on oft2 (cost=0.00..431.00 rows=1 width=4) (actual time=0.301..0.301 rows=0 loops=1)
Oracle query: SELECT /*d5d1f33ed6d5ea2f04f5479ffb4fb4ba*/ r2."C1" FROM "LOCAL_TBL2" r2
Optimizer: Pivotal Optimizer (GPORCA)
Planning Time: 11.687 ms
(slice0) Executor memory: 57K bytes. Work_mem: 26K bytes max.
Memory used: 128000kB
Execution Time: 0.888 ms
(20 rows)