Oracle分区表WHERE查询慢于PARTITION FOR的原因及优化咨询

现象 #

我有一张按kpi_date列做范围分区的performance_data表，数据量达数十亿行。此前用WHERE子句查询特定日期的转换数据，即使基于分区列查询仍需数分钟；但使用FROM table_name PARTITION FOR(DATE '2024-09-17')查询相同数据仅需数秒。

疑问 #

• 为何会出现这种性能差异？Oracle在WHERE子句中不会自动进行分区裁剪吗？
• 是我的表架构存在问题吗？
• 咨询ChatGPT得到矛盾结论：既说WHERE子句会触发全表扫描导致慢，又说Oracle会自动处理分区，该如何判断？

表与分区配置信息 #

表创建语句 #

CREATE TABLE performance_data( 
    . . . 
    kpi_date DATE, 
    . . . 
) PARTITION BY RANGE (kpi_date) ( 
    PARTITION p_init VALUES LESS THAN (TO_DATE('2024-05-16', 'YYYY-MM-DD')) 
);

分区维护方式 #

通过定时任务设置了每日自动分区：

ALTER TABLE performance_data SET INTERVAL (INTERVAL '1' DAY)

索引情况 #

近期有人为kpi_date列创建了索引，但加索引前就已经存在查询慢的问题。

查询语句对比 #

慢查询（耗时数分钟） #

SELECT * FROM performance_data WHERE kpi_date = TRUNC(SYSDATE-2);

快查询（耗时数秒） #

SELECT * FROM performance_data PARTITION FOR(DATE '2024-09-15');

但显式指定分区的方式无法实现动态获取数据，需要解决这个问题。

求助需求 #

作为新手，恳请指导以下内容：

• 需要调整的查询语句
• 表DDL或分区创建方式的修改建议
• 如何动态使用PARTITION FOR()实现日期的动态查询

内容的提问来源于stack exchange，提问作者Jay Girulkar