Kilka indeksów na tej samej kolumnie (wyrażeniu) ?!

W przeszłości wielokrotnie podejmowałem dyskusje dotyczące właściwego doboru typu indeksu w zależności od charakterystyki danych i od rodzaju wykonywanych zapytań. Wydawało się, że jednym z niespełnionych marzeń programistów było utworzenie wielu alternatywnych indeksów na tej samej kolumnie (np. bitmapowy, b-drzewo, partycjonowany lokalnie, partycjonowany globalnie,…) tak, aby to optymalizator zapytań (a nie programista) wybrał sobie ten, który jest najkorzystniejszy. Dotąd jednak było to niemożliwe.

W wersji Oracle Database 12c nieśmiało wprowadzono możliwość definiowania kilku indeksów na tej samej kolumnie (lub wyrażeniu). I chociaż nie uważam, aby dobrą praktyką było tworzenie wszystkich możliwych typów indeksów na każdej kolumnie, to jednak niewątpliwie taki mechanizm może nam się przydać podczas wstępnych testów i eksperymentów.

Oracle Database 12c umożliwia utworzenie kilku indeksów na tej samej kolumnie, ale tylko jeden z nich może być VISIBLE, pozostałe muszą być INVISIBLE. W normalnych warunkach pracy optymalizator zapytań korzysta tylko z indeksu o statusie VISIBLE. Możemy jednak zmienić tryb pracy optymalizatora i za pomocą parametru optimizer_use_invisible_indexes=true sprawić, aby optymalizator mógł rozważać także użycie każdego z indeksów INVISIBLE.

Oto przykład prostej zabawy w „który lepszy?”:

SQL> create table test_ind as select * from dba_objects;
SQL> create bitmap index ind1 on test_ind(owner) invisible;
SQL> create index ind2 on test_ind(owner) invisible;


SQL> alter session set optimizer_use_invisible_indexes=true;


SQL> set autotrace on
SQL> select count(*) from test_ind where owner='SCOTT';
...
Execution Plan
------------------------------------------------------------------------
| Id | Operation                | Name | Rows| Bytes | Cost (%CPU)|Time|
------------------------------------------------------------------------
| 0  | SELECT STATEMENT         |      | 1   | 66    | 1 (0)| 00:00:01 |
| 1  | SORT AGGREGATE           |      | 1   | 66    |      |          |
| 2  | BITMAP CONVERSION COUNT  |      | 145 | 9570  | 1 (0)| 00:00:01 |
|* 3 | BITMAP INDEX SINGLE VALUE| IND1 |     |       |      |          |
------------------------------------------------------------------------


SQL> select max(owner) from test_ind
...
Execution Plan
-------------------------------------------------------------------------
| Id | Operation                | Name | Rows | Bytes | Cost (%CPU)|Time|
-------------------------------------------------------------------------
| 0  | SELECT STATEMENT         |      | 1    | 66    | 2 (0)| 00:00:01 |
| 1  | SORT AGGREGATE           |      | 1    | 66    |      |          |
| 2  | INDEX FULL SCAN (MIN/MAX)| IND2 | 1    | 66    | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------