Zurück

Oracle 8 Partition Tables und Indexes

Inhalt

Übersicht
Table Partitionierung
Index Partitionierung
  Local prefixed Indexes
  Local NON prefixed Indexes
  Global partitionierter Index
Partition Table mit Partition Key als Teil des Primary Keys
  Local NON prefixed Index (Primary Key)
  Local prefixed Indexes
Partition Table wenn Partition Key nicht Teil des Primary Keys ist
  Normaler Primary Key, unpartitioniert
  Globaler partitionierter Primary Key
  Local prefixed Index
  Local prefixed Index, nur auf Partition Key
  Local NON prefixed Index
  Local NON prefixed Index auf normalem Attribut
Bitmapped Indexe
Anfügen von weiteren Partitionen wenn letzte Partition durch MAXVALUE begrenzt wurde
  Letzte Partition aufsplitten an geeigntem Ort
  Gesplittete Partitionen umbenennen
  Lokale Indexe ebenfalls umbenennen
  Rebuild aller lokalen Indexe
  Alternative -- letzte Partition löschen und ohne MAXVALUE erstellen
Anfügen einer weiteren, globalen Partition für den Primary Key
  Letzte Partition aufsplitten an geeigntem Ort
  Gesplittete Partitionen umbenennen
  Rebuild des Primary Key über alle Index Partitionen hinweg
Verschieben (Move) von Partitionen in anderen Tablespace
EXCHANGE Partition (Daten werden nicht transferiert !)
Enable Row Movement and LOB Support (ab 8.1.5)
Index Only Partition Table (ab 8.1.5)
Mergen von Partitions (ab 8.1.5)
Hash Partitions (ab 8.1.5)
Indexes for Hash Partitions (ab 8.1.5)
Composite Partitioning (ab 8.1.5)
Indexes for Composite Partitioning (ab 8.1.5)
Index Unusable wenn
Wahl des geeigneten Partition Indexes
Zusammenfassung der wichtigesten Punkte

Übersicht

Unter Oracle 8 können sowohl Tabellen wie auch Indexe partitioniert werden. Tabellen werden gemäss einem Attribut der Tabelle, dem Partition-Key vertikal aufgeteilt (Range Partitioning). Das Partitionieren von Indexen ist komplexer, hier unterscheidet man verschiedene Partitionierungsarten.

Table Partitionierung

  • Vollkommen transparent für die Applikation.
  • Aufteilung der Daten meist nach Zeit, Geographisch, Nummernbereich.
  • Alle Partitionen der Tabelle verfügen über die selben logischen Attribute.
  • Partitionen einer Tabelle können unterschiedliche physische Paramater haben.
  • Die Partitionen einer Tabelle können in unterschiedlichen Tablespaces sein.
  • Eine partitionierte Tabelle kann normale und partitionierte Indexe beinhalten.
  • Eine nicht partitionierte Tabelle kann nur global partitionierte Indexe haben.
  • Wenn die letzte Partition mit MAXVALUE begrenzt wird, so kann keine neue Partition
    angehängt werden. Die letzte Partition muss dann mit SPLIT PARTITION aufgeteilt werden
    was in der Regel ein erheblicher Administrationsaufwand bedeutet.

Index Partitionierung

  • Bei lokalen Indexen entspricht die Anzahl und Namensgebung den Table Partitionen.
  • Lokale Index Partitionen werden von Oracle praktisch automatisch administriert.
  • Ein Bitmapped Index kann nur lokal definiert werden.
  • Eine partitionierte Tabelle kann normale und partitionierte Indexe beinhalten.
  • Eine nicht partitionierte Tabelle kann nur global partitionierte Indexe haben.
  • Ein lokaler Index beinhaltet nur die Rows der zum Index gehörenden Tabellen Partition.
  • Der Oracle Server konstruiert einen lokalen Index automatisch so, dass er equipartitioniert ist mit der entsprechenden Tabelle. Der lokale Index hat die gleichen Partition Grenzen (Bounds) wie die entsprechende Table Partition.
  • Oracle administriert einen lokalen Index automatisch wenn eine Table Partition gedroppt, gesplittet oder addiert wird. Allerdings werden die Indexe als Index Unusable gekennzeichnet und müssen neu gebildet werden.

Local prefixed Indexes

  • 1 Index pro Table Partition
  • Linke(s) Attribut(e) im Index entspricht dem Partition-Key
  • UNIQUE möglich
  • Partition Unabhängigkeit gewärleistet
  • Gute Parallelisierung (PQO)

Local NON prefixed Indexes

  • 1 Index pro Table Partition
  • Linke(s) Attribut(e) im Index entspricht NICHT dem Partition-Key
  • UNIQUE möglich wenn Partition-Key Teil des Index-Keys ist
  • Partition Unabhängigkeit gewärleistet
  • Beste Parallelisierung (PQO)

Global partitionierter Index

  • Unabhängig von der Table Partitionierung
  • Anzahl der Index Partitionen muss nicht mit der Anzahl Table Partitionen übereinstimmen
  • Schnelle partitionsübergreifende Suche, jedoch Index-Rebuild nötig, wenn Table Partitionen verändert werden müssen.
  • Ein "MUST" für Primary Keys, welche ohne den Partition-Key auskommen müssen.
  • Für Bitmapped Indexe nicht möglich.

Partition Table mit Partition Key als Teil des Primary Keys

Dies hat den grossen Vorteil, dass der Primary Key als local Index definiert werden kann. Der Primary Key ist dann also equipartitioniert mit der Tabelle, das heisst: Der Primary Key hat die gleiche Anzahl Partitions wie die Tabelle. Die Administration von lokalen Indexen ist sehr einfach, bzw wird durch Oracle praktisch automatisch vorgenommen, im Gegensatz zu globalen Indexen welche manuell administriert werden müssen. Es ist jedoch nicht immer möglich den Partition Key als Teil des Primary Keys zu definieren.

Example

  • Partition Key = [DATE_CDR]
  • Primary Key = [BKG_ID,DATE_CDR]

CREATE TABLE cdr (
 bkg_id                          NUMBER(15)                 NOT NULL,
 date_cdr                        DATE                       NOT NULL,
 calltype                        NUMBER(2)                  NOT NULL)
PARTITION BY RANGE (date_cdr)
 (PARTITION cdr_01_1999 VALUES LESS THAN
 (TO_DATE('01.02.1999','DD.MM.YYYY'))
  TABLESPACE cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
  PARTITION cdr_02_1999 VALUES LESS THAN
 (TO_DATE('01.03.1999','DD.MM.YYYY'))
  TABLESPACE cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
  PARTITION cdr_03_1999 VALUES LESS THAN
 (TO_DATE('01.04.1999','DD.MM.YYYY'))
  TABLESPACE cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
  PARTITION cdr_04_1999 VALUES LESS THAN
 (TO_DATE('01.05.1999','DD.MM.YYYY'))
  TABLESPACE cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
  PARTITION cdr_05_1999 VALUES LESS THAN (MAXVALUE)
  TABLESPACE cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
ENABLE ROW MOVEMENT
/

Local NON prefixed Index (Primary Key)

Der linke Teil des Index stimmt nicht mit dem Partition-Key [date_cdr] überein. Dieser Index wird von Oracle automatisch administriert wenn neue Partitionen dazu kommen. Er bleibt immer equipartitioniert, was ein sehr grosser Vorteil ist, der Admin Aufwand ist sehr klein. Ein local NON prefixed Index kann UNIQUE sein, wenn der Partition Key Teil des Index Keys ist, was hier mit dem Primary Key natürlich gegeben ist.

ALTER TABLE cdr ADD (
  CONSTRAINT pk_cdr
  PRIMARY KEY (bkg_id,date_cdr)
USING INDEX
LOCAL
(PARTITION cdr_01_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_02_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_03_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_04_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_05_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0))
/

Ein normales Attribut [calltype] kann mit einem partitionierten, lokalen NON prefixed Index versehen werden. Natürlich kann dies kein UNIQUE Index sein (Ein local NON prefixed Index kann UNIQUE sein, wenn der Partition Key Teil des Index Keys ist).

CREATE INDEX cdr_idx_1 ON cdr (calltype)
LOCAL
(PARTITION cdr_01_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_02_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_03_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_04_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_05_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/

Local prefixed Indexes

Der linke Teil des Index stimmt mit dem Partition-Key [date_cdr] überein. Dieser Index wird von Oracle automatisch administriert wenn neue Partitionen dazu kommen. Er bleibt immer equipartitioniert, was ein sehr grosser Vorteil ist, der Admin Aufwand ist also sehr klein. Der Index kann UNIQUE sein, da der Partition Key im Primary Key [bkg_id, date_cdr] enthalten ist.

CREATE UNIQUE INDEX cdr_idx_1 ON cdr (date_cdr,bkg_id)
LOCAL
(PARTITION cdr_01_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_02_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_03_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_04_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_05_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/
ALTER TABLE cdr ADD (
      CONSTRAINT unq_pk_reverse
      UNIQUE (date_cdr,bkg_id))
/

Der folgende Index besteht nur aus dem Partition Key, er kann ebenfalls UNIQUE sein, da er ein Teil des Primary Keys ist.

CREATE UNIQUE INDEX un_date_cdr_idx ON cdr (date_cdr)
LOCAL
(PARTITION cdr_01_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_02_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_03_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_04_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
 PARTITION cdr_05_1999
  TABLESPACE idx_cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/

Partition Table wenn Partition Key nicht Teil des Primary Keys ist

Es ist nicht immer möglich, denn Partition Key im Primary Key zu integrieren. Manchmal ist der Partition Key ein normales Attribut.

  • Partition Key = [DATE_REQ]
  • Primary Key = [BKG_ID,REQ_ID]

CREATE TABLE req (
 bkg_id                          NUMBER(15)                 NOT NULL,
 req_id                          NUMBER(15)                 NOT NULL,
 date_req                        DATE                       NOT NULL,
 status                          NUMBER(2)                  NOT NULL)
PARTITION BY RANGE (date_req)
 (PARTITION req_01_1999 VALUES LESS THAN
 (TO_DATE('01.02.1999','DD.MM.YYYY'))
  TABLESPACE req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
  PARTITION req_02_1999 VALUES LESS THAN
 (TO_DATE('01.03.1999','DD.MM.YYYY'))
  TABLESPACE req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
  PARTITION req_03_1999 VALUES LESS THAN
 (TO_DATE('01.04.1999','DD.MM.YYYY'))
  TABLESPACE req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
  PARTITION req_04_1999 VALUES LESS THAN
 (TO_DATE('01.05.1999','DD.MM.YYYY'))
  TABLESPACE req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
  PARTITION req_05_1999 VALUES LESS THAN (MAXVALUE)
  TABLESPACE req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/

Normaler Primary Key, unpartitioniert

Normaler Primary Key, unpartitioniert, nicht zu empfehlen da der Index wieder sehr gross wird. Besser nach einem anderen Kriterium partionieren, zB [bkg_id,req_id].

Globaler partitionierter Primary Key

Globaler Primary Key, ein "MUST" für Primary Keys welche ohne den Partition-Key auskommen müssen. Die Partitionierung erfolgt hier also für den Primary Key = Partition Key, es entsteht eine vollkommen neue, von [date_req] unabhängige Partitionierung. Der grosse Nachteil der globalen Index Pertitionen besteht darin, dass diese von Oracle nicht automatisch administriert werden.

CREATE UNIQUE INDEX pk_req ON req (bkg_id,req_id)
GLOBAL
PARTITION BY RANGE (bkg_id,req_id)
(PARTITION pk_req_01
  VALUES LESS THAN (100000,100000)
  TABLESPACE idx_bkg
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION pk_req_02
  VALUES LESS THAN (200000,200000)
  TABLESPACE idx_bkg
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION pk_req_03
  VALUES LESS THAN (300000,300000)
  TABLESPACE idx_bkg
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION pk_req_04
  VALUES LESS THAN (MAXVALUE,MAXVALUE)
  TABLESPACE idx_bkg
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/
ALTER TABLE req ADD (
      CONSTRAINT pk_req
      PRIMARY KEY (bkg_id,req_id))
/

Local prefixed Index

Local prefixed Index (UNIQUE wäre möglich), der linke Teil des Index stimmt
mit dem Partition-Key [date_req] überein.

CREATE INDEX idx_req_1 ON req (date_req,req_id)
LOCAL
(PARTITION req_01_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION req_02_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION req_03_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION req_04_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION req_05_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/

Local prefixed Index, nur auf Partition Key

Local prefixed Index nur auf Partition-Key. Der Index entspricht mit dem Partition-Key [date_req], UNIQUE wäre hier nicht möglich, da der Partition Key nicht im Primary Key enthalten ist.

CREATE INDEX idx_req_2 ON req (date_req)
LOCAL
(PARTITION req_01_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION req_02_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION req_03_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION req_04_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION req_05_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/

Local NON prefixed Index

Local non prefixed Index, der linke Teil des Index stimmt NICHT mit dem Partition-Key [date_req] überein. UNIQUE wäre hier nicht möglich, da der Partition Key nicht im Primary Key enthalten ist.

CREATE INDEX idx_req_3 ON req (req_id,date_req)
LOCAL
(PARTITION req_01_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION req_02_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION req_03_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION req_04_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION req_05_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/

Local NON prefixed Index auf normalem Attribut

CREATE INDEX idx_req_4 ON req (req_id)
LOCAL
(PARTITION req_01_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION req_02_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION req_03_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION req_04_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION req_05_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/

Bitmapped Indexe

Bitmapped Indexe sind immer local, global nicht möglich.

CREATE BITMAP INDEX idx_bm_req_1 ON req (status)
LOCAL
(PARTITION req_01_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION req_02_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION req_03_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION req_04_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION req_05_1999
  TABLESPACE idx_req
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/

Anfügen von weiteren Partitionen wenn letzte
Partition durch MAXVALUE begrenzt wurde

Der Administartionsaufwand ist beträchtlich, man muss die letzte Partition zuerst in zwei "neue" Partitionen unterteilen und diese dann umbenennen. Die Lokalen Indexe werden von Oracle als IU (Index Unusable) gekennzeichnet und müssen neu generiert werden. Das folgende Beispiel erläutert dies.

Letzte Partition aufsplitten an geeigntem Ort

ALTER TABLE req
  SPLIT PARTITION req_05_1999 AT (TO_DATE('31.05.1999','DD.MM.YYYY'))
  INTO
  (PARTITION req_05_1999_1
     TABLESPACE req
     STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
   PARTITION req_05_1999_2
     TABLESPACE req
     STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/

Gesplittete Partitionen umbenennen

ALTER TABLE req
  RENAME PARTITION req_05_1999_1 TO req_05_1999
/
ALTER TABLE req
  RENAME PARTITION req_05_1999_2 TO req_06_1999
/

Lokale Indexe ebenfalls umbenennen

Die lokalen Indexe wurden auch aufgeteilt und müssen nun wieder auf die gleichen
Partionsnamen geändert werden

ALTER INDEX idx_req_1
  RENAME PARTITION req_05_1999_1 TO req_05_1999
/
ALTER INDEX idx_req_1
  RENAME PARTITION req_06_1999_1 TO req_06_1999

ALTER INDEX idx_req_2
  RENAME PARTITION req_05_1999_1 TO req_05_1999
/
ALTER INDEX idx_req_2
  RENAME PARTITION req_06_1999_1 TO req_06_1999
/
ALTER INDEX idx_req_3
  RENAME PARTITION req_05_1999_1 TO req_05_1999
/
ALTER INDEX idx_req_3
  RENAME PARTITION req_06_1999_1 TO req_06_1999
/
ALTER INDEX idx_req_4
  RENAME PARTITION req_05_1999_1 TO req_05_1999
/
ALTER INDEX idx_req_4
  RENAME PARTITION req_06_1999_1 TO req_06_1999
/
ALTER INDEX idx_bm_req_1
  RENAME PARTITION req_05_1999_1 TO req_05_1999
/
ALTER INDEX idx_bm_req_1
  RENAME PARTITION req_06_1999_1 TO req_06_1999
/

Rebuild aller lokalen Indexe

ALTER TABLE req MODIFY PARTITION req_01_1999
  REBUILD UNUSABLE LOCAL INDEXES
/ 
ALTER TABLE req MODIFY PARTITION req_02_1999
  REBUILD UNUSABLE LOCAL INDEXES
/
ALTER TABLE req MODIFY PARTITION req_03_1999
  REBUILD UNUSABLE LOCAL INDEXES
/
ALTER TABLE req MODIFY PARTITION req_04_1999
  REBUILD UNUSABLE LOCAL INDEXES
/
ALTER TABLE req MODIFY PARTITION req_05_1999
  REBUILD UNUSABLE LOCAL INDEXES
/
ALTER TABLE req MODIFY PARTITION req_06_1999
  REBUILD UNUSABLE LOCAL INDEXES
/

Alternative -- letzte Partition löschen und ohne MAXVALUE erstellen

Wir löschen wir die letzte Partition zuerst. Die local Indexes werden beim Hinzufügen von Partitions automatisch auch mit einer Indexpartition ergänzt (sehr gute Wartbarkeit).

ALTER TABLE cdr DROP PARTITION cdr_05_1999;
ALTER TABLE cdr
  ADD PARTITION cdr_05_1999 VALUES LESS THAN
    (TO_DATE('01.06.1999','DD.MM.YYYY'))
    TABLESPACE cdr
    STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0;
ALTER TABLE cdr
  ADD PARTITION cdr_06_1999 VALUES LESS THAN
    (TO_DATE('01.07.1999','DD.MM.YYYY'))
    TABLESPACE cdr
    STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0;

Anfügen einer weiteren, globalen Partition für den Primary Key

Der Administartionsaufwand ist beträchtlich, man muss die letzte Partition zuerst in zwei "neue" Partitionen unterteilen und diese dann umbenennen. Der globale Index auf dem Primary Key muss neu generiert werden. Das folgende Beispiel erläutert dies.

Letzte Partition aufsplitten an geeigntem Ort

ALTER INDEX pk_req
  SPLIT PARTITION pk_req_04 AT (400000,400000)
  INTO
  (PARTITION pk_req_04_1
     TABLESPACE req
     STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
   PARTITION pk_req_04_2
     TABLESPACE req
     STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
/

Gesplittete Partitionen umbenennen

ALTER INDEX pk_req
  RENAME PARTITION pk_req_04_1 TO pk_req_04
/
ALTER INDEX pk_req
  RENAME PARTITION pk_req_04_2 TO pk_req_05
/

Rebuild des Primary Key über alle Index Partitionen hinweg

ALTER INDEX pk_req
  REBUILD PARTITION pk_req_01
/
ALTER INDEX pk_req
  REBUILD PARTITION pk_req_02
/
ALTER INDEX pk_req
  REBUILD PARTITION pk_req_03
/
ALTER INDEX pk_req
  REBUILD PARTITION pk_req_04
/
ALTER INDEX pk_req
  REBUILD PARTITION pk_req_05
/

Verschieben (Move) von Partitionen in anderen Tablespace

ALTER TABLE req
  MOVE PARTITION req_08_1999
    TABLESPACE tab
    STORAGE (INITIAL 1K NEXT 1K MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0;

Indexe müssen rebuilded werden, da sie durch Move Partition IU wurden (Index Unusable)

ALTER INDEX idx_req_1
  REBUILD PARTITION req_08_1999
/
ALTER INDEX idx_req_2
  REBUILD PARTITION req_08_1999
/
ALTER INDEX idx_req_3
  REBUILD PARTITION req_08_1999
/
ALTER INDEX idx_req_4
  REBUILD PARTITION req_08_1999
/

EXCHANGE Partition (Daten werden nicht transferiert !)

  • Ändern einer normalen Tabelle in eine bestehende Partition.
  • Ändern einer Partition in eine bestehende normale Tabelle.
  • Praktisch um Oracle-7 Partition Views in Oracle-8 Partitions zu ändern.

ALTER TABLE sales
  EXCHANGE PARTITION feb_99 WITH TABLE sales_feb_99
  WITHOUT VALIDATION;

Dies ändert die Partition feb_99 aus der Partition Table sales zu einer normalen Tabelle mit dem Namen sales_feb_99. Man beachte nochmals, dass die Daten nicht transferiert werden.

Enable Row Movement and LOB Support (ab 8.1.5)

In Oracle 8.1.5 you can move a row from one partition to another using the UPDATE statement, if ENABLE ROW MOVEMENT is enabled in the CREATE TABLE or in the ALTER TABLE statement.

CREATE TABLE cdr
(bkg_id       NUMBER(15) NOT NULL,
 date_cdr     DATE       NOT NULL,
 description  CLOB)
PARTITION BY RANGE (date_cdr)
(PARTITION cdr_01_1999 VALUES LESS THAN (to_date('01.02.1999','DD.MM.YYYY'))
    LOB (description) STORE AS cdrdesc_01_1999 (TABLESPACE cdr
    STORAGE (INITIAL 16K NEXT 16K))
  TABLESPACE cdr
  STORAGE (INITIAL 16K NEXT 16K) PCTFREE 5,
PARTITION  cdr_02_1999 VALUES LESS THAN (MAXVALUE)
    LOB (description) STORE AS cdrdesc_02_1999 (TABLESPACE cdr
    STORAGE (INITIAL 16K NEXT 16K))
  TABLESPACE cdr
  STORAGE (INITIAL 16K NEXT 16K) PCTFREE 10)
/

INSERT INTO cdr VALUES(1,TO_DATE('15.01.1999','DD.MM.YYYY'));
INSERT INTO cdr VALUES(2,TO_DATE('10.02.1999','DD.MM.YYYY'));
COMMIT;

UPDATE cdr SET date_cdr = TO_DATE('15.01.1999','DD.MM.YYYY')
WHERE TO_CHAR(date_cdr,'DD.MM.YYYY') = '10.02.1999';

ORA-14402: updating partition key column would cause a partition change

ALTER TABLE cdr ENABLE ROW MOVEMENT;

UPDATE cdr SET date_cdr = TO_DATE('15.01.1999','DD.MM.YYYY')
WHERE TO_CHAR(date_cdr,'DD.MM.YYYY') = '10.02.1999';

1 row updated.

Index Only Partition Table (ab 8.1.5)

In Oracle 8.1.5 IOTs on Partition Tables are possible. The whole table is stored as an Index. The Partition Key must be a member of the PRIMARY KEY.

CREATE TABLE cdr
(bkg_id       NUMBER(15) NOT NULL,
 date_cdr     DATE       NOT NULL,
 description  CLOB,
PRIMARY KEY (bkg_id,date_cdr))
ORGANIZATION INDEX
PARTITION BY RANGE (date_cdr)
(PARTITION cdr_01_1999 VALUES LESS THAN (to_date('01.02.1999','DD.MM.YYYY'))
    LOB (description) STORE AS cdrdesc_01_1999 (TABLESPACE cdr
    STORAGE (INITIAL 16K NEXT 16K))
  TABLESPACE cdr
  STORAGE (INITIAL 16K NEXT 16K) PCTFREE 5,
PARTITION  cdr_02_1999 VALUES LESS THAN (MAXVALUE)
    LOB (description) STORE AS cdrdesc_02_1999 (TABLESPACE cdr
    STORAGE (INITIAL 16K NEXT 16K))
  TABLESPACE cdr
  STORAGE (INITIAL 16K NEXT 16K) PCTFREE 10)
ENABLE ROW MOVEMENT
/

Mergen von Partitions (ab 8.1.5)

With Oracle 8.1.5, you can not only split Partitions, now you can merge Partitions. Local Indexes will be automatically dropped and rebuild from Oracle, but this Indexes are IU = Index Unusable, therefore they must be rebuild. The following example shows this:

Build the Partition Table:

CREATE TABLE cdr (
bkg_id                           NUMBER(15)                  NOT NULL,
date_cdr                         DATE                        NOT NULL,
calltype                         NUMBER(2)                   NOT NULL)
PARTITION BY RANGE (date_cdr)
(PARTITION cdr_01_1999 VALUES LESS THAN
  (TO_DATE('01.02.1999','DD.MM.YYYY'))
  TABLESPACE cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION cdr_02_1999 VALUES LESS THAN
  (TO_DATE('01.03.1999','DD.MM.YYYY'))
  TABLESPACE cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION cdr_03_1999 VALUES LESS THAN
  (TO_DATE('01.04.1999','DD.MM.YYYY'))
  TABLESPACE cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION cdr_04_1999 VALUES LESS THAN
  (TO_DATE('01.05.1999','DD.MM.YYYY'))
  TABLESPACE cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION cdr_05_1999 VALUES LESS THAN (MAXVALUE)
  TABLESPACE cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0)
ENABLE ROW MOVEMENT
/

Build local Primary Key

ALTER TABLE cdr ADD (
  CONSTRAINT pk_cdr
  PRIMARY KEY (bkg_id,date_cdr)
  USING INDEX
  LOCAL
(PARTITION cdr_01_1999
  TABLESPACE idx
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION cdr_02_1999
  TABLESPACE idx
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION cdr_03_1999
  TABLESPACE idx
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION cdr_04_1999
  TABLESPACE idx
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0,
PARTITION cdr_05_1999
  TABLESPACE idx
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0))
/

Merge the the first two Partitons ....

ALTER TABLE cdr
MERGE PARTITIONS cdr_01_1999, cdr_02_1999
INTO PARTITION cdr_0102_1999
  TABLESPACE cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0
/

... and rebuild the IU Indexes, this way ...

ALTER TABLE cdr MODIFY PARTITION cdr_0102_1999
  REBUILD UNUSABLE LOCAL INDEXES;
ALTER TABLE cdr MODIFY PARTITION cdr_03_1999
  REBUILD UNUSABLE LOCAL INDEXES;
ALTER TABLE cdr MODIFY PARTITION cdr_04_1999
  REBUILD UNUSABLE LOCAL INDEXES;
ALTER TABLE cdr MODIFY PARTITION cdr_05_1999
  REBUILD UNUSABLE LOCAL INDEXES;

... or this way.

ALTER INDEX pk_cdr REBUILD PARTITION cdr_0102_1999;
ALTER INDEX pk_cdr REBUILD PARTITION cdr_03_1999;
ALTER INDEX pk_cdr REBUILD PARTITION cdr_04_1999;
ALTER INDEX pk_cdr REBUILD PARTITION cdr_05_1999;

Hash Partitions

Although partitioning by range is well-suited for historical databases, it may not be the best choice for other purposes. Another method of partitioning, hash partitioning, uses a hash function on the partitioning columns to stripe data into partitions. Hash partitioning allows data that does not lend itself to range partitioning to be easily partitioned for performance reasons (such as parallel DML).

You can specify hash partitioning in one of two ways:

  • You can specify the number of partitions. In this case, Oracle assigns partition
    names of the form SYS_Pnnn.

CREATE TABLE article
  (id    NUMBER,
  name   VARCHAR2 (60))
TABLESPACE tab
  STORAGE (INITIAL 19k)
  PARTITION BY HASH (id)
  PARTITIONS 4;

The STORE IN clause specifies one or more tablespaces where the hash partitions are to be stored. The number of tablespaces (tab1 and tab2 below) does not have to equal the number of partitions. If the number of partitions is greater than the number of tablespaces, Oracle cycles through the names of the tablespaces.

CREATE TABLE article
  (id    NUMBER,
  name   VARCHAR2 (60))
TABLESPACE tab
  STORAGE (INITIAL 19k)
  PARTITION BY HASH (id)
  PARTITIONS 4
  STORE IN (tab1, tab2);

  • Alternatively, you can specify individual partitions by name. The TABLESPACE clause specifies where the partition should be stored.

CREATE TABLE article
  (id    NUMBER,
  name   VARCHAR2 (60))
TABLESPACE tab
  STORAGE (INITIAL 19k)
  PARTITION BY HASH (id)
  (PARTITION p1 TABLESPACE tab1,
   PARTITION p2 TABLESPACE tab2);

Indexes for Hash Partitions

Hash partitioned Tables can be indexed with local, equi-partitoned Indexes. UNIQUE, Primary-Key Constraint and Bitmapped Indexes are supported.

CREATE UNIQUE INDEX pk_article ON article (id)
  STORAGE (INITIAL 19k NEXT 19k) PCTFREE 0
  LOCAL
  (PARTITION pk1 TABLESPACE tab1,
   PARTITION pk2 TABLESPACE tab2);

Composite Partitioning

Composite partitioning partitions data using the range method, and within each partition, subpartitions it using the hash method. Composite partitions are ideal for both historical data and striping, and provide improved manageability of range partitioning and data placement, as well as the parallelism advantages of hash partitioning.

CREATE TABLE cdr (
bkg_id       NUMBER(15)   NOT NULL,
date_cdr     DATE         NOT NULL,
calltype     NUMBER(2)    NOT NULL)
PARTITION BY RANGE (date_cdr)
SUBPARTITION BY HASH (bkg_id)
(PARTITION cdr_01_1999 VALUES LESS THAN
  (TO_DATE('01.02.1999','DD.MM.YYYY'))
  TABLESPACE cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0
    (SUBPARTITION cdr_01_1999_01 TABLESPACE tab,
     SUBPARTITION cdr_01_1999_02 TABLESPACE tab),
PARTITION cdr_02_1999 VALUES LESS THAN
  (TO_DATE('01.03.1999','DD.MM.YYYY'))
  TABLESPACE cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0
    (SUBPARTITION cdr_02_1999_01 TABLESPACE tab,
     SUBPARTITION cdr_02_1999_02 TABLESPACE tab),
PARTITION cdr_03_1999 VALUES LESS THAN
  (TO_DATE('01.04.1999','DD.MM.YYYY'))
  TABLESPACE cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0
    (SUBPARTITION cdr_03_1999_01 TABLESPACE tab,
     SUBPARTITION cdr_03_1999_02 TABLESPACE tab),
PARTITION cdr_04_1999 VALUES LESS THAN
  (TO_DATE('01.05.1999','DD.MM.YYYY'))
  TABLESPACE cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0
    (SUBPARTITION cdr_04_1999_01 TABLESPACE tab,
     SUBPARTITION cdr_04_1999_02 TABLESPACE tab),
PARTITION cdr_05_1999 VALUES LESS THAN (MAXVALUE)
  TABLESPACE cdr
  STORAGE (INITIAL 1K NEXT 100M MINEXTENTS 1 MAXEXTENTS UNLIMITED) PCTFREE 0
    (SUBPARTITION cdr_05_1999_01 TABLESPACE tab,
     SUBPARTITION cdr_05_1999_02 TABLESPACE tab))
ENABLE ROW MOVEMENT
/

Indexes for Composite Partitioning

Indexes for Sub-Partitions are always local Indexes

CREATE INDEX cdr_idx ON cdr (bkg_id, date_cdr)
   STORAGE (INITIAL 1M NEXT 1M MAXEXTENTS UNLIMITED)
   LOCAL
   (PARTITION cdr_01_1999
      (SUBPARTITION cdr_01_1999_01 TABLESPACE idx,
       SUBPARTITION cdr_01_1999_02 TABLESPACE idx),
    PARTITION cdr_02_1999
      (SUBPARTITION cdr_02_1999_01 TABLESPACE idx,
       SUBPARTITION cdr_02_1999_02 TABLESPACE idx),
    PARTITION cdr_03_1999
      (SUBPARTITION cdr_03_1999_01 TABLESPACE idx,
       SUBPARTITION cdr_03_1999_02 TABLESPACE idx),
    PARTITION cdr_04_1999
      (SUBPARTITION cdr_04_1999_01 TABLESPACE idx,
       SUBPARTITION cdr_04_1999_02 TABLESPACE idx),
    PARTITION cdr_05_1999
      (SUBPARTITION cdr_05_1999_01 TABLESPACE idx,
       SUBPARTITION cdr_05_1999_02 TABLESPACE idx));

Index Unusable wenn

  • Import
  • SQL*Loader mit Direct Path
  • Move Partition wenn ROWID die Partition wechselt.
  • Truncate der Partition wenn Partition Daten enthält.
  • ALTER TABLE SPLIT PARTITION.
  • ALTER INDEX SPLIT PARTITION.
  • TRUNCATE / DROP einer Partition setzt globalen Index immer aus IU.

Wahl des geeigneten Partition Indexes

  1. Stimmt die Reihenfolge des Partition Key mit der Index Reihenfolge überein ?

    Nein:

    Es kann nur ein lokal non prefixed Index benutzt werden. Dieser bietet die grösste Flexibilität, eine gute Performance solange die Abfrage auf eine Partition beschränkt ist. Der Index kann nicht UNIQUE sein, da er den Partition Key nicht im Index hat.

    Ja: Weiter zu 2.
     
  2. Wird der Index benutzt für Search auf Column, welche nicht im Partiton Key ist ?

    Nein: Es kann ein local prefixed Index benutzt werden. Dieser Index kann UNIQUE sein.
    Ja: Es (muss) ein globaler (immer prefixed) benutzt werden. Sehr gute Performance über alle Partitions hinweg, aber keine Partitionsflexibilität mehr. Dieser Index kann UNIQUE sein.

Zusammenfassung der wichtigesten Punkte

  • Equipartitioniert heisst, dass die Tabellen- und Indexpartitionen gekoppelt sind.
  • Globale Indexpartitionen haben absolut keine Relationen zu den Tabellenpartitionen.
  • Ein UPDATE auf dem Partition-Key misslingt, wenn die Row dadurch die Partition wechselt.
  • Der häufigste Fall für INDEX UNUSABLE ist ein TRUNCATE auf der betroffenen Partition.
  • Wird die erste Partition gelöscht, so werden neue Daten automatisch in die Zweite eingefügt.
  • TRUNCATE einer beliebigen Tabellenpartition hat zur Folge dass der gesamte globale Index IU wird.
  • Synonyme auf Tabellenpartitionen sind nicht möglich.
  • Views auf Tabellenpartitionen sind möglich, und damit auch Synonyme auf Views.