Skip to Main Content
  • Questions
  • Peformance issue with user_tab_partitions query


Question and Answer

Connor McDonald

Thanks for the question.

Asked: June 28, 2016 - 10:28 am UTC

Answered by: Connor McDonald - Last updated: June 29, 2016 - 7:00 am UTC

Category: Database - Version: 11

Viewed 1000+ times

You Asked


We have the below query which is taking lot of time....while reading the partition name for the given high_value of table table_1. Here P_SEQ is the input value that we are passing through a procedure to this query.

SELECT to_char(x.partition_name)
into v_partition_name -- Get Corresponding CostedEvent Partition of the Current day.
from (select dbms_xmlgen.getxmltype('
select p.high_value, p.partition_name
from user_tab_partitions p
where p.table_name = ''table_1''
') as xml
from dual) p,
xmltable('/ROWSET/ROW' passing p.xml columns high_value
varchar2(30) path '/ROW/HIGH_VALUE',
partition_name varchar2(30) path '/ROW/PARTITION_NAME') x
where x.high_value =
to_char(to_date(substr(P_SEQ, 1, 6), 'YYMMDD') + 1,
'YYMMDD') || '000';

Can you please help us to write this query in a better way to reduce the time taken??

and we said...

A little plsql helps here

SQL> create or replace
  2  function par_name(p_table_name varchar2, p_seq date) return varchar2 is
  3    l_high_value varchar2(2000);
  4    l_date       date;
  5  begin
  6    for i in (
  7      select partition_name, high_value
  8      from   user_tab_partitions
  9      where  table_name = p_table_name
 10      order by partition_position )
 11    loop
 12      l_high_value := i.high_value;
 13      execute immediate 'select '||l_high_value||' from dual' into  l_date;
 14      if l_date = p_seq then
 15        return i.partition_name;
 16      end if;
 17    end loop;
 19    return null;
 20  end;
 21  /

Function created.

SQL> select par_name('PARENT',date '2015-07-01') from dual;


1 row selected.

SQL> set timing on
SQL> declare
  2    x varchar2(30);
  3  begin
  4  for i in 1 .. 1000 loop
  5    x := par_name('PARENT',date '2015-07-01');
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.19

If you are normally getting partitions near to the "end" of the list of partitions, you could change the query to be "order by partition_position desc" for a bit more speed.

and you rated our response

  (1 rating)

Is this answer out of date? If it is, please let us know via a Review


June 29, 2016 - 6:09 am UTC

Reviewer: A reader

Thank you very much for the suggestion. We just removed the below
execute statement
and we compared directly l_high_value with p_seq that reduced further time.

l_high_value := i.high_value;
13 execute immediate 'select '||l_high_value||' from dual' into l_date;
14 if l_date = p_seq then
Connor McDonald


June 29, 2016 - 7:00 am UTC

That is risky, because you dont control the format of "high_value" in the dictionary. All you know is that it is an expression that evaluates to a date (or some other value depending on the range).