Using the "partition for" syntax, you can instruct Oracle Database to only scan the partition which the value belongs to. For example:
create table t (
x int
) partition by range (x) interval (10) (
PARTITION p0 values less than (10)
);
insert into t
select rownum from dual
connect by level < 100;
commit;
select * from t partition for (99);
X
90
91
92
93
94
95
96
97
98
99
So if you pass the maximum value for the partition key, you can then join to user_objects to get the partition name:
select max(uo.subobject_name)
from t partition for (99), user_objects uo
where dbms_rowid.rowid_object(t.rowid) = uo.data_object_id;
MAX(UO.SUBOBJECT_NAME)
SYS_P188
Unfortunately you can't pass a query or bind variable to this. So you'll have to use dynamic SQL:
declare
l t.x%type;
stmt varchar2(1000);
part varchar2(30);
begin
select max(x) into l from t;
stmt := 'select max(uo.subobject_name)
from t partition for (' || l || '), user_objects uo
where dbms_rowid.rowid_object(t.rowid) = uo.data_object_id';
execute immediate stmt into part;
dbms_output.put_line(part);
end;
/
PL/SQL procedure successfully completed.
SYS_P188
Or you could inspect the high_value in user_tab_partitions. Unfortunately this is a long, making it tricky to work with. One way around this is to use DBMS_XMLGen to convert result to XML. Then extract out the appropriate parts:
with xml as (
select dbms_xmlgen.getxmltype('
select partition_name, high_value from user_tab_partitions'
) x
from dual
), vals as (
select to_number(extractValue(column_value, 'ROW/HIGH_VALUE')) hv,
extractValue(column_value, 'ROW/PARTITION_NAME') pn,
max(to_number(extractValue(column_value, 'ROW/HIGH_VALUE'))) over () mx
from xml, table(xmlsequence(extract(xml.x, '/ROWSET/ROW')))
)
select *
from vals
where hv = mx;
HV PN MX
100 SYS_P188 100
For more about using DBMS_XMLGen to convert longs and other methods, read:
http://www.oracle-developer.net/display.php?id=430