三端互通传奇手游找服网站,长春网站设计策划书,网络平台宣传方式有哪些,什么软件可以制作图片LightDB 从23.4 版本开始支持opt_param hint, 用来设置优化器相关的guc参数#xff0c;用法和oracle的opt_param 相同。
设置的参数需要是 LightDB的参数#xff08;不是oracle的参数#xff09;。参数名用单引号括起来字符串型的参数值也需要用单引号括起来作用于整条SQL …LightDB 从23.4 版本开始支持opt_param hint, 用来设置优化器相关的guc参数用法和oracle的opt_param 相同。
设置的参数需要是 LightDB的参数不是oracle的参数。参数名用单引号括起来字符串型的参数值也需要用单引号括起来作用于整条SQL
具体用法参考如下示例
示例
表结构
create table test_opt_param1(key1 int, key2 int, primary key(key1));
create table test_opt_param2(key1 int, key2 int, primary key(key1));
create table test_opt_param3(key1 int, key2 int, primary key(key1));基本用法
EXPLAIN (COSTS false) select * from test_opt_param1 a join test_opt_param2 b on a.key1b.key1;QUERY PLAN
-------------------------------------------Hash JoinHash Cond: (a.key1 b.key1)- Seq Scan on test_opt_param1 a- Hash- Seq Scan on test_opt_param2 b
(5 rows)EXPLAIN (COSTS false) select/*opt_param(enable_hashjoin, off)*/ * from test_opt_param1 a join test_opt_param2 b on a.key1b.key1;
LOG: lt_hint_plan:
used hint:
opt_param(enable_hashjoin off)
not used hint:
duplication hint:
error hint:QUERY PLAN
--------------------------------------------------------------------------Merge JoinMerge Cond: (a.key1 b.key1)- Index Scan using test_opt_param1_pkey on test_opt_param1 a lt#0- Index Scan using test_opt_param2_pkey on test_opt_param2 b lt#0
(4 rows)EXPLAIN (COSTS false) select/*opt_param(enable_hashjoin, false)*/ * from test_opt_param1 a join test_opt_param2 b on a.key1b.key1;
LOG: lt_hint_plan:
used hint:
opt_param(enable_hashjoin false)
not used hint:
duplication hint:
error hint:QUERY PLAN
--------------------------------------------------------------------------Merge JoinMerge Cond: (a.key1 b.key1)- Index Scan using test_opt_param1_pkey on test_opt_param1 a lt#0- Index Scan using test_opt_param2_pkey on test_opt_param2 b lt#0
(4 rows)EXPLAIN (COSTS false) select/*opt_param(enable_hashjoin, 0)*/ * from test_opt_param1 a join test_opt_param2 b on a.key1b.key1;
LOG: lt_hint_plan:
used hint:
opt_param(enable_hashjoin 0)
not used hint:
duplication hint:
error hint:QUERY PLAN
--------------------------------------------------------------------------Merge JoinMerge Cond: (a.key1 b.key1)- Index Scan using test_opt_param1_pkey on test_opt_param1 a lt#0- Index Scan using test_opt_param2_pkey on test_opt_param2 b lt#0
(4 rows)EXPLAIN (COSTS false) select/*opt_param(enable_hashjoin, 1)*/ * from test_opt_param1 a join test_opt_param2 b on a.key1b.key1;
LOG: lt_hint_plan:
used hint:
opt_param(enable_hashjoin 1)
not used hint:
duplication hint:
error hint:QUERY PLAN
---------------------------------------------------Hash JoinHash Cond: (a.key1 b.key1)- Seq Scan on test_opt_param1 a lt#0- Hash- Seq Scan on test_opt_param2 b lt#0
(5 rows)错误用法
EXPLAIN (COSTS false) select/*opt_param(enable_hashjoin, off)*/ * from test_opt_param1 a join test_opt_param2 b on a.key1b.key1;
INFO: lt_hint_plan: hint syntax error at or near opt_param(enable_hashjoin, off)
DETAIL: opt_param hint requires parameter name to be enclosed in single quotation marks.
LOG: lt_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
opt_param(enable_hashjoin off)QUERY PLAN
---------------------------------------------------Hash JoinHash Cond: (a.key1 b.key1)- Seq Scan on test_opt_param1 a lt#0- Hash- Seq Scan on test_opt_param2 b lt#0
(5 rows)EXPLAIN (COSTS false) select/*opt_param(enable_hashjoin, off)*/ * from test_opt_param1 a join test_opt_param2 b on a.key1b.key1;
INFO: lt_hint_plan: hint syntax error at or near opt_param(enable_hashjoin, off)
DETAIL: opt_param hint requires parameter values that are strings to be enclosed in single quotation mark.
LOG: lt_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
opt_param(enable_hashjoin off)QUERY PLAN
---------------------------------------------------Hash JoinHash Cond: (a.key1 b.key1)- Seq Scan on test_opt_param1 a lt#0- Hash- Seq Scan on test_opt_param2 b lt#0
(5 rows)
