LightDB 从 24.1 开始支持 oracle no_expand hint。
no_expand hint 用于取消OR-expansion优化，不进行OR-expansion优化。
OR-expansion
在 Oracle 中 优化器会基于cost 优化带析取的查询块（OR）, 会拆分这个查询块为多个查询块，并用union all 组合起来， 这样就可以利用索引， 并且可以并发执行查询块。
使用示例
由于 LightDB 目前不支持 OR-expansion 优化，因此 no_expand hint 就是默认的行为， 一定会生效。
create table t_no_expand1(key1 int, key2 int);
create table t_no_expand2(key1 int, key2 int);
lightdb@postgres=# EXPLAIN (COSTS false) select * from t_no_expand1 where exists (select * from t_no_expand2 where key1= 1 or key2=1) or key1 =10;
QUERY PLAN
----------------------------------------------
Seq Scan on t_no_expand1
Filter: ($0 OR (key1 = 10))
InitPlan 1 (returns $0)
-> Seq Scan on t_no_expand2
Filter: ((key1 = 1) OR (key2 = 1))
(5 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select /*+no_expand*/ * from t_no_expand1 where exists (select * from t_no_expand2 where key1= 1 or key2=1) or key1 =10;
LOG: lt_hint_plan:
used hint:
no_expand
not used hint:
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------
Seq Scan on t_no_expand1 @"lt#0"
Filter: ($0 OR (key1 = 10))
InitPlan 1 (returns $0)
-> Seq Scan on t_no_expand2
Filter: ((key1 = 1) OR (key2 = 1))
(5 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select * from t_no_expand1 where exists (select /*+no_expand*/ * from t_no_expand2 where key1= 1 or key2=1) or key1 =10;
LOG: lt_hint_plan:
used hint:
no_expand
not used hint:
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------
Seq Scan on t_no_expand1 @"lt#1"
Filter: ($0 OR (key1 = 10))
InitPlan 1 (returns $0)
-> Seq Scan on t_no_expand2 @"lt#0"
Filter: ((key1 = 1) OR (key2 = 1))
(5 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select /*+no_expand(@qb)*/ * from t_no_expand1 where exists (select/*+qb_name(qb)*/ * from t_no_expand2 where key1= 1 or key2=1) or key1 =10;
LOG: lt_hint_plan:
used hint:
no_expand(@qb)
not used hint:
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------
Seq Scan on t_no_expand1 @"lt#0"
Filter: ($0 OR (key1 = 10))
InitPlan 1 (returns $0)
-> Seq Scan on t_no_expand2 @qb
Filter: ((key1 = 1) OR (key2 = 1))
(5 rows)
