Logo Oracle Deutschland   Datenbank Community  -   Oktober 2015
Mehrfachindizierung in 12c (multiple indexes on same set of columns)
von Ulrike Schwinn, Oracle Deutschland B.V. & Co. KG

Wenn es um Schemaänderungen geht, können auch Indizes betroffen sein. Seit 11g gibt es die Möglichkeit, statt des Indexlöschens ein Umschalten auf INVISIBLE durchzuführen. Das Indexsegment bleibt dabei weiter bestehen. Der INVISIBLE Index wird vom Optimizer dabei nicht mehr verwendet. Dieses Feature stellt eine wichtige Vereinfachung beim Umgang mit Indizes dar. Allerdings kann eine Spaltenkombination (mit gleicher Spaltenreihenfolge) nicht mit Indizes unterschiedlichen Typs (Mehrfachindizierung) belegt werden. Es könnte beispielsweise sinnvoll sein, Bitmap Indizes für Datawarehouse Abfragen anstatt B-Tree Indizes zu verwenden. Ähnliche Beispiele könnte man sich auch mit globalen und lokalen Indizes vorstellen. Der Versuch einer Mehrfachindizierung in 11g würde im Fehler ORA-01408 resultieren. 

-- Verhalten in 11g
SQL> create index i_t on t1(t);
Index created.

SQL> alter index i_t invisible;
Index altered.

SQL> create bitmap index i_t1 on t1(t);
 create bitmap index i_t1 on t1(t)
                                *
ERROR at line 1:
ORA-01408: such column list already indexed
In diesem Fall müsste man den Index wirklich löschen und wieder neuanlegen. Mit 12c ändert sich dieses Verhalten! Es ist jetzt möglich durch die Mehrfachindizierung die Indizierungsart zu ändern. Daher ist gewährleistet, dass zu jeder Zeit Indizes auf dieser Spaltenkombination vorhanden sind. Dieses Feature wird im Englischen auch mit "multiple indexes on same set of columns" bezeichnet. Eine wichtige Voraussetzung dazu ist, dass immer nur ein Index zu einer Zeit für den Optimizer verwendbar ist.

Nehmen wir in folgenden Beispielen die Kopie CUSTOMER_COPY der Tabelle CUSTOMERS, die im Moment noch keine Indizes besitzt. Im ersten Beispiel werden die Spalten CUST_FIRST_NAME und CUST_LAST_NAME indiziert. 
SQL> create index i_first_last_name on customer_copy(cust_first_name, cust_last_name);
Index created.

SQL> create index i_last_name on customer_copy (cust_last_name);
Index created.

SQL> create index i_first_name on customer_copy (cust_first_name);
Index created.
Die nächsten Beispiele zur Index Erzeugung sollen die gleiche Spaltenmenge (in gleicher Spaltenreihenfolge) - allerdings mit der Eigenschaft Bitmap - verwenden. Dies ist auch in Oracle Database 12c nicht ohne Weiteres möglich. Das erfolgreiche Anlegen dieser Indizes erfordert noch den Zusatz INVISIBLE. 
SQL> create bitmap index i_bitmap_first_last_name on customer_copy(cust_first_name, cust_last_name);
customer_copy(cust_first_name,cust_last_name)
              *
ERROR at line 2:
ORA-01408: such column list already indexed

SQL> create bitmap index i_bitmap_first_last_name on customer_copy(cust_first_name, cust_last_name)
     INVISIBLE;
Index created.

SQL> create bitmap index i_bitmap_last_name on customer_copy (cust_last_name) INVISIBLE;
Index created.
Hinweise: Es ist natürlich ebenso möglich, einen bestehenden Index mit dem Kommando ALTER INDEX in INVISIBLE umzuwandeln und danach weitere Indizes anzulegen.

Folgende Indizes stehen nun zur Verfügung. Wir verwenden die Views USER_INDEXES und USER_IND_COLUMNS, um die genaue Spaltenindizierung zu überwachen. 
SQL> SELECT i.index_name, c.column_name, i.visibility, i.index_type
     FROM user_indexes i, user_ind_columns c
     WHERE i.table_name='CUSTOMER_COPY' and i.index_name=c.index_name

INDEX_NAME                     COLUMN_NAME     VISIBILIT INDEX_TYPE
------------------------------ --------------- --------- ---------------------------
I_FIRST_LAST_NAME              CUST_FIRST_NAME VISIBLE   NORMAL
I_FIRST_LAST_NAME              CUST_LAST_NAME  VISIBLE   NORMAL
I_LAST_NAME                    CUST_LAST_NAME  VISIBLE   NORMAL
I_BITMAP_FIRST_LAST_NAME       CUST_FIRST_NAME INVISIBLE BITMAP
I_BITMAP_FIRST_LAST_NAME       CUST_LAST_NAME  INVISIBLE BITMAP
I_BITMAP_LAST_NAME             CUST_LAST_NAME  INVISIBLE BITMAP
I_FIRST_NAME                   CUST_FIRST_NAME VISIBLE   NORMAL
Vergessen Sie bitte dabei nicht, dass alle Indizes - mit der Eigenschaft VISIBLE oder INVISIBLE - Speicherplatz verbrauchen. Überprüfen kann man den Speicherplatzverbrauch mit USER_SEGMENTS. 
SQL> select segment_name, bytes/1024/1024 from user_segments
     where segment_type='INDEX' and segment_name like '%_NAME';

SEGMENT_NAME              BYTES/1024/1024
------------------------- ---------------
I_BITMAP_FIRST_LAST_NAME            .3125
I_BITMAP_LAST_NAME                   .125
I_FIRST_LAST_NAME                       2
I_FIRST_NAME                            2
I_LAST_NAME                             2
Dabei gelten folgende Regeln für die Indizes:
  • Die Indizes müssen von verschiedenem Typ sein, d.h. B-Tree, Bitmap, Reverse Key usw.
    • Ausnahmen bilden B-Tree und B-Tree Cluster Indizes sowie B-Tree und Index Organized Tables (IOTs). Übrigens sind auch für komprimierte und unkomprimierte Indizes ausgenommen.
  • Die Indizes sind verschieden partitioniert.
    • Sie können also partitioniert oder nicht partitioniert und lokal oder global partitioniert sein; oder sie unterscheiden sich in der Partitionierungsart wie z.B. HASH oder RANGE.
  • Die Indizes haben verschiedene UNIQUE und NON UNIQUE Eigenschaften.
Welche weiteren Vorteile kann diese Technik bieten? Da INVISIBLE Indizes vom Optimizer normalerweise nicht verwendet werden, kann man damit auch Indizes verschiedenen Typs sehr einfach testen. Folgendes Beispiel zeigt einen kleinen Indextest. Wir testen eine Abfrage auf die CUSTOMER_COPY Tabelle. Bei der ersten Abfrage wird der Index I_LAST_NAME verwendet. 
SQL> set autotrace on explain
SQL> select cust_last_name from customer_copy where rownum<1000;
Execution Plan
----------------------------------------------------------
Plan hash value: 553107609

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |   999 |  7992 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY        |             |       |       |            |          |
|   2 |   INDEX FAST FULL SCAN| I_LAST_NAME |   999 |  7992 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<1000)
Möchte man dieses Verhalten ändern, damit auch die INVISIBLE Indizes in Betracht kommen, setzt man den Session Parameter OPTIMIZER_USE_INVISIBLE_INDEXES auf TRUE. Unsere Abfrage verwendet nun den Bitmap Index I_BITMAP_LAST_NAME.

Lizenzierung

Die Nutzung dieses Features steht in allen Editionen zur Verfügung.

Weitere Informationen

Zurück zur Community-Seite