重庆网站建设推广,菏泽企业网站建设,钉钉如何做自己的网站,营销公司排行LightDB - oracle_fdw 过滤条件下推增强【24.1】 1. 字符串比较下推1.1 示例 2. 隐式转换下推2.1 示例 3. nvl 和trim 下推3.1 示例 LightDB 在24.1版本对oracle_fdw 的where下推进行了增强#xff0c;新增对如下两种情况进行下推#xff1a;
字符串比较下推#xff0c;如 … LightDB - oracle_fdw 过滤条件下推增强【24.1】 1. 字符串比较下推1.1 示例 2. 隐式转换下推2.1 示例 3. nvl 和trim 下推3.1 示例 LightDB 在24.1版本对oracle_fdw 的where下推进行了增强新增对如下两种情况进行下推
字符串比较下推如 a ‘100’ 这种具体见下面示例隐式转换为numeric 类型情况下的下推新增 nvl 和 trim 函数下推
1. 字符串比较下推
oracle_fdw 不支持字符串比较下推是因为 LightDB 和 Oracle 的排序规则可能不同下推和不下推的结果可能不同因此不进行下推。 但对于某些场景比如字符串都是数字是个id 那么不管什么排序规则大小比较都是相同的。因此 LightDB 提供了一个表级别的选项来支持强制下推这种情况。 选项为force_pushdown_where_op 可以设置为on/yes/true 或者 off/no/false
1.1 示例
lightdbtest_o# CREATE foreign TABLE t1 (id number(10) NOT NULL,val1 varchar(10),val2 char(10),val3 text
) SERVER oradb OPTIONS (table T1);
CREATE FOREIGN TABLE
lightdbtest_o# explain (costs false) select * from t1 where val1 1;QUERY PLAN ---------------------------------------------------------------------------------
----------------------------------Foreign Scan on t1Filter: ((val1)::text 1::text)Oracle query: SELECT /*c1f143f0d9e74f29fb779e0a2ccfbe91*/ r1.ID, r1.VAL1,
r1.VAL2, r1.VAL3 FROM T1 r1
(3 rows)lightdbtest_o# alter foreign table t1 OPTIONS (add force_pushdown_where_op true);
ALTER FOREIGN TABLE
lightdbtest_o# explain (costs false) select * from t1 where val1 1;QUERY PLAN ---------------------------------------------------------------------------------
----------------------------------------------------------Foreign Scan on t1Oracle query: SELECT /*d1d07899c1c6cc550f34054b7ba97384*/ r1.ID, r1.VAL1,
r1.VAL2, r1.VAL3 FROM T1 r1 WHERE (r1.VAL1 1)
(2 rows)lightdbtest_o# drop foreign TABLE t1;
DROP FOREIGN TABLE
lightdbtest_o# CREATE foreign TABLE t1 (id number(10) NOT NULL,val1 varchar(10),val2 char(10),val3 text
) SERVER oradb OPTIONS (table T1, force_pushdown_where_op true);
CREATE FOREIGN TABLE
lightdbtest_o# explain (costs false) select * from t1 where val1 1;QUERY PLAN ---------------------------------------------------------------------------------
----------------------------------------------------------Foreign Scan on t1Oracle query: SELECT /*d1d07899c1c6cc550f34054b7ba97384*/ r1.ID, r1.VAL1,
r1.VAL2, r1.VAL3 FROM T1 r1 WHERE (r1.VAL1 1)
(2 rows)
2. 隐式转换下推
对于如下SQL原先是不会下推的
select * from t1 where val1 1;因为val1 是字符类型 与 1 比较 在LightDB 中 val1 会被隐式转换为 numeric 类型。 下面是支持下推此情况的示例(转换为cast (val1 as numebr) 下推):
2.1 示例
lightdbtest_o# explain (costs false) select * from t1 where val1 1;QUERY PLAN---------------------------------------------------------------------------------
------------------------------------------------------------------------Foreign Scan on t1Oracle query: SELECT /*1057989d07288e060655a4377d7d36ef*/ r1.ID, r1.VAL1,
r1.VAL2, r1.VAL3 FROM T1 r1 WHERE (cast(r1.VAL1 as number) 1)
(2 rows)lightdbtest_o# explain (costs false) select * from t1 where val1::number 1;QUERY PLAN---------------------------------------------------------------------------------
------------------------------------------------------------------------Foreign Scan on t1Oracle query: SELECT /*1057989d07288e060655a4377d7d36ef*/ r1.ID, r1.VAL1,
r1.VAL2, r1.VAL3 FROM T1 r1 WHERE (cast(r1.VAL1 as number) 1)
(2 rows)
3. nvl 和trim 下推
在 LightDB 中 trim 会转换为 ltrim, rtrim 和 btrim 函数执行原先已支持 ltrim, rtrim 函数的下推24.1 支持了对btrim 的下推也即完全支持了 trim 的下推。
3.1 示例
lightdbtest_o# explain (costs false) select * from t1 where nvl(val1, ) abc order by id;QUERY PLAN ---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
---------------------Foreign Scan on t1Oracle query: SELECT /*49625285d521d9e84167b4358a1b57b0*/ r1.ID, r1.VAL1,
r1.VAL2, r1.VAL3 FROM T1 r1 WHERE (nvl(r1.VAL1, ) abc) ORDER BY r1
.ID ASC NULLS LAST
(2 rows)lightdbtest_o# explain (costs false) select * from t1 where trim(val1) abc order by id;QUERY PLAN ---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
-------------------------------Foreign Scan on t1Oracle query: SELECT /*ad50eef72115ae7525aaaf08663b014c*/ r1.ID, r1.VAL1,
r1.VAL2, r1.VAL3 FROM T1 r1 WHERE (TRIM(BOTH FROM r1.VAL1) abc) O
RDER BY r1.ID ASC NULLS LAST
(2 rows)lightdbtest_o# explain (costs false) select * from t1 where trim(LEADING val1) abc order by id;QUERY PLAN ---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
-----------------------Foreign Scan on t1Oracle query: SELECT /*e2c065886aa1e3ccb9cc00faa85f9f95*/ r1.ID, r1.VAL1,
r1.VAL2, r1.VAL3 FROM T1 r1 WHERE (ltrim(r1.VAL1, ) abc) ORDER BY
r1.ID ASC NULLS LAST
(2 rows)lightdbtest_o# explain (costs false) select * from t1 where trim(TRAILING val1) abc order by id;QUERY PLAN ---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
-----------------------Foreign Scan on t1Oracle query: SELECT /*4fc7fba86ff56a97069e1102f988519f*/ r1.ID, r1.VAL1,
r1.VAL2, r1.VAL3 FROM T1 r1 WHERE (rtrim(r1.VAL1, ) abc) ORDER BY
r1.ID ASC NULLS LAST
(2 rows)lightdbtest_o# explain (costs false) select * from t1 where nvl(trim(val1), ) abc order by id;QUERY PLAN ---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
-----------------------------------------Foreign Scan on t1Oracle query: SELECT /*77db51933cdfa1dd6edc39f35a00f952*/ r1.ID, r1.VAL1,
r1.VAL2, r1.VAL3 FROM T1 r1 WHERE (nvl(TRIM(BOTH FROM r1.VAL1), ) abc) ORDER BY r1.ID ASC NULLS LAST
(2 rows)
