Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

New Index enhancement in Oracle 12c Part 1 – Multiple indexes on the same set of columns

New Index enhancement in Oracle 12c Part 1 – Multiple indexes on the same set of columns

 

In the article you will have a look at the following new index feature in oracle 12c ‘Multiple indexes on the same set of columns’. Staring with Oracle 12c you are enabled to create multiple indexes on the same set of columns. The following different indexes can be created on the same set of columns.

  1. Index of different type. Bitmap index and B-tree index.
  2. Partitioned index and not partitioned index.
  3. Locally partitioned index and globally partitioned index.
  4. Indexes differing in partitioning range and hash.
  5. Indexes that have different uniqueness properties. You can have both unique and non-unique index on the same set of columns.

When you have multiple indexes on the same set of columns only one of those indexes must be visible at a time, the rest of the indexes should be invisible. If you are creating a visible index, then all existing indexes on the set of columns must be invisible. If OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE the optimizer can use the invisible index.

Due to the fact that only one index is visible at a time the optimizer has no choice in between several indexes. The optimizer can only decide to use the visible index or not.

Let’s create the following table and indexes:

 

SQL> connect test/test

Connected.

SQL> create table t1 as select * from scott.emp;

Table created.

SQL>

SQL> create index t1_i1 on t1 (empno) invisible;

create bitmap index t1_i2 on t1(empno) visible;

Index created.

SQL>

Index created.

SQL> exec dbms_stats.gather_schema_stats(‘TEST’);

PL/SQL procedure successfully completed.

SQL>;

By default we will have the following plan

 

SQL>

explain plan for select ename from t1 where empno=7369;

set linesize 130

set pagesize 0

select  * from   table(DBMS_XPLAN.DISPLAY);

 

SQL>

Explained.

 

SQL> SQL> SQL> Plan hash value: 2906645797

 

———————————————————————————————

| Id  | Operation                       | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

———————————————————————————————

|   0 | SELECT STATEMENT                |      |    1 |  10 |    2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |        1 |  10 |    2   (0)| 00:00:01 |

|   2 |   BITMAP CONVERSION TO ROWIDS     |      |      |      |         |         |

|*  3 |    BITMAP INDEX SINGLE VALUE     | T1_I2 |      |      |         |         |

———————————————————————————————

 

Predicate Information (identified by operation id):

—————————————————

 

   3 – access(“EMPNO”=7369)

 

15 rows selected.

 

SQL> SQL>

 

If we set OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE we will have the following:

 

SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;

explain plan for select ename from t1 where empno=7369;

set linesize 130

set pagesize 0

select  * from   table(DBMS_XPLAN.DISPLAY);

 

Session altered.

 

SQL>

Explained.

 

SQL> SQL> SQL> Plan hash value: 3320414027

 

———————————————————————————————

| Id  | Operation                       | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

———————————————————————————————

|   0 | SELECT STATEMENT                |      |    1 |  10 |    2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |        1 |  10 |    2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN               | T1_I1 |    1 |      |    1   (0)| 00:00:01 |

———————————————————————————————

 

Predicate Information (identified by operation id):

—————————————————

 

   2 – access(“EMPNO”=7369)

 

14 rows selected.

 

SQL>

 

 

November 1, 2016 - Posted by | oracle

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: