Oracle 12c permite múltiples índices en el mismo conjunto de columnas, siempre que solo un índice sea visible y todos los índices sean diferentes de alguna manera.
- Tablas sin particiones
- Tablas particionadas
- Por qué utilizar varios índices
- OPTIMIZER_USE_INVISIBLE_INDEXES
Tablas sin particiones
Cree la siguiente tabla.
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;
Cuando creamos el primer índice, funciona como se esperaba.
CREATE INDEX t1_idx1 ON t1(created_date) VISIBLE; Index created. SQL>
Si intentamos crear un índice invisible en el mismo conjunto de columnas, falla porque tiene los mismos atributos que el índice existente.
CREATE INDEX t1_idx2 ON t1(created_date) INVISIBLE;
*
ERROR at line 1:
ORA-01408: such column list already indexed
SQL>
Si modificamos algo al respecto, como convertirlo en un índice de mapa de bits, funciona.
CREATE BITMAP INDEX t1_idx3 ON t1(created_date) INVISIBLE; Index created. SQL>
Tablas particionadas
Cree la siguiente tabla particionada.
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;
Crea un índice global.
CREATE INDEX t1_idx1 ON t1(created_date) GLOBAL VISIBLE; Index created. SQL>
Cree algunos índices con diferentes esquemas de partición.
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>
Cree un índice de mapa de bits.
CREATE BITMAP INDEX t1_idx4 ON t1(created_date) LOCAL INVISIBLE; Index created. SQL>
Por qué utilizar varios índices
Los índices invisibles aún se mantienen, por lo que tener varios índices en el mismo conjunto de columnas le permite cambiar rápidamente entre ellos, lo que hace que probar el impacto de varios índices sea mucho más rápido. Recuerde, hay un impacto en el rendimiento de DML por tener demasiados índices en una tabla, por lo que esta debería ser una situación a corto plazo.
El siguiente ejemplo utilizó la tabla particionada y los índices asociados creados en la sección anterior.
-- 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
Cambie la visibilidad del índice y pruebe de nuevo.
-- 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
Cambie la visibilidad del índice una vez más y pruebe.
-- 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
Cambie la visibilidad del índice una vez más y pruebe.
-- 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
Al igual que con cualquier índice invisible disponible, establecer el parámetro OPTIMIZER_USE_INVISIBLE_INDEXES en TRUE los hace disponibles para el optimizador.
-- 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
Podemos ver que el optimizador ha elegido utilizar el índice T1_IDX1, aunque es invisible, y hay un índice visible (T1_IDX4) disponible en la misma lista de columnas.
