LightDB-opt_param hint支持

最新推荐文章于 2024-11-02 12:29:24 发布

紫无之紫

最新推荐文章于 2024-11-02 12:29:24 发布

阅读量94

点赞数

分类专栏： lightdb 文章标签： lightdb hint opt_param oracle

本文链接：https://blog.csdn.net/qq_17713935/article/details/134509570

版权

lightdb 专栏收录该内容

51 篇文章 1 订阅

订阅专栏

文章介绍了LightDB从23.4版本开始支持的opt_paramhint，用于设置针对SQL的优化器参数，与Oracle的opt_param类似，但参数需符合LightDB规范。文中通过示例展示了正确和错误的使用方法以及不同参数设置对查询计划的影响。

摘要由CSDN通过智能技术生成

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.key1=b.key1;
                QUERY PLAN                 
-------------------------------------------
 Hash Join
   Hash 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.key1=b.key1;
LOG:  lt_hint_plan:
used hint:
opt_param('enable_hashjoin' 'off')
not used hint:
duplication hint:
error hint:

                                QUERY PLAN                                
--------------------------------------------------------------------------
 Merge Join
   Merge 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.key1=b.key1;
LOG:  lt_hint_plan:
used hint:
opt_param('enable_hashjoin' 'false')
not used hint:
duplication hint:
error hint:

                                QUERY PLAN                                
--------------------------------------------------------------------------
 Merge Join
   Merge 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.key1=b.key1;
LOG:  lt_hint_plan:
used hint:
opt_param('enable_hashjoin' 0)
not used hint:
duplication hint:
error hint:

                                QUERY PLAN                                
--------------------------------------------------------------------------
 Merge Join
   Merge 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.key1=b.key1;
LOG:  lt_hint_plan:
used hint:
opt_param('enable_hashjoin' 1)
not used hint:
duplication hint:
error hint:

                    QUERY PLAN                     
---------------------------------------------------
 Hash Join
   Hash 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.key1=b.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 Join
   Hash 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.key1=b.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 Join
   Hash 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)