--- title: List 类型分区表无法分区裁剪的原因及解决办法-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于List 类型分区表无法分区裁剪的原因及解决办法相关的常见问题和使用技巧，帮助您快速解决List 类型分区表无法分区裁剪的原因及解决办法的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # List 类型分区表无法分区裁剪的原因及解决办法 更新时间：2026-05-21 09:16 适用版本： V4.2.x 内容类型：Troubleshoot ## 问题现象 SQL 查询一张 List 分区类型的分区表查询的 SQL 中 WHERE 条件是分区键字段使用分区键是范围条件，发现计划 partitions(p[0-112]) 没有分区剪裁导致全表扫性能差。 ```shell explain extended select COALESCE(sum(wkpje), 0) as kpje, count(rdid) cnt from TABLE where kfhm='0701819022' and ywlx='1' and lszt in(1,2) and cfhbzt=1 and jyjg not in('01025','01026','01027','01032') and jyrq>=to_date('20191001','yyyymmdd') and jyrq<=to_date('20191021','yyyymmdd'); =============================================================================== |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| ------------------------------------------------------------------------------- |0 |SCALAR GROUP BY | |1 |177171 | |1 |└─PX COORDINATOR | |1 |177171 | |2 | └─EXCHANGE OUT DISTR |:EX10000 |1 |177170 | |3 | └─MERGE GROUP BY | |1 |177169 | |4 | └─PX PARTITION ITERATOR| |1 |177169 | |5 | └─TABLE FULL SCAN |TABLE_XXX |1 |177169 | =============================================================================== Outputs & filters: ------------------------------------- [TABLE_XXX.RDID(0x7f354d6480d0)]), partitions(p[0-112]) ******这里可以看到没有分区裁剪****** is_index_back=false, is_global_index=false, filter_before_indexback[false,false,false,false,false,false,false], range_key([TABLE_XXX.__pk_increment(0x7f354d6498f0)]), range(MIN ; MAX)always true ``` ## 问题原因 问题原因是优化器不支持 List 类型分区表范围条件做裁剪访问了所有分区导致 SQL 执行执行慢，下面是最小化用例复现分别举例 tinyint 和 datetime 类型，可以观察 计划 partitions(p[0-5]) 访问了所有分区。 等值查询条件list类型分区表是支持分区剪裁的。 ```shell create table t1 (c1 tinyint, c2 tinyint) partition by list(c1) ( partition p0 values in (1,2), partition p1 values in (3,4), partition p2 values in (5,6), partition p3 values in (7,8), partition p4 values in (9,10), partition p5 values in (default) ); insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10); OceanBase(root@test)>explain select * from t1 where c1 < 3; +------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------+ | ============================================================= | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ------------------------------------------------------------- | | |0 |PX COORDINATOR | |1 |25 | | | |1 |└─EXCHANGE OUT DISTR |:EX10000|1 |24 | | | |2 | └─PX PARTITION ITERATOR| |1 |24 | | | |3 | └─TABLE FULL SCAN |t1 |1 |24 | | | ============================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2)]), filter(nil), rowset=16 | | 1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2)]), filter(nil), rowset=16 | | dop=1 | | 2 - output([t1.c1], [t1.c2]), filter(nil), rowset=16 | | force partition granule | | 3 - output([t1.c1], [t1.c2]), filter([t1.c1 < 3]), rowset=16 | | access([t1.c1], [t1.c2]), partitions(p[0-5]) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | +------------------------------------------------------------------------------------+ 19 rows in set (0.04 sec) create table t9 (c1 datetime, c2 datetime) partition by list columns(c1) ( partition p0 values in ('2024-01-01 12:00:00','2024-01-02 12:00:00','2024-01-03 12:00:00','2024-01-04 12:00:00','2024-01-05 12:00:00'), partition p1 values in ('2024-01-06 12:00:00','2024-01-07 12:00:00','2024-01-08 12:00:00','2024-01-09 12:00:00','2024-01-10 12:00:00') ); insert into t9 values ('2024-01-01 12:00:00','2024-01-01 12:00:00'),('2024-01-02 12:00:00','2024-01-02 12:00:00'),('2024-01-03 12:00:00','2024-01-03 12:00:00'), ('2024-01-04 12:00:00','2024-01-04 12:00:00'),('2024-01-05 12:00:00','2024-01-05 12:00:00'),('2024-01-06 12:00:00','2024-01-06 12:00:00'), ('2024-01-07 12:00:00','2024-01-07 12:00:00'),('2024-01-08 12:00:00','2024-01-08 12:00:00'),('2024-01-09 12:00:00','2024-01-09 12:00:00'), ('2024-01-10 12:00:00','2024-01-10 12:00:00'); explain select * from t9 where c1 < '2024-01-03 00:00:00'; OceanBase(root@test)>explain select * from t9 where c1 < '2024-01-03 00:00:00'; +------------------------------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------------------------------+ | ============================================================= | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ------------------------------------------------------------- | | |0 |PX COORDINATOR | |1 |10 | | | |1 |└─EXCHANGE OUT DISTR |:EX10000|1 |9 | | | |2 | └─PX PARTITION ITERATOR| |1 |8 | | | |3 | └─TABLE FULL SCAN |t9 |1 |8 | | | ============================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([INTERNAL_FUNCTION(t9.c1, t9.c2)]), filter(nil), rowset=16 | | 1 - output([INTERNAL_FUNCTION(t9.c1, t9.c2)]), filter(nil), rowset=16 | | dop=1 | | 2 - output([t9.c1], [t9.c2]), filter(nil), rowset=16 | | force partition granule | | 3 - output([t9.c1], [t9.c2]), filter([t9.c1 < cast('2024-01-03 00:00:00', MYSQL_DATETIME(-1, -1))]), rowset=16 | | access([t9.c1], [t9.c2]), partitions(p[0-1]) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([t9.__pk_increment]), range(MIN ; MAX)always true | +------------------------------------------------------------------------------------------------------------------+ 19 rows in set (0.03 sec) ``` ## 问题的风险及影响 因为优化器不支持 List 类型分区表范围条件做裁剪访问了所有分区导致 SQL 执行执行慢。 ## 影响租户 影响 OceanBase 数据库中的 SYS 租户和 Oracle 租户以及 MySQL 租户。 ## 适用版本 OceanBase 数据库 V4.2.x 版本。 ## 解决方法 升级至问题已修复版本。目前已修复的版本包括 OceanBase 数据库企业版 V4.3.5（oceanbase-4.3.5.0-100000122024123020）及之后版本。 ## 规避方式 如果明确知道访问的分区可以指定分区的方式规避。 Previous [OceanBase 开启 enable_lock_priority 参数导致动态分区失败](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000005520974) Next [OBServer 告警：partition not exist in observer](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000207678) ![有帮助](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) 咨询热线