Sonntag, 10. September 2017

12.2.: Mehr Performance für Wildcard Abfragen mit Reverse Token Index

Wildcard Abfragen stellen in allen Textsuchmaschinen gewisse Herausforderungen dar. Unterscheiden kann man dabei im Prinzip folgende Abfragen mit Wildcards
  1. zu Beginn oder von links wie zum Beispiel %frage für Abfrage, Anfrage etc.
  2. in der Mitte wie z.B. bei An%l für Anteil usw.
  3. am Ende oder von rechts wie zum Beispiel An% für Anfrage, Angst, Anteil etc.
Für die führende Wildcard Verwendung (linke Seite) gibt es bei Oracle Text schon seit jeher den sogenannten Substring Index, der zusätzlich zur $I Tabelle eine spezielle $P Hilfstabelle anlegt. In dieser Tabelle werden dann die möglichen Endungen abgelegt. Bei einer Suchabfrage wird dann zuerst auf die $P Tabelle zugegriffen. Ist die $I Tabelle allerdings groß, wächst auch die $P Tabelle an und die Gesamtzeit für die Abfrage könnte damit steigen und somit die Gesamtperformance der Abfrage sinken. Übrigens Informationen zu $P bzw. zu allen DR$ Tabellen findet sich im folgenden Blogspost mit dem Titel Welche DR$-Tabellen gibt es und wozu sind sie gut?.

In 12.2 hat man sich nun dieser Problematik angenommen und eine weitere Struktur, einen neuen Index, mit Namen $V auf die Token Tabelle $I angelegt. Der $X Index kann nämlich bei Abfragen wie %XXX nicht verwendet werden. $V Index hingegen ist ein Function Based Index, der mit der Funktion REVERSE auf die Tokens angelegt wird - also die Tokens in umgekehrter Reihenfolge (REVERSE) indiziert. Damit können nun langandauernde Abfragen mit führenden Wildcards beschleunigt werden. Wie legt man nun diesen neuen Index an und wie kann man die Verwendung monitoren? Wie alle Strukturen für den Präfix und Substring Index wird hierfür die Wordlist vorwendet. Starten wir im ersten Schritt mit einem Präfix und Substring Index wie er vor 12.2 existiert. Unsere Testtabelle mit Namen TEXT_1 besteht aus 261988 Einträgen. Indiziert wird die Spalte ORT, die verschiedene Ortsnamen speichert.
SQL> select count(*) from text_1;

  COUNT(*)
----------
    261988
SQL> select ort from text_1 where rownum<10;

ORT
--------------------------------------------------------------------------------
Madrid
Aachen
Frankfurt
Leipzig
Frankfurt/M
Bonn
Frankfurt
London
Oroville


SQL> begin
  2  ctx_ddl.create_preference('mywordlist', 'BASIC_WORDLIST');
  3  ctx_ddl.set_attribute('mywordlist','PREFIX_INDEX','TRUE');
  4  ctx_ddl.set_attribute('mywordlist','PREFIX_MIN_LENGTH', '1');
  5  ctx_ddl.set_attribute('mywordlist','PREFIX_MAX_LENGTH', '20');
  6  ctx_ddl.set_attribute('mywordlist','SUBSTRING_INDEX', 'YES');
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> CREATE INDEX text_ind ON text_1(ort) 
     INDEXTYPE IS ctxsys.context PARAMETERS ('wordlist mywordlist');

Index created.
Nun überprüfen wir die Strukturen, wir können sehen dass es unter anderem eine Hilfstabelle $P gibt.
SQL>select object_name, object_type from user_objects where object_name like 'DR$%';

OBJECT_NAME          OBJECT_TYPE
-------------------- -----------------------
DR$TEXT_IND$I        TABLE
DR$TEXT_IND$K        TABLE
DR$TEXT_IND$N        TABLE
DR$TEXT_IND$P        TABLE
DR$TEXT_IND$R        TABLE
DR$TEXT_IND$RC       INDEX
DR$TEXT_IND$U        TABLE
DR$TEXT_IND$X        INDEX

8 rows selected.
Um den neuen Index $V anzulegen, müssen wir die Wordlist um das Attribut REVERSE_INDEX erweitern und danach ein Index Rebuild durchführen.
SQL> execute ctx_ddl.set_attribute('mywordlist','REVERSE_INDEX', 'TRUE');

PL/SQL procedure successfully completed.

SQL> alter index text_ind rebuild parameters ('replace wordlist mywordlist');

Index altered.
Nun überprüfen wir erneut die Strukturen wie folgt.
SQL> select object_name, object_type from user_objects where object_name like 'DR$%';

OBJECT_NAME          OBJECT_TYPE
-------------------- -----------------------
DR$TEXT_IND$I        TABLE
DR$TEXT_IND$K        TABLE
DR$TEXT_IND$N        TABLE
DR$TEXT_IND$P        TABLE
DR$TEXT_IND$R        TABLE
DR$TEXT_IND$RC       INDEX
DR$TEXT_IND$U        TABLE
DR$TEXT_IND$V        INDEX
DR$TEXT_IND$X        INDEX

9 rows selected.
Betrachten wir uns auch etwas näher diesen neuen $V Index.
SQL> select index_name, table_name, index_type from user_indexes where index_name like 'DR$%';

INDEX_NAME           TABLE_NAME                INDEX_TYPE
-------------------- ------------------------- -------------------------
DR$TEXT_IND$RC       DR$TEXT_IND$R             NORMAL
DR$TEXT_IND$V        DR$TEXT_IND$I             FUNCTION-BASED NORMAL
DR$TEXT_IND$X        DR$TEXT_IND$I             NORMAL

SQL> select table_name, COLUMN_EXPRESSION  from user_ind_expressions where index_name='DR$TEXT_IND$V';

TABLE_NAME                COLUMN_EXPRESSION
------------------------- ------------------------------
DR$TEXT_IND$I             REVERSE("TOKEN_TEXT")
Wie sieht es nun mit der Verwendung aus? Betrachtet man den Ausführungsplan, wird man keine Änderung erkennen. Die interne Verwendung des $V Index ist nicht über den Ausführungsplan nachzuvollziehen.
SQL> select count(*) from text_1 where contains(ort,'%rg')>0;

  COUNT(*)
----------
     16634

SQL> select * from table(dbms_xplan. display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  cwns7m7vaprfc, child number 0
-------------------------------------
select count(*) from text_1 where contains(ort,'%rg')>0

Plan hash value: 2904455991

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |       |       |  2684 (100)|          |
|   1 |  SORT AGGREGATE  |          |     1 |    19 |            |          |
|*  2 |   DOMAIN INDEX   | TEXT_IND | 14137 |   262K|  2684   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CTXSYS"."CONTAINS"("ORT",'%rg')>0)


19 rows selected.
Auch hier hilft die Nutzung von Trace Events. Neu für $V Zugriffe sind dabei die Events mit Nummer 37, 38 und 39. Eine Beschreibung der Events findet sich hier.
Setzen wir diese Events in unserer Umgebung und führen wieder Abfragen mit %rg aus.
-- Time spent in executing the $V cursor
SQL> execute CTX_OUTPUT.ADD_TRACE(37);
-- Time spent in fetching rows from $V
SQL> execute CTX_OUTPUT.ADD_TRACE(38);
--Number of rows with $V fetched metadata
SQL> execute CTX_OUTPUT.ADD_TRACE(39);

SQL> select * from ctx_trace_values;

    TRC_ID  TRC_VALUE
---------- ----------
        37          0
        38          0
        39          0
Zu Beginn sind die Spalten TRC_VALUE mit 0 initialisiert. Nach der ersten Ausführung sollte sich das nun ändern.
SQL> select count(distinct(ort)) from text_1 where contains(ort,'%rg')>0;

COUNT(DISTINCT(ORT))
--------------------
                1461

SQL> select * from ctx_trace_values;

    TRC_ID  TRC_VALUE
---------- ----------
        37        558
        38       5594
        39        650
Wie man erkennen kann, ist die V$ Indexstruktur verwendet worden: 650 Zeilen sind offensichtlich über die V$ Metadaten abgerufen worden.

Donnerstag, 8. Juni 2017

Suche in JSON Dokumenten in 12.2: wie geht das?

Seit 12c (12.1.0.2) sind auch JSON Zugriffe in der Datenbank möglich. Die Verwendung ist ganz einfach: Man definiert eine Datenbankspalte mit einem beliebigen Datentyp für Textstrings (wie zum Beispiel VARCHAR2 oder CLOB). Mit der Bedingung IS JSON kann zusätzlich der Inhalt validiert werden - auf Wohlgeformtheit oder auf die Art der Syntaxverwendung (STRICT oder LAX). Die Zugriffe erfolgen dann mit Standardmitteln und neu eingeführten SQL/JSON Funktionen. Starten wir mit einem einfachen Beispiel. Wir laden uns die Datei PurchaseOrders.dmp mit JSON Dokumenten von Github und stellen diese via EXTERNAL TABLE Syntax zur Verfügung.
create table json_contents (json_document CLOB)
organization external 
(type oracle_loader default directory json_dir
 access parameters
    (records delimited by 0x'0A'
     fields (json_document CHAR(5000)))
     location ('PurchaseOrders.dmp')) reject limit unlimited;
Im vorher definierten logischen Directory JSON_DIR liegt dabei PurchaseOrders.dmp. Danach legen wir eine relationale Tabelle an, die mithilfe der Bedingung IS JSON die Dokumente auf Gültigkeit validiert. Danach laden wir die Dokumente in die Tabelle JSON_TAB.
create table json_tab 
  (id            number generated as identity, 
   json_document clob constraint ensure_json CHECK (json_document IS JSON));
insert into json_tab (json_document) 
      select json_document from json_contents;
Commit;
Soweit so gut. Wie sieht es jetzt aber mit der Suche aus? In 12.2 ist dazu eine neue Datenstruktur eingeführt worden. Ein einfaches Beispiel demonstriert die Verwendung.
create search index JSON_TAB_GUIDE 
 on JSON_TAB (JSON_DOCUMENT) 
 for json PARAMETERS ('DATAGUIDE ON SYNC (ON COMMIT)')
Was bewirkt diese Syntax? Wichtig für Oracle Text User ist die Information, dass damit eine neue Art von Text Index angelegt wird, der beim Commit synchronisiert wird - der neue JSON Search Index. Zusätzlich wird ein sogenannter Data Guide angelegt. Mit diesem wird die Struktur des JSON Dokuments im Data Dictionary hinterlegt. Diese Strukturen können manuell ausgelesen werden, zur automatischen View Erzeugung beitragen oder automatisch virtuelle Spalten anlegen bzw. löschen. Mehr dazu findet sich übrigens auch im Blockeintrag "JSON in 12.2: JSON Generierung, neues Data Guide Konzept, Objekttypen". Alle Syntaxformen und eine Erklärung dazu finden sich im Text Reference Guide. Aber listen wir einfach einmal die erzeugten Objekte auf. Einige davon kommen Ihnen sicher bekannt vor. Die Tabelle DR$JSON_TAB_GUIDE$I gibt beispielsweise die gespeicherten Tokens aus. Eine Erklärung aller Komponenten würde hier allerdings zu weit führen.
SQL> select object_name, object_type from user_objects where object_name like 'DR$%'

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
DR$JSON_TAB_GUIDE$DG           TABLE
DR$JSON_TAB_GUIDE$DGSQ         SEQUENCE
DR$JSON_TAB_GUIDE$G            TABLE
DR$JSON_TAB_GUIDE$H            INDEX
DR$JSON_TAB_GUIDE$I            TABLE
DR$JSON_TAB_GUIDE$K            TABLE
DR$JSON_TAB_GUIDE$KI           INDEX
DR$JSON_TAB_GUIDE$N            TABLE
DR$JSON_TAB_GUIDE$NI           INDEX
DR$JSON_TAB_GUIDE$R            TABLE
DR$JSON_TAB_GUIDE$RC           INDEX
DR$JSON_TAB_GUIDE$SN           TABLE
DR$JSON_TAB_GUIDE$SNI          INDEX
DR$JSON_TAB_GUIDE$ST           TABLE
DR$JSON_TAB_GUIDE$STI          INDEX
DR$JSON_TAB_GUIDE$U            TABLE
DR$JSON_TAB_GUIDE$UI           INDEX
DR$JSON_TAB_GUIDE$X            INDEX
Alle bekannten CTX Views wie wie CTX_USER_INDEXES, CTX_USER_INDEX_VALUES usw. können nun zur Hilfe genommen werden um den Index näher zu beleuchten. Interessant sind in erster Linie aber die Abfrage Möglichkeiten. Im Unterschied zu den "normalen" Textabfragen wird dazu nicht der Operator CONTAINS verwendet sondern die neue Bedingung JSON_TEXTCONTAINS, die im SQL Reference Guide beschrieben ist. In der Kurzfassung sieht die Syntax folgendermassen aus:
JSON_TEXTCONTAINS( column, JSON_basic_path_expression, string )
Dabei gilt für den Suchstring folgendes:
A character string. The condition searches for the character string in all of the string and numeric property values in the matched JSON object, including array values. The string must exist as a separate word in the property value. For example, if you search for 'beth', then a match will be found for string property value "beth smith", but not for "elizabeth smith". If you search for '10', then a match will be found for numeric property value 10 or string property value "10 main street", but a match will not be found for numeric property value 110 or string property value "102 main street".

Probieren wir eine erste Abfrage und überprüfen wir das Ganze mit dem Ausführungsplan.
SQL> select distinct(json_value(json_document, '$.CostCenter'))from json_tab
where json_textcontains(json_document, '$.CostCenter', 'A40');  2

(JSON_VALUE(JSON_DOCUMENT,'$.COSTCENTER'))
----------------------------------------------------------------------------------------------------
A40

SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  8su4ht9v5ks1a, child number 0
-------------------------------------
select distinct(json_value(json_document, '$.CostCenter'))from json_tab
where json_textcontains(json_document, '$.CostCenter', 'A40')

Plan hash value: 333039129

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |       |       |     8 (100)|          |
|   1 |  HASH UNIQUE                 |                |     1 |  1997 |     8  (13)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| JSON_TAB       |     5 |  9985 |     7   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX              | JSON_TAB_GUIDE |       |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CTXSYS"."CONTAINS"("JSON_TAB"."JSON_DOCUMENT",'A40 INPATH
              (/CostCenter)')>0)
Offensichtlich wird der Index verwendet. Aber auch typische Oracle Text Abfrage Operatoren wie NEAR, FUZZY, $ können verwendet werden. Folgende Beispiele lassen sich dazu einfach ausprobieren.
... json_textcontains(json_document,'$','Sporting near green') ...
... json_textcontains(json_document,'$','Sporting near green') ...
... json_textcontains(json_document,'$.ShippingInstructions','fuzzy(fransesco)') ...
... json_textcontains(json_document, '$.LineItems', '$tie') ...
Dabei verwendet auch der neue Operator JSON_EXISTS, den neuen JSON Search Index, wie folgendes Beispiel zeigt.
SQL>select distinct(json_value(json_document, '$.ShippingInstructions.Address.zipCode')) from json_tab
    where json_exists(json_document,'$.ShippingInstructions.Address.zipCode');

(JSON_VALUE(JSON_DOCUMENT,'$.SHIPPINGINSTRUCTIONS.ADDRESS.ZIPCODE'))
----------------------------------------------------------------------------------------------------
26192
98199
99236

SQL>  select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  brjtjcwgqz2rc, child number 0
-------------------------------------
select distinct(json_value(json_document,
'$.ShippingInstructions.Address.zipCode')) from json_tab where
json_exists(json_document,'$.ShippingInstructions.Address.zipCode')

Plan hash value: 333039129

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |       |       |     8 (100)|          |
|   1 |  HASH UNIQUE                 |                |     1 |  1997 |     8  (13)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| JSON_TAB       |     5 |  9985 |     7   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX              | JSON_TAB_GUIDE |       |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CTXSYS"."CONTAINS"("JSON_TAB"."JSON_DOCUMENT",'HASPATH(/ShippingInstruct
              ions/Address/zipCode)')>0)

Lust auf mehr JSON in der Oracle Datenbank? Weitere Informationen in deutscher Sprache finden Sie auch unter:

Donnerstag, 20. April 2017

Oracle Text in 12c Release 2

Seit März diesen Jahres gibt es ein neues White Paper zum Thema Oracle Database 12c Release 2. Hier kann man einen ersten kurzen Eindruck über einige neue Text Features im Release 2 erhalten. Schlagworte sind dabei: Sentiment Analyse, Reverse Index und Erweiterungen beim NEAR Operator. Zusätzlich sind - wie immer übrigens - alle Änderungen im neuen Release im Text Application Developer's Guide unter "Changes in Oracle Text 12c Release 2 (12.2.0.1)" zu finden.

Dienstag, 18. April 2017

REL7: Package Missing in 12.1.0.2

Seit längerer Zeit haben wir nichts mehr auf unserem Text Blog gepostet. Nun nehme ich eine plattform spezifische Problematik zum Anlass mit neuen Einträgen zu starten. Worum geht es? Es geht um das Package compat-libstdc++-33-3.2.3, das auf Red Hat Enterprise Linux Version 7 nicht mehr automatisch installiert ist. Text Funktionalität, die ctxhx nutzt - also die Oracle Filter Funktionalität - ist davon in 12.1.0.2 betroffen.

Gut beschrieben ist das Ganze in der Note mit Doc ID 2254198.1: Missing or Ignored package compat-libstdc++-33-3.2.3 causes Text Issues in 12.1.0.2 (Doc ID 2254198.1). Hier findet man auch den Tipp, einfach ein Test mit ctxhx durchzuführen, um festzustellen, ob die Library in der eigenen Umgebung vorhanden ist.

Beliebte Postings