目录
KingbaseES 在hint 使用方法上与oracle进行了兼容,hint 只允许放在 select 后面,同时对于子查询,支持使用单独的hint。
一、启用hint支持
直接设置 enable_hint = on。KingbaseES已直接将hint功能集成到内核中,不需要设置 shared_preload_libraries=‘sys_hint_plan’。
二、hint位置
hint 只能放置于select 后面,具体见以下例子。
test=# explain analyze select/*+seqscan(t1)*/ * from t1 where id=123456;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..8383.00 rows=1 width=208) (actual time=36.196..55.491 rows=1 loops=1)
Filter: (id = 123456)
Rows Removed by Filter: 199999
Planning Time: 0.177 ms
Execution Time: 55.537 ms
(5 rows)
test=# explain analyze/*+seqscan(t1)*/ select * from t1 where id=123456;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Index Scan using ind_t1_id on t1 (cost=0.42..8.44 rows=1 width=208) (actual time=0.173..0.174 rows=1 loops=1)
Index Cond: (id = 123456)
Planning Time: 0.495 ms
Execution Time: 0.291 ms
(4 rows)
三、支持子查询单独hint
支持子查询使用单独的hint,具体见以下例子:
--不使用hint 情况,走全表访问。
test=# explain analyze select count(*) from t1 where exists (select id from t2 where t2.id=t1.id);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=24644.00..24644.01 rows=1 width=8) (actual time=188.164..188.166 rows=1 loops=1)
-> Hash Semi Join (cost=11165.00..24144.00 rows=200000 width=0) (actual time=65.473..178.178 rows=200000 loops=1)
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1 (cost=0.00..7883.00 rows=200000 width=4) (actual time=0.007..30.990 rows=200000 loops=1)
-> Hash (cost=7883.00..7883.00 rows=200000 width=4) (actual time=65.243..65.243 rows=200000 loops=1)
Buckets: 131072 Batches: 4 Memory Usage: 2786kB
-> Seq Scan on t2 (cost=0.00..7883.00 rows=200000 width=4) (actual time=0.006..32.561 rows=200000 loops=1)
Planning Time: 0.183 ms
Execution Time: 188.219 ms
(9 rows)
--子查询使用hint
test=# explain select count(*) from t1 where exists (select/*+indexscan(t2 ind_t2_id)*/ id from t2 where t2.id=t1.id);
QUERY PLAN
----------------------------------------------------------------------------------------------
Aggregate (cost=25680.61..25680.62 rows=1 width=8)
-> Merge Semi Join (cost=1.36..25180.61 rows=200000 width=0)
Merge Cond: (t1.id = t2.id)
-> Index Only Scan using ind_t1_id on t1 (cost=0.42..11090.42 rows=200000 width=4)
-> Index Scan using ind_t2_id on t2 (cost=0.42..11090.42 rows=200000 width=4)
(5 rows)
四、父查询hint对于子查询同样有效
--在父查询可以对子查询的表指定hint
test=# explain select/*+indexscan(t2 ind_t2_id)*/ count(*) from t1 where exists (select id from t2 where t2.id=t1.id);
QUERY PLAN
----------------------------------------------------------------------------------------------
Aggregate (cost=25680.61..25680.62 rows=1 width=8)
-> Merge Semi Join (cost=1.36..25180.61 rows=200000 width=0)
Merge Cond: (t1.id = t2.id)
-> Index Only Scan using ind_t1_id on t1 (cost=0.42..11090.42 rows=200000 width=4)
-> Index Scan using ind_t2_id on t2 (cost=0.42..11090.42 rows=200000 width=4)
(5 rows)
--在父查询指定涉及子查询表的连接方式
test=# explain select/*+nestloop(t1 t2)*/ count(*) from t1 where exists (select id from t2 where t2.id=t1.id) ;
QUERY PLAN
-------------------------------------------------------------------------------------
Aggregate (cost=123619.00..123619.01 rows=1 width=8)
-> Nested Loop Semi Join (cost=0.42..123119.00 rows=200000 width=0)
-> Seq Scan on t1 (cost=0.00..7883.00 rows=200000 width=4)
-> Index Only Scan using ind_t2_id on t2 (cost=0.42..0.57 rows=1 width=4)
Index Cond: (id = t1.id)
(5 rows)
--甚至在子查询也指定子表与父表的连接方式
test=# explain select count(*) from t1 where exists (select/*+nestloop(t1 t2)*/ id from t2 where t2.id=t1.id) ;
QUERY PLAN
-------------------------------------------------------------------------------------
Aggregate (cost=123619.00..123619.01 rows=1 width=8)
-> Nested Loop Semi Join (cost=0.42..123119.00 rows=200000 width=0)
-> Seq Scan on t1 (cost=0.00..7883.00 rows=200000 width=4)
-> Index Only Scan using ind_t2_id on t2 (cost=0.42..0.57 rows=1 width=4)
Index Cond: (id = t1.id)
(5 rows)
五、注意点
- hint 指定的多项的分隔符只能是空格。
- 可以不用创建 sys_hint_plan,该扩展插件实际提供 hint_plan.hints 表。