Wildcard Abfragen stellen in allen Textsuchmaschinen gewisse Herausforderungen dar. Unterscheiden kann man dabei im Prinzip folgende Abfragen mit Wildcards
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.
Setzen wir diese Events in unserer Umgebung und führen wieder Abfragen mit %rg aus.
- zu Beginn oder von links wie zum Beispiel %frage für Abfrage, Anfrage etc.
- in der Mitte wie z.B. bei An%l für Anteil usw.
- am Ende oder von rechts wie zum Beispiel An% für Anfrage, Angst, Anteil etc.
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. 