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

Dienstag, 8. September 2015

Transaktionen und CTX_DDL: COMMIT oder nicht COMMIT?

Heute geht es um ein kleines, aber dennoch nützliches Feature in Oracle TEXT, wenn es um das Erstellen von Preferences geht. Die verschiedenen Prozeduren in CTX_DDL setzen alle ein implizites COMMIT ab, und verhalten sich damit wie normale SQL DDL Kommandos. Das folgende Codebeispiel zeigt das - obwohl ein ROLLBACK erfolgte, ist die neue Preference noch sichtbar - sie wurde vorher schon festgeschrieben.
SQL> select pre_name, pre_class from ctx_user_preferences;

Es wurden keine Zeilen ausgewählt

SQL> exec ctx_ddl.create_preference('MYLEXER', 'BASIC_LEXER');

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> rollback;

Transaktion mit ROLLBACK rückgängig gemacht.

SQL> select pre_name, pre_class from ctx_user_preferences;

PRE_NAME                       PRE_CLASS
------------------------------ ------------------------------
MYLEXER                        LEXER

1 Zeile wurde ausgewählt.
In den meisten Fällen ist dies sicherlich auch das gewünschte Verhalten; aber es geht auch anders. Letztlich werden durch die verschiedenen CTX_DDL-Aufrufe doch nur Zeilen in interne Tabellen eingefügt - es wäre also schon interessant, wenn man kein implizites COMMIT machen würde - dann könnte man auch mehrere CTX_DDL-Aufrufe per Rollback rückgängig machen. Und das geht so.
begin
  CTX_DDL.PREFERENCE_IMPLICIT_COMMIT := FALSE;
end;
/

PL/SQL-Prozedur erfolgreich abgeschlossen.
Wenn man die Kommandos von oben nun nochmals laufen lässt, sieht das Bild so aus.
SQL> select pre_name, pre_class from ctx_user_preferences;

Es wurden keine Zeilen ausgewählt

SQL> exec ctx_ddl.create_preference('MYLEXER', 'BASIC_LEXER');

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> rollback;

Transaktion mit ROLLBACK rückgängig gemacht.

SQL> select pre_name, pre_class from ctx_user_preferences;

Es wurden keine Zeilen ausgewählt
Kombiniert man dies in einem SQL-Skript mit einem WHENEVER SQLERROR EXIT oder WHENEVER SQLERROR ROLLBACK, so muss man sich nicht mehr mit dem Problem "halb" angelegter Index-Preferences herumschlagen. Mehr dazu findet Ihr in der Oracle TEXT Dokumentation.

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.

Montag, 2. November 2009

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

Montag, 16. März 2009

Einstellungen für einen Textindex: Komposita, Printjoins, Skipjoins, Mixed Case und mehr ...

Beim Erstellen eines Oracle TEXT-Index kann man eine ganze Menge Einstellungen vornehmen. Im letzten Posting ging es um das Thema Stopwörter, heute schauen wir uns ein paar andere Einstellungen an.
Wir beginnen ganz einfach und erzeugen zunächst eine Tabelle mit ein paar Textzeilen.
drop table texte
/

create table texte (
  id number,
  text varchar2(4000)
)
/

insert into texte values (1, 'Das Treffen am Bahnhofsplatz heute abend war schön');
insert into texte values (2, 'Dem Chat trat der Nutzer "user_7642" bei');

commit
/
Erstellen wir nun einen Index - zunächst mal ohne jede Parametrisierung ...
drop index idx_texte
/

create index idx_texte on texte (text)
indextype is ctxsys.context
/
Anschließend kann man sich "den Index" mit einem Blick auf die Token-Tabelle ansehen ...
SQL> select token_text from dr$idx_texte$i;

TOKEN_TEXT
--------------------------------------------------
7642
Bahnhof
Bahnhofsplatz
Chat
Das
Dem
Nutzer
Platz
Treffen
abend
heute
schön
trat
user
Die erste Auffälligkeit ist die Tatsache, dass die Wörter (Tokens) im Mixed Case in der Token-Tabelle stehen. Das ist für die meisten Fälle ungeeignet, da eine Suche nach "chat" (alles kleingeschrieben) zu keinem Ergebnis führen würde. Dies gälte es also durch Einstellung von Parametern zu ändern. An anderer Stelle es gut erkennbar, dass der Index die deutsche Sprache erkannt hat; das Token "Bahnhofsplatz" wurde korrekt in die zusätzlichen Tokens "Bahnhof" und "Platz" zerlegt. Experimentieren wir nun ein wenig mit den Parametern: Als erstes soll der Index nicht mehr Case-Sensitiv sein ...
drop index idx_texte
/

begin
  ctx_ddl.drop_preference( 
    preference_name => 'MY_LEXER'
  );
end;
/

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;
/

create index idx_texte on texte (text)
indextype is ctxsys.context
parameters ('LEXER MY_LEXER')
/
Die Parameter werden in die sog. Preference MY_LEXER eingestellt. Anschließend wird der Index neu erstellt - die Token-Tabelle sieht dann so aus:
SQL> select token_text from dr$idx_texte$i;

TOKEN_TEXT
----------------------------------------------------------------
7642
ABEND
BAHNHOFSPLATZ
CHAT
HEUTE
NUTZER
SCHÖN
TRAT
TREFFEN
USER
OK ... damit ist das Mixed-Case-Problem behoben. Allerdings wurde der Bahnhofsplatz nun nicht mehr zerlegt - und das war ja eigentlich ganz gut so ... Das Erstellen der Preference MY_LEXER ändern wir also nochmals und schalten die Kompositazerlegung wieder ein (von nun an stelle ich nur noch die create_preference Aufrufe hier vor.
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'
  );
  -- Kompositazerlegung einschalten
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'COMPOSITE',
    attribute_value => 'GERMAN'
  );
end;
/
Ergebnis ..
SQL>  select token_text from dr$idx_texte$i;

TOKEN_TEXT
---------------------------------------------------------
7642
ABEND
BAHNHOF
BAHNHOFSPLATZ
CHAT
HEUTE
NUTZER
PLATZ
SCHÖN
TRAT
TREFFEN
USER
Das Token user_7642 wurde offensichtlich ebenfalls zerlegt: Oracle TEXT behandelt den Unterstrich (_) als Trenner von Tokens. Auch dies kann man mit dem Parameter PRINTJOINS abschalten ...
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'
  );
  -- Kompositazerlegung einschalten
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'COMPOSITE',
    attribute_value => 'GERMAN'
  );
  -- Den Unterstrich (_) als "Printjoin" deklarieren
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'PRINTJOINS',
    attribute_value => '_'
  );
end;
/
Das Ergebnis ...
SQL>  select token_text from dr$idx_texte$i;

TOKEN_TEXT
---------------------------------------------------
ABEND
BAHNHOF
BAHNHOFSPLATZ
CHAT
HEUTE
NUTZER
PLATZ
SCHÖN
TRAT
TREFFEN
USER_7642
Doch hierbei Vorsicht: Der Unterstrich wirkt nun überhaupt nicht mehr als Trennzeichen für Tokens - die Aufnahme eines Zeichens zu den Printjoins sollte also nur dann erfolgen, wenn man sich sicher ist, dass dies auch für den gesamten Dokumentbestand in Ordnung geht. Weiterhin könnt Ihr nur einzelne Zeichen als Printjoins deklarieren, keine Zeichenketten. Hierbei muss man also ein wenig aufpassen ...
Eine andere Variante wäre, den Unterstrich als Skipjoin zu deklarieren ...

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'
  );
  -- Kompositazerlegung einschalten
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'COMPOSITE',
    attribute_value => 'GERMAN'
  );
  -- Den Unterstrich (_) als "Skipjoin" deklarieren
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'SKIPJOINS',
    attribute_value => '_'
  );
end;
/
... was dann so aussieht; der Unterstrich wäre dann verschwunden und würde bei Abfragen ignoriert.
SQL>  select token_text from dr$idx_texte$i;

TOKEN_TEXT
---------------------------------------------
ABEND
BAHNHOF
BAHNHOFSPLATZ
CHAT
HEUTE
NUTZER
PLATZ
SCHÖN
TRAT
TREFFEN
USER7642
Eine vollständige Übersicht über alle Parameter (es gibt noch ein paar mehr) findet Ihr im Handbuch Text Reference. So ... das war's für heute - mehr zu Textindex-Parametern und Einstellungsmöglichkeiten in den nächsten Postings ...

Beliebte Postings