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 ...

Beliebte Postings