LightDB - oracle_fdw 过滤条件下推增强【24.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 示例

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 t1
   Filter: ((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 t1
   Oracle 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 t1
   Oracle 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 示例

lightdb@test_o=# explain (costs false) select * from t1 where val1 > 1;
                                                                       QUERY PLAN
                                                                        
---------------------------------------------------------------------------------
------------------------------------------------------------------------
 Foreign Scan on t1
   Oracle 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 t1
   Oracle 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 示例

lightdb@test_o=# explain (costs false) select * from t1 where nvl(val1,' ') = 'abc' order by id;
                                                                                 
     QUERY PLAN                                                                  
                     
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
---------------------
 Foreign Scan on t1
   Oracle 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 t1
   Oracle 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 t1
   Oracle 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 t1
   Oracle 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 t1
   Oracle 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)

参考资料

1. lightdb oracle_fdw