金仓数据库KingbaseES Enable_hint_table 使用

目录

一、启用enable_hint_table

二、举例说明

1、没使用 hint_plan.hints 时的执行计划

2、使用 hint_plan.hints 

三、SQL 匹配

KingbaseES enable_hint_table 可以看成类似 oracle outline 工具,可在不修改SQL 的情况下,通过hint 改变SQL 的执行计划。

一、启用enable_hint_table

1、设置shared_preload_libraries 参数,增加 sys_hint_plan , 重启数据库

2、修改参数 sys_hint_plan.enable_hint=on , select sys_reload_conf()

3、create extension sys_hint_plan ;创建后,会新建 hint_plan.hints 表。

4、设置 set sys_hint_plan.enable_hint_table = on

二、举例说明

1、没使用 hint_plan.hints 时的执行计划

test=# delete from  hint_plan.hints; 
DELETE 1
test=# explain analyze select * from t1 where t1.id1 = 1;
                                                  QUERY PLAN                                                  
---------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=4.20..13.67 rows=6 width=44) (actual time=0.002..0.002 rows=0 loops=1)
   Recheck Cond: (id1 = 1)
   ->  Bitmap Index Scan on ind_t1  (cost=0.00..4.20 rows=6 width=0) (actual time=0.001..0.001 rows=0 loops=1)
         Index Cond: (id1 = 1)
 Planning Time: 0.067 ms
 Execution Time: 0.011 ms
(6 rows)

默认情况下,SQL 走索引扫描。

2、使用 hint_plan.hints 

通过在 hint_plan.hints 插入一条记录,提示使用 SeqScan。

test=# insert into hint_plan.hints(norm_query_string, application_name, hints) VALUES ( 'Explain analyze select * from t1 where t1.id1 = ?;','','SeqScan(t1)');
INSERT 0 1
test=# Explain analyze select * from t1 where t1.id1 = 1;                                                                                                     
                                          QUERY PLAN                                         
----------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..24.12 rows=6 width=44) (actual time=0.004..0.004 rows=0 loops=1)
   Filter: (id1 = 1)
 Planning Time: 0.169 ms
 Execution Time: 0.017 ms
(4 rows)

这里有两个地方需要注意:

1、SQL 后面必须有个 “;” ,否则会因为SQL 不匹配而不生效

2、application_name='' ,表示任意应用,注意中间没有空格,否则会因为应用不匹配而不生效

三、SQL 匹配

1、空格不匹配,无法使用hint_table

test=# explain analyze select * from t1 where t1.id1 =1; 
                                                  QUERY PLAN                                                  
---------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=4.20..13.67 rows=6 width=44) (actual time=0.002..0.002 rows=0 loops=1)
   Recheck Cond: (id1 = 1)
   ->  Bitmap Index Scan on ind_t1  (cost=0.00..4.20 rows=6 width=0) (actual time=0.001..0.001 rows=0 loops=1)
         Index Cond: (id1 = 1)
 Planning Time: 0.069 ms
 Execution Time: 0.011 ms
(6 rows)

2、别名不匹配,无法使用hint_table

test=# explain analyze select * from t1 a where a.id1 = 1;
                                                  QUERY PLAN                                                  
---------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1 a  (cost=4.20..13.67 rows=6 width=44) (actual time=0.003..0.003 rows=0 loops=1)
   Recheck Cond: (id1 = 1)
   ->  Bitmap Index Scan on ind_t1  (cost=0.00..4.20 rows=6 width=0) (actual time=0.002..0.002 rows=0 loops=1)
         Index Cond: (id1 = 1)
 Planning Time: 0.053 ms
 Execution Time: 0.012 ms
(6 rows)

3、大小写不匹配,可以使用hint_table

test=# explain analyze select * from t1 WHERE t1.id1 = 1;
                                          QUERY PLAN                                         
----------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..24.12 rows=6 width=44) (actual time=0.002..0.002 rows=0 loops=1)
   Filter: (id1 = 1)
 Planning Time: 0.080 ms
 Execution Time: 0.008 ms
(4 rows)

test=# explain analyze select * from T1 where t1.id1 = 1;
                                          QUERY PLAN                                         
----------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..24.12 rows=6 width=44) (actual time=0.002..0.002 rows=0 loops=1)
   Filter: (id1 = 1)
 Planning Time: 0.076 ms
 Execution Time: 0.008 ms
(4 rows)

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值