Daily Archives: March 29, 2016

12c, Administration, Performance

Oracle 12c – Use Index enhancements

Leave a comment

You can create multiple indexes of different types on the same set of columns now.   But only one index will be visible at any given time.   Multiple indexes can exist when one of the following is true

  1. B-tree vs bitmap
  2. Different partitioning sets exist.
  3. Unique vs non-unique.

 

 

Example: Creating two indexes on single column:

 

  1. Logon to SQLPLUS.

 

[oracle@linux2 ~]$ sqlplus scott/password

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 29 13:47:09 2016

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

Last Successful login time: Tue Jan 27 2016 09:57:58 -05:00

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL>

 

  1. Create bitmap index on table scott.emp(job).

 

SQL> create bitmap index bm_scott_emp_job on scott.emp(job);

Index created.

SQL>

 

  1. View the new index from user_constraints.

 

SQL> column index_name format a20

SQL> column index_type format a10

SQL> select index_name, index_type, visibility from user_indexes where table_name=’EMP’;

 

INDEX_NAME           INDEX_TYPE VISIBILIT

——————– ———- ———

BM_SCOTT_EMP_JOB     BITMAP     VISIBLE

PK_EMP               NORMAL     VISIBLE

SQL>

 

  1. Make first index invisible.

 

alter index bm_scott_emp_job invisible;

SQL> alter index bm_scott_emp_job invisible;

Index altered.

SQL>

 

 

  1. Now create second index on table scott.emp(job) but this one a b-tree.

 

create index bt_scott_emp_job on scott.emp(job);

SQL> create index bt_scott_emp_job on scott.emp(job);

Index created.

SQL>

 

  1. NOTE: the first index must be invisible before you can create the second index.   Now see the indexes on table EMP.

 

 

SQL> column index_name format a20

column index_type format a10

select index_name, index_type, visibility from user_indexes where table_name=’EMP’;SQL> SQL>

 

INDEX_NAME           INDEX_TYPE VISIBILIT

——————– ———- ———

BM_SCOTT_EMP_JOB     BITMAP     INVISIBLE

BT_SCOTT_EMP_JOB     NORMAL     VISIBLE

PK_EMP               NORMAL     VISIBLE

 

SQL>

 

  1. This completes index enhancements in Oracle 12c

 

 

Larry Catt

OCP