8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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:
- Understand When to Create Multiple Indexes on the Same Set of Columns
- Creating Multiple Indexes on the Same Set of Columns
- Multiple Indexes on the Same Set of Columns
- Invisible Indexes in Oracle Database 11g Release 1
Hope this helps. Regards Tim...