Finding partition name based on HIGH_VALUE using TBL$OR$IDX$PART$NUM in SQL, PL/SQL, keyword “PARTITION FOR”

This article presents how to convert HIGH_VALUE to (sub)partition name. HIGH_VALUE is column which you can find in USER/ALL/DBA_TAB_PARTITIONS or USER/ALL/DBA_TAB_SUBPARTITIONS. Additional it shows nice extension keyword PARTITION FOR which can simplify your operations on (sub)partitions.

Test data

Let’s prepare some test data. One table is created TEST_TBL with simple composite partitioning with six partitions. Column HIGH_VALUE shows border between (sub)partitions within the table. All examples were executed in my own schema “TOMASZ”.

show user
USER is TOMASZ

create table test_tbl
(
 area     number,
 district varchar2(1),
 rec_num  number
)
   partition by range (area, district)
subpartition by range (rec_num)
subpartition template
( 
  subpartition s1 values less than(10),
  subpartition s2 values less than(20)
)
(
 partition "P1"  values less than (10, 'B'),
 partition "P2"  values less than (10, 'C'),
 partition "P3"  values less than (10, 'D'),
 partition "P4"  values less than (20, 'B'),
 partition "P5"  values less than (20, 'C'), 
 partition "P6"  values less than (20, 'D')
);

select partition_name, high_value
from user_tab_partitions
where table_name='TEST_TBL'
order by partition_position;

PARTITION_NAME  HIGH_VALUE
--------------- -------------
P1              10, 'B'
P2              10, 'C'
P3              10, 'D'
P4              20, 'B'
P5              20, 'C'
P6              20, 'D'

select partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name='TEST_TBL'
order by partition_name, subpartition_position;

PARTITION_NAME   SUBPARTITION_NAME    HIGH_VALUE   
---------------- -------------------- -------------
P1               P1_S1                10           
P1               P1_S2                20           
P2               P2_S1                10           
P2               P2_S2                20           
P3               P3_S1                10           
P3               P3_S2                20           
P4               P4_S1                10           
P4               P4_S2                20           
P5               P5_S1                10           
P5               P5_S2                20           
P6               P6_S1                10           
P6               P6_S2                20

Now it’s time to insert data into TEST_TBL. Each record is stored in separate subpartition and 2 records per partition.

insert into test_tbl values(10, 'A', 5);
insert into test_tbl values(10, 'B', 5);
insert into test_tbl values(10, 'C', 5);
insert into test_tbl values(10, 'A', 15);
insert into test_tbl values(10, 'B', 15);
insert into test_tbl values(10, 'C', 15);
insert into test_tbl values(20, 'A', 5);
insert into test_tbl values(20, 'B', 5);
insert into test_tbl values(20, 'C', 5);
insert into test_tbl values(20, 'A', 15);
insert into test_tbl values(20, 'B', 15);
insert into test_tbl values(20, 'C', 15);
commit;

select 'P1' part_name, a.*  from test_tbl partition (p1) a
union all
select 'P2', a.* from test_tbl partition (p2) a 
union all
select 'P3', a.* from test_tbl partition (p3) a
union all
select 'P4', a.* from test_tbl partition (p4) a
union all
select 'P5', a.* from test_tbl partition (p5) a
union all
select 'P6', a.* from test_tbl partition (p6) a;

PART_NAME       AREA DISTRICT    REC_NUM
--------- ---------- -------- ----------
P1                10 A                 5 
P1                10 A                15 
P2                10 B                 5 
P2                10 B                15 
P3                10 C                 5 
P3                10 C                15 
P4                20 A                 5 
P4                20 A                15 
P5                20 B                 5 
P5                20 B                15 
P6                20 C                 5 
P6                20 C                15

select 'P1_S1' part_name, a.*  from test_tbl subpartition (p1_s1) a
union all
select 'P1_S2', a.* from test_tbl subpartition (p1_s2) a 
union all
select 'P2_S1', a.* from test_tbl subpartition (p2_s1) a
union all
select 'P2_S2', a.* from test_tbl subpartition (p2_s2) a
union all
select 'P3_S1', a.* from test_tbl subpartition (p3_s1) a
union all
select 'P3_S2', a.* from test_tbl subpartition (p3_s2) a
union all
select 'P4_S1', a.* from test_tbl subpartition (p4_s1) a
union all
select 'P4_S2', a.* from test_tbl subpartition (p4_s2) a 
union all
select 'P5_S1', a.* from test_tbl subpartition (p5_s1) a
union all
select 'P5_S2', a.* from test_tbl subpartition (p5_s2) a
union all
select 'P6_S1', a.* from test_tbl subpartition (p6_s1) a
union all
select 'P6_S2', a.* from test_tbl subpartition (p6_s2) a;

PART_NAME       AREA DISTRICT    REC_NUM
--------- ---------- -------- ----------
P1_S1             10 A                 5 
P1_S2             10 A                15 
P2_S1             10 B                 5 
P2_S2             10 B                15 
P3_S1             10 C                 5 
P3_S2             10 C                15 
P4_S1             20 A                 5 
P4_S2             20 A                15 
P5_S1             20 B                 5 
P5_S2             20 B                15 
P6_S1             20 C                 5 
P6_S2             20 C                15

Function TBL$OR$IDX$PART$NUM

In Oracle you can find very interesting undocumented function TBL$OR$IDX$PART$NUM with following syntax.

TBL$OR$IDX$PART$NUM("PARTITIONED_TABLE_NAME", 0, d#, p#, "HIGH_VALUE")

The function returns (sub)partition position within partitioned table based on HIGH_VALUE parameter.

Examples

This example returns partition position within table TEST_TBL based on HIGH_VALUE defined manually – 5, ‘A’

select 
  TBL$OR$IDX$PART$NUM
   ("TOMASZ"."TEST_TBL", 0, 1, 0, 5, 'A') pos
from dual;

       POS
----------
         1

Following example returns partition position based on HIGH_VALUE created dynamically from two partition key columns AREA, DISTRICT. Each records will tell you to which partition it belongs.

SELECT a.*, 
  TBL$OR$IDX$PART$NUM("TOMASZ"."TEST_TBL", 0, 1, 0, 
                      a."AREA", a."DISTRICT") part
FROM tomasz.test_tbl a;

      AREA DISTRICT    REC_NUM       PART
---------- -------- ---------- ----------
        10 A                 5          1 
        10 A                15          1 
        10 B                 5          2 
        10 B                15          2 
        10 C                 5          3 
        10 C                15          3 
        20 A                 5          4 
        20 A                15          4 
        20 B                 5          5 
        20 B                15          5 
        20 C                 5          6 
        20 C                15          6

It’s very useful command to find a partitions name which you want to split.

For example new AREA can appear with value 15 and you want to create new partitions for it. So question is which partition should be split for HIGH_VALUE 15, ‘B’. With the function TBL$OR$IDX$PART$NUM it’s piece of cake.

SELECT table_name, partition_name, high_value 
FROM user_tab_partitions
where table_name='TEST_TBL'
  and partition_position=TBL$OR$IDX$PART$NUM
  ("TOMASZ"."TEST_TBL", 0, 1, 0, 15, 'B');

TABLE_NAME    PARTITION_NAME    HIGH_VALUE
------------- ----------------- -----------
TEST_TBL      P4                20, 'B'

Finding subpartition is more complicated. You need to first find data_object_id for subpartition segment

SELECT a.*, 
  TBL$OR$IDX$PART$NUM("TOMASZ"."TEST_TBL", 0, 3, 0, 
             a."AREA", a."DISTRICT", a."REC_NUM") data_object_id
FROM tomasz.test_tbl a;

      AREA DISTRICT    REC_NUM DATA_OBJECT_ID
---------- -------- ---------- --------------
        10 A                 5       76274464 
        10 A                15       76274465 
        10 B                 5       76274466 
        10 B                15       76274467 
        10 C                 5       76274468 
        10 C                15       76274469 
        20 A                 5       76274470 
        20 A                15       76274471 
        20 B                 5       76274472 
        20 B                15       76274473 
        20 C                 5       76274474 
        20 C                15       76274475

then you can join it with USER_OBJECTS to get subpartition_name

SELECT 
  a.*, 
  b.object_name, b.subobject_name, b.object_type
FROM test_tbl a,
     user_objects b
where
  TBL$OR$IDX$PART$NUM("TOMASZ"."TEST_TBL", 0, 3, 0, 
               a."AREA", a."DISTRICT", a."REC_NUM")=b.data_object_id
  and b.object_name='TEST_TBL';

AREA DISTRICT REC_NUM OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE       
---- -------- ------- ----------- -------------- ------------------
  20 C             15 TEST_TBL    P6_S2          TABLE SUBPARTITION
  20 C              5 TEST_TBL    P6_S1          TABLE SUBPARTITION
  20 B             15 TEST_TBL    P5_S2          TABLE SUBPARTITION
  20 B              5 TEST_TBL    P5_S1          TABLE SUBPARTITION
  20 A             15 TEST_TBL    P4_S2          TABLE SUBPARTITION
  20 A              5 TEST_TBL    P4_S1          TABLE SUBPARTITION
  10 C             15 TEST_TBL    P3_S2          TABLE SUBPARTITION
  10 C              5 TEST_TBL    P3_S1          TABLE SUBPARTITION
  10 B             15 TEST_TBL    P2_S2          TABLE SUBPARTITION
  10 B              5 TEST_TBL    P2_S1          TABLE SUBPARTITION
  10 A             15 TEST_TBL    P1_S2          TABLE SUBPARTITION
  10 A              5 TEST_TBL    P1_S1          TABLE SUBPARTITION

Keyword PARTITION FOR

Oracle 11g introduces very nice keyword “PARTITION FOR” which helps to manipulate partitions based on HIGH_VALUE. It’s silently converted into partition name by Oracle.

PARTITION FOR(HIGH_VALUE)

The syntax is supported in many operations on Oracle partitions.

Examples

SELECT from a partition based on HIGH_VALUE

select * from test_tbl PARTITION FOR(7, null);

      AREA DISTRICT    REC_NUM
---------- -------- ----------
        10 A                 1

SPLIT a partition based on HIGH_VALUE

alter table test_tbl
split PARTITION FOR(7, null) at(7, maxvalue)
into
(
  partition p0,
  partition p1
);
select partition_name, high_value 
from user_tab_partitions
where table_name='TEST_TBL'
order by partition_position;

PARTITION_NAME  HIGH_VALUE
--------------- ----------------
P0              7, MAXVALUE
P1              10, 'B'
P2              10, 'C'
P3              10, 'D'
P4              20, 'B'
P5              20, 'C'
P6              20, 'D'

DROP a partition based on HIGH_VALUE

alter table test_tbl drop PARTITION FOR (1, null);

select partition_name, high_value 
from user_tab_partitions
where table_name='TEST_TBL'
order by partition_position;

PARTITION_NAME  HIGH_VALUE 
--------------- ----------------
P1              10, 'B'
P2              10, 'C'
P3              10, 'D'
P4              20, 'B'
P5              20, 'C'
P6              20, 'D'

Have a fun 🙂

Tomasz

2 thoughts on “Finding partition name based on HIGH_VALUE using TBL$OR$IDX$PART$NUM in SQL, PL/SQL, keyword “PARTITION FOR””

Łukasz on December 10, 2014 at 3:05 pm said:

Thanks! That helped me.

Reply ↓
Venkat on April 3, 2015 at 6:16 am said:

Thanks a lot for Good Article, It helped me.

Reply ↓

DBAORA

oracle expert presents