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.
