给定 Oracle 中的已知分区键值，如何判断一行将进入哪个分区? [英] How to tell which partition a row would go into, given a known partition key value in Oracle?
问题描述
如果我知道一个batch_id，提前说1234，确定它在这个表中属于哪个子分区名称的最有效方法是什么，理想情况下不必已经提交一行将此值放入表中?
我知道我可以做到这一点，但是解释计划看起来很糟糕，它要求批 ID 为 1234 的行已经提交到表中
有了这个测试数据
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/
One important feature of the ora_hash() function is that it seems to be the function used internally – with a zero seed – to determine which partition a row belongs to in a hash partitioned table. Of course, as I pointed out in the previous article, you have to tailor the use of the function to the number of partitions you have declared – rounding up to the nearest power of two (and subtracting one) for the "max bucket" input, then adding one to the result, then dropping the highest bit of the result if the number of partitions is not a power of two.
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 中的已知分区键值，如何判断一行将进入哪个分区?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
