Donnerstag, 10. September 2009

Oracle TEXT und Database Links ...

Heute widmen wir uns in einem ganz kurzen Tipp dem Thema Database Links, diese können mit Oracle TEXT verwendet werden. Es sind allerdings ein paar Feinheiten zu beachten: Angenommen, wir haben eine Tabelle DOK_TAB auf einer entfernten Datenbank. Diese hat eine Spalte DOK, die auch mit Oracle TEXT indiziert ist.
Auf der lokalen Datenbank können wir nun einen Database Link anlegen ...
create database link {dblink-name} connect to {remote-username} identified by {remote-passwd} using '{connection-string}'
Ein erster Versuch führt nun scheinbar ins Leere ...
select * from dok_tab@{dblink-name} where contains(dok, '$Bahnhof') > 0;
                                       *
FEHLER in Zeile 1:
ORA-20000: Oracle Text error:
DRG-10599: column is not indexed
Diese Fehlermeldung kommt, obwohl die Spalte indiziert ist. Oracle TEXT hat die Eigenheit, dass der Database Link auch bei der CONTAINS-Funktion angegeben werden muss. Damit wird sichergestellt, dass die CONTAINS-Funktion auch auf der entfernten Datenbank ausgeführt wird. Also ...
select * from dok_tab@{dblink-name} where contains@{dblink-name}(dok, '$Bahnhof') > 0;

        ID DOK
---------- ------------------------------
         2 Hauptbahnhof
Eins ist im Zusammenhang mit Database Links noch wichtig: CLOB- oder BLOB-Spalten können nicht via Database Link übertragen werden. Was geht, sind die ersten 32 Kilobyte - dazu kann man mit DBMS_LOB.SUBSTR arbeiten. Zur Selektion der ganzen Dokumente kann man mit Database Links also nicht arbeiten - die Selektion von IDs, URLs oder Titeln ist jedoch problemlos möglich.

Mittwoch, 19. August 2009

Abfrage-Optimierung mit Composite Domain Index

Wie in vorangegangen Blogs gezeigt wurde, kann die sogenannte Mixed Query Problematik - eine Kombination aus relationalem und Volltextrecherche-Anteil - mit neuen Section Features in Oracle Database 10g bzw. 11g angegangen und teilweise gelöst werden. Der MData Section und MULTI_COLUMN_DATASTORE-Blog verwendete dabei zur Lösung, die in 10g eingeführte MDATA-Sections und der SData Section-Blog die neue SDATA-Section. Darüber hinaus ist in 11g eine neue Form des Context Index, der sogenannte „Composite Domain Index“ (kurz CDI) neu eingeführt worden, um speziell bei der Optimierung von Mixed Queries eine einfache direkte Lösung zu bieten. Ein Composite Domain Index ist dabei ein zusammengesetzter Index, der sich nicht auf die Textinformation beschränkt, sondern auch strukturelle Informationen mitführt und mit einem einzigen Aufruf an die Textengine ausgeführt wird.
So können Queries die aus
  • Textanteilen und strukturierten Anteilen in the SQL WHERE Klausel
  • Textanteilen und strukturierten Anteilen in der ORDER BY Klausel
  • eine Kombination aus beidem

  • einfach optimiert werden. Dieses Feature kann dabei unabhängig von den Section Features genutzt werden. Keine Sections oder gar Änderung am Abfragecode ist notwendig. Die Technologie verwendet allerdings aus Optimierungsgründen im Hintergrund genau wie bei SDATA-Sections ein zusätzliches Indexsegment, eine IOT-Tabelle mit Namen DR$SDATA_INDEX$S. Das Anlegen des CDI erfolgt dann mit folgender einfacher erweiterter Syntax.
    
    CREATE INDEX comp_ind ON customers(cust_first_name)
    INDEXTYPE IS ctxsys.context
    FILTER BY cust_id
    ORDER BY cust_year_of_birth
    
    Oracle Text wird nun die Daten aus CUST_ID und CUST_YEAR_OF_BIRTH im Textindex speichern; dabei besteht keine Notwendigkeit, die Queries anzupassen. Der Optimizer wird feststellen, dass die Abfrage durch den Textindex allein verifiziert werden kann. Die Erweiterung mit ORDER BY führt sogar dazu, dass die abgerufenen Zeilen danach sortiert ausgeliefert werden können. Im Unterschied zu B*Tree Indizes können allerdings nur die Informationen gefiltert werden, die auch schon synchronisiert worden sind.
    An einem Beispiel wollen wir die Optimierung aufzeigen. Im ersten Fall verwenden wir folgenden einfachen CONTEXT Index
    
    CREATE INDEX text_ind ON customers(cust_first_name) 
    INDEXTYPE IS ctxsys.context;
    
    Sehen wir uns nun die Ausführungszeit folgender Query an. Die Abfrage ist wie häufig bei Webanwendungen anzutreffen optimiert im Hinblick auf sortierten Zugriffe der ersten Zeilen.
    
    SELECT /*+ first_rows(10) */ cust_id
    FROM (select cust_id, cust_first_name,cust_year_of_birth from customers
    WHERE contains (cust_first_name, 'A% or D% or N% or B%',1)>0  AND cust_id>100000 
    ORDER BY cust_year_of_birth, score(1))
    WHERE rownum<10
    
       CUST_ID
    ----------
        102011
        103921
        100199
        100930
        104242
        103080
        103187
        103412
        103684
    
    9 rows selected.
    
    Elapsed: 00:00:00.12
    
    Der folgende Ausführungsplan ist etwas länglich, zeigt allerdings schon ohne genaue Analyse dass Textindex TEXT_IND und B*Index CUSTOMERS_PK für die Ausführung notwendig sind.
    
    SELECT * FROM table(dbms_xplan.display_cursor(format=>'basic'))
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    EXPLAINED SQL STATEMENT:
    ------------------------
    SELECT /*+ first_rows(10) */ cust_id FROM (select cust_id,
    cust_first_name,cust_year_of_birth from customers WHERE contains
    (cust_first_name, 'A% or D% or N% or B%',1)>0  AND cust_id>100000 order
    by cust_year_of_birth, score(1)) where rownum<10
    Plan hash value: 1704212880
    ------------------------------------------------------------
    | Id  | Operation                           | Name         |
    ------------------------------------------------------------
    |   0 | SELECT STATEMENT                    |              |
    |   1 |  COUNT STOPKEY                      |              |
    |   2 |   VIEW                              |              |
    |   3 |    SORT ORDER BY STOPKEY            |              |
    |   4 |     TABLE ACCESS BY INDEX ROWID     | CUSTOMERS    |
    |   5 |      BITMAP CONVERSION TO ROWIDS    |              |
    |   6 |       BITMAP AND                    |              |
    |   7 |        BITMAP CONVERSION FROM ROWIDS|              |
    |   8 |         SORT ORDER BY               |              |
    |   9 |          DOMAIN INDEX               | TEXT_IND     |
    |  10 |        BITMAP CONVERSION FROM ROWIDS|              |
    |  11 |         SORT ORDER BY               |              |
    |  12 |          INDEX RANGE SCAN           | CUSTOMERS_PK |
    ------------------------------------------------------------
    
    Zum Vergleich verwenden wir statt des einfachen Context Index nun den neuen CDI mit der oben angegebenen Syntax und führen die Abfrage noch einmal durch:
    
    SELECT /*+ first_rows(10) */ cust_id
    FROM (select cust_id, cust_first_name,cust_year_of_birth from customers
    WHERE contains (cust_first_name, 'A% or D% or N% or B%',1)>0  AND cust_id>100000 
    ORDER BY cust_year_of_birth, score(1))
    WHERE rownum<10
    
       CUST_ID
    ----------
        102011
        103921
        100199
        100930
        104242
        103080
        103187
        103412
        103684
    
    9 rows selected.
    
    Elapsed: 00:00:00.02
    
    Die Abfragezeit beträgt nur noch ein Sechstel der Zeit. Nun schauen wir uns noch den zugehörigen Ausführungsplan an:
     
    SELECT * FROM table (dbms_xplan.display_cursor());
    ...
    Plan hash value: 3210723938
    -------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time    |
    -------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |           |       |       |     5 (100)|         |
    |*  1 |  COUNT STOPKEY                 |           |       |       |            |         |
    |   2 |   VIEW                         |           |     1 |    13 |     5  (20)| 00:00:01|
    |*  3 |    SORT ORDER BY STOPKEY       |           |     1 |    28 |     5  (20)| 00:00:01|
    |   4 |     TABLE ACCESS BY INDEX ROWID| CUSTOMERS |     1 |    28 |     4   (0)| 00:00:01|
    |*  5 |      DOMAIN INDEX              | COMP_IND  |       |       |     4   (0)| 00:00:01|
    -------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    -------------------------------------------------------------------------------------------
       1 - filter(ROWNUM<10)
       3 - filter(ROWNUM<10)
       5 - access("CTXSYS"."CONTAINS"("CUST_FIRST_NAME",'A% or D% or N% or B%',1)>0)
    
    Da weniger oder keine DOCID->ROWID Transformationen für Sätze, die nicht in der finalen Ergebnisliste sind, notwendig sind, erhalten wir einen effizienteren Zugriff. Generell zeigt sich, dass grosse Ergebnismengen im Textindex in Verbindung mit den stark nachfilternden Indizes, am Besten im CDI abgebildet werden sollten.
    Mehr zur Tipps und Tricks in einem der nächsten Blogs.....

    Dienstag, 28. Juli 2009

    CTX_QUERY.EXPLAIN: Erklärungen für eine TEXT-Abfrage

    Vor einiger Zeit hatten wir ein Blog Posting zum Thema "Fuzzy-Suche". Darin könnt Ihr nachlesen, wie eine Ähnlichkeitssuche mit Oracle TEXT funktioniert und wie Ihr sie parametrisieren könnt. Wie beschrieben, führt Oracle TEXT eine sog. Termexpansion durch. Die Suchabfrage wird also zunächst um Tokens aus dem Textindex, die dem gesuchten Begriff ähnlich sind, erweitert und anschließend wird mit dieser Token-Liste eine OR-Suche durchgeführt. Die Parameter des FUZZY-Operators steuern diese Termexpansion.
    Nun wäre es schön, wenn man sich ansehen könnte, was er da tut - welche Tokens also in die Liste mit aufgenommen werden. Und genau das ist mit der Prozedur CTX_QUERY.EXPLAIN möglich (übrigens nicht nur für die Fuzzy-Suche, sondern für alle TEXT-Abfragen). Die EXPLAIN-Funktion generiert eine Art "Ausführungsplan" für den Oracle TEXT Index.
    Zunächst benötigen wir eine EXPLAIN TABLE - dort schreibt die Prozedur die Erklärungen zu einer Textquery hinein. In der Oracle-Dokumentation "Text Reference" findet Ihr Erlärungen zu Aufbau und Inhalt. Erzeugt wird sie mit diesem CREATE TABLE-Kommando:
    create table meine_explain_tabelle(
      explain_id  varchar2(30),
      id          number,
      parent_id   number,
      operation   varchar2(30),
      options     varchar2(30),
      object_name varchar2(64),
      position    number,
      cardinality number
    );
    
    Wenn Ihr die Tabelle erstellt habt, könnt Ihr euch eine Textquery beschreiben lassen.
    begin
      ctx_query.explain(
        index_name    => 'IDX_DOKUMENTE',
        text_query    => '?sptial or geodaten',
        explain_table => 'MEINE_EXPLAIN_TABELLE'
      );
    end;
    /
    
    Anschließend stehen die Erklärungen in der erzeugten EXPLAIN-Tabelle. Wichtig ist der Parameter SHARELEVEL, der in diesem Aufruf nicht angegeben wurde. Der Default ist "0" (Null), was bedeutet, dass die Tabelle vorher leergeräumt wurd (TRUNCATE). Wenn Ihr EXPLAIN-Ergebnisse aufheben möchtet, setzt den Parameter auf "1" - zusätzlich benötigt Ihr dann eine EXPLAIN_ID, damit Ihr die Erklärungen später wiederfinden könnt.
    Die Einträge in der Tabelle sind hierarchisch organisiert - am besten fragt Ihr sie daher mit einem START WITH - CONNECT BY wie folgt ab.
    select
      lpad(' ',level * 2)|| to_char(id, '99') id, 
      operation,
      options,
      object_name,
      position
    from meine_explain_tabelle
    start with parent_id =0 
    connect by parent_id = prior id
    /
    
    Das Ergebnis sieht dann etwa so aus
    ID         OPERATION       OPTIONS    OBJECT_NAME     POSITION 
    ---------- --------------- ---------- --------------- --------
        1      OR                                                1
          2    EQUIVALENCE     (?)        sptial                 1
            3  WORD                       Spatial                1
            4  WORD                       special                2
            5  WORD                       Special                3
            6  WORD                       Spezial                4
            7  WORD                       Spiel                  5
            8  WORD                       spielt                 6
            9  WORD                       spielte                7
           10  WORD                       Spitze                 8
           11  WORD                       sptial                 9
         12    WORD                       geodaten               2
    
    Man erkennt sehr schön die ähnlichen Worte, die Oracle TEXT in die Abfrage eingebunden hat; man könnte nun, wie im Blog Posting zur "Fuzzy-Suche" beschrieben, das Schlüsselwort FUZZY nutzen und parametrisieren: Wie versuchen also die Abfrage FUZZY(sptial,70,5) or geodaten. Die in Frage kommenden Wörter müssen sich also ähnlicher sein (Wert "70" im Gegensatz zum Default von "60") und es werden maximal fünf Wörter in die Termexpansion einbezogen. Das Ergebnis ...
    ID         OPERATION       OPTIONS    OBJECT_NAME     POSITION 
    ---------- --------------- ---------- --------------- -------- 
        1      OR                                                1
          2    EQUIVALENCE     (?)        sptial                 1
            3  WORD                       Spatial                1
            4  WORD                       sptial                 2
          5    WORD                       geodaten               2
    
    Und wenn man nach "Spatial" im Zusammenhang mit "Geodaten" sucht, erkennt man sofort, dass diese Suche viel zielgenauer ist. Gerade wenn es um das Spielen mit den Parametern für eine FUZZY-Suche geht, ist die EXPLAIN-Funktion eine wertvolle Hilfe.
    Viel Spaß beim Ausprobieren!

    Dienstag, 7. Juli 2009

    Mixed Queries in 11g

    MDATA Sections sind in 10g eingeführt worden, um gemischte Abfragen (auch mixed queries genannt) - also Abfragen mit Text- und relationalen Anteilen, besser handhaben zu können. Generell können damit kurze Textfelder(sogenannte Metadaten), die als Ganzes im Textindex indiziert wurden, einfach abgefragt werden. Mehr Informationen zur MDATA-Nutzung finden Sie Metadatensuche mit MDATA Blog und im MData Section und MULTI_COLUMN_DATASTORE Blog. Abfragen auf die Metadaten wie prod_list_price und flag sehen dann beispielsweise folgendermassen aus:
    
    SELECT prod_id, prod_list_price, prod_desc
    FROM products
    WHERE contains (prod_desc, 'Card AND MDATA(prod_list_price, 69.99)
    AND MDATA(flag,N)') > 0;
    
    
    Ein wichtiger Unterschied zu gewohntem Sectionverhalten ist, dass MDATA Bereiche transaktionell verändert werden können, ohne den Rest des Index zu beeinträchtigen bzw. zu re-indizieren. Nachteile dieser Technologie ist die Tatsache, dass nur auf Gleichheit abgfragt werden kann und zusätzlich die MDATA Werte als einziges Token behandelt und minimal normalisiert werden können (Whitespace-Entfernung etc). Daher ist in 11g eine weitere Form der Section Suche eingeführt worden - die SDATA Section (SDATA steht dabei für Structured Data). Die Indizierung der SDATA Section erlaubt Operationen wie Range Scans, Nutzung von Funktionen, Projektionen usw. So können neue Kombinationen aus Text und strukturierte Anteilen abgefragt werden. Um die Unterschiede aufzuzeigen, nehmen wir das Beispiel aus MData Section und MULTI_COLUMN_DATASTORE und verwenden dabei die neue SDATA Section. Wir belassen den MULTI_COLUMN_DATASTORE my_multi_pref, und erzeugen eine SDATA Section mit Namen prod_list_price.
    
    connect sh/sh
    execute ctx_ddl.drop_section_group('my_seg');
    begin
    ctx_ddl.create_section_group(group_name=>'my_seg',group_type=>'basic_section_group');
    ctx_ddl.add_sdata_section('my_seg','PROD_LIST_PRICE','prod_list_price', 'NUMBER');
    end;
    /
    DROP INDEX mdata_index;
    DROP INDEX sdata_index;
    CREATE INDEX sdata_index ON products(prod_desc)
    INDEXTYPE IS ctxsys.context
    PARAMETERS ('DATASTORE my_multi_pref SECTION GROUP my_seg sync (on commit)');
    
    SELECT err_text FROM ctx_user_index_errors WHERE err_index_name = 'SDATA_INDEX';
    no rows selected
    
    
    Folgende Abfrageart mit dem SDATA-Operator ist nun möglich.
    
    SELECT prod_id, prod_list_price, prod_desc FROM products
    WHERE contains (prod_desc, 'Card AND SDATA(prod_list_price >= 69.99)') > 0;
    
       PROD_ID PROD_LIST_PRICE
    ---------- ---------------
    PROD_DESC
    --------------------------------------------------------------------------------
            25          112.99
    SIMM- 8MB PCMCIAII card
    
            26          149.99
    SIMM- 16MB PCMCIAII card
    
           138           69.99
    256MB Memory Card
    
    
    Untersucht man genauer die neuangelegten Objekte, wird man feststellen, dass ein zusätzliches Indexsegment, eine IOT-Tabelle mit Namen DR$SDATA_INDEX$S, erzeugt wurde.

    Nun stellt sich die Frage, ob das Ganze nicht einfacher zu bewerkstelligen ist, ohne zusätzlich Sections zu verwenden. Die Antwort dazu gibt die neue Composite Domain Index Technologie. Mit nur einem CREATE INDEX-Kommando ohne zusätzliche SDATA Sections kann dies erreicht werden. Folgendes Kommando zeigt die Implementierung in unserem Fall. Die FILTER BY Klausel ermöglicht dabei die Teilabfrage auf die Spalte PROD_LIST_PRICE vollständig im Text-Index durchzuführen.
    
    DROP INDEX sdata_index;
    
    CREATE INDEX comp_index ON products(prod_desc)
    INDEXTYPE IS ctxsys.context
    FILTER BY prod_list_price;
    
    
    Ein kurzer Blick auf die erzeugten Objekte, gibt den Hinweis darauf, dass die SDATA Technologie offensichtlich als Grundlage dient, da wir nun eine zusätzliche IOT-Tabelle DR$COMP_INDEX$S besitzen. Die Abfragen können nun im gewohnten Stil ohne Verwendung von speziellen Operatoren verwendet werden.
    
    SELECT prod_id, prod_list_price, prod_desc 
    FROM products
    WHERE contains (prod_desc, 'Card')>0  AND prod_list_price <= 69.99;
    
       PROD_ID PROD_LIST_PRICE
    ---------- ---------------
    PROD_DESC
    --------------------------------------------------------------------------------
           136           32.99
    64MB Memory Card
    
           137           52.99
    128MB Memory Card
    
           138           69.99
    256MB Memory Card
    
     
    Mehr zur Composite Domain Index Technik in einem der nächsten Blogs.....

    Montag, 22. Juni 2009

    Wie ähnlich soll es sein ...? Ein paar Worte zum FUZZY-Operator

    Es ist weitgehend bekannt, dass Oracle TEXT eine Ähnlichkeitssuche mit dem Fuzzy-Operator unterstützt. Die einfachste Variante ist die Verwendung des Fragezeichens ?. Der Fuzzy-Operator ist gut geeignet, um mit etwaigen Rechtschreibfehlern in den Dokumenten umzugehen. Sucht man bspw. nach ?Spatial, so findet der Index auch Dokumente, in denen fälschlicherweise "Sptial" geschrieben wurde.
    select dateiname, score(0) from dokumente where contains (content, '?Spatial', 0) > 0
    
    Diese Ähnlichkeitssuche kann übrigens auch parametrisiert werden: Neben dem Fragezeichen steht auch das Schlüsselwort FUZZY zur Verfügung. Oder anders ausgedrückt: Obige SQL-Abfrage ließe sich auch so schreiben:
    select dateiname, score(0) from dokumente where contains (content, 'FUZZY(Spatial, 60, 100, N)'), 0) > 0
    
    Und mit diesen Parametern kann man das Verhalten des FUZZY-Operators nun steuern. Der erste Parameter legt fest, wie ähnlich die Tokens im Dokument dem Suchbegriff sein müssen. Erlaubt sind Werte von 1 bis 80. Je niedriger Ihr den Wert ansetzt, desto mehr Begriffe kommen in Frage; desto mehr Dokumente werden also gefunden. Allerdings stellt sich die Frage, wie relevant die Dokumente bei sehr niedrigen Grenzen noch sind.
    Der zweite Parameter legt fest, wieviele Werte überhaupt in die Termexpansion einbezogen werden. Dazu kurz einige Worte: Oracle TEXT führt die Fuzzy-Suche über eine Termexpansion durch; es werden also zunächst aus der Token-Tabelle ($I) die ähnlichen Tokens herausgesucht (der erste Parameter legt, wie gesagt, fest, wie ähnlich die Tokens sein müssen). Mit den so gefundenen Tokens wird dann eine ODER Suche durchgeführt.
    Mit dem zweiten Parameter legt man also fest, wieviele ähnliche Wörter maximal einbezogen werden sollen. Erlaubt sind Werte zwischen 1 und 5000. Ein Wert von 20 würde bewirken, dass maximal 20 ähnliche Wörter in der Suche berücksichtigt werden, auch wenn anhand des ersten Parameters mehr in Frage kämen. Hier gilt also: Je höher der Wert gesetzt wird, desto mehr Dokumente werden tendenziell gefunden ...
    Der letzte Parameter wirkt sich nur auf den Score aus, den ein Dokument im Relevanz-Ranking bekommt. Mit einem W werden die Scores anhand der Ähnlichkeit der Suchwörter gewichtet; mit einem N geschieht das nicht. Ein W führt zu tendenziell höheren Scores.
    Mit diesen Parametern könnt Ihr also spielen, um mit der Fuzzy-Suche mehr oder weniger Ergebnisse zu bekommen. Was konkret gebraucht wird, hängt von den Anforderungen des Projekts ab ... zur Verdeutlichung hier nochmals zwei Extrembeispiele. Das erste ist zwar "formal" eine Ähnlichkeitssuche, aber die Parameter "würgen" die Fuzzy-Logik weitgehend ab.
    select dateiname, score(0) from dokumente 
    where contains(content, 'fuzzy(sptial, 80, 1, W)',0) > 0;
    
    Das zweite bohrt die Grenzen so weit auf, dass sehr viele Dokumente in Frage kommen ...
    select dateiname, score(0) from dokumente 
    where contains(content, 'fuzzy(sptial, 1, 5000, W)',0) > 0;
    
    Dies lässt sich auch sehr gut mit Query Relaxation verbinden. In einer ersten Stufe würde ohne den Fuzzy-Operator suchen, in einer nächsten Stufe dann mit dem Fuzzy-Operator, aber eher restriktiven Kriterien und danach mit sehr weit gehenden Parametern. Query Relaxation arbeitet die Stufen dann bekanntlich so lange ab, bis genügend Treffer gefunden sind - mehr dazu im Blog Posting.

    Montag, 8. Juni 2009

    USER_DATASTORE ... indiziert wirklich alles

    Eine der interessanten Eigenschaften des Oracle TEXT-Index ist, dass die zu indizierenden Dokumente nicht nur einfach aus einer Tabellenspalte, sondern aus unterschiedlichen Data Stores kommen können.
    • DIRECT_DATASTORE: der "Normalfall"
    • MULTICOLUMN_DATASTORE: Mehrere Tabellenspalten (dazu hatten wir schon Blog-Postings
    • FILE_DATASTORE: Die Dokumente liegen im Dateisysten; die Tabelle enthält für jedes Dokument einen vollständigen, absoluten Pfad
    • URL_DATASTORE: Die Dokumente liegen im "Netzwerk"; die Tabelle enthält für jedes Dokument einen vollständigen, absoluten URL (FTP oder HTTP)
    • DETAIL_DATASTORE: Die Dokumente liegen in einer anderen Tabelle, welche mit der zu indizierenden in einer Master-Detail-Beziehung steht
    • NESTED_DATASTORE: Die Dokumente liegen in nicht direkt in der zu indizierenden Tabelle, sondern in einer Nested Table (dürfte selten vorkommen).
    Und schließlich gibt es den USER_DATA_STORE, der quasi "alles kann". Und um den soll es in diesem Posting auch gehen. Als Ausgangspunkt haben wir eine Tabelle mit Dokumenten und diese sollen nicht direkt indiziert werden, sondern über einen USER_DATA_STORE. Warum? Weil wir über eine zusätzliche Spalte (INDEX_DOCUMENT) kontrollieren möchten, ob die Dokumente indiziert werden sollen oder nicht. Beginnen wir mit dem Tabellenaufbau ...
    SQL> desc dokumente
     Name                                      Null?    Typ
     ----------------------------------------- -------- ---------------------
    
     ID                                                 NUMBER
     FILENAME                                           VARCHAR2(2000)
     DOCUMENT                                           BLOB
     INDEX_DOCUMENT                                     CHAR(1)
    
    Nun geht es ans Erstellen des Textindex mit dem User Datastore. Ein User Datastore bedeutet nichts weiter als dass der Index seine Dokumente von einer PL/SQL-Prozedur zugewiesen bekommt und diese eben nicht direkt aus der Tabelle holt. Da man in der PL/SQL-Prozedur programmieren kann, was man möchte, ist das Verhalten des User Datastore auch sehr individuell. Dieses Beispiel soll (wie gesagt), das Dokument normal indizieren, wenn die Spalte INDEX_DOCUMENT auf "Y" steht und gar nicht indizieren, wenn die Spalte auf "N" steht. Beginnen wir mit der PL/SQL-Prozedur:
    create or replace procedure dokument_uds_proc (
      rid  in              rowid,
      tlob in out NOCOPY   blob    
    ) is
    begin
      begin
        select document into tlob
        from dokumente where rowid = rid and index_document = 'Y';
      exception
        when NO_DATA_FOUND then tlob := null;
      end;
    end;
    /
    
    Wichtig an dieser Prozedur ist die Signatur, diese muss genau so aussehen, wie in diesem Beispiel vorgegeben. Die zu indizierende ROWID wird in die Prozedur hineingegeben, das zu indizierende Dokument kommt als OUT-Parameter wieder zurück. Man sieht, dass man das Dokument selbst nun beliebig zusammensetzen kann. Die Inhalte können sehr wohl aus verschiedensten Tabellen oder anderen Datenquellen kommen.
    Aber bis jetzt ist die Prozedur nur eine normale PL/SQL-Prozedur. Damit sie für etwas ORACLE Text etwas bewirken kann, muss sie zunächst in Oracle TEXT bekannt gemacht werden; die folgenden PL/SQL-Blöcke richten den User Datastore im Dictionary von Oracle TEXT ein.
    begin
      ctx_ddl.drop_preference('DOKUMENT_UDS');
    end;
    /
    sho err
    
    begin
      ctx_ddl.create_preference('DOKUMENT_UDS','user_datastore');
      ctx_ddl.set_attribute('DOKUMENT_UDS','procedure','dokument_uds_proc');
      ctx_ddl.set_attribute('DOKUMENT_UDS','output_type','blob_loc');
    end;
    /
    sho err
    
    Bevor der Index nun erzeugt wird, fehlt noch eine Kleinigkeit: Der Index muss in zwei Fällen aktualisiert werden: Erstens wenn sich das Dokument ändert und zweitens wenn die Spale INDEX_DOCUMENT verändert wird. Wenn der Index also auf die Spalte INDEX_DOCUMENT gelegt wird, benötigen wir noch einen Trigger, der UPDATE-Operationen in DOCUMENT auf die Spalte INDEX_DOCUMENT überträgt:
    create or replace trigger trg_uds_document
    before update on dokumente
    for each row
    begin
      :new.index_document := :new.index_document;
    end;
    /
    
    Nun kann der Index erstellt werden. Wie schon beschrieben, wird der Index auf die Spalte INDEX_DOCUMENT gelegt:
    create index idx_text_dokumente
    on dokumente (index_document)
    indextype is ctxsys.context
    parameters ('filter ctxsys.auto_filter
                 datastore dokument_uds
                 memory 200M
                 transactional')
    /
    
    Wenn alle Einträge ein "N" in der Spalte INDEX_DOCUMENT haben, ist der Index nach Erstellung immer noch leer. Setzt man eine oder mehrere Zeilen auf "Y", so können diese anschließend dank des TRANSACTIONAL-Parameters (Posting!) gefunden werden, das dauert aber sehr lange. Kein Wunder, denn der Index ist noch nicht synchronisiert; die Dokumente in der Pending-Tabelle werden also on-the-fly durchsucht und dabei muss natürlich auch die PL/SQL-Prozedur des User Datastore durchlaufen werden. Nach einem CTX_DDL.SYNC_INDEX('IDX_TEXT_DOKUMENTE') befinden sich die Einträge denn auch im Textindex.
    Dieses einfache Beispiel zeigt, wie man einen User Datastore nutzen kann. Wichtig ist, dass hier keine Grenzen existieren; die PL/SQL-Prozedur des UDS ist für Oracle TEXT eine "Black Box"; man kann dort hineinprogrammieren, was man möchte ...

    Freitag, 15. Mai 2009

    MDATA Section und MULTI_COLUMN_DATASTORE

    Ab Oracle Database 10g gibt es ein neues „Section“ Feature - die neue MDATA Section, um Dokument-Metadaten separat zu handhaben. Die MDATA Section ist vergleichbar mit einer Zone- oder Field- Section, d.h. das Dokument muss eine interne Struktur („Section“) wie HTML oder XML besitzen. Ein Beispiel dazu findet sich in folgendem Blog. Der MULTI_COLUMN_DATASTORE (siehe dazu auch Blog) führt die Spalten einer Tabellen zu einem Dokument zusammen und trennt die Informationen durch XML Tags.
    Bringt man nun diese beiden Techniken zusammen, so lassen sich Spalten im MULTI_COLUMN_DATASTORE zusammenführen und danach als MDATA Metadaten Sections kennzeichen und nutzen.

    Für das folgenden Beispiel verwenden wir die Tabelle PRODUCTS im Schema SH. Zu beachten ist, um das Paket CTX_DDL nutzen zu können, benötigt man die Rolle CTXAPP (oder ein explizites EXECUTE-Privileg).
    Zuerst legen wir den MULTI_COLUMN_DATASTORE mit folgenden Statements an. Die Spalten "PROD_NAME", "PROD_STATUS", "PROD_LIST_PRICE" und "PROD_DESC" werden für den MULTI_COLUMN_DATASTORE konfiguriert:
    
    connect sh/sh
    execute ctx_ddl.drop_preference (preference_name =>'MY_MULTI_PREF');
    begin 
      ctx_ddl.create_preference(preference_name =>'MY_MULTI_PREF',
                                object_name => 'multi_column_datastore' );
      ctx_ddl.set_attribute(preference_name => 'MY_MULTI_PREF',
               attribute_name  => 'COLUMNS',
               attribute_value => 'PROD_NAME, PROD_STATUS, PROD_LIST_PRICE, PROD_DESC');
    end;
    /
    sho err
    
    
    Im nächsten Schritte werden die MDATA Sections "PROD_NAME", "PROD_STATUS", "PROD_LIST_PRICE" mit ADD_MDATA_SECTION konfiguriert. Die Section "PROD_DESC" wird als eine FIELD-Section angelegt. Zusätzlich zu den existierenden Sections erzeugen wir eine neue Section "flag", die neue Informationen, die nicht in der Tabelle enthalten sind, aufnehmen kann. Eine typische Verwendung wäre zum Beispiel eine zusätzliche Kennzeichnung der Daten durch spezielle Zugriffsrechte.
    
    execute ctx_ddl.drop_section_group('my_seg');
    
    execute ctx_ddl.create_section_group(group_name=>'my_seg',   
                                         group_type=>'basic_section_group');
    execute ctx_ddl.add_mdata_section(group_name=>'my_seg', section_name=>'PROD_NAME', 
                                      tag=>'prod_name');
    execute ctx_ddl.add_mdata_section(group_name=>'my_seg', section_name=>'PROD_STATUS', 
                                      tag=>'prod_status');
    execute ctx_ddl.add_mdata_section(group_name=>'my_seg',  
                                      section_name=>'PROD_LIST_PRICE', 
                                      tag=>'prod_list_price');
    execute ctx_ddl.add_field_section(group_name=>'my_seg', section_name=>'PROD_DESC', 
                                      tag=>'prod_desc', visible=>true);
    execute ctx_ddl.add_mdata_section(group_name=>'my_seg', section_name=>'flag', 
                                      tag=>'flag');
    
    sho err
    
    
    Nun kann der Index mit dem DATASTORE my_multi_pref und der SECTION GROUP my_seg erzeugt werden. Die Synchronisierung soll dabei automatisch nach jedem COMMIT erfolgen.
    
    DROP INDEX mdata_index;
    
    CREATE INDEX mdata_index ON products(prod_desc)
    indextype IS ctxsys.context 
    parameters ('DATASTORE my_multi_pref SECTION GROUP my_seg sync (on commit)');
    
    SELECT err_text FROM ctx_user_index_errors WHERE err_index_name = 'MDATA_INDEX';
    no rows selected
    
    
    Folgende Abfrageart mit dem MDATA-Operator ist nun möglich.
    
    SELECT prod_id, prod_list_price, prod_desc FROM products 
    WHERE contains (prod_desc, 'Card AND MDATA(prod_list_price, 69.99)') > 0;
    
       PROD_ID PROD_LIST_PRICE
    ---------- ---------------
    PROD_DESC
    --------------------------------------------------------------------------------
           138           69.99
    256MB Memory Card
    
    
    Die Tokentypes größer gleich 400 in der $I-Tabelle zeigen an, dass MDATA-Sectionen vorhanden sind, da MDATA Tokens mit Tokentype zwischen 400 und 499 gekennzeichnet sind. Gibt es zusätzlich negative Werte (kleiner gleich 400), die sogenannten "Delta-Zeilen", ist der Index nicht optimiert. Dies wird nun mit folgender Abfrage verifiziert:
    
    SELECT token_type, count(*) 
    FROM dr$mdata_index$i 
    WHERE token_type<= -400 OR token_type>=400 GROUP BY token_type;
    TOKEN_TYPE   COUNT(*)
    ---------- ----------
           400         71
           402         42
           401          1
    
    
    Die 3 Zeilen zeigen, dass 3 verschiedene MDATA Tokentypes mit 400, 401 und 402 existieren. Diese stehen für die Sections "PROD_NAME", "PROD_STATUS" und "PROD_LIST_PRICE".
    Nun werden manuell Werte mit der Prozedur ADD_MDATA in die "flag" Metadaten-Section eingefügt. Dazu erzeugen wir mit folgendem Skript die entsprechenden Aufrufe, um "flag"-Einträge mit Wert "J" bzw. "N" je nach Größe des PROD_LIST_PRICE zu generieren.
    
    spool liste.lst
    SELECT 
    'execute ctx_ddl.add_mdata'||'(''MDATA_INDEX'''||','||'''flag'''||','||'''N'''||','||''''||rowid||''''||')'||';' 
    FROM products WHERE prod_list_price<=70;
    SELECT 'execute ctx_ddl.add_mdata'||'(''MDATA_INDEX'''||','||'''flag'''||','||'''J'''||','||''''||rowid||''''||')'||';'
    FROM products WHERE prod_list_price>70;
    COMMIT;
    spool off
    
    -- start des ablaufbaren Skripts mit
    start liste.lst
    execute ctx_ddl.add_mdata('MDATA_INDEX','flag','N','AAAew2AAEAADLwUAAG');
    execute ctx_ddl.add_mdata('MDATA_INDEX','flag','N','AAAew2AAEAADLwUAAJ');
    execute ctx_ddl.add_mdata('MDATA_INDEX','flag','N','AAAew2AAEAADLwUAAK');
    ...
    
    
    ADD_MDATA ist transaktional und muss mit COMMIT oder ROLLBACK abgeschlossen werden. Danach ist die "flag" Section in Abfragen wie folgt nutzbar:
    
    SELECT prod_id, prod_list_price, prod_desc 
    FROM products WHERE contains (prod_desc, 
    'Card AND MDATA(prod_list_price, 69.99) AND MDATA(flag,N)') > 0;
       PROD_ID PROD_LIST_PRICE
    ---------- ---------------
    PROD_DESC
    --------------------------------------------------------------------------------
           138           69.99
    256MB Memory Card
    
    
    SELECT prod_id, prod_list_price, prod_desc 
    FROM products WHERE contains (prod_desc, 
    'Card AND MDATA(prod_list_price, 69.99) AND MDATA(flag,J)') > 0;
    
    no rows selected
    
    
    Der Index ist allerdings noch nicht optimiert, wie die Überprüfung der $I Tabelle zeigt:
    
    SELECT token_type, count(*)
    FROM dr$mdata_index$i 
    WHERE token_type<= -400 OR token_type>=400 GROUP BY token_type;
    
    TOKEN_TYPE   COUNT(*)
    ---------- ----------
           400         71
           403          2
           402         42
          -403         70
           401          1
    
    
    Tokenweise Optimierung ist mit folgender speziellen OPTIMIZE_INDEX Prozedur möglich:
    
    execute ctx_ddl.optimize_index(idx_name=> 'MDATA_INDEX',
                  optlevel=> ctx_ddl.optlevel_token_type, 
                  token_type=> ctx_report.token_type('MDATA_INDEX', 'mdata flag')); 
    
    
    Der Index liegt nun optimiert vor. Das Ergebnis in der Token-Tabelle sieht nun folgendermassen aus:
    
    SELECT token_type, count(*)
    FROM dr$mdata_index$i 
    WHERE token_type<= -400 OR token_type>=400 GROUP BY token_type;
    
    TOKEN_TYPE   COUNT(*)
    ---------- ----------
           400         71
           403          2
           402         42
           401          1
    
    
    Zusätzlich zu den MDATA Sections gibt es in 11g SDATA Sections, die einige der Einschränkungen von MDATA aufheben. Darüberhinaus eröffnet der Composite Domain Index in 11g ganz neue Möglichkeiten, um die Problematik der Mixed Queries zu lösen. Mehr dazu in einer der nächsten Postings...

    Beliebte Postings