Crear particiones con o sin indices :: Oracle 12c :: New Features

:: Crear particiones con o sin indices :: Oracle 12c :: New Features

Saludos gente,

Este feature esta buenísimo, los que trabajamos con las particiones como DBA's Oracle sabemos que algunas veces seria mejor apagar ciertos indices sobre ciertas particiones.... esto por supuesto por una cantidad de temas: ahorro del espacio, muchas veces el indice no se necesita en todas las particiones, rendimiento(no le podemos andar metiendo mano al código pl/sql de la aplicación, a veces el indice ayuda y aveces no, etc, etc).

La gente de Oracle se invento en 12c este nuevo feature que nos permite sacarle provecho a los "indices parciales".

Con este feature cuando la clausula indexing off es establecida sobre una partición, la misma no va a tener particiones de indices disponibles, sea un indice parcial o global, particionado o no el indice. No hay data que considerar a nivel de indexado, dado que apagamos la data del Indice por decirlo de alguna manera.

Vamos al ejemplo:

Acá creamos la tabla HR,Productos particionada por "fecha_recepción":

CREATE TABLE hr.productos
(
   product_id        NUMBER,
   product_name      VARCHAR2 (15),
   fecha_recepcion   DATE,
   desc_producto     VARCHAR2 (255)
)
INDEXING OFF // *1
PARTITION BY RANGE
   (fecha_recepcion)
   (
      PARTITION
         PART_2011 VALUES LESS THAN (TO_DATE ('01-01-2012', 'DD-MM-YYYY')) INDEXING OFF,
      PARTITION
         PART_2012 VALUES LESS THAN (TO_DATE ('01-01-2013', 'DD-MM-YYYY')) INDEXING ON,
      PARTITION
         PART_2014 VALUES LESS THAN (TO_DATE ('01-01-2014', 'DD-MM-YYYY')))
ENABLE ROW MOVEMENT;

Acá pueden notar lo siguiente:

• Primero nos encontramos un gran INDEXING OFF, que indica el estado default para los indices asociados a la partición de la tabla que no tenga la clausula indexing off/on especificada. (Ver *1)
• Luego nos encontramos el INDEXING OFF o INDEXING ON que indica finalmente si la partición debe considerar indices o no.

Revisemos a fondo si las particiones pueden quedar indexadas o no:

SELECT table_name, partition_name, indexing
  FROM dba_tab_partitions
 WHERE table_name = 'PRODUCTOS'

Ahora creamos un indices global full y otro global partial:

CREATE INDEX idnx_full

   ON hr.productos (product_id)

   GLOBAL indexing full;

CREATE INDEX idnx_partial

   ON hr.productos (product_name)

   GLOBAL indexing partial;

Revisamos a fondo:

SELECT table_name, index_name, indexing

  FROM dba_indexes

 WHERE table_name = 'PRODUCTOS';

Ingresemos alguna data loca, un registro por partición:

Insert into HR.PRODUCTOS

   (PRODUCT_ID, PRODUCT_NAME, FECHA_RECEPCION, DESC_PRODUCTO)

 Values

   (3, 'Mermelada', TO_DATE('11/09/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Producto para el pan');

Insert into HR.PRODUCTOS

   (PRODUCT_ID, PRODUCT_NAME, FECHA_RECEPCION, DESC_PRODUCTO)

 Values

   (2, 'Gel', TO_DATE('11/10/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Producto para el cabello');

Insert into HR.PRODUCTOS

   (PRODUCT_ID, PRODUCT_NAME, FECHA_RECEPCION, DESC_PRODUCTO)

 Values

   (1, 'Shampoo', TO_DATE('11/11/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Producto para el cabello');

COMMIT;

Ahora si, activen el auto trace on y revisen los explain plan de los querys. Y la data generada indexada o no.

Espero que les sea de utilidad.

Saludos a todos,

Miguel Vega