Posts mit dem Label doag werden angezeigt. Alle Posts anzeigen
Posts mit dem Label doag werden angezeigt. Alle Posts anzeigen

Montag, 17. Februar 2014

Welche DR$-Tabellen gibt es und wozu sind sie gut?

Den meisten Anwendern fällt recht schnell auf, dass sich nach dem Erstellen eines Oracle TEXT-Index einige zusätzliche Tabellen im Datenbankschema befinden.
SQL> create index FT_PO on PURCHASEORDER_TAB (XML_DOCUMENT)
  2  indextype is ctxsys.context
  3  /

Index wurde erstellt.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
PURCHASEORDER_TAB              TABLE
DR$FT_PO$N                     TABLE
DR$FT_PO$R                     TABLE
DR$FT_PO$K                     TABLE
DR$FT_PO$I                     TABLE
:                              :
Diese Tabellen enthalten den eigentlichen Textindex - meist sind es vier Tabellen, wie wir noch sehen werden, können es aber durchaus mehr werden. Das Namensschema ist immer gleich.
  • Es beginnt mit dem Präfix DR
  • Bei nichtpartitionierten Indizes folgt ein "$", bei partitionierten Indizes ein "#"
  • Dann folgt der Name des Volltextindex
  • Bei einem partitionierten Index schließt sich eine "Partitions-ID" direkt an den Indexnamen an
  • Es folgt wieder ein "$"
  • Abschließend folgt ein Suffix, welches die genaue Aufgabe der Tabelle bezeichnet - im folgenden werden die einzelnen Tabellentypen näher erläutert
Ein Oracle TEXT Index besteht aber immer aus wenigstens vier Tabellen - in Oracle12c können es aber bis zu neun werden.

$I: Token-Tabelle

 ----------------------------------------- -------- ------------------
 TOKEN_TEXT                                NOT NULL VARCHAR2(64)
 TOKEN_TYPE                                NOT NULL NUMBER(10)
 TOKEN_FIRST                               NOT NULL NUMBER(10)
 TOKEN_LAST                                NOT NULL NUMBER(10)
 TOKEN_COUNT                               NOT NULL NUMBER(10)
 TOKEN_INFO                                         BLOB
 ----------------------------------------- -------- ------------------
Die Token-Tabelle ist die eigentliche Indextabelle. Sie speichert die Tokens, also die einzelnen Wörter, die sich durch die Zerlegung des Fließtexts ergeben haben, in der Spalte TOKEN_TEXT ab. Die Spalte TOKEN_INFO enthält (in binärer Form) die Informationen, in welchen Dokumenten, an welchen Stellen das jeweilige Token vorkommt. Die übrigen Spalten sind Hilfsspalten, damit das Interpretieren des BLOB effizienter wird.
Wird eine Volltextabfrage ausgeführt, so schaut Oracle TEXT zuerst in diese Tabelle - anhand der Suchbegriffe werden die BLOBs aus TOKEN_INFO ausgelesen und weiter verarbeitet.

$R: Mapping von DOCID auf ROWID

 ----------------------------------------- -------- ------------
 ROW_NO                                    NOT NULL NUMBER(3)
 DATA                                               BLOB
 ----------------------------------------- -------- ------------
Oracle Text arbeitet intern nicht mit ROWIDs als Zeiger auf die Tabellenzeilen, sondern mit DOCIDs. Bei der Indizierung erhält die erste Zeile die DOCID 1 und dann wird weitergezählt. Der Grund dafür ist, dass ein Volltextindex eine invertierte Liste ist - im Gegensatz zu einem "normalen" Index zeigen mehrere Einträge auf eine Tabellenzeile. Die Verwendung von ROWIDs würde zu extrem großen Indizes führen, so dass man die sparsameren DOCIDs verwendet. Die bereits erwähnte Spalte TOKEN_INFO in der $I-Tabelle liefert also DOCIDs zurück. Damit Oracle TEXT aber Tabellenzeilen zurückliefern kann, braucht es eine Mapping-Tabelle - anhand der $R-Tabelle kann nun zu jeder gefundenen DOCID die ROWID herausgesucht werden. Die ROWID zeigt dann schließlich auf die gewünschte Tabellenzeile. Normalerweise ist ein Oracle Text Index so aufgesetzt, dass diese Tabelle immer im Hauptspeicher residiert, also zur Abfragezeit keine I/O-Last generiert.

$K: Mapping von ROWID auf DOCID

 ----------------------------------------- -------- ----------------------------
 DOCID                                              NUMBER(38)
 TEXTKEY                                   NOT NULL ROWID
 ----------------------------------------- -------- ----------------------------
Die $K-Tabelle ist das "Gegenstück" zur $R-Tabelle - anhand einer gegebenen ROWID findet sie die für den Textindex relevante DOCID heraus. Für Abfragen ist diese Tabelle weniger wichtig; benötigt wird sie aber bei DML-Operationen auf bereits indizierte Zeilen - wird beispielsweise eine Zeile mit SQL DELETE gelöscht, dann braucht Oracle TEXT die DOCID, um das Löschen auch im Textindex zu vermerken.

$N: Negativliste - enthält gelöschte Dokumente

 ----------------------------------------- -------- ----------------------------
 NLT_DOCID                                 NOT NULL NUMBER(38)
 NLT_MARK                                  NOT NULL CHAR(1)
 ----------------------------------------- -------- ----------------------------
Bekanntlich arbeitet Oracle TEXT asynchron (der Parameter TRANSACTIONAL bleibt hier außer Acht). Eine SQL INSERT-Anweisung bewirkt, dass die ROWID der Tabellenzeile in die PENDING-Tabelle eingetragen und damit "zur Indizierung vorgesehen" wird (die tatsächliche Indizierung erfolgt durch eine SYNC-Operation). Eine SQL DELETE Anweisung bewirkt, dass die Datenbank die DOCID ermittelt ($K-Tabelle) und diese dann in die Negativliste einträgt. Bei Abfragen ermittelt Oracle TEXT die Treffermenge ganz normal und filtert dann die Einträge der Negativliste heraus. Ein SQL UPDATE wird wie ein DELETE, gefolgt von einem INSERT, behandelt. Das hat den interessanten Effekt, dass neu eingefügte Dokumente erst nach dem SYNC sichtbar werden, gelöschte dagegen sofort verschwinden und mit SQL UPDATE veränderte Einträge ebenfalls bis zum nächsten SYNC "unsichtbar" werden.

$P: Hilfstabelle für den Substring-Index

 ----------------------------------------- -------- ----------------------------
 PAT_PART1                                 NOT NULL VARCHAR2(61)
 PAT_PART2                                 NOT NULL VARCHAR2(64)
 ----------------------------------------- -------- ----------------------------
Der Substring-Index muss mit Hife der Wordlist-Preference explizit eingeschaltet werden und unterstützt Abfragen mit einem Wildcard auf der linken Seite. Sucht man bspw. nach dem Text %DATENBANK, so ist klar, dass die $I-Tabelle (Spalte TOKEN_TEXT) prinzipiell komplett geparst werden muss - anstelle des Wildcard können ja beliebige Zeichen stehen. Bei sehr großen Indizes kann das ein Problem sein - schließlich wird auch die $I-Tabelle nun sehr groß. Der Substring-Index legt die $P-Tabelle als Hilfstabelle an - das Token ORACLEDATENBANK würde in dieser Tabelle wie folgt abgelegt:
PAT_PART1            PAT_PART2
-------------------- --------------------
ORACLEDATENB         ANK
ORACLEDATEN          BANK
ORACLEDATE           NBANK
ORACLEDAT            ENBANK
:                    :
ORACLE               DATENBANK
:
Ist diese Tabelle vorhanden, so werden die Suchbegriffe mit einer Wildcard links zuerst anhand dieser Tabelle über die Spalte PAT_PART2 aufgelöst - mit der sich ergebenden Tokenliste wird danach normal verfahren. Die Größe der $P-Tabelle liegt typischerweise zwischen 10 und 20 Prozent der Größe des Textindex.

$S: Hilfstabelle für strukturierte Elemente (SDATA-Sections)

 ----------------------------------------- -------- ----------------------------
 SDATA_ID                                  NOT NULL NUMBER
 SDATA_LAST                                NOT NULL NUMBER
 SDATA_DATA                                         RAW(2000)
 ----------------------------------------- -------- ----------------------------
In der $S-Tabelle werden strukturierte Bestandteile des Index gespeichert - dies ist seit Oracle11g mit den Composite Domain Indexes möglich. Oracle TEXT erlaubt auf diesen SDATA-Section zusätzlich auch Suchen wie <, >, BETWEEN und andere. SDATA-Sections haben einen Datentypen - unterstützt ist neben VARCHAR2 auch NUMBER oder DATE, was für Abfragen wichtig ist (intern speichert die Tabelle die Daten im RAW-Format ab). SDATA-Sections können entweder durch die Klauseln FILTER BY und ORDER BY im CREATE INDEX-Kommando oder durch die Erstellung einer Section Group mit expliziten SDATA Sections deklariert werden.

$G: Automatic Near Realtime Index (Oracle12c)

 ----------------------------------------- -------- ----------------------------
 TOKEN_TEXT                                NOT NULL VARCHAR2(64)
 TOKEN_TYPE                                NOT NULL NUMBER(10)
 TOKEN_FIRST                               NOT NULL NUMBER(10)
 TOKEN_LAST                                NOT NULL NUMBER(10)
 TOKEN_COUNT                               NOT NULL NUMBER(10)
 TOKEN_INFO                                         BLOB
 ----------------------------------------- -------- ----------------------------
Die $G-Tabelle wurde mit dem neuen Feature Automatic Near Realtime Indexing in Oracle12c eingeführt; sie sieht genauso aus wie die Token-Tabelle ($I-Tabelle). Das Feature wurde bereits in einem Blog Posting näher beschrieben.

$E: Hilfstabelle für XML-Namespaces (Oracle12c)

 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL VARCHAR2(12)
 NAMESPACE                                          VARCHAR2(4000)
 LNAME                                     NOT NULL VARCHAR2(256)
 NS                                                 VARCHAR2(100)
 ----------------------------------------- -------- ----------------------------
Ein weiteres neues Feature in Oracle12c ist die Unterstützung von XQuery Fulltext, also der Volltextsuche in XML-Dokumenten mit vollständiger Unterstützung des XML-Datenmodells. Die $E-Tabelle speichert dabei XML-Zusatzinformationen ab, die im "normalen" Volltextindex keinen Platz finden - hier sind vor allem die XML-Namespaces zu nennen, die denn auch vor Oracle12c nicht von Oracle TEXT unterstützt wurden. Das Feature wurde ebenfalls bereits in einem Blog-Posting näher beschrieben.

$D: Hilfstabelle für das Save-Copy Feature (Oracle12c)

 ----------------------------------------- -------- ----------------------------
 DOCID                                     NOT NULL NUMBER(10)
 ATTRIBUTES                                         BLOB
 DOC                                                BLOB
 CONFIG                                             VARCHAR2(2000)
 FLAG                                      NOT NULL NUMBER(5)
Auch das Save Copy Feature wurde in Oracle12c eingeführt. Ist es aktiviert, so speichert Oracle TEXT die Plaintext- oder gefilterte Version eines Binärformates wie PDF, DOC, PPT oder anderen in der $D-Tabelle ab. Zum Generieren von SNIPPETs, MARKUPs oder HIGHLIGHT-Dokumenten können dann die in dieser Tabelle abgelegten Kopien verwendet werden - früher musste das Originaldokument hierfür nochmals gefiltert werden, was sich natürlich negativ auf die Performance auswirkte ...

Montag, 10. September 2012

Einige Gedanken zu Oracle TEXT und Tabellen-Partitionierung

Dieses Blog Posting widmet sich dem Thema Partitionierung und Oracle TEXT. Die Grundzüge der Partitionierung werden hier jedoch nicht mehr erläutert; es wird davon ausgegangen, dass der Leser weiss, wie Partitionierung funktioniert. Informationen zum Thema finden sich in der der Dokumentation im "VLDB and Partitioning Guide" und im Data Sheet "Partitioning".
Es wird also darüber nachgedacht, eine Tabelle mit Dokumenten, auf die ein Oracle TEXT Index angelegt werden soll, zu partitionieren. Partitionierung kann folgende Vorteile bieten:
  • Wenn der Partitionierungsschlüssel gleichzeitig ein Abfragekriterium ist, kann der Optimizer die jeweilige Abfrage auf die relevanten Partitionen beschränken (Partition Pruning). Damit Oracle TEXT davon profitiert, braucht es einen lokal partitionierten Textindex.
  • Partitionierung erlaubt administrative Arbeiten an ganzen Partitionen - so können Partitionen als Ganzes gelöscht werden. Als eigene Tabelle vorhandene Daten können per Partition Exchange als neue Partition an die Tabelle gehängt werden. Auch hier sollte der Oracle TEXT Index lokal partitioniert sein; ein globaler Textindex müsste ansonsten komplett neu gebaut werden.
Ein Oracle TEXT Index sollte also fast immer lokal partitioniert sein, also die gleiche Partitionierung aufweisen, wie die zugrundeliegende Tabelle. Ist der Index nicht partitioniert, gehen nicht nur einige Vorteile verloren; es können sogar Mehraufwände entstehen; bspw. wenn eine Tabellenpartition (alte Daten) gelöscht wird. Ein lokal partitionierter Oracle TEXT Index wird wie folgt erstellt - zunächst erzeugen wir eine Beispieltabelle.

create table doktest_part (
  id          number(10),
  text        varchar2(200),
  datum       date
)
partition by range(datum) (
  partition p_alt values less than (to_date('2010-12-31','YYYY-MM-DD')),
  partition p_2011 values less than (to_date('2011-12-31','YYYY-MM-DD')),
  partition p_2012 values less than (to_date('2012-12-31', 'YYYY-MM-DD'))
)
/

insert into doktest_part values (1, 'Dies ist ein Text aus 2011', DATE'2011-08-01');
insert into doktest_part values (2, 'Oracle TEXT indiziert Texte auch in 2012', DATE'2012-09-01');

commit
/
Danach kommt der Volltextindex:
create index ft_doktest on doktest_part (text)
indextype is ctxsys.context
local
/
Das Schlüsselwort local erzeugt einen zur Tabelle "lokal" partitionierten Textindex. Eine wichtige Einschränkung sei an dieser Stelle genannt. Alle Domain-Indizes, also auch Oracle TEXT, unterstützen nur RANGE-Partitioning. Die Tabelle muss also RANGE-Partitioniert sein, damit ein lokal partitionierter Textindex gebaut werden kann. Ist die Tabelle anders partitioniert, stößt das CREATE INDEX auf einen Fehler.
Wenn nun aber eher eine List oder Hash-Partitionierung gebraucht wird, muss man diese mit einer RANGE-Partitionierung emulieren. Wird beispielsweise ein HASH-Partitioning mit 4 Partitionen benötigt, so kann man die Partitionsnummer mit der Funktion ORA_HASH selbst generieren. Dann lässt sich die Tabelle mit einer virtuellen Spalte ausstatten - und danach kann man wieder eine normale RANGE-Partitionierung einsetzen. Diese wirkt nun aber wie eine Hash-Partitionierung; anhand des Primärschlüssels werden die Zeilen nun möglichst gleichmäßig über die vier Partitionen verteilt.
create table doktest_hashpart (
  id          number(10),
  text        varchar2(200),
  part# as (ora_hash(id, 4, 81978923))
)
partition by range(part#) (
  partition p_h1 values less than (2),
  partition p_h2 values less than (3),
  partition p_h3 values less than (4),
  partition p_h4 values less than (5)
)
/
Auch LIST-Partitioning lässt sich mit RANGE-Partitioning emulieren. Geht es um einfache Buchstabenkürzel, so kann man diese alphabetisch sortieren und direkt mit LESS THAN arbeiten; Oracle ordnet die Zeilen dann anhand binärer Sortierung zu. Manchmal ist die Praxis aber nicht so einfach. Angenommen, es soll nach einem Abteilungskürzel wie folgt partitioniert werden:
  • Partition 1: Abteilungen A, B, C
  • Partition 2: Abteilungen A1, A2, A3
  • Partition 3: Abteilungen D-F
Also - "A" ist eine andere Abteilung als "A1" - und die werden in unterschiedliche Partitionen einsortiert. Mit einem einfachen LESS THAN kann das nicht mehr ausgedrückt werden; da die Partition 1 als LESS THAN ('B') definiert werden müsste, würden A1 bis A3 ebenfalls dort einsortiert. Allerdings kann uns ein SQL CASE Konstrukt weiterhelfen ...
  case
    when abteilung in ('A', 'B', 'C') then 1
    when abteilung in ('A1', 'A2', 'A3') then 2
    when abteilung in ('D', 'E', 'F') then 3
  end
Damit können wir die Tabelle wieder als RANGE-Partionierte Tabelle erzeugen, wobei wir aber de-facto eine LIST-Partionierte Tabelle haben.
create table doktest_listpart (
  id          number(10),
  text        varchar2(200),
  abteilung   varchar2(3),
  part# as (case 
    when abteilung in ('A', 'B', 'C') then 1
    when abteilung in ('A1', 'A2', 'A3') then 2
    when abteilung in ('D', 'E', 'F') then 3
    end
  )
)
partition by range(part#) (
  partition p_abc    values less than (2),
  partition p_a1a2a3 values less than (3),
  partition p_def    values less than (4)
)
/
An dieser Stelle aber eine Warnung zu den virtuellen Spalten: Sobald eine Tabelle anhand einer virtuelle Spalte partitioniert wurde, kann diese virtuelle Spalte nicht mehr geändert werden! Wenn also in diesem Beispiel neue Abteilungen eingeführt werden, wäre es nicht mehr möglich, den CASE-Ausdruck zu erweitern, um die neue Abteilung auf eine Partition abzubilden. Man sollte also sicher sein, dass man mit der virtuellen Spalte die ganze Fachlichkeit korrekt, umfassend und nachhaltig abbildet. Wenn Ihr euch da nicht sicher seid, ist eine "normale" Spalte mit einem Trigger (wie früher) vielleicht die sicherere Alternative ...
create table doktest_listpart_tr (
  id          number(10),
  text        varchar2(200),
  abteilung   varchar2(3),
  part#       number(4)
)
partition by range(part#) (
  partition p_abc    values less than (2),
  partition p_a1a2a3 values less than (3),
  partition p_def    values less than (4)
)
/

create or replace trigger tr_setpart#
before insert or update on doktest_listpart_tr
for each row
begin
  :new.part# := ( 
    case 
      when :new.abteilung in ('A', 'B', 'C') then 1
      when :new.abteilung in ('A1', 'A2', 'A3') then 2
      when :new.abteilung in ('D', 'E', 'F') then 3
    end
  );
end;
/
... denn den Trigger kann man auch nachträglich noch beliebig ändern.
Nun kann auf jede der Tabelle mit obigem CREATE INDEX-Kommando und dem Schlüsselwort LOCAL ein lokal partitinierter Textindex erzeugt werden. For Composite Partitioning kann das Verfahren ähnlich funktionieren; allerdings dürfte dieses Partitionsverfahren für Oracle TEXT Anwendungen meist nicht das richtige sein. In Oracle10g gab es mit 9999 noch ein eigenes Limit für die Anzahl der Partitionen. Ab Oracle11g unterstützt Oracle TEXT ebensoviele Partitionen wie die Datenbank selbst, nämlich 1048575 Die Tabellenstruktur sieht nach Erstellung des Index wie folgt aus.
TNAME                          TABTYPE  CLUSTERI
------------------------------ ------- ---------
DR#FT_PART10K0001$I            TABLE
DR#FT_PART10K0001$K            TABLE
DR#FT_PART10K0001$N            TABLE
DR#FT_PART10K0001$R            TABLE
DR#FT_PART10K0002$I            TABLE
DR#FT_PART10K0002$K            TABLE
DR#FT_PART10K0002$N            TABLE
DR#FT_PART10K0002$R            TABLE
:
  • DR#
  • Name des Volltextindex
  • Laufende Nummer der Partition. Ab Partition 10000 arbeitet Oracle TEXT mit Buchstaben: 10000 ist also AAAA, 10001 ist AAAB und so fort.
  • Kürzel für den Tabellentyp ($I, $N, $K, $R, $P, $S)
Ist der Partitionsschlüssel nun Teil der Abfrage, so findet (wie immer bei Partitioning) ein Partition Pruning statt; der Optimizer beschränkt die Abfrage also auf die relevante Partition. Im Ausführungsplan sieht das dann wie folgt aus.
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart | Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                     |     1 |    14 |     4   (0)| 00:00:01 |        |       |
|   1 |  PARTITION RANGE SINGLE            |                     |     1 |    14 |     4   (0)| 00:00:01 |      2 |     2 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| DOKTEST_LISTPART    |     1 |    14 |     4   (0)| 00:00:01 |      2 |     2 |
|*  3 |    DOMAIN INDEX                    | FT_DOKTEST_LISTPART |       |       |     4   (0)| 00:00:01 |        |       |
---------------------------------------------------------------------------------------------------------------------------
Man erkennt, dass die Abfrage auf die Partition 2 beschränkt wurde. Wurde allerdings, wie oben beschrieben, eine Hash- oder List-Partitionierung "emuliert", so muss man aufpassen. Ein Beispiel anhand der oben vorgestellten Emulation eines List-Partitioning:
create table doktest_listpart (
  id          number(10),
  text        varchar2(200),
  abteilung   varchar2(3),
  part# as (case 
    when abteilung in ('A', 'B', 'C') then 1
    when abteilung in ('A1', 'A2', 'A3') then 2
    when abteilung in ('D', 'E', 'F') then 3
    end
  )
)
partition by range(part#) (
  partition p_abc    values less than (2),
  partition p_a1a2a3 values less than (3),
  partition p_def    values less than (4)
)
/
Ein paar Zeilen einfügen ...
SQL> insert into doktest_listpart values (1, 'Die Oracle-Datenbank enthält Oracle TEXT','A1', DEFAULT);

1 Zeile wurde erstellt.

SQL> insert into doktest_listpart values (2, 'Mit Oracle Spatial werden Geodaten verwaltet.','D', DEFAULT);

1 Zeile wurde erstellt.
Nun wird eine Abfrage ausgeführt - der "Partitionsschlüssel" liegt in Form der Abteilung vor ...
SQL> select * from doktest_listpart where contains(text, 'Spatial') > 0 and abteilung='D';

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart | Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                     |     1 |    14 |     4   (0)| 00:00:01 |        |       |
|   1 |  PARTITION RANGE ALL               |                     |     1 |    14 |     4   (0)| 00:00:01 |      1 |     3 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| DOKTEST_LISTPART    |     1 |    14 |     4   (0)| 00:00:01 |      1 |     3 |
|*  3 |    DOMAIN INDEX                    | FT_DOKTEST_LISTPART |       |       |     4   (0)| 00:00:01 |        |       |
---------------------------------------------------------------------------------------------------------------------------
Am PARTITION RANGE ALL erkennt man, dass der Optimizer kein Partition Pruning durchgeführt, sondern die Abfrage über alle Partitionen ausgeführt hat. Das ist -strenggenommen- auch logisch, denn aus Sicht von Oracle TEXT ist die Tabelle gar nicht anhand der Spalte ABTEILUNG partitioniert - sondern anhand der Spalte PART#. Das muss sich auch in der Abfrage widerspiegeln. Zunächst macht es an dieser Stelle absolut Sinn, das "Mapping" der Abteilung zu einer Partitionsnummer in einer PL/SQL-Funktion zu kapseln.
create or replace function get_partid_for_range (
  p_abteilung in varchar2
) return number deterministic is
  l_partnum number;
begin
  l_partnum := case 
    when p_abteilung in ('A', 'B', 'C') then 1
    when p_abteilung in ('A1', 'A2', 'A3') then 2
    when p_abteilung in ('D', 'E', 'F') then 3
    end;
  return l_partnum;
end;
/
Mit Hilfe dieser Funktion lässt sich die Abfrage recht einfach formulieren ...
select  * from doktest_listpart
where contains(text, 'Spatial') > 0 
and part# = get_partid_for_range('D')
/
Der Ausführungsplan sieht dann so aus ...

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                     |     1 |    42 |     0   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE            |                     |     1 |    42 |     0   (0)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| DOKTEST_LISTPART    |     1 |    42 |     0   (0)| 00:00:01 |   KEY |   KEY |
|*  3 |    DOMAIN INDEX                    | FT_DOKTEST_LISTPART |       |       |     0   (0)| 00:00:01 |       |       | 
--------------------------------------------------------------------------------------------------------------------------
KEY in den Spalten PSTART und PSTOP meint lediglich, dass der Wert zur Compile-Zeit des SQL noch nicht bekannt ist und erst zur Ausführungszeit Run-Time ermittelt wird. Die Abfrage wird aber, wie man erkennen kann, auf eine Partition begrenzt. Für eine "emulierte" Hash-Partition arbeitet man genauso.
Die Tatsache, ob ein Textindex partitioniert ist oder nicht, hat großen Einfluß auf die Wartung desselben. Die Aufrufe zur Synchronisierung oder zur Optimierung erwarten einen Parameter part_name. Für nicht-partitionierte Indizes kann dieser SQL NULL sein. Bei einem lokal partitinierten Index wird dagagen der Name einer Partition erwartet. Das bedeutet, dass das komplette Synchronisieren einex lokal partitionierten Oracle TEXT Index nicht mehr mit einem einzigen Aufruf von CTX_DDL.SYNC_INDEX erledigt wird, sondern es wird etwas mehr gebraucht ...
SQL> exec ctx_ddl.sync_index('FT_DOKTEST_LISTPART');
BEGIN ctx_ddl.sync_index('FT_DOKTEST_LISTPART'); END;

*
FEHLER in Zeile 1:
ORA-20000: Oracle Text-Fehler:
DRG-13102: Name von Index-Partition muss angegeben werden
ORA-06512: in "CTXSYS.DRUE", Zeile 160
ORA-06512: in "CTXSYS.CTX_DDL", Zeile 848
ORA-06512: in Zeile 1
Der Name der Indexpartition entspricht normalerweise dem der Tabellenpartition. Im Zweifelsfalle kann man sie aber aus dem Oracle TEXT Data Dictionary auslesen:
SQL> select IXP_INDEX_PARTITION_NAME from CTX_USER_INDEX_PARTITIONS where ixp_index_name='FT_DOKTEST_LISTPART';

IXP_INDEX_PARTITION_NAME
------------------------------
P_ABC
P_A1A2A3
P_DEF
Braucht man nun doch ein Kommando, welches den ganzen Index synchronisiert, kann man sich also mit dieser Abfrage helfen ...
create or replace procedure sync_full_index(p_idx_name in varchar2) as 
begin
  for ip in (
    select IXP_INDEX_PARTITION_NAME from CTX_USER_INDEX_PARTITIONS 
    where ixp_index_name =  p_idx_name
  ) loop
    ctx_ddl.sync_index(
      idx_name      => p_idx_name,  
      part_name     => ip.IXP_INDEX_PARTITION_NAME
  );
  end loop;
end;
/
Wobei die Möglichkeit, eine Synchronisierung bzw. eine Optimierung nur partitionsweise durchführen zu können, in vielen Fällen gerade ein Vorteil sein dürfte. Denn es kann ja sein, dass unterschiedliche Partitionen unterschiedliche Anforderungen haben. Und mit der Partitionierung kann man den Aufwand auf das nötige Maß begrenzen. Nicht nur die Synchronisierung wird partitionsweise durchgeführt, auch einige andere Operationen arbeiten so ...
  • Index-Synchronisierung (CTX_DDL.SYNC_INDEX)
  • Asynchroner Index-Aufbau (CTX_DDL.POPULATE_PENDING)
  • Indexoptimierung (CTX_DDL.OPTIMIZE_INDEX)
  • Indexoptimierung (CTX_DDL.OPTIMIZE_INDEX)
  • Indexstatistiken werden ebenfalls partitionsweise ermittelt (CTX_REPORT.INDEX_STATS, CTX_REPORT.INDEX_SIZE und CTX_REPORT.TOKEN_INFO)
  • Online Index Rebuild (CTX_DDL.CREATE_SHADOW_INDEX und CTX_DDL.REBUILD_INDEX_ONLINE) arbeiten ebenfalls partitionsweise
Eine Operation wie ALTER TABLE DROP PARTITION funktioniert mit Oracle TEXT transparent; die relevante Partition des Oracle TEXT Index wird mitsamt der Tabellenpartition gelöscht. Gerade bei Oracle TEXT ergibt sich jedoch ein entscheidender Vorteil: Durch ein "klassisches" SQL DELETE würden die Einträge in die Negativliste wandern ($N-Tabelle) und es würde eine Indexoptimierung nötig. Beim partitinierten Textindex wird die Tabellenpartition einfach mitsamt der Indexpartition in einem Zug gelöscht. Es ist weder ein Index-Sync noch ein Index Optimize nötig. Ein partitionierter Oracle TEXT Index kann bei großen Datenmengen also eine Menge Vorteile bieten - und das betrifft nicht nur die Abfrageperformance, sondern auch, und besonders die Wartungs- und "Housekeeping"-Arbeiten an Tabelle und Index.
  • Partition Pruning bei Volltextabfragen - Abfragen werden auf die relevante Indexpartition begrenzt - was zu besserer Performance führt. Davon profitieren übrigens nicht die die Abfragen, sondern auch etwaige Sortierungen.
  • Mit einem ALTER TABLE DROP PARTITION können veraltete Daten recht einfach und ohne viel Aufwand gelöscht werden.
  • Neue Datenbestände können in eine eigene Tabelle geladen, indiziert und dann per ALTER TABLE EXCHANGE PARTITION in die Zieltabelle eingehängt werden. Auf gleichem Wege kann so ein Online-Rebuild einer Indexpartition erfolgen. Die Daten werden in eine eigene Tabelle kopiert; darauf wird der Index neu erstellt und ein ALTER TABLE EXCHANGE PARTITION nimmt Tabelle und Index auf einmal auf.
  • Auch Oracle-TEXT spezifische Operationen können partitionsweise durchgeführt und damit besser gesteuert werden.

Montag, 21. November 2011

Tablespace und mehr: Storage-Klausel für den Oracle-TEXT Index

Nachdem uns jüngst die Frage erreichte, wie man den Tablespace für einen Oracle TEXT-Index festlegen
kann ... machen wir dazu gleich ein Posting: Im Gegensatz zu einem normalen Textindex kann man nicht
einfach eine TABLESPACE-Klausel an das CREATE INDEX-Kommando anhängen. Das folgende funktioniert also
nicht.
create index ft_dokument on dokumente_tab(dokument)
indextype is mdsys.context
tablespace TS_IDXDATA
/
Bei Oracle TEXT ist hier etwas mehr zu tun. Oracle TEXT erlaubt unglaublich viel Flexibilität, was
auf der anderen Seite natürlich immer Konfigurationsaufwand bedeutet. Aber zum Thema: Ein Oracle TEXT Index
besteht intern stets aus mindestens vier Tabellen: $I, $R, $K und $N. Je nachdem, welche zusätzlichen
Features aktiviert wurden, kommen noch Tabellen dazu. Und jede dieser Tabellen kann ihre eigene
Storage-Klausel bekommen. Wie immer bei Oracle TEXT wird auch hier mit den Preference-Objekten
gearbeitet - diese physikalischen Details werden in eine Storage Preference gespeichert (nicht zu
verwechseln mit der Datastore Preference, die beschreibt, wo die zu indizierenden Daten herkommen). Wir
wollen also unseren TEXT Index in den Tablespace TS_IDXDATA legen. Also brauchen alle Tabellen, die
Oracle TEXT unter der Haube erzeugt, eine entsprechende Storage-Klausel.
begin
  ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');

  ctx_ddl.set_attribute('mystore', 'I_TABLE_CLAUSE', 'tablespace ts_idxdata '); 
  ctx_ddl.set_attribute('mystore', 'K_TABLE_CLAUSE', 'tablespace ts_idxdata '); 
  ctx_ddl.set_attribute('mystore', 'R_TABLE_CLAUSE', 'tablespace ts_idxdata LOB(DATA) STORE AS (CACHE)');
  ctx_ddl.set_attribute('mystore', 'N_TABLE_CLAUSE', 'tablespace ts_idxdata '); 
  ctx_ddl.set_attribute('mystore', 'I_INDEX_CLAUSE', 'tablespace ts_idxdata compress 2');
  ctx_ddl.set_attribute('mystore', 'P_TABLE_CLAUSE', 'tablespace ts_idxdata '); 
  ctx_ddl.set_attribute('mystore', 'S_TABLE_CLAUSE', 'tablespace ts_idxdata ');
end;
/
Wie man am Skript sehr schön sehen kann, werden hier die einzelnen Storage Klauseln für alle
an einem Textindex beteiligten Tabellen hinterlegt. Hier kann alles eingetragen werden, was auch
in die Storage-Klausel einer "normalen" Tabelle hineinkann. Wichtig sind allerdings die Einstellungen
LOB (DATA) STORE AS (CACHE) bei der R_TABLE_CLAUSE und COMPRESS 2 bei der I_INDEX_CLAUSE. Diese
Defaulteinstellungen sind sehr wichtig und es ist für die Performance ganz entscheidend, diese
bei eigenen Storage-Klauseln ebenfalls zu übernehmen. Hieran sollte man immer denken!
Die Nutzung der Storage Preference ist nun wieder einfach:
create index ft_dokument on dokumente_tab(dokument)
indextype is mdsys.context
parameters ('storage mystore')
/
Ansonsten gilt, wie gesagt: Es werden Standard-Storage-Klauseln für Tabellen hinterlegt. Neben dem
Tablespace selbst können also alle möglichen Details eingetragen werden. Am wichtigsten sind jedoch
wahrscheinlich in allen Fällen die Einstellungen zum Tablespace, zur Extent-Verwaltung oder zum Cache.
Weitere Details lassen sich in der Dokumentation nachlesen.

Dienstag, 21. September 2010

Neues Oracle TEXT-Feature NAME SEARCH

Seit kurzem ist das erste Patchset für 11g Release 2 (11.2.0.2) erschienen. Das ist in der Tat besonders für die Nutzer von Oracle TEXT wichtig, denn das Basisrelease 11.2.0.1 enthielt keine neuen Funktionen für Oracle TEXT - die wurden nun mit dem Patchset eingeführt. Im einzelnen sind das ...
  • Name search - dieser werden wir uns heute widmen
  • Entity extraction and identification
  • Result Set Interface
Interessant ist daher auch die Dokumentation zu Oracle TEXT - diese wurde mit dem Erscheinen des Patchset übrigens ebenfalls ausgetauscht.
Doch heute mehr zum Thema Name Search. Diese neue Funktion in Oracle TEXT ist speziell für die Suche nach Namen vorgesehen. Namen werden ja, insbesondere wenn Sie aus einem anderen Sprachraum kommen, recht häufig falsch geschrieben oder falsch verstanden. Man wendet die Rechtschreibregeln, die man kennt (hierzulande Deutsch) auf den fremden Namen an. Und das resultiert dann in einer völlig anderen Schreibweise. Eine exakte Suche hilft oft nicht weiter. Mit der FUZZY-Suche kann man zwar schon nach ähnlich geschriebenen Begriffen suchen, für eine umfassende Suche nach Namen reicht das jedoch vielfach nicht aus. Ich kenne das selbst recht gut - und werde auch meinen Namen "Czarski" hernehmen, um die neue Funktionalität vorzustellen.
Damit Namen mit der neuen Name Search-Funktion durchsucht werden können, müssen Sie beim Indizieren besonders behandelt werden - wie wir noch sehen werden, erfolgt die Zerlegung in Tokens etwas anders als bei normalen Texten. Aus diesem Grunde müssen Sie durch eine SECTION_GROUP mit Hilfe von XML-Tags vom Rest des Dokumentes abgegrenzt werden. Das kann in beispielsweise so aussehen ...
drop table names
/

create table names (
  id number(10),
  name varchar2(200)
)
/

insert into names values (1, '<name>Max Mustermann</name>');
insert into names values (2, '<name>Larry Ellison</name>');
insert into names values (3, '<name>Ulrike Schwinn</name>');
insert into names values (4, '<name>Carsten Czarski</name>');
insert into names values (5, '<name>Günther Stürner</name>');
Natürlich kann man auch Namen aus normalen, relationalen Tabellenspalten durchsuchbar machen. Diese indiziert man dann mit dem Multicolumn Datastore oder dem User Datastore. Wichtig ist, dass der Name in einem XML-Tag steht - das Tag selbst kann frei gewählt werden. Die Dokumentation zeigt im Abschnitt "Name Search" weiterführende Beispiele zur Namens-Indizierung von Tabellenspalten. Die SECTION_GROUP für die Namen muss dann als NDATA-Section Group deklariert werden.
begin
  ctx_ddl.drop_section_group('name_sg');
end;
/

begin
  ctx_ddl.create_section_group('name_sg', 'BASIC_SECTION_GROUP');
  ctx_ddl.add_ndata_section('name_sg', 'name', 'name');
end;
/
Alles innerhalb des XML-Tags <name> gehört nun zur NDATA-Section und wird speziell für die Namenssuche indiziert. Der nächste Schritt ist folgerichtig das Erstellen des Volltextindex.
create index ft_names on names (name)
indextype is ctxsys.context
parameters ('section group name_sg')
/
Anschließend der erste spannende Moment - wir werfen mal einen Blick in die Tokentabelle ...
select token_type, token_text from dr$ft_names$i
/

TOKEN_TYPE TOKEN_TEXT
---------- ----------------------------------------------------------------
       200 ^arr
       200 ^ars
       200 ^ax$
       200 ^azi
       200 ^bck
       200 ^bec
       200 ^bek
       200 ^car
       200 ^cas
       200 ^chw
         : :
Das sieht ja schonmal etwas anders aus ... nun probieren wir das mal aus. Zunächst kann man die Namen in beliebiger Reihenfolge suchen (schon mal nicht schlecht). Obwohl in der Tabelle Carsten Czarski steht, kann ich nach Czarski, Carsten suchen ...
SQL> select * from names where contains(name, 'NDATA(name,Czarski\, Carsten)') > 0

        ID NAME
---------- ----------------------------------------
         4 Carsten Czarski

1 Zeile wurde ausgewählt.
Beachtet die Syntax: Eine Namenssuche wird durch den NDATA-Operator innerhalb der CONTAINS-Abfrage eingeleitet. Der erste Parameter des NDATA-Operators ist wiederum die NDATA-Section-Group, also das XML-Tag, in dem sich der Name befindet (hier: name). Es könnte ja sein, dass es in XML-Dokumenten mehrere Namensabschnitte gibt. Danach kommt die Phrase, nach der gesucht wird und danach kommen noch zwei Parameter: ORDER / NOORDER legt fest, ob die Reihenfolge der Namensbestandteile berücksichtigt werden soll (NOORDER ist der Default) und PROXIMITY / NOPROXIMITY bestimmt, ob die Ähnlichkeit des Suchbegriffs zum tatsächlichen Namen den Score beeinflussen soll.
Als nächstes habe ich ein paar Tests gemacht - mit dem Namen Czarski ist man ja einiges gewöhnt. Erstmal einfach ...
SQL> select * from names where contains(name, 'NDATA(name,zarsky)') > 0

        ID NAME
---------- ----------------------------------------
         4 Carsten Czarski

1 Zeile wurde ausgewählt.
Gut ... das hätte man mit dem FUZZY-Operator auch noch geschafft. Also noch etwas nachlegen ...
SQL> select * from names where contains(name, 'NDATA(name,Tsarski)') > 0

        ID NAME
---------- ----------------------------------------
         4 Carsten Czarski
Noch ein wenig ...
SQL> select * from names where contains(name, 'NDATA(name,Tscharski)') > 0

        ID NAME
---------- ----------------------------------------
         4 Carsten Czarski
Und ein letztes, weil's so schön ist ...
SQL> select * from names where contains(name, 'NDATA(name,Saarski Karsden)') > 0

        ID NAME
---------- ----------------------------------------
         4 Carsten Czarski
Das ist schonmal nicht schlecht - ein FUZZY-Operator hätte hier schon seine Grenzen gehabt ... Probieren wir mal was anderes ... nicht ganz so exotisch und in Deutschland durchaus häufig anzutreffen.
SQL> select * from names where contains(name, 'NDATA(name,Schtürner)') > 0;

        ID NAME
---------- ----------------------------------------
         5 Günther Stürner

1 Zeile wurde ausgewählt.
Auch das konnte ich mit FUZZY nicht hinbekommen. Die ersten Tests sind also recht ermutigend; ich könnte mir vorstellen, dass dieses neue Feature nicht nur für die Volltextrecherche, sondern für viele "ganz normale" Applikationen interessant ist, denn nach Namen sucht man häufig und vielfach hat man einen Namen "nur gehört" und ist sich nicht sicher, wie er geschrieben ist. Es kann also durchaus sein, dass wir in Zukunft öfter mal einen Oracle TEXT-Index auf einer ganz normalen Tabelle (mit Namensspalten) sehen werden.
Viel Spaß beim Ausprobieren!

Montag, 17. Mai 2010

"Sicheres" Suchen: Oracle TEXT und die "Virtual Private Database"

Eine Volltextsuche geht bekanntlich immer über die ganze Tabelle. Manchmal bestehen allerdings Anforderungen an "sicheres Suchen", es soll also nicht jeder alles sehen können. Hierzu bietet sich im "normalen" Datenbankumfeld die Virtual Private Database an. Diese bewirkt, kurzgefasst, dass unterschiedliche Nutzer ein SELECT * FROM TABELLE absetzen, jeder sieht jedoch was anderes (das Feature gehört zur Enterprise Edition der Datenbank). Das wollen wir nun im Zusammenspiel mit Oracle TEXT einsetzen. Zuerst erzeugen wir die Tabelle und fügen ein paar Spalten ein:
create table dokumente(
  id                 number(10),
  sicherheitsmerkmal varchar2(50),
  dokument           clob
)
/

insert into dokumente values (1, 'LOW', 'Oracle TEXT dient zur Volltextrecherche');
insert into dokumente values (2, 'LOW', 'Oracle TEXT beherrscht linguistische Suche');
insert into dokumente values (3, 'MEDIUM', 'Der Gewinn wird im nächsten Jahr steigen');
insert into dokumente values (4, 'HIGH', 'Die Spionageabteilung sitzt in Berlin');
Das Besondere ist die Spalte SICHERHEITSMERKMAL - sie kann die Werte MEDIUM, HIGH oder LOW annehmen. Es fehlt nun nur noch eine Tabelle, mit welcher wir die Datenbanknutzer auf die Sicherheitsklassen abbilden ...
create table dokumente_nutzer(
  userid             varchar2(30),
  sicherheitsmerkmal varchar2(100)
)
/

insert into dokumente_nutzer values ('SCOTT', 'HIGH');
insert into dokumente_nutzer values ('SCOTT', 'MEDIUM');
insert into dokumente_nutzer values ('SCOTT', 'LOW');
insert into dokumente_nutzer values ('REVISION', 'MEDIUM');
insert into dokumente_nutzer values ('REVISION', 'LOW');
insert into dokumente_nutzer values ('ALLE', 'LOW');
Dann werden (als SYS) die Nutzer angelegt und, da die Privilegiensteuerung komplett über die Virtual Private Database gesteuert wird, SELECT-Privilegien an der Tabelle an PUBLIC vergeben.
create user revision identified by revision
/

create user alle identified by alle
/

grant create session to revision
/

grant create session to alle
/

grant select on scott.dokumente to public
/

Nun geht es ans Einrichten der Virtual Private Database. Zunächst wird die sog. Policy Function benötigt. Die Policy Function generiert eine WHERE-Bedingung, die dann von der Datenbank automatisch in das SELECT-Kommando integriert wird. Durch diese Vorgehensweise (die Sicherheitsregel "hängt" direkt an der Tabelle) kann die Sicherheitsregel nicht umgangen werden! Unsere PL/SQL-Funktion ist recht einfach ...
create or replace function my_policy(
 p_schema varchar2, p_object varchar2
) return varchar2 is
  v_where varchar2(32767) := 'SICHERHEITSMERKMAL IN (';
  v_users boolean := false;
begin
  for i in (
    select sicherheitsmerkmal from dokumente_nutzer
    where userid = user
  ) loop
    v_users := true;
    v_where := v_where || '''' || i.sicherheitsmerkmal || ''', ';
  end loop;
  if v_users then 
    v_where := substr(v_where, 1, length(v_where) - 2) || ')'; 
  else
    v_where := '1=0';
  end if;
  return v_where;
end;  
Als letztes machen wir die Virtual Private Database "scharf" ... Um dieses Kommando laufen zu lassen, wird das EXECUTE-Privileg auf DBMS_RLS benötigt - oder der DBA macht es gleich selbst ...
begin
 DBMS_RLS.ADD_POLICY(
    object_schema => 'SCOTT',
    object_name => 'DOKUMENTE',
    policy_name => 'SCOTT_DOKUMENT_POLICY',
    function_schema => 'SCOTT',
    policy_function => 'MY_POLICY'
  );
end;
/
Und nun kommt der erste Test (noch ohne Volltextindex). Probiert nun mal, die Tabelle mit verschiedenen Nutzerkonten zu selektieren ... Zuerst als SCOTT:
SQL> select * from scott.dokumente;

        ID SICHERHEITSMERKMAL                                 DOKUMENT
---------- -------------------------------------------------- --------------------------------------------------
         1 LOW                                                Oracle TEXT dient zur Volltextrecherche
         2 LOW                                                Oracle TEXT beherrscht linguistische Suche
         3 MEDIUM                                             Der Gewinn wird im nächsten Jahr steigen
         4 HIGH                                               Die Spionageabteilung sitzt in Berlin

4 Zeilen ausgewählt.
... dann als User ALLE:
SQL> select * from scott.dokumente;

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
         1 LOW             Oracle TEXT dient zur Volltextrecherche
         2 LOW             Oracle TEXT beherrscht linguistische Suche

2 Zeilen ausgewählt.
... schließlich als irgendein User, der in der Tabelle DOKUMENTE_NUTZER nicht vorkommt ...
SQL> select * from scott.dokumente;

Es wurden keine Zeilen ausgewählt
Man sieht, dass zwar alle die Tabelle selektieren können, jeder sieht allerdings was anderes. Und das kann man natürlich auch mit dem Volltextindex kombinieren. Den müssen wir natürlich zuerst mal anlegen. Und hier wollen wir auch gleich das in 11g neue Feature des Composite Domain Index nutzen (siehe das eigene Blog Posting dazu. Nötig ist das, da in diesem Fall jede Abfrage eine Mixed Query sein wird - die Virtual Private Database hängt ja eine relationale Abfrage an jede Textabfrage an ...
create index vt_dokumente on dokumente(dokument)
indextype is ctxsys.context
filter by sicherheitsmerkmal
parameters ('transactional')
/
Nun können wir testen: Wir arbeiten als User ALLE und probieren mal einige Vollextabfragen ...
SQL> select * from scott.dokumente where contains (dokument,'Oracle') > 0;

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
         1 LOW             Oracle TEXT dient zur Volltextrecherche
         2 LOW             Oracle TEXT beherrscht linguistische Suche

2 Zeilen ausgewählt.

SQL> select * from scott.dokumente where contains (dokument,'Gewinn') > 0;

Es wurden keine Zeilen ausgewählt
Die Abfrage mit dem Gewinn probieren wir danach mal als User REVISION - der darf mehr sehen, aber nicht alles ...
SQL> select * from scott.dokumente where contains (dokument,'Gewinn') > 0;

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
         3 MEDIUM          Der Gewinn wird im nächsten Jahr steigen

1 Zeile ausgewählt.

SQL> select * from scott.dokumente where contains (dokument,'Spionageabteilung') > 0;

Es wurden keine Zeilen ausgewählt.
Die Spionageabteilung darf aber nur der SCOTT sehen ...
SQL> select * from scott.dokumente where contains (dokument,'Spionageabteilung') > 0;

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
         4 HIGH            Die Spionageabteilung sitzt in Berlin

1 Zeile ausgewählt.
Und das Schöne ist, dass diese Sicherheitsregeln unabhängig vom Textindex arbeiten; sie ziehen sowohl bei "normalen" als auch bei Volltextrecherchen. Zum Abschluß werfen wir einen Blick auf die Performance - normalerweise sind solche Mixed Queries ja problematisch ... insbesondere, wenn das Volltextkriterium nur gemeinsam mit dem relationalen Kriterium selektiv ist. Auch dazu bauen wir einen kleinen Testcase und fügen einige Sätze in die Tabelle DOKUMENTE ein.
drop index vt_dokumente
/

begin
  for i in 1..100000 loop
    insert into dokumente values (i+10, 'MEDIUM', 'Der Gewinn ist dieses Jahr stabil');
    insert into dokumente values (i+200010, 'LOW', 'Oracle TEXT ist Teil der Datenbank');
   end loop;
end;
/

commit
/

insert into dokumente values (500000, 'LOW', 'Gewinn ist: Mehr Einnahmen als Ausgaben');
    

create index vt_dokumente on dokumente(dokument)
indextype is ctxsys.context
filter by sicherheitsmerkmal
parameters ('transactional')
/
Diese Situation bringt das Mixed Query-Problem auf den Punkt. Die Volltextrecherche nach Gewinn allein ist nun extrem unselektiv (100.000 mal in MEDIUM, 1 mal in LOW). Die Sicherheitsklasse LOW ist aber auch unselektiv (kommt auch 100.000 mal vor). Die Kombination als Volltextsuche und relationalen Kriterium allein ist sehr selektiv - denn Gewinn kommt in der Sicherheitsklasse LOW nur einmal vor. Schauen wir uns die Abfrage nun an - wir arbeiten mit dem User ALLE (dieser hat nur Zugriff auf LOW), es darf also nur die Zeile der Klasse LOW zurückkommen ...
SQL>select * from scott.dokumente where contains (dokument,'Gewinn') > 0

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
    300000 LOW             Gewinn ist: Mehr Einnahmen als Ausgaben

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:00.17
Sieht auf den ersten Blick ganz gut aus - und der Ausführungsplan ...?
SQL> set autotrace traceonly
SQL> select * from scott.dokumente where contains (dokument,'Gewinn') > 0;

1 Zeile wurde ausgewählt.

Ausführungsplan
----------------------------------------------------------
Plan hash value: 3982102634

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              | 42738 |  6135K|  9161   (4)| 00:01:50 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DOKUMENTE    | 42738 |  6135K|  9161   (4)| 00:01:50 |
|*  2 |   DOMAIN INDEX              | VT_DOKUMENTE |       |       |  7710   (1)| 00:01:33 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CTXSYS"."CONTAINS"("DOKUMENT",'Gewinn')>0)
       filter("SICHERHEITSMERKMAL"='LOW')


Statistiken
----------------------------------------------------------
         35  recursive calls
          0  db block gets
        551  consistent gets
          0  physical reads
          0  redo size
        928  bytes sent via SQL*Net to client
        648  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
Im Ausführungsplan erkennt man, dass die komplette Abfrage aus dem Volltextindex heraus bedient wurde. Genau für solche Fälle wurde dieses Feature in 11g auch geschaffen ... Zum Abschluß noch ein paar Links zu weiterführender Dokumentation:

Freitag, 9. April 2010

Die Wordlist und das wildcard_maxterms Attribut

In folgendem Blog wird das WILDCARD_MAXTERMS Attribut, das eine Eigenschaft der BASIC_WORDLIST ist, kurz beschrieben und an einem Beispiel erklärt. Die Einstellung von WILDCARD_MAXTERMS gibt die maximale Anzahl der Ergebnisbegriffe einer Wildcard Suche an. Was bedeutet dies nun?

Wir nutzen im Beispiel die Tabelle TEXTTABELLE aus dem Blog und definieren zusätzlich die Präferenz für Mixed Case.

execute ctx_ddl.drop_preference('MY_LEXER');
begin
  ctx_ddl.create_preference(
    preference_name => 'MY_LEXER',
    object_name     => 'BASIC_LEXER');
  -- Mixed Case abschalten
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'MIXED_CASE',
    attribute_value => 'NO');
end;
/

Danach setzen wir explizit die BASIC_WORDLIST auf den Wert 1, um einen Fehler auszulösen.

execute ctx_ddl.drop_preference('my_wordlist');
BEGIN
  ctx_ddl.create_preference('my_wordlist', 'BASIC_WORDLIST');
  ctx_ddl.set_attribute('my_wordlist', 'WILDCARD_MAXTERMS', 1);
END;
/

Nun wird der Index angelegt ...

CREATE INDEX idx_text ON texttabelle (dokument)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('WORDLIST my_wordlist LEXER MY_LEXER')
/

Bei der folgenden Abfrage erhalten wir nun einen Fehler, da eigentlich mehr als ein Ergebnis zu erwarten ist.

SQL> SELECT * FROM texttabelle WHERE contains (dokument,'%gewinn%')>0;
SELECT * FROM texttabelle WHERE contains (dokument,'%gewinn%')>0
*
FEHLER in Zeile 1:
ORA-29902: Fehler bei der Ausführung von Routine ODCIIndexStart()
ORA-20000: Oracle Text error:
DRG-51030: wildcard query expansion resulted in too many terms

Verifizieren wir das Ganze nun mit der Token-Tabelle, um die Ergebnisbegriffe einzusehen.

SQL> SELECT token_text FROM dr$idx_text$i WHERE upper(token_text) like '%GEWINN%';

TOKEN_TEXT
----------------------------------------------------------------
GEWINNT
GEWINNZUWACHS

Ändern wir also in der Wordlist das WILDCARD_MAXTERMS Attribut auf den Wert 20000. Dies ist übrigens in 11g der Default-Wert.
 
execute ctd_ddl.drop_preference('my_wordlist');
BEGIN  
  ctx_ddl.create_preference ('my_wordlist', 'BASIC_WORDLIST');
  ctx_ddl.set_attribute ('my_wordlist', 'WILDCARD_MAXTERMS', 20000);
END;
/

Passen wir im nächsten Schritt die Wordlist des Index an. Dies könnte Online mit Unterstützung des Shadow Index durchgeführt (siehe Blog) werden.

execute ctx_ddl.recreate_index_online('IDX_TEXT','REPLACE WORDLIST my_wordlist');

Nun erhalten wir das erwartete Ergebnis.

SQL> SELECT * FROM texttabelle WHERE contains (dokument,'%gewinn%')>0

        ID DOKUMENT
---------- --------------------------------------------------
         2 A-Partei gewinnt Wahl in Hansestadt
         4 Wirtschaft: Erneuter Gewinnzuwachs in diesem Jahr

Je nach Release der Datenbank gibt es unterschiedliche Default-Werte für das WILDCARD_MAXTERMS Attribut. Prüfen lässt sich dies mit folgender Abfrage:

SQL> SELECT oat_default, oat_min, oat_max  
FROM ctx_object_attributes 
WHERE oat_attribute LIKE '%MAXTERM%'

OAT_DEFAULT             OAT_MIN OAT_MAX
-------------------- ---------- ----------------------------------------
20000                         0 50000

Der aktuelle Wert lässt sich über folgende Abfrage ermitteln:

SQL> SELECT * FROM ctx_user_preference_values;

PRV_PREFERENCE                 PRV_ATTRIBUTE                  PRV_VALUE
------------------------------ ------------------------------ ----------
MY_LEXER                       MIXED_CASE                     NO
MY_WORDLIST                    WILDCARD_MAXTERMS              50000

Eine Anmerkung zum Schluss: Eine Erhöhung von WILDCARD_MAXTERMS muss nicht die optimale Lösung sein. Manchmal hilft eine Erweiterung der Stoppliste oder eine Anpassung der Auswahlliste innerhalb einer Webapplikation um den Anforderungen zu genügen.

Dienstag, 16. März 2010

Arbeiten mit einem Thesaurus in Oracle TEXT

Oracle TEXT bietet Thesaurus-Unterstützung out-of-the-box an. Das bedeutet, dass man nicht nur nach einem Wort oder einer Phrase suchen kann, sondern auch nach "verwandten" Wörtern. Die Beziehungen zwischen verwandten Wörtern müssen allerdings in der Datenbank hinterlegt sein. Dieses Posting stellt kurz vor, wie man einen einfachen Thesaurus anlegt und nutzt. Oracle selbst stellt Thesauri aber nur für die englische Sprache zur Verfügung; im deutschsprachigen Bereich muss man sich diesen selbst erstellen.
Thesauri werden angelegt und verwaltet mit dem PL/SQL-Paket CTX_THES. Der folgende Aufruf legt einen neuen (noch leeren) Thesaurus an. Wie für andere administrative Aufgaben benötigt Ihr auch zum Verwalten von Thesauri die Rolle CTXAPP:
begin
  ctx_thes.create_thesaurus(
    name     => 'MEIN_THESAURUS',
    casesens => false
  );
end;
/
Nun geht es daran, Wortbeziehungen in den Thesaurus einzutragen. Dabei werden folgende Beziehungen unterstützt:
  • Narrower Term (NT): Ein Begriff wird enger gefasst. Eine solche Beziehung könnte sein Sport NT Fußball.
  • Broader Term (BT): Ein Begriff wird weiter gefasst. Das ist das Gegenteil zum Narrower Term. Eine solche Beziehung könnte sein AktieBT Wertpapier.
  • Broader Term (BT): Ein Begriff wird weiter gefasst. Das ist das Gegenteil zum Narrower Term. Eine solche Beziehung könnte sein AktieBT Wertpapier.
  • Synonym (SYN): Eine Synonymbeziehung meint, dass zwei Begriffe das gleiche bedeuten. Geld SYN Moneten ist ein Beispiel für eine solche Beziehung.
  • Übersetzungen (TR): Wie der Name schon sagt; im Thesaurus lassen sich auch Übersetzungen eines Begriffs in andere Sprachen verwalten. Ein Beispiel wäre Geld ENGLISH: Money.
Das folgende Beispiel generiert einige Beziehungen in den soeben erstellten Thesaurus:
begin
  ctx_thes.create_relation('MEIN_THESAURUS', 'Elfmeter', 'SYN', 'Strafstoß');
  ctx_thes.create_relation('MEIN_THESAURUS', 'Schiedsrichter', 'SYN', 'Unparteiischer');
  ctx_thes.create_relation('MEIN_THESAURUS', 'Trainer', 'SYN', 'Coach');

  ctx_thes.create_relation('MEIN_THESAURUS', 'Ballspiel', 'NT', 'Fußball');
  ctx_thes.create_relation('MEIN_THESAURUS', 'Liga', 'NT', 'Bundesliga');
  ctx_thes.create_relation('MEIN_THESAURUS', 'Liga', 'NT', 'Regionalliga');
  ctx_thes.create_relation('MEIN_THESAURUS', 'Liga', 'NT', 'Champions League');

  ctx_thes.create_relation('MEIN_THESAURUS', 'Handspiel', 'BT', 'Foul');
  ctx_thes.create_relation('MEIN_THESAURUS', 'Stürmer', 'BT', 'Spieler');
  ctx_thes.create_relation('MEIN_THESAURUS', 'Libero', 'BT', 'Spieler');
end;
/
Nun können die Beziehungen schon getestet werden. Zunächst wollen wir prüfen, ob die Synonyme für "Trainer" funktionieren.
select ctx_thes.syn('Trainer', 'MEIN_THESAURUS') from dual;

CTX_THES.SYN('TRAINER','MEIN_THESAURUS')
---------------------------------------------
{TRAINER}|{COACH}

select ctx_thes.syn('Coach', 'MEIN_THESAURUS') from dual;

CTX_THES.SYN('COACH','MEIN_THESAURUS')
----------------------------------------------------
{COACH}|{TRAINER}
Nun geht es an einen richtigen Test. Der Thesaurus soll in einer Volltextabfrage mit CONTAINS genutzt werden. Die folgenden Anweisungen erzeugen eine Tabelle, einige Dokumente und schließlich einen Volltextindex. Thesaurus und Index sind aber voneinander unabhängig! Ein vorhandener Volltextindex muss nach Erstellen oder Ändern eines Thesaurus nicht neu gebaut werden.
create table test_thes_tab(text varchar2(4000))
/

insert into test_thes_tab values ('Der Trainer wurde entlassen');
insert into test_thes_tab values ('Der Verein war zurück in der Bundesliga');
insert into test_thes_tab values ('Der Stürmer lieferte eine Glanzleistung ab.');
insert into test_thes_tab values ('In der 15. Minute gab es einen Elfmeter');
insert into test_thes_tab values ('Der Schiedsrichter blieb hart.');

create index idx_thestest_volltext on test_thes_tab (text) 
indextype is ctxsys.context
/
Nun einige Abfragen ...
  • Die einfache Suche nach Coach bringt keine Ergebnisse:
    select text from test_thes_tab where contains(text, 'Coach') > 0;
    
    No rows selected.
    
  • Die Synonymbeziehung bringt den Erfolg:
    select text from test_thes_tab where contains(text, 'SYN(Coach, MEIN_THESAURUS)') > 0;
    
    TEXT
    --------------------------------------------------------------------------------
    Der Trainer wurde entlassen
    
  • Die Suche nach Spieler bringt keine Treffer:
    select text from test_thes_tab where contains(text, 'Spieler') > 0;
    
    Es wurden keine Zeilen ausgewählt.
    
  • Sucht man nach den Narrower Terms (hier: bis zu 10 Stufen), so findet man auch die Stürmer.
    select text from test_thes_tab where contains(text, 'NT(Spieler,10,MEIN_THESAURUS)') > 0;
    
    TEXT
    --------------------------------------------------------------------------------
    Der Stürmer lieferte eine Glanzleistung ab.
    
In diesen Beispielen wurde der Thesaurus mit MEIN_THESAURUS überall mit angegeben. Es ist jedoch auch möglich, einen Thesaurus namens DEFAULT zu erzeugen oder einen bestehenden in DEFAULT umzubenennen. Dieser wird dann immer verwendet, wenn kein Thesaurus-Name angegeben wird. Das folgende Kommando benennt den Thesaurus MEIN_THESAURUS in DEFAULT um.
begin
  ctx_thes.alter_thesaurus(
    tname   => 'MEIN_THESAURUS',
    op      => ctx_thes.op_rename, 
    operand => 'DEFAULT'
  );
end;
/
Nun muss der Thesaurus-Name nicht mehr explizit angegeben werden.
select text from test_thes_tab where contains(text, 'NT(Spieler,10)') > 0;

TEXT
--------------------------------------------------------------------------------
Der Stürmer lieferte eine Glanzleistung ab.
Wie für alles gibt es auch für Thesauri in der Datenbank Dictionary Views:
  • CTX_USER_THESAURI enthält die definierten Thesauri.
  • CTX_USER_THES_PHRASES gibt die in den Thesauri enthaltenen Begriff zurück. Die Beziehungen selbst sind darin aber nicht enthalten; diese können mit dem Paket CTX_THES und den darin enthaltenen Funktionen wie NT oder BT oder SYN ermittelt werden.
Schließlich können auch Übersetzungen im Thesaurus gespeichert werden. Hier ein Beispiel:
begin
  ctx_thes.create_relation('DEFAULT', 'Referee', 'GERMAN:', 'Schiedsrichter');
end;  
Nun kann man auch nach den Übersetzungen von Referee suchen ...
select text from test_thes_tab where contains(text, 'TR(Referee)') > 0;

TEXT
-------------------------------------------------------------------------------
Der Schiedsrichter blieb hart.
Weitere Informationen zum Thema findet Ihr hier:

Montag, 18. Januar 2010

Abfragen vordefinieren: Stored Query Expressions (SQE)

Eine wenig bis gar nicht bekannte Eigenschaft von Oracle TEXT sind die Stored Query Expressions (SQE). Damit können bestimmte TEXT-Abfragen quasi im Vorfeld unter einem Begriff gespeichert und anschließend von allen Nutzern verwendet werden ... Ein einfaches Beispiel anhand des Oracle TEXT Handbuchs ...
begin
  ctx_query.store_sqe('textdebug_cczarski', 'trace or log or logging or ctx_log');
end;
Von nun an kann man diesen Ausdruck wie ein normales Wort in CONTAINS-Abfragen verwenden ...
SQL> select id from dokument_tab where contains(content, 'sqe(textdebug_cczarski)') > 0;

        ID
----------
         1
Wendet man das in einem früheren Blog-Posting vorgestellte CTX_QUERY.EXPLAIN an, so kann man sich die Vorgehensweise von Oracle TEXT näher ansehen ...
ID         OPERATION       OPTIO OBJECT_NAME       POSITION
---------- --------------- ----- --------------- ----------
    1      OR                                             1
      2    WORD                  trace                    1
      3    WORD                  log                      2
      4    WORD                  logging                  3
      5    WORD                  ctx_log                  4
Man sieht von der SQE eigentlich gar nichts mehr - Oracle TEXT löst diese einfach transparent auf. SQE's sind insbesondere hilfreich, wenn es darum geht, schwierige CONTAINS-Abfragen (deren Ausarbeitung viel Arbeit war) für andere einfach nutztbar zu machen.

Mittwoch, 2. Dezember 2009

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

Beliebte Postings