--- title: 如何在行列混存的场景下，指定 Hint 走上预期的计划-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 如何在行列混存的场景下，指定 Hint 走上预期的计划相关的常见问题和使用技巧，帮助您快速解决 如何在行列混存的场景下，指定 Hint 走上预期的计划的难题。 image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # 如何在行列混存的场景下，指定 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 种形式。 ```SQL 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 关键字来标识列存： ```SQL 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) */`： ```SQL 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) */`： ```SQL 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，走上列存： ```SQL +--------------------------------------------------------------------------------------+ | 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) */`： ```SQL 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，走上列存： ```SQL 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 版本。 Previous [OceanBase 数据库 V4.x 版本中 select count(*) from table_A 的统计原理](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000003192503) Next [SPJ where 子查询的 where 条件中包含两参数的 substr 函数，做条件单调性判断时触发 core](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002396427) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线