已知Oracle分区键值，如何判断行数据所属分区与子分区？

嘿，针对你创建的这个Oracle分区表（范围分区+哈希子分区），要根据batch_id判断对应行所属的分区和子分区，我给你整理了几种实用的方法，都很容易上手：

方法1：通过数据字典视图+哈希函数推算 #

因为你的表是**范围分区（按creation_date）+哈希子分区（按batch_id）**的组合，我们可以利用Oracle的ORA_HASH函数结合数据字典视图来定位：

SELECT 
    p.partition_name AS 主分区名称,
    sp.subpartition_name AS 子分区名称
FROM 
    USER_TAB_SUBPARTITIONS sp
JOIN 
    USER_TAB_PARTITIONS p ON sp.partition_name = p.partition_name
WHERE 
    sp.table_name = 'FOOS'
    AND sp.hash_partition_position = MOD(ORA_HASH(1234, 3), 4) + 1;

解释： #

• ORA_HASH(1234, 3)：计算batch_id=1234的哈希值，第二个参数3表示哈希结果的最大值（因为你定义了4个子分区H0-H3，哈希范围是0~3）。
• MOD(...,4)+1：将哈希结果转换为Oracle子分区的位置编号（子分区位置从1开始），从而匹配到对应的子分区。
• 主分区的判断还可以结合creation_date的范围，比如你创建的R0分区是VALUES LESS THAN (DATE'2018-04-01')，如果行的creation_date满足这个条件，就属于R0分区。

方法2：直接查询指定行的分区信息 #

如果已经存在目标行数据，你可以通过ROWID关联数据字典视图，直接获取分区信息，这个方法最直观：

SELECT 
    DISTINCT -- 如果同一batch_id有多行，去重返回分区信息
    p.partition_name AS 主分区名称,
    sp.subpartition_name AS 子分区名称
FROM 
    FOOS f
JOIN 
    USER_TAB_SUBPARTITIONS sp ON DBMS_ROWID.ROWID_OBJECT(f.rowid) = sp.object_id
JOIN 
    USER_TAB_PARTITIONS p ON sp.partition_name = p.partition_name
WHERE 
    f.batch_id = 1234;

解释： #

• DBMS_ROWID.ROWID_OBJECT(f.rowid)：提取行的对象ID，用来关联子分区的对象ID，从而定位到具体的子分区。
• 用DISTINCT是因为同一batch_id的行肯定在同一个子分区（哈希子分区的规则决定的），所以去重后结果更简洁。

方法3：手动根据分区规则推算 #

如果你不想写SQL查询，也可以手动计算：

• 主分区：直接看行的creation_date，如果creation_date < DATE'2018-04-01'，就属于R0分区；如果后续新增了其他范围分区，同理对比即可。
• 子分区：用ORA_HASH函数计算哈希值，对应关系如下：

  SELECT 
      ORA_HASH(1234, 3) AS 哈希值,
      CASE ORA_HASH(1234, 3)
          WHEN 0 THEN 'H0'
          WHEN 1 THEN 'H1'
          WHEN 2 THEN 'H2'
          WHEN 3 THEN 'H3'
      END AS 子分区名称
  FROM DUAL;
  

  执行这段SQL就能得到batch_id=1234对应的子分区，哈希值0对应H0，1对应H1，以此类推。

内容的提问来源于stack exchange，提问作者Matthew Moisen