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.

Ergebnisse zählen: Nicht mit count(*), sondern mit COUNT_HITS!

Wenn man Oracle TEXT in einer Applikation nutzt und Abfragen mit der CONTAINS-Funktion durchführt, möchte man fast immer auch die Anzahl der Treffer auf der Ergebnisseite anzeigen. Und der erste, naheliegende Gedanke ist, hierfür ein SELECT COUNT(*) zu verwenden - die Abfrage findet ja auch mit einem SELECT statt.

select count(*) from dokument_tab where contains(dokument, 'Bundeskanzler') > 0

Gerade bei Oracle TEXT ist diese Variante jedoch sehr ineffektiv - denn die Datenbank muss hierfür sowohl auf den Volltextindex als auch auf die Tabelle zugreifen. Besser ist es, die dafür vorgesehene Funktion COUNT_HITS im PL/SQL-Paket CTX_QUERY zu nutzen.

set serveroutput on

declare
  v_number number;
begin
  v_number := ctx_query.count_hits(
    index_name => 'MY_FULLTEXT_IDX',
    text_query => 'oracle and text',
    exact =>      true
  );
  dbms_output.put_line('Anzahl Treffer: '||v_number);
end;
/
 
Anzahl Treffer: 2657

Man muss den Namen des Index kennen, um die Funktion nutzen zu können; mit der Dictionary View CTX_USER_INDEXES ist dieser aber leicht zu ermitteln. Interessant ist noch der Parameter exact - gibt man hier false an, so arbeitet die Funktion schneller, dafür ungenauer: Seit der letzten Indexoptimierung gelöschte und veränderte Dokumente werden dann nicht berücksichtigt; der Wert ist also zu hoch. Direkt nach einer Indexoptimierung liefern sowohl exact => true als auch exact => false gleiche Ergebnisse zurück. In allen Fällen ist CTX_QUERY.COUNT_HITS schneller als ein SELECT COUNT(*).

Mehr Information in der Oracle Dokumentation - TEXT Reference.