LightDB-A 支持oracle执行计划下推

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)

posted on   Gao科技  阅读(31)  评论(0)  编辑  收藏  举报

相关博文：
· 使用Patroni管理LightDB高可用
· LightDB支持从节点转发DML
· LightDB内置特性之访问oracle之oracle_fdw介绍
· lightdb 练习题
· lightdb fdw性能测试
阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· 记一次.NET内存居高不下排查解决与启示
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· .NET10 - 预览版1新功能体验（一）

统计

点击右上角即可分享
微信分享提示
AI FOR CODE 大赛