Oracle 23c: Table Value Constructor

Nehmen wir an, wir brauchen eine Möglichkeit, Bytes in etwas für Menschen lesbares umzuwandeln, z. B. kB, MB usw. Zu diesem Zweck benötigen wir eine konstante Tabelle, möchten aber keine persistente erstellen.

Vor Oracle 23c hatten Sie die folgenden Optionen, wenn es darum ging, Daten „on the fly“ zu erstellen oder eine sofortige Tabelle in Oracle SQL zu benötigen. Erstellen Sie diese Daten mithilfe von SELECTS, verkettet durch UNION ALLs:

WITH unit_table AS (
   SELECT 1 AS id, 'B' AS unit, 0 AS lo, 899 AS hi FROM dual
   UNION ALL
   SELECT 2, 'kB', 900, 9e5 - 1 FROM dual
   UNION ALL
   SELECT 3, 'MB', 9e5, 9e8 - 1 FROM dual
   UNION ALL
   SELECT 4, 'GB', 9e8, 9e100 FROM dual
   )
SELECT f.file_id, ROUND (f.bytes / POWER (1024, u.id - 1), 2) AS f_size, u.unit
   FROM dba_data_files f
   JOIN unit_table u
     ON f.bytes BETWEEN u.lo AND u.hi
  ORDER BY f.file_id;

Es reicht aus, aber die WITH-Klausel sieht ungeschickt aus. In Oracle gibt es noch einen anderen Weg. Die sehr unterschätzte CONNECT BY-Klausel kann nicht nur für hierarchische Abfragen, sondern – da es sich lediglich um eine Schleife handelt – auch für Iterationen verwendet werden:

WITH unit_table AS (
   SELECT ROWNUM AS id,
          REGEXP_SUBSTR ('B kB MB GB', '\S+', 1, ROWNUM) AS unit,
          TRUNC (9*POWER (10, 3*(ROWNUM - 1) - 1)) AS lo,
          CASE ROWNUM WHEN 1 THEN 900 WHEN 2 THEN 9e5 WHEN 3 THEN 9e8 ELSE 9e100 END AS hi 
      FROM dual
      CONNECT BY rownum <= 4
   )
SELECT f.file_id, ROUND (f.bytes / POWER (1024, u.id - 1), 2) AS f_size, u.unit
   FROM dba_data_files f
   JOIN unit_table u
     ON f.bytes BETWEEN u.lo AND u.hi
  ORDER BY f.file_id;

Noch schwieriger zu lesen, aber Extrapunkte für Einfallsreichtum!

Mit Oracle 23c steht endlich eine bessere Methode zur Verfügung: Der Tabellenwertkonstruktor.

Der Table Value Constructor!

WITH unit_table AS (
   SELECT * FROM (VALUES (1,  'B',   0, 899),
                         (2, 'kB', 900, 9e5 - 1),
                         (3, 'MB', 9e5, 9e8 - 1),
                         (4, 'GB', 9e8, 9e100)
                 ) t1 (id, unit, lo, hi)
   )
SELECT f.file_id, ROUND (f.bytes / POWER (1024, u.id - 1), 2) AS f_size, u.unit
   FROM dba_data_files f
   JOIN unit_table u
     ON f.bytes BETWEEN u.lo AND u.hi
  ORDER BY f.file_id;

Dies ist schließlich eine gültige Lösung und lesbar. Die Gesamtsyntax lautet

 (VALUES (expression_list) [, (expression_list) ...]) name (column_list)

dabei ist expression_list eine durch Kommas getrennte Liste von Ausdrücken und Column_list eine durch Kommas getrennte Liste von Spaltennamen. Die Anzahl der Ausdrücke und Spalten muss übereinstimmen, ebenso die Datentypen der Ausdrücke.

Da es sich um eine Klausel für Unterabfragen handelt, kann der Wertkonstruktor auch in der Anweisung „create table“ verwendet werden:

CREATE TABLE my_table AS
   SELECT * FROM (
      VALUES (1, sysdate, 'nix'),
             (2, sysdate-1, 'hihi'),
             (3, NULL, 'also nix')
        ) t1 (id, datum, tx);

Das sieht praktisch aus, hat aber seine Eigenheiten. Sie können vor dem „AS“ eine Spaltenliste hinzufügen, müssen aber am Ende des Wertkonstruktors noch eine zusätzliche angeben. Sind beide vorhanden, gewinnt der Erste.

Außerdem: Sie können die Datentypen für die neue Tabelle nicht definieren, sie werden vom Wertekonstruktor abgeleitet. Das ist nicht so schlimm, aber leider wird auch die Präzision durch diese Klausel definiert, sodass wir am Ende Folgendes erhalten:

DESC my_table
 Name    Null?    Type
 ------- -------- -----------
 ID               NUMBER
 DATUM            DATE
 TX               VARCHAR2(8)

Die Genauigkeit der Spalte TX ergibt sich aus der maximalen Länge ihrer Daten. Wenn Sie dieser Tabelle zusätzliche Daten hinzufügen möchten, müssen Sie sich entweder an diese maximale Länge halten oder sie ändern – was natürlich eine einfache Sache ist:

ALTER TABLE my_table MODIFY (tx VARCHAR2(100));

Sie können den Wertkonstruktor auch in INSERTs verwenden:

INSERT INTO my_table 
  VALUES (4, NULL,'another set of data'),
         (5, TO_DATE('19700101','yyyymmdd'), 'The Epoch');

2 rows created.

Especially if you can insert several rows – eg all lines items of an order – it even will be much faster, since it is only one statement which has to be parsed and executed. This means you get bulk operation performance with a simple to write INSERT!

Der Vorteil daran ist, dass man in einem INSERT mehrere Datensätze eines Objekts – beispielsweise alle Positionen einer Bestellung – auf einmal einfügen kann. Damit erreicht man Bulk Operation Performance mit einem einfach zu schreibendem Statement.

Oder in UPDATEs:

UPDATE employees e
   SET e.salary = e.salary + u.sal_inc
   FROM (VALUES (30, 100),
                (50, -100))
              u (dep, sal_inc)
   WHERE e.department_id = u.dep;

Hier nutzen wir auch das 23c neue Feature „FROM-Klausel in UPDATEs“.

Fazit: Oracle 23c erleichtert Entwicklern das Leben, wenn es darum geht, Daten spontan zu erstellen.