Enable_hint_table 使用
2024-10-20 03:51:11
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)
最新文章
- shallow copy &; deep copy
- poj 3154 Graveyard 贪心
- python简单小爬虫爬取易车网图片
- 用jquery制作加载条
- ajax_异步交互-get/post方式
- 576. Out of Boundary Paths
- load vs. initialize
- C#中DataGridView 对XML文档的使用
- 开源litemall学习
- java问题
- ios 容错处理JKDataHelper和AvoidCrash
- chrome版本与对应的谷歌驱动(chromedriver)
- lvs+keepalived+nginx实现高性能负载均衡集群【转】
- 【Python】【元编程】【三】【元类】
- 如何使用App.config文件,读取字符串?
- jQuery源码学习笔记一
- MySQL字符集编码相关
- google Agent
- swoft| 源码解读系列一: 好难! swoft demo 都跑不起来怎么破? docker 了解一下呗~
- 攻克数据库核心技术壁垒,实现百万级QPS的高吞吐
热门文章
- 用python做个计算器不是轻轻松松吗~
- Springboot 整合 MongoDB
- FFT 学习笔记(自认为详细)
- P2599 [ZJOI2009]取石子游戏 做题感想
- linux Error downloading packages free 0 * needed 71 k
- intellidea 快捷键-*01
- (一)java基础篇---第一个程序
- Node.js精进(10)——性能监控(下)
- grafana监控配置
- DDL_操作数据库_创建&;查询和DDL_操作数据库_修改&;删除&;使用