联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
从ORACLE 12C开始允许在同一列上创建不同类型的index,用来做性能调优,不同类型的index主要包括(B*TREE vs BITMAP,Local vs Global)
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_XIFENFEI 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
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_XIFENFEI 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 ; 索引已创建。