Oracle Text Composite Domain Index

Bei Nutzung der Oracle Text Volltextsuche kann es oft vorkommen, dass in der WHERE-Bedingung eines SQL-Query sowohl gegen die indizierte Textspalte (mit CONTAINS), als auch gegen eine oder mehrere normale relationale Spalten gefiltert wird, man also eine gemischte Abfrage oder auch „Mixed Query“ hat. Eine solche Abfrage ist womöglich nicht optimal, da sie zu Performanceproblemen führen kann. Was ist in einem solchen Fall zu tun?

Seit Oracle 11g kann man unter Verwendung von z.B. Multi-Column-Datastores, die zusätzlich die relationalen Spalten enthalten, sowie passenden SDATA-Sections die strukturellen Informationen mit in den Volltextindex holen. Die relationalen Abfragen werden dann mit dem SDATA-Operator innerhalb des CONTAINS ausgeführt.

Diese Vorgehensweise hatte bei einem Kunden nicht zum erwünschten Erfolg geführt, was mich zu einer anderen Maßnahme greifen liess:

Ebenfalls seit Oracle 11g gibt es noch eine weitere Möglichkeit, mit „Mixed Queries“ umzugehen: Den Composite-Domain-Index (CDI). Hierbei kann man sich das Anlegen des Multi-Column-Datastore und der SDATA-Sections sparen. Man gibt einfach beim Anlegen des Volltext-Indexes die entsprechenden Where-Filter-Spalten als FILTER BY Spalten und/oder die entsprechenden Sortier-Spalten als ORDER BY Spalten an. An der Struktur, die der Index anlegt kann man erkennen, dass hier im Hintergrund eine SDATA-ähnliche Technik arbeitet.

CREATE INDEX mein_index ON meine_tabelle(meine_textspalte)    INDEXTYPE IS ctxsys.context    FILTER BY suchspalte1, suchspalte2, ...    ORDER BY sortierspalte1, sortierspalte2, ...

Dies brachte eine deutliche Performance-Verbesserung gegenüber der „manuellen“ SDATA-Anwendung. Ein weiterer Vorteil: Man muss die SQL-Queries nicht umstellen, die relationalen Abfragen werden bei Verwendung eines CDI wie gehabt verwendet und müssen nicht mit in den CONTAINS-Ausdruck.

WM_CONCAT? Ab 11gR2 dann doch lieber LISTAGG

Ausgangssituation:

Als SQL-Programmierer steht man häufig vor der Aufgabenstellung Werte aus mehreren Spalten in einer einzelnen Spalte darzustellen. Als Beispiel soll hier mal das bekannte Oracle HR-Schema mit der Tabelle Employees herhalten.

Ein einfacher Select auf die Tabelle liefert die Mitarbeiter mit ihrer Department-ID

select e.department_id, e.last_name from employees e;

DEPARTMENT_ID LAST_NAME
------------- --------------------------------------------------------------------------------
           90 King
           90 Kochhar
           90 De Haan
           60 Hunold
           60 Ernst
           60 Austin
           60 Pataballa
           60 Lorentz
          100 Greenberg
          100 Faviet
          100 Chen
          100 Sciarra
          100 Urman
          100 Popp
           30 Raphaely
           30 Khoo
           30 Baida
           30 Tobias
           30 Himuro
           30 Colmenares

……………………………….

Ist die jetzt die Aufgabenstellung zu jedem Department die Mitarbeiter in einer Spalte darzustellen wir sehr häufig die nicht dokumentierte Oracle-Funktion wm_concat benutzt. Mit dieser Funktion lässt sich folgendes Ergebnis erzielen:

select e.department_id, wm_concat(e.last_name) name from employees e
group by e.department_id

DEPARTMENT_ID NAME
------------- --------------------------------------------------------------------------------
           10 Whalen
           20 Hartstein,Fay
           30 Raphaely,Colmenares,Himuro,Tobias,Baida,Khoo
           40 Mavris
           50 Weiss,Grant,OConnell,Feeney,Walsh,Jones,McCain,Everett,Bell,Perkins,Gates,Dilly,
           60 Hunold,Lorentz,Pataballa,Austin,Ernst
           70 Baer
           80 Russell,Johnson,Livingston,Taylor,Hutton,Abel,Kumar,Bates,Smith,Fox,Bloom,Ozer,B
           90 King,De Haan,Kochhar
          100 Greenberg,Popp,Urman,Sciarra,Chen,Faviet
          110 Higgins,Gietz
                Grant

Das Problem hierbei ist allerdings, dass es sich um eine nicht dokumentierte Oracle-Funktion handelt und Oracle sich das Recht vorbehält Änderung an solchen Funktionen vorzunehmen ohne diese zu publizieren.

Genau dieses ist auch der Fall bei der Funktion wm_concat. Bis zu den Datenbank-Versionen 10.2.0.4 und 11.2.0.1 war der Rückgabe-Wert vom Typ varchar2. Ab den Versionen 10.2.0.5 und 11.2.0.2 ist es ein clob.

Die Auswirkungen bei einem Upgrade der Datenbank sollten den meisten bekannt sein. (invalide Views,….).

Hier die passende Oracle  Support Eintrag:

WM_CONCAT- Oracle Support

 

Lösung:

Um diese Problematik zu umgehen, empfiehlt es sich ab der Datenbank Version 11gR2 die analytische Funktion LISTAGG einzusetzen. Sie bietet nicht nur den kompletten Funktionsumfang von WM_CONCAT, sondern ist auch wesentlich schneller.

 

SELECT e.department_id, LISTAGG(e.last_name, ',') WITHIN GROUP (ORDER BY e.last_name) emps
FROM   employees e
GROUP BY e.department_id;

 

DEPARTMENT_ID EMPS
------------- --------------------------------------------------------------------------------
           10 Whalen
           20 Fay,Hartstein
           30 Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
           40 Mavris
           50 Atkinson,Bell,Bissot,Bull,Cabrio,Chung,Davies,Dellinger,Dilly,Everett,Feeney,Fle
           60 Austin,Ernst,Hunold,Lorentz,Pataballa
           70 Baer
           80 Abel,Ande,Banda,Bates,Bernstein,Bloom,Cambrault,Cambrault,Doran,Errazuriz,Fox,Gr
           90 De Haan,King,Kochhar
          100 Chen,Faviet,Greenberg,Popp,Sciarra,Urman
          110 Gietz,Higgins
                Grant

 

Exkurs:

Erweitert man das SQL-Statement um ein PARTITION BY ist es sogar möglich Werte der Einzeldatensätze kombiniert mit den zusammengefassten Daten in einer Zeile darzustellen:

SELECT department_id Dept,
       hire_date Datum,
       last_name Name,
       LISTAGG(last_name, ', ')
           WITHIN GROUP (ORDER BY hire_date, last_name)
                   OVER (PARTITION BY department_id) as Emp_list
FROM employees
ORDER BY Dept, Datum, Name;

 

DEPT DATUM NAME EMP_LIST

----- ----------- -------------------------------------------------------------------------------- ---------------------------------------------------------------

10 17.09.1987 Whalen Whalen

20 17.02.1996 Hartstein Hartstein, Fay

20 17.08.1997 Fay Hartstein, Fay

30 07.12.1994 Raphaely Raphaely, Khoo, Tobias, Baida, Himuro, Colmenares

30 18.05.1995 Khoo Raphaely, Khoo, Tobias, Baida, Himuro, Colmenares

30 24.07.1997 Tobias Raphaely, Khoo, Tobias, Baida, Himuro, Colmenares
.
.
.

50 17.10.1995 Rajs Kaufling, Ladwig, Rajs, Sarchand, Bell, Mallin, Weiss, Davies, Marlow, Bull, Eve

     24.05.1999 Grant Grant

107 rows selected

Manchmal ist char kürzer als char(xxx)

Aufgabe: Mit Hilfe einer einer externen Tabelle sollen Daten aus einer csv-Datei mit Oracle Warehouse Builder eingelesen und weiter verarbeitet werden.

Damit die ohne Probleme verarbeitet werden können und es zu keinen Fehlern mit falschen Formatmasken kommt wird die Tabellenstruktur folgendermaßen angelegt:

-- Create table
create table IMPORT_SRC
(
  id         VARCHAR2(4000),
  datum VARCHAR2(4000),
  daten VARCHAR2(4000)
)
organization external
(
  type ORACLE_LOADER
  default directory MOD_CSV_LOCATION
  access parameters
  (
    RECORDS DELIMITED BY '\r\n'
    CHARACTERSET WE8MSWIN1252
    STRING SIZES ARE IN BYTES
    NOBADFILE
    NODISCARDFILE
    NOLOGFILE
    SKIP 1
    FIELDS
      TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '"' AND '"'
      NOTRIM
      (
        "ID" CHAR,
        "DATUM" CHAR,
        "DATEN" CHAR      )
  )
  location (MOD_CSV_LOCATION:Import.csv')
)
reject limit UNLIMITED;

 

Problem: Es werden nicht alle Zeilen der Datei verarbeitet. Auch ein SQL-Statement auf die Externe Tabelle liefert von den 10 sich in der Datei befindeten Zeilen nur 8 zurück. Beim betrachten der Datei sticht hervor, dass in 2 Zeilen die Werte in der Spalte für Daten deutlich länger sind als in den anderen Zeilen.

Lösung: Nach einigem Testen und Modifizieren der Datei stellt sich raus, dass die magische Grenze bei 255 Zeichen liegt. Sobald die Länge diesen Wert übersteigt, wird die Zeile nicht mehr verarbeitet.

Abhilfe schafft hier die Definition der Externen Tabelle mit einer Angabe für die maximale Spaltenlänge. So greift die Einschränkung der Länge nicht mehr und die Daten können verarbeitet werden.

-- Create table
create table IMPORT_SRC
(
id VARCHAR2(4000),
datum VARCHAR2(4000),
daten VARCHAR2(4000)
)
organization external
(
type ORACLE_LOADER
default directory MOD_CSV_LOCATION
access parameters
(
RECORDS DELIMITED BY '\r\n'
CHARACTERSET WE8MSWIN1252
STRING SIZES ARE IN BYTES
NOBADFILE
NODISCARDFILE
NOLOGFILE
SKIP 1
FIELDS
TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' AND '"'
NOTRIM
(
"ID" CHAR,
"DATUM" CHAR,
"DATEN" CHAR (1000) )
)
location (MOD_CSV_LOCATION:Import.csv')
)
reject limit UNLIMITED;

 

Lesson learned: Manchmal ist char kürzer als char(xxx)