Der CTXCAT-Index

Neben dem "normalen" Context Index gibt es schon seit geraumer Zeit den kaum bekannten "kleinen Bruder" CTXCAT. Dieses Posting möchte ich daher gerne dem CTXCAT-Index widmen. Möchte man die Unterschiede in einem Satz herausarbeiten, so bietet der CTXCAT-Index zunächst mal weniger Features, ist für kleinere Dokumente gedacht und arbeitet synchron, ist also einfacher zu verwalten. Im einzelnen ...

• Der CTXCAT Index ist für kleinere Textfragmente gedacht - in der Dokumentation ist von "wenigen Zeilen" die Rede; um das auf den Punkt zu bringen; das Maximum sollten so 50 Worte sein ...
• Der Index arbeitet komplett synchron - per DML gemachte Änderungen sind sofort im Index sichtbar. Der Prozess des Index-Synchronisierens und -Optimierens fällt hier also weg.
• Es werden weniger "Features" unterstützt - so gibt es keine Data Stores wie beim CONTEXT-Index. Die zu indizierenden Daten müssen also genau so in der Tabellenspalte drinstehen.
• XML wird nicht unterstützt, da CTXCAT keine Section Groups kennt
• CTXCAT unterstützt allerdings sog. Sub-Indexes, mit denen man Mixed Queries unterstützen kann. Eine Mixed Query ist eine Abfrage, die sowohl relationale (strukturierte) als auch Volltextkriterien enthält.

Der CTXCAT-Index wird genauso angelegt wie ein CONTEXT-Index, nur mit einem anderen Indextypen ... Als Beispiel nehmen wir eine Art Umfragetabelle, in der die Ergebnisse einer Umfrage zur "Servicequalität" gespeichert werden ...

create table umfrageergebnis(
  id        number(10),
  altersang number(3),
  note      number(1),
  kommentar varchar2(1000)
);

Der Index kann sofort erzeugt werden ...

create index suche_idx on UMFRAGEERGEBNIS(KOMMENTAR)
indextype is ctxsys.ctxcat;

Wie der CONTEXT-Index unterstützt auch der CTXCAT-Index linguistische Features. Das folgende Beispiel legt den Index explizit als Case-Sensitiv fest und definiert den Bindestrich als sog. Printjoin.

begin
  ctx_ddl.create_preference('CAT_LEXER','BASIC_LEXER');
  ctx_ddl.set_attribute('CAT_LEXER','MIXED_CASE','YES');
  ctx_ddl.set_attribute('CAT_LEXER','PRINTJOINS', '-');
end;
/

create index suche_idx on UMFRAGEERGEBNIS(KOMMENTAR)
indextype is ctxsys.ctxcat
parameters ('LEXER CAT_LEXER');

Stopwortlisten werden natürlich analog unterstützt. Nun kann man ein paar Dokumente einpflegen ...

insert into umfrageergebnis values (1, 35, 1, 'service war ausgezeichnet');
insert into umfrageergebnis values (2, 55, 3, 'ganz gut');
insert into umfrageergebnis values (3, 45, 5, 'ich wurde unfreundlich bedient');
insert into umfrageergebnis values (4, 42, 2, 'etwas unfreundlich, aber fehlerfrei und korrekt');

Und man kann sofort abfragen ...

select * from umfrageergebnis where catsearch(kommentar, 'unfreundlich', null) > 0;

        ID  ALTERSANG       NOTE KOMMENTAR
---------- ---------- ---------- ------------------------------------------------
         3         45          5 ich wurde unfreundlich bedient
         4         42          2 etwas unfreundlich, aber fehlerfrei und korrekt

Interessant wären nun die Mixed Queries. Angenommen, wir interessieren uns für die Ergebnisse, die aufgrund von "Unfreundlichkeit" schlecht waren. Also brauchen wir noch ein relationales Kriterium auf der Schulnote. Zunächst also den Index wieder droppen ...

drop index suche_idx;

Und nun erzeugen wir ein Index Set; dieses enthält die strukturierten "Unter-Indizes". Dem Index-Set wird dann ein Index auf die Spalten "Note", "Alter" und auf die Kombination "Note und Alter" hinzugefügt.

begin
  ctx_ddl.create_index_set('umfrage_iset');
  ctx_ddl.add_index('umfrage_iset','note'); 
  ctx_ddl.add_index('umfrage_iset','altersang'); 
  ctx_ddl.add_index('umfrage_iset','note, altersang');
end;

Danach den Index neu anlegen ...

create index suche_idx on UMFRAGEERGEBNIS(KOMMENTAR)
indextype is ctxsys.ctxcat
parameters ('LEXER CAT_LEXER INDEX SET UMFRAGE_ISET');

Nun wieder abfragen ... mit dem CATSEARCH-Operator ...

select * from umfrageergebnis where catsearch(kommentar, 'unfreundlich', 'note >= 4') > 0;

        ID  ALTERSANG       NOTE KOMMENTAR
---------- ---------- ---------- ------------------------------------------------
         3         45          5 ich wurde unfreundlich bedient

Schauen wir uns mal den Index genauer an. Wie der CONTEXT-Index besteht auch der CTXCAT-Index aus Tabellen, die ins Schema gelegt werden, und die mit dem Präfix DR$ beginnen. Nur ist es beim CTXCAT-Index genau eine-Tabelle ...

SQL> desc DR$SUCHE_IDX$I
 Name                                      Null?    Typ
 ----------------------------------------- -------- ------------------

 DR$TOKEN                                  NOT NULL VARCHAR2(64)
 DR$TOKEN_TYPE                             NOT NULL NUMBER(3)
 DR$ROWID                                  NOT NULL ROWID
 DR$TOKEN_INFO                             NOT NULL RAW(2000)
 ALTERSANG                                 NOT NULL NUMBER(3)
 NOTE                                      NOT NULL NUMBER(1)

Und hier seht Ihr, dass der CTXCAT-Index wesentlich simpler aufgebaut ist als der CONTEXT-Index. So werden ROWIDs direkt verwendet (der CONTEXT-Index generiert die kompakteren DOCIDs). Die soeben erzeugten "Sub-Indexes" führen dazu, dass zusätzliche Spalten angelegt werden. Das alles führt dazu, dass CTXCAT-Indizes recht viel Platz verbrauchen - die ROWIDs belegen 10 Byte und auch die Werte der zusätzlichen Spalten werden für jedes Wort (Token) dupliziert. Es ist einleuchtend, warum dieser Index nur für kleine Textfragmente geeignet ist - bei großen Dokumenten würde er schlicht explodieren ...

Hierzu ein kleiner Test. Wir haben eine Tabelle mit ca. 300.000 kleineren Texten (ein CTXCAT-Index kommt also in Frage). Die Tabelle ist (festgestellt mit diesem Skript) ca. 30MB groß.

select * from table(get_space_info('TEST_CTXCAT'));

SEGMENT_NAME                     COLUMN_NAME                    PARTITION_NAME                   SEGMENT_TYPE                     ALLOC_BYTES FREE_BYTES
-------------------------------- ------------------------------ -------------------------------- -------------------------------- ----------- ----------
TEST_CTXCAT                                                                                      TABLE                               31457280      65536

1 Zeile wurde ausgewählt.

Auf diese Tabelle legen wir nun einen CTXCAT-Index und einen CONTEXT-Index und stellen auch hierfür die jeweilige Größe fest. Zunächst der CTXCAT-Index (er besteht nur aus einer einzigen "DR$"-Tabelle.

select
  segment_name, 
  column_name, 
  segment_type, 
  alloc_bytes / 1048576 alloc_mb, 
  free_bytes / 1048576 free_mb 
from table(get_space_info('DR$IDX_TEST_CTXCAT$I'))

SEGMENT_NAME                     COLUMN_NAME                              SEGMENT_TYPE                     ALLOC_MB  FREE_MB
-------------------------------- ---------------------------------------- -------------------------------- -------- --------
DR$IDX_TEST_CTXCAT$I                                                      TABLE                               80,00     0,00
DR$IDX_TEST_CTXCAT$R             DR$ROWID                                 INDEX                               62,00     0,56
DR$IDX_TEST_CTXCAT$X             DR$TOKEN,DR$TOKEN_TYPE,DR$ROWID          INDEX                               59,00     0,14

Das wären also ca. 200MB für den CTXCAT-Index. Schauen wir uns den CONTEXT-Index an. Hierfür muss die GET_SPACE_INFO-Funktion für alle zum CONTEXT-Index gehörenden DR$-Tabellen ausgeführt werden - das wären die $I, die $N, die $R und die $K-Tabelle.

TABLE SEGMENT_NAME                     COLUMN_NAME                              SEGMENT_TYPE                     ALLOC_MB  FREE_MB
----- -------------------------------- ---------------------------------------- -------------------------------- -------- --------
$I    DR$IDX_TEST_CONTEXT$I                                                     TABLE                               11,00     0,95  
$I    SYS_LOB0000225699C00006$$        TOKEN_INFO                               LOBSEGMENT                           0,06     0,00
$I    DR$IDX_TEST_CONTEXT$X            TOKEN_TEXT,TOKEN_TYPE,TOKEN_FIRST, ...   INDEX                                0,19     0,03
$I    SYS_IL0000225699C00006$$                                                  LOBINDEX                             0,06     0,03

$N    SYS_IOT_TOP_225707               NLT_DOCID                                INDEX                                0,06     0,03

$R    DR$IDX_TEST_CONTEXT$R                                                     TABLE                                0,06     0,00
$R    SYS_LOB0000225704C00002$$        DATA                                     LOBSEGMENT                           6,00     0,13
$R    SYS_IL0000225704C00002$$                                                  LOBINDEX                             0,06     0,00

$K    SYS_IOT_TOP_225702               TEXTKEY                                  INDEX                                9,00     0,59

Zusammengezählt in etwa 25MB. Alle linguistischen Einstellungen Stopwortlisten, Lexer sind identisch. Man sieht hier sehr deutlich den Unterschied.

Wann ist also ein CTXCAT-Index sinnvoll ... ? Naja, vor allem dann, wenn Ihr kleine Datenmengen in der Tabellenspalte habt, auf einen ständig synchronen Volltextindex Wert legt und Mixed Queries benötigt. Letzteres ist ab Oracle11g allerdings auch mit dem CONTEXT Index möglich (Composite Domain Index). Wenn der Text aber in mehreren Spalten ist oder die Textfragmente größer werden, dann werden CTXCAT-Indizes schnell zu groß. Ein CONTEXT-Index mit Multi-Column Datastore wesentlich kompakter und effizienter. Der CTXCAT-Index ist halt, wie eingangs gesagt, der "kleine Bruder" des CONTEXT Index und eignet sich nur für bestimmte Fälle ...

Hochverfügbarer Textindex mit Schattenindex-Technologie

Heutzutage müssen auch Anwendungen mit Oracle Textindizes hochverfügbar sein. Allerdings kann schon ein REBUILD des Index dazu führen, dass der Index nicht mehr online zur Verfügung steht. Ein Neuaufbau des Index könnte z.B. aus mehreren Gründen nötig werden:

• Spezielle Index-Preferences bzw. -Optionen wie Lexereinstellungen sollen geändert werden oder Stoppwörter hinzugefügt werden.
• Aus Maintenance- und Performance-Gründen soll der Index neu aufgebaut werden - um z.B. eine stark vergrößerte $I Tabelle zu optimieren.

Ein REBUILD des Index ist bis einschliesslich Oracle Database 10g nicht immer vollständig online möglich. Eine mögliche Lösung wäre einen zusätzlichen zweiten Schatten-Index manuell zu erzeugen und diesen mitzupflegen. Die Tabelle könnte dabei folgendermassen aussehen:

CREATE TABLE mytable (text CLOB, dummy1 CHAR, dummy2 CHAR);

Da nur ein einziger Textindex auf einer Spalte möglich ist, wird die Indizierung über den Einsatz von USER_DATA_STORE bzw. MULTI_COLUMN_DATATORE gelöst. Diese erlauben es, einen Index auf der Spalte DUMMY1 zu erzeugen, aber die aktuellen zu indizierenden Daten werden von einer anderen Spalte z.B. TEXT geliefert. Die Abfragen sehen also folgendermassen aus:

... WHERE CONTAINS (dummy1, '') > 0

Im Falle eines Neuaufbaus steht die Spalte DUMMY2 zur Verfügung, die ihre Daten ebenfalls aus der Spalte TEXT erhält. Falls der Aufbau beendet ist, müssen die Queries folgendermassen geändert werden:

... WHERE CONTAINS (dummy2, '') > 0

In Oracle Database 11g ist es nun möglich, ein vollständiges REBUILD online durchzuführen - entweder in einem Ein-Schritt-Verfahren oder um mehr Kontrolle zu gewährleisten bzw. falls Partitionen verwendet werden als mehrstufiger Prozess. Die Idee dabei ist der Einsatz eines Schatten-Index (auch Shadow Index), der parallel zum urspünglichen Indexaufbau mitgeführt wird. Um die Nutzung an einem Beispiel zu demonstrieren, nehmen wir folgenden Index, der auf eine Tabelle SEC_TABLE und der Spalte TEXT erzeugt wurde als Grundlage.

CREATE INDEX sec_ind ON sec_table(text) INDEXTYPE IS ctxsys.context PARALLEL 4;
SELECT * FROM ctx_user_index_errors;
no rows selected

Nun wollen wir eine zusätzliche Spalte für Sprachen ergänzen und den folgenden Multi-Lexer nutzen.

execute ctx_ddl.create_preference('en_lx','basic_lexer');
execute ctx_ddl.set_attribute('en_lx','index_themes','yes');
execute ctx_ddl.create_preference('f_lx','basic_lexer');
execute ctx_ddl.set_attribute('f_lx','base_letter','yes');
execute ctx_ddl.create_preference('d_lx','basic_lexer');
execute ctx_ddl.set_attribute('d_lx','composite','german');
execute ctx_ddl.set_attribute('d_lx','mixed_case','yes');
execute ctx_ddl.set_attribute('d_lx','alternate_spelling','german');
begin
ctx_ddl.create_preference('multi_lx','multi_lexer');
ctx_ddl.add_sub_lexer('multi_lx','german','d_lx');
ctx_ddl.add_sub_lexer('multi_lx','french','f_lx');
ctx_ddl.add_sub_lexer('multi_lx','default','en_lx');
end;
PL/SQL procedure successfully completed.

ALTER TABLE sec_table ADD (lang varchar2(10) default 'de');

Nun erzeugen wir den Schattenindex in einem Einschrittverfahren. Laut Syntax ist zwar die Angabe einer DOP (Degree Of Parallelism) möglich, leider aber noch nicht im aktuellen Release verwendbar. Die Dokumentation gibt dazu folgenden Hinweis: "Reserved for future use. Specify the degree of parallelism. Parallel operation is not supported in the current release."

SQL> execute CTX_DDL.RECREATE_INDEX_ONLINE('SEC_IND','REPLACE LEXER multi_lx language column lang');
PL/SQL procedure successfully completed.

Schaut man zwischendurch in CTX_USER_INDEXES kann man den Aufbau des temporären Index (siehe Präfix RIO) monitoren.

SQL> SELECT idx_name, idx_table, idx_status FROM ctx_user_indexes;
IDX_NAME                       IDX_TABLE                      IDX_STATUS
------------------------------ ------------------------------ ------------
BASIC_IND1                     BASIC_TABLE1                   INDEXED
COMPRESS_IND                   COMPRESS_TABLE                 INDEXED
RIO$1880                       SEC_TABLE                      POPULATE
SEC_IND                        SEC_TABLE                      INDEXED

Nach Aufbau der Schattenindexstruktur erfolgt automatisch ein Austausch (auch Exchange). Textabfragen sind zu jedem Zeitpunkt möglich und dabei ist kein Eingriff in die Applikationen notwendig. Überprüft man zum Schluss die Struktur, kann man die Veränderungen beispielsweise mit CTX_REPORT.CREATE_INDEX_SCRIPT feststellen.

SQL> set long 10000
SQL> set pagesize 1000
SQL> SELECT ctx_report.create_index_script('SEC_IND') FROM dual;

CTX_REPORT.CREATE_INDEX_SCRIPT('SEC_IND')
--------------------------------------------------------------------------------
begin
  ctx_ddl.create_preference('"SEC_IND_DST"','DIRECT_DATASTORE');
end;
/
begin
  ctx_ddl.create_preference('"SEC_IND_FIL"','NULL_FILTER');
end;
/
begin
  ctx_ddl.create_section_group('"SEC_IND_SGP"','NULL_SECTION_GROUP');
end;
/
begin
  ctx_ddl.create_preference('"SEC_IND_LEX"','MULTI_LEXER');
end;
/
begin
  ctx_ddl.create_preference('"SEC_IND_L00"','BASIC_LEXER');
  ctx_ddl.set_attribute('"SEC_IND_L00"','INDEX_THEMES','YES');
end;
/
begin
  ctx_ddl.create_preference('"SEC_IND_LF"','BASIC_LEXER');
  ctx_ddl.set_attribute('"SEC_IND_LF"','BASE_LETTER','YES');
end;
/
begin
  ctx_ddl.create_preference('"SEC_IND_LD"','BASIC_LEXER');
  ctx_ddl.set_attribute('"SEC_IND_LD"','COMPOSITE','GERMAN');
  ctx_ddl.set_attribute('"SEC_IND_LD"','MIXED_CASE','YES');
  ctx_ddl.set_attribute('"SEC_IND_LD"','ALTERNATE_SPELLING','GERMAN');
end;
/
begin
  ctx_ddl.add_sub_lexer('"SEC_IND_LEX"','DEFAULT','"SEC_IND_L00"');
  ctx_ddl.add_sub_lexer('"SEC_IND_LEX"','FRENCH','"SEC_IND_LF"');
  ctx_ddl.add_sub_lexer('"SEC_IND_LEX"','GERMAN','"SEC_IND_LD"');
end;
/
begin
  ctx_ddl.create_preference('"SEC_IND_WDL"','BASIC_WORDLIST');
  ctx_ddl.set_attribute('"SEC_IND_WDL"','STEMMER','ENGLISH');
  ctx_ddl.set_attribute('"SEC_IND_WDL"','FUZZY_MATCH','GENERIC');
end;
/
begin
  ctx_ddl.create_stoplist('"SEC_IND_SPL"','BASIC_STOPLIST');
...

Möchte man das Ganze kontrolliert in einem 2 Schrittverfahren durchführen, kann man die folgenden beiden Prozeduren verwenden. Auch hier ist noch (im aktuellen Release) keine Parallelisierung möglich.

execute CTX_DDL.CREATE_SHADOW_INDEX (idx_name=>'SEC_IND',parameter_string=>'REPLACE LEXER multi_lx language column lang');
execute CTX_DDL.EXCHANGE_SHADOW_INDEX(idx_name=>'SEC_IND');

Um den Schattenindex zu löschen, kann man die spezielle Prozedur CTX_DDL.DROP_SHADOW_INDEX verwenden. Mehr Tipps und Tricks in einem der nächsten Blogs.....

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.

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

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!

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

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.