11. August 2015

Arrays in Tabellen: Child-Tabelle, VARRAY, XML oder JSON ...?

In diesem Blog-Posting geht es um die Ablage von Arrays in Tabellen. Normalerweise würde man solche Daten in einer relationalen Datenbank in einer Child-Tabelle (mit entsprechendem Foreign Key) abspeichern. Wie jede andere Tabelle kann diese dann indiziert und beliebig mit SQL abgefragt werden. Manchmal geht es aber eher darum, das Array "als Ganzes" zu speichern und auszulesen; in solchen Fällen können andere Storage-Varianten die besseren Alternativen sein. Und in der Oracle-Datenbank gibt es eine ganze Reihe von Möglichkeiten ...
  • Ablage des Arrays als VARRAY Data Type. VARRAYs gibt es seit Oracle8.
  • Ablage des Arrays als XML - das hier verwendete Binary XML steht ab Oracle11g zur Verfügung.
  • Neu in Oracle 12.1.0.2: Ablage des Arrays als JSON.
Schauen wir uns die Varianten an. Wir erzeugen eine Tabelle mit drei Spalten. In der ersten speichern wir das Array als VARRAY-Data Type, in der zweiten als XML und in der dritten als JSON. Zusätzlich bauen wir (der Vollständigkeit halber) noch eine eigene Child-Tabelle, um das "klassische" relationale Modell ebenfalls zu berücksichtigen. Der Code sieht so aus.
-- VARRAY Data Type erstellen 

create type t_array is varray(1048576) of number
/


-- Tabelle erstellen

create table tab_arraytest (
  id         number(10) primary key,
  arr_varray t_array,
  arr_xml    xmltype,
  arr_json   clob
)
xmltype column arr_xml store as binary xml
/

-- Child-Tabelle erstellen für relationalen Ansatz

create table tab_array (
  parent_id   number(10) not null,
  arr_idx     number(10) not null,
  arr_val     number(10) not null
)
/
Dann geht es daran, die Arrays zu füllen. Das geschieht hier mit etwas PL/SQL-Code: Jedes Array wird mit 100.000 Werten (von 2 bis 200.000) gefüllt.
declare
  l_xmlc clob;
  l_json clob;
  l_array t_array;

  procedure write(lob in out nocopy clob, string varchar2) is 
  begin
    dbms_lob.writeappend(lob, length(string), string);
  end;
begin
  -- Relationale Tabelle füllen
  insert into tab_array (select 1, level, level * 2 from dual connect by level <= 100000);

  -- VARRAY konstruieren
  select cast(collect(level * 2) as t_array) into l_array from dual connect by level <= 100000;
  -- JSON und XML konstruieren

  dbms_lob.createtemporary(l_xmlc, true, dbms_lob.call);
  dbms_lob.createtemporary(l_json, true, dbms_lob.call);
  write(l_xmlc, '<array>');
  write(l_json, '[');
  for i in 1..100000 loop
    write (l_xmlc, '<v>'|| i * 2 ||'</v>');
    write (l_json, i * 2 ||',');
  end loop;
  write(l_xmlc, '</array>');
  write(l_json, ']');
  insert into tab_arraytest values (1, l_array, xmltype(l_xmlc), l_json);

  dbms_lob.freetemporary(l_xmlc);
  dbms_lob.freetemporary(l_json);
end;
/
sho err
Die Inhalte der Tabellen sehen nun in SQL*Plus wie folgt aus - alle drei Spalten enthalten die gleiche Information - nur unterschiedlich strukturiert.
SQL> select arr_xml from tab_arraytest;

ARR_XML
--------------------------------------------------------------------------------
<array>
  <v>2</v>
  <v>4</v>
  <v>6</v>
  <v>8</v>
  <v>10</v>
  <v>12</v>

1 Zeile wurde ausgewählt.

SQL> select arr_json from tab_arraytest;

ARR_JSON
--------------------------------------------------------------------------------
[2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38,40,42,44,46,48,50,52,54,56

1 Zeile wurde ausgewählt.

SQL> select arr_varray from tab_arraytest;

ARR_VARRAY
--------------------------------------------------------------------------------
T_ARRAY(2, 4, 6, 8, 10, 12, 14, 16, 18, 20, 22, 24, 26, 28, 30, 32, 34, 36, 38,
40, 42, 44, 46, 48, 50, 52, 54, 56, 58, 60, 62, 64, 66, 68, 70, 72, 74, 76, 78,
80, 82, 84, 86, 88, 90, 92, 94, 96, 98, 100, 102, 104, 106, 108, 110, 112, 114,
Zuerst schauen wir nach, wieviel Platz von den verschiedenen Varianten verbraucht wurde. Da sowohl die XML-, die JSON- als auch die VARRAY-Variante die Daten als LOB (also in einem eigenen Segment) speichern, nehme ich die vor langer Zeit in diesem Blog vorgestellte Funktion GET_SPACE_INFO her, um festzustellen, wie groß die einzelnen Segmente sind.
SQL> select column_name, segment_name, alloc_bytes - free_bytes as bytes from table(get_space_info('TAB_ARRAYTEST'));

COLUMN_NAME     SEGMENT_NAME                          BYTES
--------------- -------------------------------- ----------
                TAB_ARRAYTEST                         65536
ARR_VARRAY      SYS_LOB0000137862C00002$$            507904
ARR_JSON        SYS_LOB0000137862C00005$$           1310720
SYS_NC00004$    SYS_LOB0000137862C00004$$            655360
                SYS_IL0000137862C00002$$              32768
                SYS_IL0000137862C00004$$              32768
                SYS_IL0000137862C00005$$              32768
ID              SYS_C0013365                          32768

8 Zeilen ausgewählt.

SQL> select column_name, segment_name, alloc_bytes - free_bytes as bytes from table(get_space_info('TAB_ARRAY'));

COLUMN_NAME     SEGMENT_NAME                          BYTES
--------------- -------------------------------- ----------
                TAB_ARRAY                           3145728
Man sieht, dass die eigene Tabelle den meisten Platz verbraucht; es werden etwa 3MB allokiert. Diese Variante bietet, wie schon oben erläutert, den besten Einzelsatzzugriff und typischerweise auch die beste Performance für SQL-Funktionen. Am besten schneidet beim Platzverbrauch der VARRAY-Datentyp ab - mit nur etwa 500k ist dieser sehr platzsparend. Die XML-Variante ist ebenfalls sehr effizient (die XML-Storagevariante "Binary XML" führt eine interne Komprimierung durch); JSON verbraucht schon mehr Plattenplatz, aber immer noch weniger als die separate Tabelle.
Schauen wir uns nun die Performance an - wir wollen die Summe aller Array-Elemente ausrechnen. Alle Datenbankblöcke befinden sich im Hauptspeicher - I/O-Effekte sind eliminiert.
SQL> select sum(arr_val) from tab_array;

SUM(ARR_VAL)
------------
  1,0000E+10

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:00.03

SQL> select sum(column_value) from tab_arraytest, table(arr_varray);

SUM(COLUMN_VALUE)
-----------------
       1,0000E+10

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:01.00

SQL> select sum(val) from tab_arraytest, xmltable('//v' passing arr_xml columns val number path 'text()');

  SUM(VAL)
----------
1,0000E+10

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:00.35

SQL> select sum(val) from tab_arraytest, json_table(arr_json, '$[*]' columns (val number path '$'));

  SUM(VAL)
----------
1,0000E+10

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:00.11
Zusammengefasst ergibt sich:

SpeicherformPlatzbedarfZeit für Summe
Array als eigene Tabelle speichern31457280.03
Array als VARRAY speichern5079041.00
Array als XML speichern6553600.35
Array als JSON speichern13107200.11

Besonders beeindruckt hat mich die JSON-Performance. Letztlich wird das Array ja als einfacher Text abgelegt - und das Parsing mitsamt der Summenberechnung läuft in unglaublichen 0,11 Sekunden ab. Nimmt man noch dazu, dass aus Sicht der Datenbank und der Client-Applikation ein einfacher CLOB vorliegt, der von nahezu allen Client-Umgebungen problemlos verarbeitet werden kann, so wird die JSON-Variante schon sehr interessant. Im Gegensatz dazu erfordern VARRAYs das Erstellen und Nutzen eines eigenen, neuen Datentyps - für die eine oder andere Client-Anwendung kann sowas ein Problem sein. Dass die normale Child-Tabelle am besten performt, dürfte klar gewesen sein.
Schauen wir uns den Einzelsatzzugriff an - wir wollen das 27.653-Element jedes Arrays auslesen. Beim VARRAY-Type müssen wir uns ein wenig PL/SQL schreiben, da die Oracle-Datenbank im SQL-Umfeld eine solche Funktion nicht anbietet. Für den JSON-Ansatz ist wichtig, dass das erste Element hier den Index "0" hat, alle anderen beginnen mit der "1".
SQL> with
  2    function get_array_element(p_array in t_array, p_idx in number) return number is
  3    begin
  4      return p_array(p_idx);
  5    end;
  6  select get_array_element(arr_varray, 27653) varrayval from tab_arraytest;

 VARRAYVAL
----------
     55306

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:00.06

SQL> select arr_val relationalval from tab_array where arr_idx = 27653;

RELATIONALVAL
-------------
        55306

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:00.03

SQL> select val xmlval from tab_arraytest, xmltable('/array/v[27653]' passing arr_xml columns val number path 'text()');

    XMLVAL
----------
     55306

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:00.02

SQL> select val jsonval from tab_arraytest, json_table(arr_json, '$[27652]' columns (val number path '$'));

   JSONVAL
----------
     55306

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:00.05

SpeicherformPlatzbedarfZeit für SummeZeit Einzelzugriff
Array als eigene Tabelle speichern31457280.030.06
Array als VARRAY speichern5079041.000.03
Array als XML speichern6553600.350.02
Array als JSON speichern13107200.110.05

Auch hier zeigt sich JSON als überraschend schnell. Weitere Tests mit anderen Queries überlasse ich Euch; aber ich denke, dass man schon damit einige Ergebnisse formulieren kann.
  • Wenn Arrays "als Ganzes" abgelegt werden sollen, der Zugriff auf das Gesamtarray im Vordergrund steht und Indizes keine Rolle spielen, so gibt es (platzsparende) Alternativen zur klassischen Child-Tabelle.
  • Die seit Oracle8 vorhandenen VARRAYS sind dabei aber nicht die erste Wahl. Zwar bieten sie die kompakteste Speicherung und den geringsten Platzbedarf, sie sind wegen des eigenen Datentyps, der erst noch erstellt werden muss, aber eher schwerfällig zu nutzen. Auch die Performance ist nicht die beste; die in der Datenbank vorhandenen XML und JSON-Parser können hier sehr gut mithalten oder sind sogar besser.
  • Vorteile haben VARRAYs, wenn feste Datenstrukturen gefragt sind - das XML oder JSON-Dokument kann alles Mögliche enthalten, solange die jeweilige Syntax intakt ist. Das VARRAY speichert tatsächlich nur den geforderten Datentyp.
  • Vor allem JSON ist interessant. Die Nutzung ist mit bestehenden String-Datentypen extrem einfach und das Binding in der Client-Applikation könnte nicht leichter sein. Der Effizienz des JSON-Parsers in der Datenbank ist erstaunlich: Obwohl er mit einem Text arbeitet und alle Werte parsen muss, ist er schneller als das VARRAY, welches ja schon "weiß", dass NUMBER-Values vorliegen.
Fassen wir zusammen:

SpeicherformKomplexitätZugriffs-PerformanceFlexibilitätIndizierbarkeit
VARRAYHochSchwachGeringNein
Child-TabelleMittelSehr gutGeringBeliebig
XMLGeringGutHochXML-Index
JSONSehr GeringSehr gutHochJSON-Volltextindex

Speziell ab Oracle12c kann (und sollte) man ruhig ernsthaft über JSON als Datentyp für Arrays oder andere Daten, die man nicht im Schema modellieren möchte, nachdenken. Die Oracle-Datenbank kann damit wirklich extrem gut umgehen.
This blog posting will be about storing Arrays in relational tables. In a relational database, the classic approach is to create another table (the "child table"). Both tables are then linked with a foreign key. As the "array values" are stored as ordinary table rows, you can apply the full SQL functionality (single row queries, analytic functions, indexing). But there are also cases, where the focus is on "handling the array as a whole". And for this, we have some interesting alternatives to creating a separate table ...
  • We can store the Array in a column of a VARRAY data type. VARRAYs are available since 8.
  • We can use XML to store the array data. The Binary XML storage, which is being used here, is available in Oracle11g and higher.
  • Beginning with Oracle 12.1.0.2, we can use JSON to store the array.
So we'll create a table with three columns. One for VARRAY storage, one for XML and one for JSON. For the VARRAY, we have to create the VARRAY data type as an object type beforehand. To have the comparison to the classic relational model, we'll create a "child table" as the fourth approach. The CREATE TABLE statements look as follows:
-- create the VARRAY Data Type 

create type t_array is varray(1048576) of number
/


-- create the table

create table tab_arraytest (
  id         number(10) primary key,
  arr_varray t_array,
  arr_xml    xmltype,
  arr_json   clob
)
xmltype column arr_xml store as binary xml
/

-- create the "child table" for the relational approach

create table tab_array (
  parent_id   number(10) not null,
  arr_idx     number(10) not null,
  arr_val     number(10) not null
)
/
Then we need to seed some data - we'll create arrays with 100,000 elements each. The values range from 2 to 200,000, incremented by 2. The following PL/SQL code does the job.
declare
  l_xmlc clob;
  l_json clob;
  l_array t_array;

  procedure write(lob in out nocopy clob, string varchar2) is 
  begin
    dbms_lob.writeappend(lob, length(string), string);
  end;
begin
  -- load the relational table
  insert into tab_array (select 1, level, level * 2 from dual connect by level <= 100000);

  -- construct the VARRAY instance
  select cast(collect(level * 2) as t_array) into l_array from dual connect by level <= 100000;
  -- construct JSON and XML instances

  dbms_lob.createtemporary(l_xmlc, true, dbms_lob.call);
  dbms_lob.createtemporary(l_json, true, dbms_lob.call);
  write(l_xmlc, '<array>');
  write(l_json, '[');
  for i in 1..100000 loop
    write (l_xmlc, '<v>'|| i * 2 ||'</v>');
    write (l_json, i * 2 ||',');
  end loop;
  write(l_xmlc, '</array>');
  write(l_json, ']');
  insert into tab_arraytest values (1, l_array, xmltype(l_xmlc), l_json);

  dbms_lob.freetemporary(l_xmlc);
  dbms_lob.freetemporary(l_json);
end;
/
sho err
In SQL*Plus, the table contents now look as follows. All arrays contain the same information, but using a different storage approach.
SQL> select arr_xml from tab_arraytest;

ARR_XML
--------------------------------------------------------------------------------
<array>
  <v>2</v>
  <v>4</v>
  <v>6</v>
  <v>8</v>
  <v>10</v>
  <v>12</v>

1 Zeile wurde ausgewählt.

SQL> select arr_json from tab_arraytest;

ARR_JSON
--------------------------------------------------------------------------------
[2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38,40,42,44,46,48,50,52,54,56

1 Zeile wurde ausgewählt.

SQL> select arr_varray from tab_arraytest;

ARR_VARRAY
--------------------------------------------------------------------------------
T_ARRAY(2, 4, 6, 8, 10, 12, 14, 16, 18, 20, 22, 24, 26, 28, 30, 32, 34, 36, 38,
40, 42, 44, 46, 48, 50, 52, 54, 56, 58, 60, 62, 64, 66, 68, 70, 72, 74, 76, 78,
80, 82, 84, 86, 88, 90, 92, 94, 96, 98, 100, 102, 104, 106, 108, 110, 112, 114,

SQL> select * from tab_array; 

 PARENT_ID    ARR_IDX    ARR_VAL
---------- ---------- ----------
         1       1827       3654
         1       1828       3656
         1       1829       3658
         1       1830       3660
         1       1831       3662
         1       1835       3670
         :          :          :
First, we'll check the amount of disk space used by each of the alternatives. The VARRAY, JSON and XML variants use LOB segments to store the data. So I used the GET_SPACE_INFO function (which I introduced in a more recent posting) to get that information.
SQL> select column_name, segment_name, alloc_bytes - free_bytes as bytes from table(get_space_info('TAB_ARRAYTEST'));

COLUMN_NAME     SEGMENT_NAME                          BYTES
--------------- -------------------------------- ----------
                TAB_ARRAYTEST                         65536
ARR_VARRAY      SYS_LOB0000137862C00002$$            507904
ARR_JSON        SYS_LOB0000137862C00005$$           1310720
SYS_NC00004$    SYS_LOB0000137862C00004$$            655360
                SYS_IL0000137862C00002$$              32768
                SYS_IL0000137862C00004$$              32768
                SYS_IL0000137862C00005$$              32768
ID              SYS_C0013365                          32768

8 Zeilen ausgewählt.

SQL> select column_name, segment_name, alloc_bytes - free_bytes as bytes from table(get_space_info('TAB_ARRAY'));

COLUMN_NAME     SEGMENT_NAME                          BYTES
--------------- -------------------------------- ----------
                TAB_ARRAY                           3145728
The classic relational table has the largest footprint - this becomes clear, when we remember that the parent ID and the array index position has to be stored separately. We have the best SQL flexibility - but (as we see) consume most disk space. The VARRAY data type requires the least disk space; the array values are being stored in a very compact format. XML as also very efficient, since the used Binary XML storage compresses the XML internally. JSON requires some more disk space, but still less than the separate table.
Then we'll look at SQL performance. We want to compute the sum of all array elements - with a single SQL query. This is possible for all alternatives - I/O effects are eliminated in the following results.
SQL> select sum(arr_val) from tab_array;

SUM(ARR_VAL)
------------
  1,0000E+10

1 row selected.

Elapsed: 00:00:00.03

SQL> select sum(column_value) from tab_arraytest, table(arr_varray);

SUM(COLUMN_VALUE)
-----------------
       1,0000E+10

1 row selected.

Elapsed: 00:00:01.00

SQL> select sum(val) from tab_arraytest, xmltable('//v' passing arr_xml columns val number path 'text()');

  SUM(VAL)
----------
1,0000E+10

1 row selected.

Elapsed: 00:00:00.35

SQL> select sum(val) from tab_arraytest, json_table(arr_json, '$[*]' columns (val number path '$'));

  SUM(VAL)
----------
1,0000E+10

1 row selected.

Elapsed: 00:00:00.11
An intermediate summary ...

Storage formDisk spacesum() performance
separate "child" table31457280.03
VARRAY data type5079041.00
XML storage6553600.35
JSON storage13107200.11

I think, we all expected that the separate "child" table had the best performance, but I was stunned when I first saw the JSON results. The data is actually been stored as a text in a simple CLOB column. So the database did JSON parsing and sum computation for 100,000 values in 0.11 seconds. On the other side, the VARRAY approach, which stores NUMBER values instead of text and does not need any text parsing, needs almost 10 times more time. Beyond this, the VARRAY alternative requires us to create a separate data type which a client application has to "understand" and work with.
The next test will be the performance of single value lookups - we'll have the database retrieve a single element from the array. For the VARRAY approach we don't have such a function at the SQL level, so we'll create one first. Note, that the first element in a JSON array has the index "0" - in all other approaches it's "1".
SQL> with
  2    function get_array_element(p_array in t_array, p_idx in number) return number is
  3    begin
  4      return p_array(p_idx);
  5    end;
  6  select get_array_element(arr_varray, 27653) varrayval from tab_arraytest;

 VARRAYVAL
----------
     55306

1 row selected.

Elapsed: 00:00:00.06

SQL> select arr_val relationalval from tab_array where arr_idx = 27653;

RELATIONALVAL
-------------
        55306

1 row selected.

Elapsed: 00:00:00.03

SQL> select val xmlval from tab_arraytest, xmltable('/array/v[27653]' passing arr_xml columns val number path 'text()');

    XMLVAL
----------
     55306

1 row selected.

Elapsed: 00:00:00.02

SQL> select val jsonval from tab_arraytest, json_table(arr_json, '$[27652]' columns (val number path '$'));

   JSONVAL
----------
     55306

1 row selected.

Elapsed: 00:00:00.05

StorageDisk Spacesum() performancen'th element performance
separate "child" table31457280.030.06
VARRAY data type5079041.000.03
XML storage6553600.350.02
JSON storage13107200.110.05

Once more, JSON is super fast. Further testing with different queries is up to you - for this blog posting let me summarize.
  • When it's about storing arrays and the focus is on handling these "as a whole", we habe some alternatives to the classic relational "child" table.
  • The VARRAY data type is available since Oracle8 and provides the most compact array storage. A great disadvantage is the need to create an own data type. Some client applications cannot deal with custom data types and would have problems with VARRAY columns. The access performance is mediocre - both JSON and XML were faster when working with array data.
  • The strict typing of a VARRAY column can be its strict typing (but can be a disadvantage as well). We can only store numeric values in a NUMBER VARRAY - the XML and JSON arrays might contain text data as well - and this might break some client applications.
  • JSON is a very interesting approach. JSON usage is extremely simple - it's just about storing a text value into the database - all client applications should be able to do this. The JSON parser inside the database is really fast: Although it has to actually parse and interpret a text, the sum computation was still faster than working with the structured VARRAY type.
Here are all results in one overview table:

StorageComplexityAccess PerformanceFlexibilityIndexability
VARRAYHighLowLowNo
Child TableMediumVery goodLowAny Index
XMLLowGoodHighXML Index
JSONVery lowVery goodHighJSON Fulltext Index

If you are on Oracle12c, you might think about using JSON for the flexible parts of your data and also for arrays. You don't need to model your data types beforehand and the parsing performance is really good.

Beliebte Postings