Oracle单表能否基于不同字段创建多分区及相关性能疑问

一、按created_on分区后，使用updated_on查询的性能影响 #

• 如果查询仅包含updated_on条件（无created_on相关过滤），数据库无法利用created_on的分区规则做分区裁剪（partition pruning），会扫描所有分区：
  • 若分区数量不多、单分区数据量远小于原表，即使遍历所有分区，每个分区内的updated_on局部索引扫描速度也会快于单表全局索引，整体性能仍优于未分区状态；
  • 若分区数量极大（比如按天划分数千个分区），遍历所有分区的额外开销会抵消单分区查询的优势，可能出现性能下降。
• 如果查询同时包含created_on和updated_on条件，数据库会先通过created_on过滤出目标分区，再在这些分区内用updated_on索引查询，性能和按created_on分区的预期一致，不会有额外下降。

二、能否基于updated_on单独创建分区？ #

主流关系型数据库（如MySQL、PostgreSQL）不支持同一个表同时存在两套独立的分区规则（即同时按created_on和updated_on各自分区），但可以通过以下替代方案实现类似效果：

• 子分区（Subpartitioning）：先按created_on做RANGE主分区，每个主分区内部再按updated_on做RANGE/LIST子分区。这种方案仅在查询同时涉及两个日期字段时能最大化裁剪分区；若仅查updated_on，仍需扫描所有主分区的子分区，但子分区的存在能缩小单查询的数据范围。
  示例（MySQL）：

  CREATE TABLE your_table (
    -- 表字段定义
    id INT PRIMARY KEY,
    created_on DATE,
    updated_on DATE,
    -- 其他字段
  )
  PARTITION BY RANGE (TO_DAYS(created_on))
  SUBPARTITION BY RANGE (TO_DAYS(updated_on)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')) (
      SUBPARTITION sp202301_01 VALUES LESS THAN (TO_DAYS('2023-01-16')),
      SUBPARTITION sp202301_02 VALUES LESS THAN (TO_DAYS('2023-02-01'))
    ),
    -- 其他主分区及子分区定义
  );
  

• 物化视图（Materialized View）：创建一个按updated_on分区的物化视图，专门承载基于updated_on的查询。但需注意数据同步问题——如果数据更新频繁，需要定期刷新或配置实时刷新，否则会出现数据不一致。
• 权衡选择分区键：如果两个字段的查询频率相近，优先选择能覆盖更多查询场景的字段作为分区键（比如created_on的查询范围更集中，或updated_on的查询占比更高），同时保留另一个字段的局部索引，平衡两种查询的性能。

内容的提问来源于stack exchange，提问作者jichoul shin