SQL Server 2000 Volltextsuche

Geschrieben von: Christoph Wille
Kategorie: Datenbank

This printed page brought to you by AlphaSierraPapa

Wer kennt das Problem nicht - man soll in einer Tabelle suchen, und zwar nach Textfragmenten. Solange es nur eine Spalte ist, über die gesucht werden soll, kann man sich ja noch mehr oder minder elegant mit LIKE und dem Prozentzeichen über die Runden retten. Sollen es aber mehrere Spalten sein, über die nach einem bestimmten Fragment gesucht werden soll, wird es interessant. Und gesellen sich dann noch Memo Felder dazu, ist es endgültig aus.

Habe ich gerade endgültig aus gesagt? Stimmt überhaupt nicht! Es gibt einen Weg, der noch dazu einfach, schnell und sehr elegant ist: die Volltextsuche von SQL Server 2000. Die hier vorgestellte Technologie gibt es auch bereits in SQL Server 7, allerdings können einige Screenshots und Befehlsreihenfolgen differieren.

Installation der Volltextsuche

Standardmäßig findet die Volltextsuche ihren Weg leider nicht auf den Rechner - das muß man durch ein Custom Setup mit SQL Server nachholen. Ist aber recht einfach, weil man nur ein Häkchen in der Dialogbox Select Components setzen muß.

Dieser Schritt installiert den Microsoft Search Service (der ist für die Volltextsuche zuständig), sowie 2 weitere SQL Kommandos: FREETEXT und CONTAINS.

Erstellen eines Volltextindexes

Standardmäßig ist die Volltextsuche für keine Datenbank aktiviert. Man muß einen Volltextcatalog (ähnlich zu Index Server) erzeugen, Tabellen für die Volltextsuche aktivieren, und in diesen Tabellen die Spalten wählen, die indiziert werden sollen.

Das klingt zwar nach viel Arbeit, ist es aber Dank eines Wizards nicht wirklich. Dazu wählt man im SQL Server Enterprise Manager die in Frage kommende Datenbank aus (ich habe die Beispieldatenbank pubs gewählt), und startet via Tools/Full-Text Indexing... den Full Text Indexing Wizard. Auf der ersten Seite sehen Sie, was Sie alles mit dem Wizard anstellen können.

Schritt 1 Wählen Sie die Tabelle aus, für die Sie einen Volltextindex anlegen wollen. In diesem Beispiel nehmen wir auch gleich die erste Tabelle, [dbo].[authors]. Obwohl auch der Wizard Sie darauf hinweist, möchte ich es auch explizit tun: Sie müssen der Eigentümer (oder alias) der Tabelle sein - "nur" Administrator reicht nicht.

Schritt 2 Jetzt werden Sie nach einem Index gefragt, der für JOIN's verwendet werden soll. Warum muß ich das angeben, werden sich einige fragen? Der Grund ist, daß der Microsoft Search Service getrennt vom Query Processor läuft, also Tabellensuchresultate aus defakto zwei verschiedenen authors "Tabellen" gejoint werden - die aus der Volltextsuche, und der Rest vom "normalen" Query Processor.

In unserem Fall ist bereits der richtige Index selektiert. Verwenden Sie immer den Primary Key Index für diese Selektion - dieser wird auch vom Query Processor verwendet.

Schritt 3 Nun können Sie die Spalten aussuchen, die in den Volltextindex integriert werden sollen (also alle Spalten, die ein Statement abfragen kann). Volltextindizes können nur über Textspalten (char, varchar, text) gemacht werden, daher wählen wir der Einfachheit diesmal auch alle aus.

Schritt 4 Die Grundangaben haben wir jetzt alle gemacht, allerdings müssen wir Microsoft Search jetzt noch sagen, wohin der Index gespeichert werden soll - der Speicherort des Catalogs (analog zu Index Server Catalogs). Am besten, Sie behalten das Verzeichnis bei, das vorgeschlagen wird, und geben nur den Namen des Catalogs an - pubstest.

Schritt 5 Wichtig für die Aktualität des Volltextindexes ist wann und wie er aktualisiert wird. Zur Aktualisierung gibt es drei Varianten:

• Voller Rebuild Baut den Volltextindex von Grund auf neu auf. Verbraucht naturgemäß viel Rechenzeit, kann aber als Task mit dem SQL Server Agent gescheduled werden.
• Inkrementeller Rebuild Macht Änderungen im Volltextindex nur für Zeilen, die sich seit dem letzten Rebuild geändert haben. Dazu benötigt man allerdings eine zusätzlich Spalte in der Tabelle, und zwar vom Datentyp timestamp. Vorteil: deutlich schneller, läuft ebenso wie der Full Rebuild zu bestimmten Off-Hours, benötigt weniger Resourcen.
• Änderungsverfolgung Die Änderungen werden aktiv verfolgt, daß heißt, eine Liste der veränderten Daten wird mitgeführt. Im Gegensatz zum inkrementellen Rebuild muß die Tabelle nicht umgebaut werden, allerdings bekommt man einen Geschwindigkeitsnachteil durch die aktive Nachverfolgung.

Der inkrementelle Rebuild als auch die Änderungsverfolgung haben einen gemeinsamen Nachteil: alle Änderungen an Memofeldern, die mit WRITETEXT oder UPDATETEXT durchgeführt wurden, werden nicht erkannt. Daher sind regelmäßige Full Rebuilds zuätzlich notwendig.

Daher legen wir uns einen Full Rebuild Schedule an, und lassen ihn einmal pro Woche am Sonntag um Mitternacht laufen. Die weiteren inkrementellen Rebuilds können Sie sich dann selbst anlegen.

Schritt 6 Damit sind wir schon beim letzten Schritt des Wizards. Begutachten Sie nochmals die Einstellungen und klicken dann auf Finish.

Damit wir den Index sofort verwenden können, gehen Sie danach zum Folder Full-Text Catalogs, klicken mit der rechten Maustaste auf den neuen Catalog und wählen Start Full Population.

Abfragen mit dem CONTAINS Schlüsselwort

Öffnen Sie den Query Analyzer um die Abfragen interaktiv ausprobieren zu können (natürlich hindere ich Sie nicht, es sofort in ASP auszuprobieren). Stellen Sie sicher, daß Sie in die pubs Datenbank gewechselt sind!

Tippen Sie folgende einfache Abfrage ein:

SELECT * FROM authors

Diese Abfrage liefert alle Autoren, die in dieser Tabelle gespeichert sind. Um das ganze jetzt auf Autoren einzuschränken, die das Wort "green" in Ihren Nachnamen haben, könnten Sie folgendes LIKE Statement verwenden:

SELECT * FROM authors WHERE au_lname LIKE '%green%'

Unter Ausnutzung der Volltextsuche würde das nun wie folgt aussehen:

SELECT * FROM authors WHERE CONTAINS(au_lname,'green')

Nun könnten Sie berechtigt argumentieren, daß ich dafür nun wirklich nicht den Volltextindex hätte bemühen müssen. Stimmt. Allerdings wie machen Sie das folgende in normalen SQL?

SELECT * FROM authors WHERE CONTAINS(*,'green')

Jetzt wird der Volltextindex für alle Spalten durchsucht, und wenn eine Spalte green aufweist, wird die Zeile zurückgeliefert. Das wäre mit normalen SQL zwar noch möglich, allerdings ziemlich länglich:

SELECT * FROM authors WHERE 
	au_lname LIKE '%green%' OR
	au_fname LIKE '%green%' OR
	address LIKE '%green%' OR
	city LIKE '%green%' OR
	state LIKE '%green%'

Mit der Volltextsuche kann man natürlich auch nach Phrasen suchen:

SELECT * FROM authors WHERE CONTAINS(address,'"Seventh Av"')

Damit bekommt man beide Autoren Ringer zurückgeliefert, da diese in der Seventh Avenue leben. Will man nebenbei auch noch zusätzlich alle Autoren, die in einer Street leben, sieht das Statement wie folgt aus:

SELECT * FROM authors WHERE CONTAINS(address,'"Seventh Av" OR "St"')

Neben OR kann man auch noch AND und AND NOT verwenden, um boolsche Abfragen zu gestalten. Obwohl die authors Tabelle keinerlei Memofelder beinhaltet, können diese natürlich auch über den Volltextindex gesucht werden - und das wird wohl das Hauptanwendungsgebiet dieser Technologie in Ihren Applikationen werden.

Die Programmierung in ASP ist vollständig gleich zu einer normalen Abfrage, die keinen Volltextindex verwendet. Es gibt keinerlei Einschränkungen in punkto Updatefähigkeit des Recordsets, das man zurückgeliefert bekommt. Die Volltextsuche verhält sich völlig transparent.

Schlußbemerkung

Dieser Artikel zeigt, wie man einen Volltextindex anlegt, und einfache Abfragen auf diesen absetzt. Ich habe Details wie Wortstämme oder die FREETEXT Funktion ausgelassen. Sie haben aber dennoch genug Wissen, um der LIKE Funktion Ade zu sagen - und Memofelder bequem suchen zu können.

This printed page brought to you by AlphaSierraPapa

Links zu anderen Sites

SQL Server Developer Center
http://msdn.microsoft.com/sqlserver/
Stephen Wynkop's SQL Server Home
http://www.swynk.com/sysapps/sql.asp