HIGH_VALUE-Angaben für Partitionen

Dass Oracle regelmäßig darauf hinweist, dass man vom Datentyp LONG Abstand nehmen sollte, ist bekannt. Und bekannt ist auch, dass im data dictionary auch in aktuellen Releases (hier 11.1.0.7) immer noch eine ganze Reihe von LONG-Spalten zu finden ist:

select owner
     , count(*) cnt
  from dba_tab_columns
 where data_type = 'LONG'
 group by owner
 order by count(*) desc

OWNER                                 CNT
------------------------------ ----------
SYS                                   229
SYSTEM                                 12

Ich hatte an dieser Stelle gelegentlich schon mal auf Adrian Billingtons umfassende Erläuterung der Möglichkeiten, mit LONGs fertig zu werden, hingewiesen - und darin findet man neben diversen PL/SQL-Optionen auch eine reine SQL-Variante zum Auslesen der LONGs, die mit dem DBMS_XMLGEN-Package operiert. Als ich dieser Tage ein Script basteln wollte, mit dem ich prüfen kann, ob es in einer Datenbank partitionierte Tabellen gibt, die Bewegungsdaten bereits in eine MAXVALUE-Partition schreiben, habe ich damit experimentiert:

-- Anlage einer partitionierten Tabelle
-- im gegebenen Fall ist die Tabelle interval partitioned,
-- was die Frage nach der MAXVALUE-Partition natürlich hinfällig macht,
-- aber das ist für den Test unerheblich
create table test_partition_high_value
  partition by range (id)
  interval (1)
  (partition test_p1 values less than (1))
as
select rownum id
  from dual
connect by level <= 3;

select table_name
     , partition_name
     , high_value
  from user_tab_partitions
 where table_name = 'TEST_PARTITION_HIGH_VALUE';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------
TEST_PARTITION_HIGH_VALUE      TEST_P1                        1
TEST_PARTITION_HIGH_VALUE      SYS_P1161                      2
TEST_PARTITION_HIGH_VALUE      SYS_P1162                      3
TEST_PARTITION_HIGH_VALUE      SYS_P1163                      4

Wenn ich jetzt nach dem maximalen HIGH_VALUE suchen will, treffe ich auf die Beschränkungen des LONG-Typs:

select table_name
     , partition_name
     , max(high_value) max_high_value
  from user_tab_partitions
 where table_name = 'TEST_PARTITION_HIGH_VALUE'
 group by table_name
        , partition_name
, max(high_value) max_high_value
      *

FEHLER in Zeile 3:
ORA-00997: Unzulässige Verwendung des Datentyps

Und das ist dann der Moment, wo das DBMS_XMLGEN-Package interessant wird:

select xmltype(DBMS_XMLGEN.GETXML('select table_name, high_value
                                     from user_tab_partitions
                                    where table_name = ''TEST_PARTITION_HIGH_VALUE'' ')) AS xml
  from dual

XML
------------------------------------------------------
<?xml version="1.0"?>
<rowset>
  <row>
    <table_name>TEST_PARTITION_HIGH_VALUE</TABLE_NAME>
    <high_value>1</HIGH_VALUE>
  </ROW>
  <row>
    <table_name>TEST_PARTITION_HIGH_VALUE</TABLE_NAME>
    <high_value>2</HIGH_VALUE>
  </ROW>
  <row>
    <table_name>TEST_PARTITION_HIGH_VALUE</TABLE_NAME>
    <high_value>3</HIGH_VALUE>
  </ROW>
  <row>
    <table_name>TEST_PARTITION_HIGH_VALUE</TABLE_NAME>
    <high_value>4</HIGH_VALUE>
  </ROW>
</ROWSET>

Die GETXML-Funktion liefert dabei - wenig überraschend - eine XML-Repräsentation der Ergebnisse einer übergebenen Abfrage. Mit Hilfe weiterer Funktionen, die ebenfalls bei Adrian Billington erläutert werden, kann man dieses XML dann wieder parsen:

with
xml
as
(
select xmltype(DBMS_XMLGEN.GETXML('select table_name, high_value
                                     from user_tab_partitions
                                    where table_name = ''TEST_PARTITION_HIGH_VALUE'' ')) AS xml
  from dual)
,
high_values
as
(
select extractValue(test.object_value, '/ROW/TABLE_NAME') table_name
     , extractValue(test.object_value, '/ROW/HIGH_VALUE') high_value
  from xml x
     , table(xmlsequence(extract(x.xml, '/ROWSET/ROW'))) test
)
select * from high_values

TABLE_NAME                     HIGH_VALUE
------------------------------ ----------
TEST_PARTITION_HIGH_VALUE      1
TEST_PARTITION_HIGH_VALUE      2
TEST_PARTITION_HIGH_VALUE      3
TEST_PARTITION_HIGH_VALUE      4

Und diese Ergebnisse kann man dann gruppieren:

with
xml
as
(
select xmltype(DBMS_XMLGEN.GETXML('select table_name, high_value
                                     from user_tab_partitions
                                    where table_name = ''TEST_PARTITION_HIGH_VALUE'' ')) AS xml
  from dual)
,
high_values
as
(
select extractValue(test.object_value, '/ROW/TABLE_NAME') table_name
     , extractValue(test.object_value, '/ROW/HIGH_VALUE') high_value
  from xml x
     , table(xmlsequence(extract(x.xml, '/ROWSET/ROW'))) test
)
,
max_values
as
(select table_name
      , max(high_value) high_value
   from high_values
  group by table_name
)
select * from max_values

TABLE_NAME                     HIGH_VALUE
------------------------------ ----------
TEST_PARTITION_HIGH_VALUE      4

Und damit wäre ich wieder einmal um PL/SQL herum gekommen ... - besten Dank an Adrian Billington, dessen Webseite sehr viele relativ komplexe Themen mit sehr handlichen Beispielen erläutert.