JSON Dokumente indizieren
Das JSON-Format hat sich mittlerweile als Quasi-Standard für strukturierte Daten etabliert und kann damit als legitimer Nachfolger von XML gelten. Das Format ist schlank mit wenig Overhead und sehr flexibel einsetzbar. Die meisten Web Services nutzen das Format zum Datenaustausch, genauso wie Web-Applikationen und vieles mehr. Auch die Oracle-Datenbank unterstützt die Speicherung und Verarbeitung von JSON-Dokumenten nativ. Allerdings muss man bei der Verarbeitung der JSON-Daten einiges beachten, da die Daten nicht in relationalen Tabellen sondern schlicht in Textspalten abgelegt werden. Demnach nehmen wir folgendes Beispiel, den Namen und das Einstellungsdatum als JSON-Objekt in einer eigenen Tabelle.
MARCO @ ORCL19:PDB1:>create table json_docs (json clob);
Table JSON_DOCS created.
HR @ ORCL19:PDB1:>insert into json_docs(json)
2* select json_object(first_name, last_name, hire_date) from employees;
107 rows inserted.
HR @ ORCL19:PDB1:>commit;
Commit complete.
Die Tabelle beinhaltet nun also 107 JSON-Dokumente mit den genannten Daten. Die Funktion JSON_OBJECT generiert aus beliebigen Eingabewerten gültige JSON-Dokumente, die dann in die Tabelle eingefügt wurden. Der Inhalt der Tabelle bzw. der JSON-Dokumente sieht dann so aus:
HR @ ORCL19:PDB1:>select * from json_docs where rownum <11;
JSON
___________________________________________________________________________________
{"first_name":"Ellen","last_name":"Abel","hire_date":"2004-05-11T00:00:00"}
{"first_name":"Alyssa","last_name":"Hutton","hire_date":"2005-03-19T00:00:00"}
{"first_name":"Jonathon","last_name":"Taylor","hire_date":"2006-03-24T00:00:00"}
{"first_name":"Jack","last_name":"Livingston","hire_date":"2006-04-23T00:00:00"}
{"first_name":"Kimberely","last_name":"Grant","hire_date":"2007-05-24T00:00:00"}
{"first_name":"Charles","last_name":"Johnson","hire_date":"2008-01-04T00:00:00"}
{"first_name":"Winston","last_name":"Taylor","hire_date":"2006-01-24T00:00:00"}
{"first_name":"Jean","last_name":"Fleaur","hire_date":"2006-02-23T00:00:00"}
{"first_name":"Martha","last_name":"Sullivan","hire_date":"2007-06-21T00:00:00"}
{"first_name":"Girard","last_name":"Geoni","hire_date":"2008-02-03T00:00:00"}
10 rows selected.
Mit der Funktion JSON_VALUE kann man einzelne Werte mit einer Art Pfad-Syntax aus einem JSON-Dokument extrahieren. Damit kann z.B. Abfragen nach bestimmten Mitarbeitern ausführen:
HR @ ORCL19:PDB1:>select * from json_docs where json_value(json, '$.last_name')='King';
JSON
________________________________________________________________________________
{"first_name":"Steven","last_name":"King","hire_date":"2003-06-17T00:00:00"}
{"first_name":"Janette","last_name":"King","hire_date":"2004-01-30T00:00:00"}
Das hat erst mal wunderbar funktioniert, die beiden passenden Dokumente wurden ermittelt. Allerdings sieht der Ausführungsplan noch nicht so optimal aus:
HR @ ORCL19:PDB1:>select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
__________________________________________________________________________________
SQL_ID 1t64f57kumdd6, child number 0
-------------------------------------
select * from json_docs where json_value(json, '$.last_name')='King'
Plan hash value: 743818738
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
|* 1 | TABLE ACCESS FULL| JSON_DOCS | 2 | 4004 | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(JSON_VALUE("JSON" FORMAT JSON , '$.last_name' RETURNING
VARCHAR2(4000) NULL ON ERROR)='King')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Zur Auswertung der WHERE-Bedingung muss die ganze Tabelle gelesen und gefiltert werden. Bei 107 Datensätzen mag das in Ordnung sein, wird aber mit steigender Datenmenge zum Problem werden. Also muss ein Index her. Das geht ganz einfach mit einem Function Based Index, der auf das entsprechende Element gelegt wird.
HR @ ORCL19:PDB1:>create index json_docs_lastname_ix on json_docs( json_value(json, '$.last_name') );
Index JSON_DOCS_LASTNAME_IX created.
Die Abfrage kann nun den frisch angelegten Index verwenden.
HR @ ORCL19:PDB1:>select * from json_docs where json_value(json, '$.last_name')='King';
JSON
________________________________________________________________________________
{"first_name":"Steven","last_name":"King","hire_date":"2003-06-17T00:00:00"}
{"first_name":"Janette","last_name":"King","hire_date":"2004-01-30T00:00:00"}
HR @ ORCL19:PDB1:>select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
________________________________________________________________________________________________________________
SQL_ID 1t64f57kumdd6, child number 0
-------------------------------------
select * from json_docs where json_value(json, '$.last_name')='King'
Plan hash value: 254356931
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| JSON_DOCS | 2 | 8008 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | JSON_DOCS_LASTNAME_IX | 2 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JSON_DOCS"."SYS_NC00002$"='King')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Das war zu erwarten. Aber was passiert, wenn nicht nach dem Nachnamen, sondern dem Vornamen gesucht werden soll?
HR @ ORCL19:PDB1:>select * from json_docs where json_value(json, '$.first_name')='Steven';
JSON
_________________________________________________________________________________
{"first_name":"Steven","last_name":"King","hire_date":"2003-06-17T00:00:00"}
{"first_name":"Steven","last_name":"Markle","hire_date":"2008-03-08T00:00:00"}
HR @ ORCL19:PDB1:>select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
__________________________________________________________________________________
SQL_ID 9958m6f75m9nt, child number 0
-------------------------------------
select * from json_docs where json_value(json, '$.first_name')='Steven'
Plan hash value: 743818738
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| JSON_DOCS | 2 | 4004 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(JSON_VALUE("JSON" FORMAT JSON , '$.first_name' RETURNING
VARCHAR2(4000) NULL ON ERROR)='Steven')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Da wird natürlich wieder die ganze Tabelle gelesen, geht auch gar nicht anders. Weil man nun aber nicht alle möglichen Elemente in den JSON-Dokumenten indizieren will, hat sich Oracle den Search Index überlegt, der das gesamte Dokument indiziert.
HR @ ORCL19:PDB1:>create search index json_docs_idx on json_docs(json) for json;
Error starting at line : 1 in command -
create search index json_docs_idx on json_docs(json) for json
Error report -
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10720: syntax only supported with constraint: IS JSON
ORA-06512: at "CTXSYS.DRUE", line 186
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 320
29855. 00000 - "error occurred in the execution of ODCIINDEXCREATE routine"
*Cause: Failed to successfully execute the ODCIIndexCreate routine.
*Action: Check to see if the routine has been coded correctly.
Natürlich muss das erst einmal schief gehen, wäre ja sonst auch zu einfach. Man sieht aber direkt zwei Dinge aus der Fehlermeldung. Zum ersten, dass Oracle im Hintergrund die Text-Option verwendet, um einen Search Index zu erstellen. Und zum zweiten, dass es einen Constraint gibt, mit dem man den Inhalt einer Spalte auf gültiges JSON hin überprüfen kann. Also her mit diesem Constraint.
HR @ ORCL19:PDB1:>alter table json_docs add constraint json_docs_json_chk check (json is json);
Table JSON_DOCS altered.
Anschließend muss man den halb angelegten Index löschen, übrigens ohne das Keyword "SEARCH", und kann ihn dann nochmal richtig erstellen.
HR @ ORCL19:PDB1:>drop index json_docs_idx;
Index JSON_DOCS_IDX dropped.
HR @ ORCL19:PDB1:>create search index json_docs_idx on json_docs(json) for json;
Index JSON_DOCS_IDX created.
Nun profitiert auch die Suche nach den Vornamen von diesem Index.
select * from json_docs where json_value(json, '$.first_name')='Steven'
Plan hash value: 368198394
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| JSON_DOCS | 1 | 2014 | 4 (0)| 00:00:01 |
|* 2 | DOMAIN INDEX | JSON_DOCS_IDX | | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(JSON_VALUE("JSON" FORMAT JSON , '$.first_name' RETURNING
VARCHAR2(4000) NULL ON ERROR)='Steven')
2 - access("CTXSYS"."CONTAINS"("JSON_DOCS"."JSON",'({Steven} INPATH
(/first_name))')>0)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Man sieht aus dem Ausführungsplan, dass nun der Search Index zur Filterung der Daten verwendet wird.
Der große Vorteil eines Search Index ist, dass er eben das komplette JSON-Dokument indiziert und daher für alle möglichen Bedingungen herhalten kann. So kann z.B. mittels JSON_TEXTCONTAINS auch eine Volltextsuche durchgeführt werden.
HR @ ORCL19:PDB1:>select json from json_docs where json_textcontains(json, '$.*', 'King');
JSON
________________________________________________________________________________
{"first_name":"Steven","last_name":"King","hire_date":"2003-06-17T00:00:00"}
{"first_name":"Janette","last_name":"King","hire_date":"2004-01-30T00:00:00"}
HR @ ORCL19:PDB1:>select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
________________________________________________________________________________________________
SQL_ID ax9gr05gj0x9m, child number 0
-------------------------------------
select json from json_docs where json_textcontains(json, '$.*', 'King')
Plan hash value: 368198394
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| JSON_DOCS | 1 | 2014 | 4 (0)| 00:00:01 |
|* 2 | DOMAIN INDEX | JSON_DOCS_IDX | | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CTXSYS"."CONTAINS"("JSON_DOCS"."JSON",'(King)')>0)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
23 rows selected.
Allerdings setzt diese Funktion zwingend einen Search Index voraus, ohne einen solchen Index kann die Funktion nicht verwendet werden.
HR @ ORCL19:PDB1:>drop index json_docs_idx;
Index JSON_DOCS_IDX dropped.
HR @ ORCL19:PDB1:>select json from json_docs where json_textcontains(json, '$.*', 'King');
Error starting at line : 1 in command -
select json from json_docs where json_textcontains(json, '$.*', 'King')
Error at Command Line : 1 Column : 18
Error report -
SQL Error: ORA-40467: JSON_TEXTCONTAINS() cannot be evaluated without a JSON-enabled context index
40467. 00000 - "JSON_TEXTCONTAINS() cannot be evaluated without a JSON-enabled context index"
*Cause: There was no JavaScript Object Notation (JSON)-enabled context
index available.
*Action: Create a JSON-enabled context index.
Der JSON Developer Guide hat viele weitere Erklärungen zu JSON in der Oracle-Datenbank parat.
In Oracle 23ai ist das CREATE SEARCH INDEX erweitert worden und erlaubt dann auch das Erstellen von Full Text Indizes für einfachen Text und für XML-Daten, die Details dazu finden sich in der Dokumentation zu Oracle Text.
Kommentare
Keine Kommentare