How to Get the Partition Name
for each Row in the Table?
In some cases there is no trivial way to get the partition name of a
particular row (e.g. hash partitioning, composite partitioning).
In those cases the function PMarker (Package DBMS_Mview) can be very usefull. See the script below how
to assign the partition name to a particular row of a partitioned table.
The following steps are performed
1) PMarker get ROWID as input and return the object_id of the partition
/ subpartition
2) the object_id is used to get the partition name in the user_object view
J.Nemec
25.5.2006
Source
http://www.freelists.org/archives/oracle-l/05-2006/msg00491.html
SQL> @how_to_get_partition_name
SQL> --- How to
identify the partiton?
SQL> -- JN
25.5.2006
SQL>
---------------------------------
SQL> set echo on
SQL> -- range
partitioning
SQL> drop table t;
Table dropped.
SQL> create table
t (c1,c2)
2
partition by range (c1)
3
(
4
PARTITION p1 VALUES LESS THAN (5),
5
PARTITION p2 VALUES LESS THAN (MAXVALUE)
6
)
7 as
8
select rownum, rownum from dual connect by level < 10;
Table created.
SQL> --
SQL> select
subobject_name, data_object_id
2
from user_objects
3
where object_name = 'T' and
4 object_type = 'TABLE PARTITION'
5 ;
SUBOBJECT_NAME DATA_OBJECT_ID
------------------------------
--------------
P1 118151
P2 118152
SQL> with parts as
2
(select --+ materialize
3
data_object_id,
4 subobject_name
5
from user_objects
6
where object_name = 'T' and
7 object_type = 'TABLE PARTITION')
8
select subobject_name,
9
c1,c2
10
from T,
11
parts
12
where data_object_id =
DBMS_MView.PMarker(t.rowid)
13
order by 1;
SUBOBJECT_NAME C1 C2
------------------------------
---------- ----------
P1 4 4
P1 3 3
P1 2 2
P1 1 1
P2 8 8
P2 7 7
P2 6 6
P2 9 9
P2 5 5
9 rows selected.
SQL> --- hash
partitioning
SQL> drop table t;
Table dropped.
SQL> create table
t (c1,c2)
2
partition by hash (c1)
3 (partition
a, partition b, partition c, partition d)
4 --
partitions 4 -- alternativ syntax
5 as
6 select rownum, rownum from
dual connect by level < 10;
Table created.
SQL> --
SQL> select
subobject_name, data_object_id
2
from user_objects
3
where object_name = 'T' and
4 object_type = 'TABLE PARTITION'
5 ;
SUBOBJECT_NAME DATA_OBJECT_ID
------------------------------
--------------
A 118154
B 118155
C 118156
D 118157
SQL> with parts as
2
(select --+ materialize
3
data_object_id,
4 subobject_name
5
from user_objects
6
where object_name = 'T' and
7 object_type = 'TABLE PARTITION')
8
select subobject_name,
9
c1,c2
10
from T,
11
parts
12
where data_object_id =
DBMS_MView.PMarker(t.rowid)
13
order by 1;
SUBOBJECT_NAME C1 C2
------------------------------
---------- ----------
A 6 6
B 9 9
C 5 5
C 2 2
C 8 8
D 1 1
D 3 3
D 4 4
D 7 7
9 rows selected.
SQL> --- now
subpartitions
SQL> drop table t;
Table dropped.
SQL> create table
t (c1,c2)
2
partition by range (c1)
3
subpartition by list (c2)
4 SUBPARTITION TEMPLATE
5
(subpartition aa values (1,7,
null),
6 subpartition bb values (default)
7 )
8
(
9
PARTITION p1 VALUES LESS THAN (5),
10
PARTITION p2 VALUES LESS THAN (MAXVALUE)
11
)
12 as
13
select rownum, rownum from dual connect by level < 10;
Table created.
SQL> --
SQL> select
subobject_name, data_object_id
2
from user_objects
3
where object_name = 'T' and
4 object_type = 'TABLE SUBPARTITION'
5 ;
SUBOBJECT_NAME DATA_OBJECT_ID
------------------------------
--------------
P1_AA 118161
P1_BB 118162
P2_AA 118163
P2_BB 118164
SQL> with parts as
2
(select --+ materialize
3 data_object_id,
4 subobject_name
5
from user_objects
6
where object_name = 'T' and
7 object_type = 'TABLE SUBPARTITION')
8
select subobject_name,
9
c1,c2
10
from T,
11
parts
12
where data_object_id =
DBMS_MView.PMarker(t.rowid)
13
order by 1;
SUBOBJECT_NAME C1 C2
------------------------------
---------- ----------
P1_AA 1 1
P1_BB 2 2
P1_BB 3 3
P1_BB 4 4
P2_AA 7 7
P2_BB 5 5
P2_BB 6 6
P2_BB 8 8
P2_BB 9 9
9 rows selected.
SQL>