12C新特性：相同字段上的多重索引

12C开始可以在同一字段上创建不同种类的索引，前提条件是只能启用一个。建第二个索引时，需要把第一个索引invisible。

SQL> CREATE TABLE t_whs (ID number, WNAME varchar2(10));

Table created.

SQL> begin
  2  for i in 0 .. 49 loop
  3  insert into t_whs values(i,'whs');
  4  end loop ;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> create index idx_id1 on t_whs(id);

Index created.

SQL> create bitmap index idx_id1 on t_whs(id);
create bitmap index idx_id1 on t_whs(id)
                    *
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> create bitmap index idx_id2 on t_whs(id);
create bitmap index idx_id2 on t_whs(id)
                                     *
ERROR at line 1:
ORA-01408: such column list already indexed

SQL> alter index idx_id1 invisible;     《==需要invisible后才行

Index altered.

SQL> create bitmap index idx_id2 on t_whs(id);

Index created.

以下为11.2.0.4上的操作，是不支持的

[oracle@hammer ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 3 11:00:32 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE TABLE t_whs (ID number, WNAME varchar2(10));

Table created.

SQL> begin
  2  for i in 0 .. 49 loop
  3  insert into t_whs values(i,'whs');
  4  end loop ;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> create index idx_id1 on t_whs(id);

Index created.

SQL> create bitmap index idx_id2 on t_whs(id);
create bitmap index idx_id2 on t_whs(id)
                                     *
ERROR at line 1:
ORA-01408: such column list already indexed

SQL> alter index idx_id1 invisible;

Index altered.

SQL> create bitmap index idx_id2 on t_whs(id);
create bitmap index idx_id2 on t_whs(id)
                                     *
ERROR at line 1:
ORA-01408: such column list already indexed