天萃荷净
ORACLE 12C 支持在相同列创建多个索引,从ORACLE 12C开始允许在同一列上创建不同类型的index,用来做性能调优,不同类型的index主要包括(B*TREE vs BITMAP,Local vs Global)
1.ORACLE 11G
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> CREATE TABLE T_oracleplus
2 (
3 EMPNO NUMBER(9) PRIMARY KEY ,
4 EMPNAME VARCHAR2(15) NOT NULL,
5 )
6 /
Table created.
SQL> CREATE INDEX EMP_DEPT_BTREE_IDX ON EMP(DEPTNO);
Index created.
SQL> CREATE BITMAP INDEX EMP_DEPTNO_BITMAP_IDX ON EMP(DEPTNO) INVISIBLE ;
CREATE BITMAP INDEX EMP_DEPTNO_BITMAP_IDX ON EMP(DEPTNO) INVISIBLE
*
ERROR at line 1:
ORA-01408: such column list already indexed
2.ORACLE 12C
CDB_PDB@CHF> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
PL/SQL Release 12.1.0.1.0 - Production 0
CORE 12.1.0.1.0 Production 0
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production 0
NLSRTL Version 12.1.0.1.0 - Production 0
CDB_PDB@CHF> CREATE TABLE T_oracleplus
2 (
3 EMPNO NUMBER(9) PRIMARY KEY ,
4 EMPNAME VARCHAR2(15) NOT NULL
5 )
6 /
表已创建。
CDB_PDB@CHF> CREATE INDEX EMP_DEPT_BTREE_IDX ON EMP(DEPTNO);
索引已创建。
CDB_PDB@CHF> CREATE BITMAP INDEX EMP_DEPTNO_BITMAP_IDX ON EMP(DEPTNO);
CREATE BITMAP INDEX EMP_DEPTNO_BITMAP_IDX ON EMP(DEPTNO)
*
第 1 行出现错误:
ORA-01408: 此列列表已索引
CDB_PDB@CHF> CREATE BITMAP INDEX EMP_DEPTNO_BITMAP_IDX ON EMP(DEPTNO) INVISIBLE ;
索引已创建。
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之学习笔记:Oracle 12C 支持在相同列创建多个索引index