1.12c新特性-相同列创建多个索引
在12c中，在一个表的相同列上可以有多个索引（同类型或不同类型），但是某一时刻只能有一个设置为可见，除非其他索引是基于函数的索引（其实基于函数的索引并不基于相同的列，而是基于列上的函数）。 而在12c之前，相同的列（单个列或者顺序相同的多个列）只能有一个索引，即使是不同类型的索引（比如B树索引和位图索引）也不行。
2.用途
这个特性对于基于一个表的不同类型的负载很有用。一个系统在白天或者晚上的不同时间段，负载类型也会不一样，比如批处理、查询或数据仓库等，通过这个特性可以创建不同类型的索引来满足不同负载的需求。
3.限制
在相同的列上创建多个索引有这样的限制：
- 不能在相同的列上同时创建B树索引和B树聚簇索引；
- 不能在相同的列上创建B树索引和索引组织表（IOT）；
- 在做DML操作时，所有的索引都要维护（即便设置为不可见）。
记住，创建太多的索引会拖慢DML操作。
4.示例
4.1.在表dept_test列deptno创建第一个索引
--首先创建测试表dept1
SQL> create table dept_test as select * from dept;
表已创建。
--查看现在表上存在的索引
SQL> select * from dept_test;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select index_name,table_name,column_name from user_ind_columns where table_name='DEPT_TEST';
未选定行
SQL>
--在deptno列上创建第一个索引
SQL> create unique index dept_test_unique1 on dept_test(deptno);
索引已创建。
SQL> select index_name,table_name,column_name from user_ind_columns where table_name='DEPT_TEST';
INDEX_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ----------------------------------------
DEPT_TEST_UNIQUE1 DEPT_TEST DEPTNO
SQL> insert into dept_test values(10,'oracle1','oracle2');
insert into dept_test values(10,'oracle1','oracle2')
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.DEPT_TEST_UNIQUE1)
SQL>
--可以看到，创建的唯一索引已经生效
--测试当设置索引为不可见的时候，是否生效
SQL> set line 200
SQL> col TABLE_NAME for a20
SQL> col index_name for a20
SQL> col column_name for a40
SQL> select a.table_name,a.index_name,b.column_name,a.visibility
from user_indexes a,user_ind_columns b
where a.index_name = b.index_name
and a.table_name = 'DEPT_TEST';
TABLE_NAME INDEX_NAME COLUMN_NAME VISIBILIT
-------------------- -------------------- ---------------------------------------- ---------
DEPT_TEST DEPT_TEST_UNIQUE1 DEPTNO VISIBLE
SQL>
SQL> alter index dept_test_unique1 invisible;
Index altered.
SQL> select a.table_name,a.index_name,b.column_name,a.visibility
from user_indexes a,user_ind_columns b
where a.index_name = b.index_name
and a.table_name = 'DEPT_TEST'; 2 3 4
TABLE_NAME INDEX_NAME COLUMN_NAME VISIBILIT
-------------------- -------------------- ---------------------------------------- ---------
DEPT_TEST DEPT_TEST_UNIQUE1 DEPTNO INVISIBLE
SQL>
--可以看到，就算索引设置为不可见，依旧在生效
SQL> insert into dept_test values(10,'oracle1','oracle2');
insert into dept_test values(10,'oracle1','oracle2')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.DEPT_TEST_UNIQUE1) violated
SQL>
复制4.2.在表dept_test列deptno创建第二个索引
SQL> alter index dept_test_unique1 visible;
Index altered.
SQL> select a.table_name,a.index_name,b.column_name,a.visibility
from user_indexes a,user_ind_columns b
where a.index_name = b.index_name
and a.table_name = 'DEPT_TEST'; 2 3 4
TABLE_NAME INDEX_NAME COLUMN_NAME VISIBILIT
-------------------- -------------------- ---------------------------------------- ---------
DEPT_TEST DEPT_TEST_UNIQUE1 DEPTNO VISIBLE
SQL>
--当列上已经有索引，并且为可见的时候，相同列创建索引会报错
SQL> create index dept_test_normal1 on dept_test(deptno);
create index dept_test_normal1 on dept_test(deptno)
*
ERROR at line 1:
ORA-01408: such column list already indexed
SQL>
--我们把第一个索引设置为不可见之后创建第二个索引
SQL> alter index dept_test_unique1 invisible;
Index altered.
SQL> select a.table_name,a.index_name,b.column_name,a.visibility
from user_indexes a,user_ind_columns b
where a.index_name = b.index_name
and a.table_name = 'DEPT_TEST';
TABLE_NAME INDEX_NAME COLUMN_NAME VISIBILIT
-------------------- -------------------- ---------------------------------------- ---------
DEPT_TEST DEPT_TEST_UNIQUE1 DEPTNO INVISIBLE
SQL> create index dept_test_normal1 on dept_test(deptno);
Index created.
SQL> select a.table_name,a.index_name,b.column_name,a.visibility
from user_indexes a,user_ind_columns b
where a.index_name = b.index_name
and a.table_name = 'DEPT_TEST';
SQL>
TABLE_NAME INDEX_NAME COLUMN_NAME VISIBILIT
-------------------- -------------------- ---------------------------------------- ---------
DEPT_TEST DEPT_TEST_UNIQUE1 DEPTNO INVISIBLE
DEPT_TEST DEPT_TEST_NORMAL1 DEPTNO VISIBLE
SQL>
--可以看到在同一列上创建了两个索引，其中一个为可见，一个为不可见。我们尝试把不可见的索引设置为可见。
SQL> alter index DEPT_TEST_UNIQUE1 visible;
alter index DEPT_TEST_UNIQUE1 visible
*
ERROR at line 1:
ORA-14147: There is an existing VISIBLE index defined on the same set of columns.
SQL>
复制值得注意的是尽管有唯一索引，但是只有可见的索引才能被用到。
SQL> select deptno from dept_test where deptno=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 4002397063
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| DEPT_TEST_NORMAL1 | 1 | 3 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
复制4.3.在表dept_test列deptno创建函数索引
如果创建普通索引，那必须把其他索引，也就是前两个索引都设置为不可见才能继续创建。
但如果创建基于函数的索引，则列上可以有两个可见的索引。
SQL> create index dept_test_fb on dept_test(substr(deptno,1,1));
Index created.
SQL> select a.table_name,a.index_name,b.column_name,a.visibility
from user_indexes a,user_ind_columns b
where a.index_name = b.index_name
and a.table_name = 'DEPT_TEST';
TABLE_NAME INDEX_NAME COLUMN_NAME VISIBILIT
-------------------- -------------------- ---------------------------------------- ---------
DEPT_TEST DEPT_TEST_UNIQUE1 DEPTNO INVISIBLE
DEPT_TEST DEPT_TEST_NORMAL1 DEPTNO VISIBLE
DEPT_TEST DEPT_TEST_FB SYS_NC00004$ VISIBLE
SQL>
复制可以看到，有两个索引是可见的。
5.总结
在oracle 12c以上，通过使用不可见索引，可以在同一列或相同的多个列上创建多个索引。这对于不同负载会很有用。比如白天的负载中国使用B树索引，而在晚上的负载中使用反向键索引。但是需要注意的是，在做DML操作的时候，这些索引需要额外的开销进行维护。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
