Mehrere Tabellenspalten indizieren: MULTICOLUMN_DATASTORE

Mitunter kommt es vor, dass die Dokumente in einer Tabelle in mehreren Spalten vorliegen. Eine Tabelle könnte wie folgt angelegt sein:

create table produktions_hinweise(
  fall_id               number(10),
  kommentar_werk        varchar2(4000),
  kommentar_controlling varchar2(4000),
  hinweise_produktion   clob
)
/

Wenn die Spalten KOMMENTAR_WERK, KOMMENTAR_CONTROLLING und HINWEISE_PRODUKTION nun volltextindiziert werden sollen, wird häufig (in Analogie zum normalen B-Baum-Index) der Weg gegangen, diese Spalten einzeln zu indizieren und später per AND bzw. OR kombinierte CONTAINS-Abfragen zu verwenden ...

/*
 * Abfragen auf mehrere Tabellenspalten NIEMALS so durchführen!
 */
select fall_id from produktions_hinweise
where
  contains(hinweise_produktion, 'Spezialmaschine') >0 and
  contains(kommentar_controlling, 'Abschreibungen') >0 and
  contains(kommentar_werk, 'Auslastung') >0
/

Dies führt allerdings immer zu sehr schlechter Performance. Denn es existieren nun drei Textindizes, die unabhängig voneinander ausgewertet werden. Die drei Zwischenergebnisse werden dann mit AND kombiniert. Insbesondere bei großen Datenmengen und wenn die Einzelabfragen nicht selektiv sind, führt dies zu sehr langen Antwortzeiten. Man kann als "Faustregel" festhalten, dass eine Volltextabfrage möglichst wenig CONTAINS()-Aufrufe (am besten nur einen) enthalten sollte. Das bedeutet aber auch, dass ein einziger Volltextindex auf alle drei Spalten erzeugt werden muss. Und das geht auch: Mit dem MULTI_COLUMN_DATASTORE.

Um den Multicolumn Datastore nutzen zu können, muss zunächst eine sog. Preference erzeugt werden; hier werden die Spalten, welche gemeinsam indiziert werden sollen, konfiguriert. Die folgenden SQL-Anweisungen erzeugen eine Preference vom Typ MULTICOLUMN_DATASTORE und legen die zu indizierenden Spalten der Tabelle als Inhalt fest.

begin
  ctx_ddl.create_preference(
    preference_name => 'kommentare_store'
   ,object_name     => 'MULTI_COLUMN_DATASTORE'
  );
  ctx_ddl.set_attribute(
    preference_name => 'kommentare_store'
   ,attribute_name  => 'columns'
   ,attribute_value => 'KOMMENTAR_WERK, KOMMENTAR_CONTROLLING, HINWEISE_PRODUKTION'
  );
end;
/

Um das Paket CTX_DDL nutzen zu können, benötigt man die Rolle CTXAPP (oder ein explizites EXECUTE-Privileg). Der Textindex wird im folgenden CREATE INDEX Kommando auf eine Tabellenspalte erzeugt (die tatsächlich angesprochenen Spalten sind in der Preference konfiguriert). Man kann nun eine der drei Dokumentspalten nehmen; aus Gründen der Übersichtlichkeit empfiehlt es sich jedoch, eine eigene "Dummy"-Spalte zu erzeugen.

alter table produktions_hinweise add (indexspalte char(1))
/

Nachtrag (danke für den Kommentar): Diese "Dummy-Spalte" muss bei einem Update der Tabellenzeile immer mit aktualisiert werden, damit der Index die Änderungen bemerkt. Das läßt sich z.B. durch einen Before-Update-Trigger realisieren.

create or replace trigger trg_produktions_hinweise
before update on produktions_hinweise
for each row
begin
  :new.indexspalte := :new.indexspalte;
end;
/

Nun kann der Index erzeugt werden.

create index idx_kommentare  on produktions_hinweise (indexspalte)
indextype is ctxsys.context
parameters ('
  datastore kommentare_store
  section group CTXSYS.AUTO_SECTION_GROUP'
)
/

Wie arbeitet der Multicolumn-Datastore nun? Obwohl es der Index formal für die Tabellenspalte INDEXSPALTE erzeugt wird, werden die tatsächlich indizierten Inhalte aus den drei in der Preference KOMMENTARE_STORE konfigurierten Spalten genommen. Die drei Dokumente pro Tabellenzeile werden zu einem einzigen zusammengefasst. Damit man jedoch noch in der Lage ist, auch gezielt in einer Spalte abzufragen, werden sie innerhalb des zusammengesetzten Dokuments durch XML Tags getrennt. Für das Beispiel wird (intern) also ein Dokument wie folgt generiert:

<KOMMENTAR_WERK>
  :
  Inhalt der Spalte KOMMENTAR_WERK hier  
  :
</KOMMENTAR_WERK>
<KOMMENTAR_CONTROLLING>
  :
  Inhalt der Spalte KOMMENTAR_CONTROLLING hier  
  :
</KOMMENTAR_CONTROLLING>
<HINWEISE_PRODUKTION>
  :
  Inhalt der Spalte HINWEISE_PRODUKTION hier  
  :
</HINWEISE_PRODUKTION>

Wie gesagt: Dieses Dokument wird nicht materialisiert - es wird nur während der Indexerstellung transient generiert. Nachdem es indiziert und alle Informationen in den Textindex aufgenommen wurden, wird es wieder zerstört. Für den Textindex gibt es pro Tabellenzeile jedoch nur noch ein Dokument, welches mehrere Abschnitte (Sections) hat. Damit diese Abschnitte separat abgefragt werden können, wurde dem CREATE INDEX Kommando der Parameter SECTION_GROUP CTXSYS.AUTO_SECTION_GROUP hinzugefügt (siehe oben). Eine kombinierte Abfrage (Beispiel oben) kann nun so aussehen:

select fall_id from produktions_hinweise
where
  contains(indexspalte, '(Spezialmaschine) and (Abschreibungen) and (Auslastung)') >0
/

Aus den drei CONTAINS-Aufrufen ist nun einer geworden; die AND bzw. OR-Verknüpfungen befinden sich nun innerhalb des CONTAINS(). Allerdings entspricht diese Abfrage der obigen nicht ganz genau. Denn das Token Spezialmaschine wurde oben gezielt in der Spalte HINWEISE_PRODUKTION gesucht; hier muss es nur in einer der Spalten vorkommen. Es wird also ein Zugriff auf die einzelnen Abschnitte des Dokuments benötigt ...

select fall_id from produktions_hinweise
where contains(
  indexspalte, 
  '(Spezialmaschine WITHIN (HINWEISE_PRODUKTION)) and 
   (Abschreibungen WITHIN (KOMMENTAR_CONTROLLING)) and 
   (Auslastung WITHIN (KOMMENTAR_WERK))'
) > 0
/

Mit der WITHIN-Klausel kann ein Token gezielt in einem der Abschnitt gesucht werden. Man kann die Begriffe nun also sehr flexibel innerhalb aller indizierten Spalten oder in bestimmten Spalten suchen. Alle anderen Operatoren der SQL-Funktion CONTAINS funktionieren wie gehabt.

Das entscheidende ist jedoch, dass diese Abfragen stets komplett in ein- und demselben Volltextindex ausgeführt wird. Es ergibt sich eine um Faktoren bessere Performance als mit einzelnen Textindizes ...

Übrigens: Man kann mit dem Multicolumn Datastore auch gewöhnliche relationale Tabellenspalten (bspw. Adressfelder) indizieren. Dann lassen sich die linguistischen Features (Fuzzy-Suche) auch für Abfragen in strukturierten Daten nutzen. Wie das geht und was man damit erreichen kann, ist in einem Tipp der deutschsprachigen Application Express-Community beschrieben.