Oracle 12C 单列上多个索引

在12C中，在单个列上可以建立多个索引，但是同一时间只有一个索引可用，通过将索引设置成INVISIBLE和VISIBLE实现。
下面的实例，是在同一个字段上建立了1各B Tree索引和1各BITMAP索引，通过trace跟踪索引使用情况

SQL> desc n_obj;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(128)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(13)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)

SQL> delete from n_obj;

90809 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into n_obj select * from dba_objects where rownum<1001;

1000 rows created.

SQL> commit;

Commit complete.

SQL> create index n_obj_id_btree on n_obj(OBJECT_ID);

Index created.
SQL> create bitmap index n_obj_id_bitmap on n_obj(OBJECT_ID) INVISIBLE;

Index created.

SQL> set auto trace traceonly
SQL> select OBJECT_ID from c##awen.n_obj where OBJECT_ID>100;

901 rows selected.

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 2281190708

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 90711 | 442K| 3 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| N_OBJ_ID_BTREE | 90711 | 442K| 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID">100)


Statistics
----------------------------------------------------------
205 recursive calls
0 db block gets
414 consistent gets
4 physical reads
0 redo size
16731 bytes sent via SQL*Net to client
1203 bytes received via SQL*Net from client
62 SQL*Net roundtrips to/from client
30 sorts (memory)
0 sorts (disk)
901 rows processed

SQL> alter index n_obj_id_btree INVISIBLE;

Index altered.

SQL> alter index n_obj_id_bitmap VISIBLE;

Index altered.

SQL> select OBJECT_ID from c##awen.n_obj where OBJECT_ID>100;

901 rows selected.

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 1931127315

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 90711 | 442K| 4 (0)| 00:00:01 |
| 1 | BITMAP CONVERSION TO ROWIDS | | 90711 | 442K| 4 (0)| 00:00:01 |
|* 2 | BITMAP INDEX FAST FULL SCAN| N_OBJ_ID_BITMAP | | | | |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID">100)


Statistics
----------------------------------------------------------
57 recursive calls
0 db block gets
116 consistent gets
6 physical reads
0 redo size
16731 bytes sent via SQL*Net to client
1203 bytes received via SQL*Net from client
62 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
901 rows processed

SQL>