Mittwoch, 8. Dezember 2010

Mächtige Suchabfragen: PL/SQL-Funktionen innerhalb CONTAINS()

Wusstet Ihr schon, dass Ihr in einer CONTAINS-Abfrage auch SQL- und PL/SQL-Funktionen aufrufen könnt ...?
Das kann man nutzen, um Suchbegriffe durch eine Funktion aufzubereiten. Einfache Synonymbeziehungen lassen sich zwar auch mit einem Thesaurus abbilden, wenn die Beziehungen aber komplexerer Natur sind oder zwingend prozeduralen Code erfordern, ist die Nutzung einer PL/SQL-Funktion eine gute Alternative. Dazu ein Beispiel:
Zuerst Tabelle erstellen und einige "Dokumente" einfügen.
create table doc (
  id     number,
  doc    varchar2(4000)
);

insert into doc values (1, 'Oracle 11g');
insert into doc values (2, 'Ein Test');
insert into doc values (3, 'Oracle 9iR2');
insert into doc values (4, 'Oracle 11.2.0.1');
insert into doc values (5, 'Oracle 11gR2');
Dann indizieren ...
create index ft_doc on doc (doc)
indextype is ctxsys.context
/
Man sieht, dass die Datenbankversionen völlig unterschiedlich in der Tabelle auftauchen. Eine Variante wäre mit Sicherheit ein Thesaurus, aber in diesem Beispiel möchten wir das mit einer PL/SQL-Funktion erschlagen. Und die sähe wie folgt aus.
create or replace function format_release(
  p_release in varchar2
) return varchar2 is 
  v_tokens varchar2(4000);
begin
  if p_release like '11.2%' then
    v_tokens := '(11.2%) or {11gR2} or {11g} or {11g Release 2}';
  elsif p_release like '11.1%' then
    v_tokens := '(11.1%) or {11gR1} or {11g} or {11g Release 1}';
  elsif p_release like '10.2%' then
    v_tokens := '(10.2%) or {10gR1} or {10g} or {10g Release 2}';
  elsif p_release like '9.2%' then
    v_tokens := '(9.2%) or {9iR2} or {9i} or {9i Release 2}';
  else 
    v_tokens := p_release;
  end if;
  return v_tokens;
end;
/
Die Anwendung sieht dann so aus ...
SQL> select * from doc where contains(doc, format_release('11.2%')) > 0

        ID DOC
---------- ------------------------------
         1 Oracle 11g
         4 Oracle 11.2.0.1
         5 Oracle 11gR2

3 Zeilen ausgewählt.
In der Funktion lässt sich natürlich kodieren, was man möchte. So kann man auch Informationen aus einer Tabelle holen - damit könnte man ein Synonym wie "bester_kunde" definieren; diese Funktion holt den Namen des umsatzstärksten Kunden aus einer Tabelle und liefert ihn zurück. Man könnte damit also (lediglich anhand des Stichworts bester_kunde) nach allen Dokumenten suchen, in denen der Name des aktuell umsatzstärksten Kunden vorkommt. Eine andere Variante wäre die Kombination mit räumlichen Features der Datenbank. Dann könnte die Funktion in etwa so aussehen (Pseudocode) ...
create or replace function kunden_nahe(
  p_stadt in varchar2
) return varchar2 is 
  v_tokens varchar2(4000) := '';
begin
  -- Räumliche Abfrage: Hole alle Kundennamen, die sich
  -- innerhalb eines 10km-Radius um die gegebene Stadt 
  -- befinden
  for kd in (
    select k.name 
    from kunden k, staedte s
    where sdo_within_distance(k.position, s.position, 10, 'unit=km') = 'TRUE'
    and s.name = p_stadt
  ) loop
    v_tokens := v_tokens ||'(' || kd.name || ') or ';
  end loop;
  v_tokens := substr(v_tokens, 1, length(v_tokens) - 4);
  return v_tokens;
end;
/
Man sieht, dass diese "kleine Randnotiz" (man kann PL/SQL-Funktionen in CONTAINS verwenden), zu sehr mächtigen Suchanfragen führen kann. Die Praxis kennt die besten Beispiele ...

Kommentare:

Laubi hat gesagt…

Besten Dank für die Oracle Text Beiträge, schön zu sehen dass hier jemand in diesem Bereich etwas publiziert!

Ich wollte dieses Beispiel in einer angepassten Form übernehmen, hab allerdings ein Problem, das ich nicht ganz verstehe.

Ausgeschrieben wäre mein Query wie folgt aufgebaut (meine Daten sehen natürlich anders aus):
select 1 from doc where contains(doc, '11%2')

Damit möchte ich:
"11g Release 2" oder "11gR2" finden. Allerdings wird bei mir nie etwas gefunden wenn das %-Zeichen im String ist.

Die Oracle sagt mir bez. % nur dass dies ein Wildcard ist, mehr finde ich leider nicht.. Wo ist mein Überlegungsfehler?

Carsten Czarski hat gesagt…

Hallo,

etwas spät - aber die Antwort kommt :-)

Das liegt daran, dass Oracle TEXT eben keine Substringsuche macht, sondern eine Volltextsuche. Die Texte in der Tabelle werden in Tokens zerlegt - und die werden dann indiziert. Im Index findet sich also nicht "11g Release 2", sondern ... "11g", "Release" und "2". Insofern sollte die Abfrage "11%2" den Text "11gR2" finden, nicht aber "11g Release 2" ...

Beste Grüße

-Carsten

Beliebte Postings