DBA Hub

📋Steps in this guide1/4

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.

oracle 12cconfigurationintermediate
by OracleDba
14 views
1

Non-Partitioned Tables

Create the following table. When we create the first index, it works as expected. 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. If we alter something about it, like make it a bitmapped index, it works.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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;

CREATE INDEX t1_idx1 ON t1(created_date) VISIBLE;

Index created.

SQL>

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

SQL>

CREATE BITMAP INDEX t1_idx3 ON t1(created_date) INVISIBLE;

Index created.

SQL>
2

Partitioned Tables

Create the following partitioned table. Create a global index. Create some indexes with differing partitioning schemes. Create a bitmapped index.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
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 INDEX t1_idx1 ON t1(created_date) GLOBAL VISIBLE;

Index created.

SQL>

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 BITMAP INDEX t1_idx4 ON t1(created_date) LOCAL INVISIBLE;

Index created.

SQL>
3

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. Switch the index visibility and test again. Switch the index visibility once again and test.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
-- 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 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 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 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
4

OPTIMIZER_USE_INVISIBLE_INDEXES

As with any available invisible index, setting the parameter to TRUE makes them available for the optimizer. 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...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- 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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!