LightDB 在24.1版本對oracle_fdw 的where下推進(jìn)行了增強(qiáng)，新增對如下兩種情況進(jìn)行下推：

• 字符串比較下推，如 a >‘100’ 這種，具體見下面示例
• 隱式轉(zhuǎn)換為numeric 類型情況下的下推
• 新增 nvl 和 trim 函數(shù)下推

1. 字符串比較下推

oracle_fdw 不支持字符串比較下推是因為 LightDB 和 Oracle 的排序規(guī)則可能不同，下推和不下推的結(jié)果可能不同，因此不進(jìn)行下推。
但對于某些場景，比如字符串都是數(shù)字，是個id， 那么不管什么排序規(guī)則，大小比較都是相同的。因此 LightDB 提供了一個表級別的選項來支持強(qiáng)制下推這種情況。
選項為：force_pushdown_where_op
可以設(shè)置為：on/yes/true 或者 off/no/false

1.1 示例

lightdb@test_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
lightdb@test_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)lightdb@test_o=#  alter foreign table t1 OPTIONS (add force_pushdown_where_op 'true');
ALTER FOREIGN TABLE
lightdb@test_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)lightdb@test_o=# drop foreign TABLE t1;
DROP FOREIGN TABLE
lightdb@test_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
lightdb@test_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. 隱式轉(zhuǎn)換下推

對于如下SQL，原先是不會下推的：

select * from t1 where val1 > 1;

因為val1 是字符類型， 與 1 比較， 在LightDB 中 val1 會被隱式轉(zhuǎn)換為 numeric 類型。
下面是支持下推此情況的示例(轉(zhuǎn)換為cast (val1 as numebr) 下推):

2.1 示例

lightdb@test_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)lightdb@test_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 會轉(zhuǎn)換為 ltrim, rtrim 和 btrim 函數(shù)執(zhí)行，原先已支持 ltrim, rtrim 函數(shù)的下推，24.1 支持了對btrim 的下推，也即完全支持了 trim 的下推。

3.1 示例

lightdb@test_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)lightdb@test_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)lightdb@test_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)lightdb@test_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)lightdb@test_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)