文章目录
- oracle_fdw join下推增强
-
- oracle_fdw join下推特性（24.1之前）
- 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/seft/right/gull 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
'
' DBMS_STATS.GATHER_TABLE_STATS (''SCOTT'', ''T1'', NULL, 100);
'
'END;'
);
SELECT oracle_execute(
'oracle',
E'BEGIN
'
' DBMS_STATS.GATHER_TABLE_STATS (''SCOTT'', ''T2'', NULL, 100);
'
'END;'
);
SELECT oracle_execute(
'oracle',
E'BEGIN
'
' DBMS_STATS.GATHER_TABLE_STATS (''SCOTT'', ''T3'', NULL, 100);
'
'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)