给定 Oracle 中的已知分区键值，如何判断一行将进入哪个分区?

本文介绍了给定 Oracle 中的已知分区键值，如何判断一行将进入哪个分区?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

创建表 foos (batch_id NUMBER，创建日期 DATE)按范围分区(创建日期)按哈希子分区(batch_id)子分区模板(子分区 H0，子分区 H1，子分区 H2，子分区 H3)(分区 R0 值小于(日期'2018-04-01'));在 foos (batch_id) 上创建索引 foos_n1;插入 foos 值 (1234, SYSDATE);插入 foos 值 (1234, SYSDATE);插入 foos 值 (1234, SYSDATE);插入 foos 值 (1234, SYSDATE);

如果我知道一个batch_id，提前说1234，确定它在这个表中属于哪个子分区名称的最有效方法是什么，理想情况下不必已经提交一行将此值放入表中?

我知道我可以做到这一点，但是解释计划看起来很糟糕，它要求批 ID 为 1234 的行已经提交到表中

SELECT subpartition_name FROM (SELECT ao.subobject_name subpartition_name从富斯JOIN all_objects aoON DBMS_ROWID.ROWID_OBJECT(foos.rowid) = ao.data_object_id哪里 1=1和 foos.batch_id = 1234--AND ao.owner = '我'AND ao.object_name = 'FOOS'AND ao.object_type = '表子分区')哪里 rownum = 1

解决方案

有了这个测试数据

INSERT INTO foos VALUES (1234, SYSDATE);插入 foos 值 (1235, SYSDATE);插入 foos 值 (1236, SYSDATE);

如此处所述 https://jonathanlewis.wordpress.com/2009/11/21/ora_hash-function/

ora_hash() 函数的一个重要特性是它似乎是内部使用的函数 - 带有零种子 - 以确定行在哈希分区表中属于哪个分区.当然，正如我在上一篇文章中指出的，您必须根据您声明的分区数量调整函数的使用——四舍五入到最接近的 2 的幂(并减去 1)作为最大桶"输入，然后将结果加一，如果分区数不是 2 的幂，则丢弃结果的最高位.

你得到

with hsh as (从 foos 中选择 BATCH_ID, ora_hash(BATCH_ID, 3)+1 subpartition_position)选择 BATCH_ID、SUBPARTITION_POSITION、(select subpartition_name from user_tab_subpartitions where table_name = 'FOOS' and SUBPARTITION_POSITION = hsh.SUBPARTITION_POSITION) subpartition_name来自 hsh;BATCH_ID SUBPARTITION_POSITION SUBPARTITION_NAME---------- --------------------- ----------------------1236 1 R0_H01235 3 R0_H21234 4 R0_H3

注意ora_hash中的参数3是(子)分区数减1.(= 4-1).如果分区数不是参考中描述的 2 的幂(不推荐)，您将不得不进行额外的处理.

您可以使用如下显式分区查询来验证结果

select * from foos subpartition( R0_H0 );-- 1236select * from foos subpartition( R0_H1 );-    空的select * from foos subpartition( R0_H2 );-- 1235select * from foos subpartition( R0_H3 );-- 1234

当然它也适用于新键，新键用于 1237，不在表中.

with hsh as (选择 1237 BATCH_ID, ora_hash(1237, 3)+1 subpartition_position from dual)选择 BATCH_ID、SUBPARTITION_POSITION、(select subpartition_name from user_tab_subpartitions where table_name = 'FOOS' and SUBPARTITION_POSITION = hsh.SUBPARTITION_POSITION) subpartition_name来自 hsh;BATCH_ID SUBPARTITION_POSITION SUBPARTITION_NAME---------- --------------------- ----------------------1237 2 R0_H1

预测"子分区是 R0_H1，让我们看看 INSERT 会去哪里:

INSERT INTO foos VALUES (1237, SYSDATE);select * from foos subpartition( R0_H1 );-- 1237

但请谨慎使用，因为它是 IMO 未记录的功能...

CREATE TABLE foos (
    batch_id NUMBER,
    creation_date DATE
)
PARTITION BY RANGE (creation_date)
SUBPARTITION BY HASH (batch_id)
SUBPARTITION TEMPLATE (
    SUBPARTITION H0,
    SUBPARTITION H1,
    SUBPARTITION H2,
    SUBPARTITION H3
)
(
    PARTITION R0 VALUES LESS THAN (DATE'2018-04-01')
)
;

CREATE INDEX foos_n1 ON foos (batch_id);

INSERT INTO foos VALUES (1234, SYSDATE);
INSERT INTO foos VALUES (1234, SYSDATE);
INSERT INTO foos VALUES (1234, SYSDATE);
INSERT INTO foos VALUES (1234, SYSDATE);

If I know a batch_id, say 1234 in advance, what is the most efficient way of determining the subpartition name that it would belong to in this table, ideally without having to have already committed a row with this value into the table?

I know I can do this, but the explain plan looks pretty nasty, and it requires that a row with a batch id of 1234 already be committed into the table

SELECT subpartition_name FROM (
    SELECT ao.subobject_name subpartition_name
    FROM foos
        JOIN all_objects ao
            ON DBMS_ROWID.ROWID_OBJECT(foos.rowid) = ao.data_object_id
    WHERE 1=1
        AND foos.batch_id = 1234
        --AND ao.owner = 'ME'
        AND ao.object_name = 'FOOS'
        AND ao.object_type = 'TABLE SUBPARTITION'
)
WHERE rownum = 1

解决方案

With this test data

INSERT INTO foos VALUES (1234, SYSDATE);
INSERT INTO foos VALUES (1235, SYSDATE);
INSERT INTO foos VALUES (1236, SYSDATE);

As described here https://jonathanlewis.wordpress.com/2009/11/21/ora_hash-function/

you get

with hsh as (
select  BATCH_ID, ora_hash(BATCH_ID, 3)+1 subpartition_position  from foos)
select BATCH_ID, SUBPARTITION_POSITION,
(select subpartition_name from   user_tab_subpartitions where   table_name = 'FOOS' and SUBPARTITION_POSITION = hsh.SUBPARTITION_POSITION) subpartition_name
from hsh;

  BATCH_ID SUBPARTITION_POSITION SUBPARTITION_NAME
---------- --------------------- ------------------------------
      1236                     1 R0_H0
      1235                     3 R0_H2
      1234                     4 R0_H3

Note that the parameter 3 in ora_hash is the number of (sub)partitions subtracted by 1. (= 4-1). You'll have to do additional processing if the number of partition is not a power of two (which is not recommendet) as described in the reference.

You may verify the result with an explicite partition query as below

select * from foos subpartition( R0_H0 ); --   1236
select * from foos subpartition( R0_H1 ); --   empty
select * from foos subpartition( R0_H2 ); --   1235
select * from foos subpartition( R0_H3 ); --   1234

And of course it works also for new keys, new for 1237 which in not in the table.

with hsh as (
select  1237 BATCH_ID, ora_hash(1237, 3)+1 subpartition_position  from dual)
select BATCH_ID, SUBPARTITION_POSITION,
(select subpartition_name from   user_tab_subpartitions where   table_name = 'FOOS' and SUBPARTITION_POSITION = hsh.SUBPARTITION_POSITION) subpartition_name
from hsh;

  BATCH_ID SUBPARTITION_POSITION SUBPARTITION_NAME
---------- --------------------- ------------------------------
      1237                     2 R0_H1

The "predicted" subpartition is R0_H1, let*s see where the INSERT will go:

INSERT INTO foos VALUES (1237, SYSDATE);
select * from foos subpartition( R0_H1 ); --  1237

But use with caution, as it is IMO not documented feature ...

这篇关于给定 Oracle 中的已知分区键值，如何判断一行将进入哪个分区?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！