8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

Multiple Indexes on the Same Set of Columns in Oracle Database 12c Release 1

Oracle 12c allows multiple indexes on the same set of columns, provided only one index is visible and all indexes are different in some way.

Related articles.

Non-Partitioned Tables

Create the following table.

DROP TABLE t1 PURGE;

CREATE TABLE t1 (
  id            NUMBER,
  description   VARCHAR2(50),
  created_date  DATE
);

INSERT INTO t1 VALUES (1, 't1 ONE', TO_DATE('01/07/2014', 'DD/MM/YYYY'));
INSERT INTO t1 VALUES (2, 't1 TWO', TO_DATE('01/07/2015', 'DD/MM/YYYY'));
INSERT INTO t1 VALUES (3, 't1 THREE', TO_DATE('01/07/2016', 'DD/MM/YYYY'));
COMMIT;

When we create the first index, it works as expected.

CREATE INDEX t1_idx1 ON t1(created_date) VISIBLE;

Index created.

SQL>

If we attempt to create an invisible index on the same set of columns it fails because it has the same attributes are the existing index.

CREATE INDEX t1_idx2 ON t1(created_date) INVISIBLE;
                           *
ERROR at line 1:
ORA-01408: such column list already indexed

SQL>

If we alter something about it, like make it a bitmapped index, it works.

CREATE BITMAP INDEX t1_idx3 ON t1(created_date) INVISIBLE;

Index created.

SQL>

Partitioned Tables

Create the following partitioned table.

DROP TABLE t1 PURGE;

CREATE TABLE t1 (
  id            NUMBER,
  description   VARCHAR2(50),
  created_date  DATE
)
PARTITION BY RANGE (created_date) (
  PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users,
  PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users,
  PARTITION part_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')) TABLESPACE users
);

INSERT INTO t1 VALUES (1, 't1 ONE', TO_DATE('01/07/2014', 'DD/MM/YYYY'));
INSERT INTO t1 VALUES (2, 't1 TWO', TO_DATE('01/07/2015', 'DD/MM/YYYY'));
INSERT INTO t1 VALUES (3, 't1 THREE', TO_DATE('01/07/2016', 'DD/MM/YYYY'));
COMMIT;

Create a global index.

CREATE INDEX t1_idx1 ON t1(created_date) GLOBAL VISIBLE;

Index created.

SQL>

Create some indexes with differing partitioning schemes.

CREATE INDEX t1_idx2 ON t1(created_date) GLOBAL
PARTITION BY RANGE (created_date) (
  PARTITION t1_p1 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users,
  PARTITION t1_p2 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users,
  PARTITION t1_p3 VALUES LESS THAN (MAXVALUE) TABLESPACE users
)
INVISIBLE;

Index created.

SQL>


CREATE INDEX t1_idx3 ON t1(created_date) LOCAL INVISIBLE;

Index created.

SQL>

Create a bitmapped index.

CREATE BITMAP INDEX t1_idx4 ON t1(created_date) LOCAL INVISIBLE;

Index created.

SQL>

Why Use Multiple Indexes

Invisible indexes are still maintained, so having multiple indexes on the same set of columns allows you to quickly switch between them, making testing the impact of various indexes much quicker. Remember, there is an impact on DML performance of having too many indexes on a table, so this should be a short term situation.

The following example used the partitioned table and the associated indexes created in the previous section.

-- Check visibility of indexes.
COLUMN index_name FORMAT A10
COLUMN index_type FORMAT A10
COLUMN partitioned FORMAT A12
COLUMN locality FORMAT A8
COLUMN visibility FORMAT A10

SELECT a.index_name,
       a.index_type,
       a.partitioned,
       b.partitioning_type,
       b.locality,
       a.visibility
FROM   user_indexes a
       LEFT OUTER JOIN user_part_indexes b ON a.index_name = b.index_name
ORDER BY index_name;

INDEX_NAME INDEX_TYPE PARTITIONED  PARTITION LOCALITY   VISIBILITY
---------- ---------- ------------ --------- ---------- ----------
T1_IDX1    NORMAL     NO                                VISIBLE
T1_IDX2    NORMAL     YES          RANGE     GLOBAL     INVISIBLE
T1_IDX3    NORMAL     YES          RANGE     LOCAL      INVISIBLE
T1_IDX4    BITMAP     YES          RANGE     LOCAL      INVISIBLE

SQL>


-- Test the index usage.
SET AUTOTRACE TRACE EXPLAIN

SELECT *
FROM   t1
WHERE created_date = TO_DATE('01/07/2014', 'DD/MM/YYYY');

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |         |     1 |    49 |     1   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T1      |     1 |    49 |     1   (0)| 00:00:01 |     1 |     1 |
|*  2 |   INDEX RANGE SCAN                         | T1_IDX1 |     1 |       |     1   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------

SET AUTOTRACE OFF

Switch the index visibility and test again.

-- Switch indexes.
ALTER INDEX t1_idx1 INVISIBLE;
ALTER INDEX t1_idx2 VISIBLE;


-- Check visibility of indexes.
SELECT a.index_name,
       a.index_type,
       a.partitioned,
       b.partitioning_type,
       b.locality,
       a.visibility
FROM   user_indexes a
       LEFT OUTER JOIN user_part_indexes b ON a.index_name = b.index_name
ORDER BY index_name;

INDEX_NAME INDEX_TYPE PARTITIONED  PARTITION LOCALITY   VISIBILITY
---------- ---------- ------------ --------- ---------- ----------
T1_IDX1    NORMAL     NO                                INVISIBLE
T1_IDX2    NORMAL     YES          RANGE     GLOBAL     VISIBLE
T1_IDX3    NORMAL     YES          RANGE     LOCAL      INVISIBLE
T1_IDX4    BITMAP     YES          RANGE     LOCAL      INVISIBLE

SQL>


-- Test the index usage.
SET AUTOTRACE TRACE EXPLAIN

SELECT *
FROM   t1
WHERE created_date = TO_DATE('01/07/2014', 'DD/MM/YYYY');

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |         |     1 |    49 |     1   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE                     |         |     1 |    49 |     1   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T1      |     1 |    49 |     1   (0)| 00:00:01 |     1 |     1 |
|*  3 |    INDEX RANGE SCAN                         | T1_IDX2 |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
-----------------------------------------------------------------------------------------------------------------------

SET AUTOTRACE OFF

Switch the index visibility once again and test.

-- Switch indexes.
ALTER INDEX t1_idx2 INVISIBLE;
ALTER INDEX t1_idx3 VISIBLE;


-- Check visibility of indexes.
SELECT a.index_name,
       a.index_type,
       a.partitioned,
       b.partitioning_type,
       b.locality,
       a.visibility
FROM   user_indexes a
       LEFT OUTER JOIN user_part_indexes b ON a.index_name = b.index_name
ORDER BY index_name;

INDEX_NAME INDEX_TYPE PARTITIONED  PARTITION LOCALITY   VISIBILITY
---------- ---------- ------------ --------- ---------- ----------
T1_IDX1    NORMAL     NO                                INVISIBLE
T1_IDX2    NORMAL     YES          RANGE     GLOBAL     INVISIBLE
T1_IDX3    NORMAL     YES          RANGE     LOCAL      VISIBLE
T1_IDX4    BITMAP     YES          RANGE     LOCAL      INVISIBLE

SQL>


-- Test the index usage.
SET AUTOTRACE TRACE EXPLAIN

SELECT *
FROM   t1
WHERE created_date = TO_DATE('01/07/2014', 'DD/MM/YYYY');

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |         |     1 |    49 |     1   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE                    |         |     1 |    49 |     1   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1      |     1 |    49 |     1   (0)| 00:00:01 |     1 |     1 |
|*  3 |    INDEX RANGE SCAN                        | T1_IDX3 |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
----------------------------------------------------------------------------------------------------------------------

SET AUTOTRACE OFF

Switch the index visibility once again and test.

-- Switch indexes.
ALTER INDEX t1_idx3 INVISIBLE;
ALTER INDEX t1_idx4 VISIBLE;


-- Check visibility of indexes.
SELECT a.index_name,
       a.index_type,
       a.partitioned,
       b.partitioning_type,
       b.locality,
       a.visibility
FROM   user_indexes a
       LEFT OUTER JOIN user_part_indexes b ON a.index_name = b.index_name
ORDER BY index_name;

INDEX_NAME INDEX_TYPE PARTITIONED  PARTITION LOCALITY   VISIBILITY
---------- ---------- ------------ --------- ---------- ----------
T1_IDX1    NORMAL     NO                                INVISIBLE
T1_IDX2    NORMAL     YES          RANGE     GLOBAL     INVISIBLE
T1_IDX3    NORMAL     YES          RANGE     LOCAL      INVISIBLE
T1_IDX4    BITMAP     YES          RANGE     LOCAL      VISIBLE

SQL>


-- Test the index usage.
SET AUTOTRACE TRACE EXPLAIN

SELECT *
FROM   t1
WHERE created_date = TO_DATE('01/07/2014', 'DD/MM/YYYY');

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |         |     1 |    49 |    15   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE                    |         |     1 |    49 |    15   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1      |     1 |    49 |    15   (0)| 00:00:01 |     1 |     1 |
|   3 |    BITMAP CONVERSION TO ROWIDS             |         |       |       |            |          |       |       |
|*  4 |     BITMAP INDEX SINGLE VALUE              | T1_IDX4 |       |       |            |          |     1 |     1 |
----------------------------------------------------------------------------------------------------------------------

SET AUTOTRACE OFF

OPTIMIZER_USE_INVISIBLE_INDEXES

As with any available invisible index, setting the OPTIMIZER_USE_INVISIBLE_INDEXES parameter to TRUE makes them available for the optimizer.

-- Check visibility of indexes.
SELECT a.index_name,
       a.index_type,
       a.partitioned,
       b.partitioning_type,
       b.locality,
       a.visibility
FROM   user_indexes a
       LEFT OUTER JOIN user_part_indexes b ON a.index_name = b.index_name
ORDER BY index_name;

INDEX_NAME INDEX_TYPE PARTITIONED  PARTITION LOCALITY VISIBILITY
---------- ---------- ------------ --------- -------- ----------
PK_EMP     NORMAL     NO                              VISIBLE
T1_IDX1    NORMAL     NO                              INVISIBLE
T1_IDX2    NORMAL     YES          RANGE     GLOBAL   INVISIBLE
T1_IDX3    NORMAL     YES          RANGE     LOCAL    INVISIBLE
T1_IDX4    BITMAP     YES          RANGE     LOCAL    VISIBLE

SQL>


-- Allow the optimizer to use invisible indexes.
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;


SET AUTOTRACE TRACE EXPLAIN

SELECT *
FROM   t1
WHERE created_date = TO_DATE('01/07/2014', 'DD/MM/YYYY');

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |         |     1 |    49 |     1   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T1      |     1 |    49 |     1   (0)| 00:00:01 |     1 |     1 |
|*  2 |   INDEX RANGE SCAN                         | T1_IDX1 |     1 |       |     1   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------

SET AUTOTRACE OFF

We can see the optimizer has chosen to use the T1_IDX1 index, even though it is invisible, and there is a visible index (T1_IDX4) available on the same column list.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.