Globale Indizes auf partitionierte Tabellen

Wird ein globaler Index auf eine partitionierte Tabelle erstellt, so wird der Index implizit ungültig, sobald  eine Partition der indizierten Tabelle hinzugefügt oder aber eine bestehende gelöscht wird. Die Fehlermeldung lautet dann: ORA-01502 Oracle Index in Unusable State.

Welche Indizes invalide sind, können Sie mit dem folgenden Statement ermitteln:

Oracle: Invalider Index / Unusable State
SELECT * 
FROM  DBA_INDEXES 
WHERE STATUS = 'INVALID';

Die Korrektur ist recht einfach. Ein Index-Rebuild schafft hier Abhilfe:

Oracle: Index Rebuild
ALTER INDEX index_name REBUILD;
 

Um von vorneherein zu verhindern, dass ein Index durch Änderungen an Partitionen der darunterliegenden Tabellen invalide werden, können Indizes auf Tabellen als lokaler Index erzeugt werden. Ein lokaler Index ist equipartitioniert zur darunter liegenden Tabelle:

Oracle: Lokalen Index erstellen
CREATE INDEX  <index_name> 
ON            <tabellen_name> (<spalten_liste>) 
LOCAL 
ONLINE;

Um zu prüfen, welche Ihrer partitionierten Tabellen einen globalen Index besitzen, können Sie die folgende Abfrage verwenden:

Oracle: Globale Indizes auf lokale Tabellen ermitteln
-- Geben Sie hier den Namen des Schemas 
-- an, das überprüft werden soll:
DEFINE Benutzername=SCOTT 
 
 
-- Script-Start
SET LINES 180
SET pages 3000
SET trimspool ON
SET trimout ON
 
SELECT a.index_name, b.table_name 
FROM
   (
    SELECT owner, index_name
    FROM   dba_indexes
    WHERE  owner = '&&Benutzername'
    MINUS  
    SELECT index_owner, index_name
    FROM   dba_ind_partitions
    WHERE  index_owner = '&&Benutzername'
   ) a,
   dba_indexes b
WHERE a.index_name = b.index_name
AND   a.owner = b.owner
AND   b.table_name IN
          (SELECT table_name 
           FROM   dba_tab_partitions  p
           WHERE  p.table_owner = '&&Benutzername');
/