LightDB - oracle_fdw join下推增强【24.1】

oracle_fdw join下推增强

oracle_fdw join下推特性（24.1之前）

在之前的版本中 oracle_fdw 支持对join 进行下推，但有如下的限制：

• 只支持两表join的下推，不支持3表及以上的下推
• 连接条件和where 条件可以下推
• 如果连接被下推， order by 不会被下推
• 没有连接条件的cross join 不会被下推
• 只有select 语句支持连接下推
• 表必须在同一外部服务器上
• 只支持inner/left/right/full join 下推

oracle_fdw join增强特性（24.1）

从24.1版本开始， LightDB 支持对semi-join 进行下推，可以把semi-join转为exists 子查询下推到 Oracle 中。同时也支持对insert select语句中的join 进行下推。

测试

初始化环境

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 '', isolation_level 'read_committed', nchar 'true');

CREATE USER MAPPING FOR PUBLIC SERVER oracle OPTIONS (user 'SCOTT', password 'tiger');


SELECT oracle_execute(
          'oracle',
          E'CREATE TABLE t1(id number(10,0) primary key, val1 varchar(10), val2 char(10), val3 varchar2(10))'
       );

SELECT oracle_execute(
          'oracle',
          E'CREATE TABLE t2(id number(10,0) primary key, start_date number(10,0), val1 varchar(10), val2 char(10))'
       );

SELECT oracle_execute(
          'oracle',
          E'CREATE TABLE t3(start_date number(10,0) primary key, val1 varchar(10))'
       );

-- gather statistics
SELECT oracle_execute(
          'oracle',
          E'BEGIN\n'
          '   DBMS_STATS.GATHER_TABLE_STATS (''SCOTT'', ''T1'', NULL, 100);\n'
          'END;'
       );

SELECT oracle_execute(
          'oracle',
          E'BEGIN\n'
          '   DBMS_STATS.GATHER_TABLE_STATS (''SCOTT'', ''T2'', NULL, 100);\n'
          'END;'
       );

SELECT oracle_execute(
          'oracle',
          E'BEGIN\n'
          '   DBMS_STATS.GATHER_TABLE_STATS (''SCOTT'', ''T3'', NULL, 100);\n'
          'END;'
       );

CREATE foreign TABLE t1 (
    id number(10) OPTIONS (key 'yes') NOT NULL,
    val1 varchar(10),
    val2 char(10),
    val3 text
) SERVER oracle OPTIONS (table 'T1', force_pushdown_where_op 'true');

CREATE foreign TABLE t2 (
    id number(10) OPTIONS (key 'yes') NOT NULL,
    start_date number(10),
    val1 varchar(10),
    val2 char(10)
) SERVER oracle OPTIONS (table 'T2', force_pushdown_where_op 'true');

CREATE foreign TABLE t3 (
    start_date number(10) OPTIONS (key 'yes') NOT NULL,
    val1 varchar(10)
) SERVER oracle OPTIONS (table 'T3');


insert into t1 values(1, '1234', '1234','1234');
insert into t1 values(2, '2234', '2234','2234');
insert into t1 values(3, '2', '2','2');
insert into t1 values(4, '3', '3','3');
insert into t1 values(5, '4', '3','3');
insert into t1 values(6, '5', '3','3');

insert into t2 values(1, '20240118', 'abc', '2');
insert into t2 values(2, '20240118', 'qwe', '2');
insert into t2 values(3, '20240119', 'abc', '2');
insert into t2 values(4, '20240119', 'qwe', '3');
insert into t2 values(5, '20240121', 'zxc', '2');
insert into t2 values(6, '20240119', '', '3');

insert into t3 values('20240118', 'aaa');
insert into t3 values('20240119', 'bbb');
insert into t3 values('20240120', '');

create table t1_copy(
    id number(10) NOT NULL,
    val1 varchar(10),
    val2 char(10),
    val3 text
);

示例

lightdb@test_o=# explain (costs false) insert into t1_copy select * from t1 where exists (select * from t2 where t1.id=t2.id);
                                                                                       QUE
RY PLAN                                                                                   
     
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
-----
 Insert on t1_copy
   ->  Foreign Scan
         Oracle query: SELECT /*0354f41de5ae3cfec39f466b95199ae0*/ r3."ID", r3."VAL1", r3.
"VAL2", r3."VAL3" FROM "T1" r3 WHERE EXISTS (SELECT 1 FROM "T2" r4 WHERE (r3."ID" = r4."ID
") )
(3 rows)

lightdb@test_o=# explain (costs false) insert into t1_copy select t1.* from t1 join t2 on t1.id = t2.id;
                                                                             QUERY PLAN   
                                                                           
------------------------------------------------------------------------------------------
---------------------------------------------------------------------------
 Insert on t1_copy
   ->  Foreign Scan
         Oracle query: SELECT /*8198ab105b0cf1a88507da8440560abf*/ r3."ID", r3."VAL1", r3.
"VAL2", r3."VAL3" FROM ("T1" r3 INNER JOIN "T2" r4 ON (r3."ID" = r4."ID"))
(3 rows)

lightdb@test_o=# explain (costs false) insert into t1_copy select t1.* from t1 left join t2 on t1.id = t2.id;
                                                                             QUERY PLAN   
                                                                          
------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
 Insert on t1_copy
   ->  Foreign Scan
         Oracle query: SELECT /*9590092cd58c52862a9f87d57d5cc7b0*/ r3."ID", r3."VAL1", r3.
"VAL2", r3."VAL3" FROM ("T1" r3 LEFT JOIN "T2" r4 ON (r3."ID" = r4."ID"))
(3 rows)

lightdb@test_o=# explain (costs false) insert into t1_copy select t1.* from t1 right join t2 on t1.id = t2.id;
                                                                             QUERY PLAN   
                                                                          
------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
 Insert on t1_copy
   ->  Foreign Scan
         Oracle query: SELECT /*5b0638eb17c55e93456a02170c07ed6f*/ r3."ID", r3."VAL1", r3.
"VAL2", r3."VAL3" FROM ("T2" r4 LEFT JOIN "T1" r3 ON (r3."ID" = r4."ID"))
(3 rows)

lightdb@test_o=# explain (costs false) insert into t1_copy select t1.* from t1 full join t2 on t1.id = t2.id;
                                                                             QUERY PLAN   
                                                                          
------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
 Insert on t1_copy
   ->  Foreign Scan
         Oracle query: SELECT /*0175371a65b00ef9b62ca21e1910774f*/ r3."ID", r3."VAL1", r3.
"VAL2", r3."VAL3" FROM ("T1" r3 FULL JOIN "T2" r4 ON (r3."ID" = r4."ID"))
(3 rows)

参考资料

1. lightdb oracle_fdw