1 在12c中,允许多个索引存在于同一个列中
SQL> create unique index dept_idx1 on dept1(deptno);
Index created.
2 如果我们尝试在相同列创建列一个索引将报错
SQL> create unique index dept_idx2 on dept1(deptno);
create unique index dept_idx2 on dept1(deptno)
*
ERROR at line 1:
ORA-01408: such column list already indexed
3 将唯一列设置为不可见列之后,可在相同列上创建索引
SQL> alter index dept_idx1 invisible;
Index altered.
SQL> create index dept_idx2 on dept1(deptno);
Index created.
虽然索引不可见,但是被不可见的唯一索引还是生效的,我们无法插入重复值
SQL> insert into dept1(deptno) values (10);
insert into dept1(deptno) values (10)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.DEPT_IDX1) violated
查看表上的索引以及索引类型
SQL> col index_name for a30
SQL> select a.table_name,a.index_name,b.column_name,a.uniqueness,a.visibility
from user_indexes a,user_ind_columns b where a.index_name=b.index_name and a.table_name='DEPT1' ;
TABLE_NAME INDEX_NAME COLUMN_NAME UNIQUENES VISIBILIT
-------------------- ------------------------------ -------------------------- --------
DEPT1 DEPT_IDX1 DEPTNO UNIQUE INVISIBLE
DEPT1 DEPT_IDX2 DEPTNO NONUNIQUE VISIBLE