Varios índices en el mismo conjunto de columnas en Oracle Database 12c a partir del release 1

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.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *