基于湖库一体架构，统一管理结构化、半结构化与非结构化等多模态数据，一个系统承载事务处理、实时分析与 AI 工作负载。
划线反馈
如何在行列混存的场景下，指定 Hint 走上预期的计划
更新时间：2026-05-14 07:41
分享
适用版本：V4.0.x、V4.1.x、V4.2.x、V4.3.x、V4.4.x内容类型：TechNote
在纯列存表的场景，可以通过使用下面的 Hint 指定是否走列存。
/*+ use_column_table(table_name) */：使用列存。
/*+ no_use_column_table(table_name) */：不使用列存
但是当表，索引存在行列混存的场景，单纯使用上面的 Hint 是不能走上希望的计划的。需要配合使用行存 Hint 和普通 Hint 走上需要的计划。
本文结合一些实例来解释如何使行列混存表/索引正确被使用。
详细说明
首先创建一张行列混存表。其中 all columns 代表行存，each column 代表列存。表和索引都同时创建了行存和列存 2 种形式。
create table tcg(
c1 int,
c2 int,
c3 int,
primary key(c1),
key idx_tcg_c2(c2) with column group(all columns, each column) -- 创建了行存/列存索引
) partition by hash(c1) partitions 3 with column group (all columns, each column); -- 创建了行存/列存表
查询 c2 字段，存在列存索引，优化器默认选择了列存索引的计划。 带有 column 关键字来标识列存：
obclient [test]> explain select * from tcg where c2 > 10;
+-----------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------+
| ======================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------------------ |
| |0 |PX COORDINATOR | |1 |17 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000 |1 |17 | |
| |2 | └─PX PARTITION ITERATOR | |1 |16 | |
| |3 | └─COLUMN TABLE RANGE SCAN|tcg(idx_tcg_c2)|1 |16 | |////列存索引
| ======================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(tcg.c1, tcg.c2, tcg.c3)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(tcg.c1, tcg.c2, tcg.c3)]), filter(nil), rowset=16 |
| dop=1 |
| 2 - output([tcg.c1], [tcg.c2], [tcg.c3]), filter(nil), rowset=16 |
| force partition granule |
| 3 - output([tcg.c1], [tcg.c2], [tcg.c3]), filter(nil), rowset=16 |
| access([tcg.c1], [tcg.c2], [tcg.c3]), partitions(p[0-2]) |
| is_index_back=true, is_global_index=false, |
| range_key([tcg.c2], [tcg.c1]), range(10,MAX ; MAX,MAX), |
| range_cond([tcg.c2 > 10]) |
+-----------------------------------------------------------------------------------+
20 rows in set (0.021 sec)
查询走行存索引，需要先指定不使用列存，同时加上使用的行存索引的名字。/*+ no_use_column_table(tcg) index(tcg idx_tcg_c2) */：
obclient [test]> explain select /*+ no_use_column_table(tcg) index(tcg idx_tcg_c2) */ * from tcg where c2 > 10;
+-----------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------+
| ==================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| -------------------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |17 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000 |1 |17 | |
| |2 | └─PX PARTITION ITERATOR| |1 |16 | |
| |3 | └─TABLE RANGE SCAN |tcg(idx_tcg_c2)|1 |16 | |//// 行存索引
| ==================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(tcg.c1, tcg.c2, tcg.c3)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(tcg.c1, tcg.c2, tcg.c3)]), filter(nil), rowset=16 |
| dop=1 |
| 2 - output([tcg.c1], [tcg.c2], [tcg.c3]), filter(nil), rowset=16 |
| force partition granule |
| 3 - output([tcg.c1], [tcg.c2], [tcg.c3]), filter(nil), rowset=16 |
| access([tcg.c1], [tcg.c2], [tcg.c3]), partitions(p[0-2]) |
| is_index_back=true, is_global_index=false, |
| range_key([tcg.c2], [tcg.c1]), range(10,MAX ; MAX,MAX), |
| range_cond([tcg.c2 > 10]) |
+-----------------------------------------------------------------------------------+
20 rows in set (0.171 sec)
走列存全表 /*+ use_column_table(tcg) full(tcg) */：
obclient [test]> explain select /*+ use_column_table(tcg) full(tcg) */ * from tcg where c2 > 10;
+------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------+
| ================================================================ |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ---------------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |20 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000|1 |20 | |
| |2 | └─PX PARTITION ITERATOR | |1 |19 | |
| |3 | └─COLUMN TABLE FULL SCAN|tcg |1 |19 | |////列存全表扫
| ================================================================ |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(tcg.c1, tcg.c2, tcg.c3)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(tcg.c1, tcg.c2, tcg.c3)]), filter(nil), rowset=16 |
| dop=1 |
| 2 - output([tcg.c1], [tcg.c2], [tcg.c3]), filter(nil), rowset=16 |
| force partition granule |
| 3 - output([tcg.c1], [tcg.c2], [tcg.c3]), filter([tcg.c2 > 10]), rowset=16 |
| access([tcg.c1], [tcg.c2], [tcg.c3]), partitions(p[0-2]) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([tcg.c1]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------+
19 rows in set (0.025 sec)
也可以通过 /*+opt_param('tcg', 'column_store')*/ 下面的 Hint，走上列存：
+--------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------+
| ======================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------------------ |
| |0 |PX COORDINATOR | |1 |17 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000 |1 |17 | |
| |2 | └─PX PARTITION ITERATOR | |1 |16 | |
| |3 | └─COLUMN TABLE RANGE SCAN|tcg(idx_tcg_c2)|1 |16 | |
| ======================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(tcg.c1, tcg.c2, tcg.c3)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(tcg.c1, tcg.c2, tcg.c3)]), filter(nil), rowset=16 |
| dop=1 |
| 2 - output([tcg.c1], [tcg.c2], [tcg.c3]), filter(nil), rowset=16 |
| force partition granule |
| 3 - output([tcg.c1], [tcg.c2], [tcg.c3]), filter(nil), rowset=16 |
| access([tcg.__pk_increment], [tcg.c1], [tcg.c2], [tcg.c3]), partitions(p[0-2]) |
| is_index_back=true, is_global_index=false, |
| range_key([tcg.c2], [tcg.__pk_increment]), range(10,MAX ; MAX,MAX), |
| range_cond([tcg.c2 > 10]) |
走行存全表 /*+ no_use_column_table(tcg) full(tcg) */：
obclient [test]> explain select /*+ no_use_column_table(tcg) full(tcg) */ * from tcg where c2 > 10;
+------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------+
| ============================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |7 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000|1 |7 | |
| |2 | └─PX PARTITION ITERATOR| |1 |7 | |
| |3 | └─TABLE FULL SCAN |tcg |1 |7 | |///行存全表
| ============================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(tcg.c1, tcg.c2, tcg.c3)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(tcg.c1, tcg.c2, tcg.c3)]), filter(nil), rowset=16 |
| dop=1 |
| 2 - output([tcg.c1], [tcg.c2], [tcg.c3]), filter(nil), rowset=16 |
| force partition granule |
| 3 - output([tcg.c1], [tcg.c2], [tcg.c3]), filter([tcg.c2 > 10]), rowset=16 |
| access([tcg.c1], [tcg.c2], [tcg.c3]), partitions(p[0-2]) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([tcg.c1]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------+
19 rows in set (0.177 sec)
也可以通过 /*+opt_param('tcg', 'row_store')*/ 下面的 Hint，走上列存：
obclient [test]> explain select /*+opt_param('tcg', 'row_store') */ * from tcg where c2 > 10;
+--------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------+
| ==================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| -------------------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |17 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000 |1 |17 | |
| |2 | └─PX PARTITION ITERATOR| |1 |16 | |
| |3 | └─TABLE RANGE SCAN |tcg(idx_tcg_c2)|1 |16 | |
| ==================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(tcg.c1, tcg.c2, tcg.c3)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(tcg.c1, tcg.c2, tcg.c3)]), filter(nil), rowset=16 |
| dop=1 |
| 2 - output([tcg.c1], [tcg.c2], [tcg.c3]), filter(nil), rowset=16 |
| force partition granule |
| 3 - output([tcg.c1], [tcg.c2], [tcg.c3]), filter(nil), rowset=16 |
| access([tcg.__pk_increment], [tcg.c1], [tcg.c2], [tcg.c3]), partitions(p[0-2]) |
| is_index_back=true, is_global_index=false, |
| range_key([tcg.c2], [tcg.__pk_increment]), range(10,MAX ; MAX,MAX), |
| range_cond([tcg.c2 > 10]) |
+--------------------------------------------------------------------------------------+
20 rows in set (0.034 sec)
适用版本
OceanBase 数据库 V4.x 版本。
