Oracle Live

11/05/2016

Indices Parciales en tablas particionadas

Filed under: Database,New 12c — mogukiller @ 12:48 pm

En 12c se añade la posibilidad de indexar de forma parcial una tabla particionada. Y como se hace el mantenimiento de estos indices en operaciones de DROP / TRUNCATE de particiones.


CREATE TABLE T_PARTI
(ID_PETICION NUMBER, ID_PEDIDO NUMBER, CANTIDAD NUMBER, FECHA DATE)
INDEXING OFF
PARTITION BY RANGE(FECHA)
(PARTITION P_2011 VALUES LESS THAN (TO_DATE('2012-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))
INDEXING OFF, <<-- Decimos que no la indexe
PARTITION P_2012 VALUES LESS THAN (TO_DATE('2013-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))
INDEXING OFF, <<-- Decimos que no la indexe
PARTITION P_2013 VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))
INDEXING ON,
PARTITION P_2014 VALUES LESS THAN (TO_DATE('2015-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))
INDEXING ON,
PARTITION P_2015 VALUES LESS THAN (TO_DATE('2016-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))
INDEXING ON,
PARTITION P_2016 VALUES LESS THAN (TO_DATE('2017-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))
INDEXING ON)
;

-- Creamos un indice local.

CREATE INDEX ID_L_FECHA ON T_PARTI (FECHA) LOCAL INDEXING PARTIAL;

-- Creamos un indice global

CREATE INDEX ID_G_PETICION ON T_PARTI (ID_PETICION) GLOBAL PARTITION BY HASH(ID_PETICION) PARTITIONS 4;

-- Miramos como ha creado las particiones

COL INDEX_NAME FOR A30
COL PARTITION_NAME FOR A30
SELECT A.INDEX_NAME, PARTITION_NAME, INDEXING, A.STATUS FROM DBA_IND_PARTITIONS A, DBA_INDEXES B
WHERE A.INDEX_NAME=B.INDEX_NAME AND B.TABLE_NAME='T_PARTI' ORDER BY 1,2;

/*
INDEX_NAME PARTITION_NAME INDEXIN STATUS
------------------------------ ------------------------------ ------- --------

ID_G_PETICION SYS_P471 FULL USABLE
ID_G_PETICION SYS_P472 FULL USABLE
ID_G_PETICION SYS_P473 FULL USABLE
ID_G_PETICION SYS_P474 FULL USABLE

ID_L_FECHA P_2011 PARTIAL UNUSABLE <<-- Las no indexadas las crea UNUSABLE
ID_L_FECHA P_2012 PARTIAL UNUSABLE
ID_L_FECHA P_2013 PARTIAL USABLE
ID_L_FECHA P_2014 PARTIAL USABLE
ID_L_FECHA P_2015 PARTIAL USABLE
ID_L_FECHA P_2016 PARTIAL USABLE

*/

-- Vemos como se comportan los indices ante un DROP o un TRUNCATE.

SQL> ALTER TABLE T_PARTI DROP PARTITION P_2012;

/*
INDEX_NAME PARTITION_NAME INDEXIN STATUS
------------------------------ ------------------------------ ------- --------
ID_G_PETICION SYS_P471 FULL UNUSABLE <<-- El global se envalida entero
ID_G_PETICION SYS_P472 FULL UNUSABLE
ID_G_PETICION SYS_P473 FULL UNUSABLE
ID_G_PETICION SYS_P474 FULL UNUSABLE
ID_L_FECHA P_2011 PARTIAL UNUSABLE <<-- Solo desaparece la particion
ID_L_FECHA P_2013 PARTIAL USABLE
ID_L_FECHA P_2014 PARTIAL USABLE
ID_L_FECHA P_2015 PARTIAL USABLE
ID_L_FECHA P_2016 PARTIAL USABLE
*/

ALTER TABLE T_PARTI DROP PARTITION P_2013 UPDATE INDEXES;
COL INDEX_NAME FOR A30
COL PARTITION_NAME FOR A30
SELECT A.INDEX_NAME,A.PARTITION_NAME, A.ORPHANED_ENTRIES, A.STATUS FROM DBA_IND_PARTITIONS A, DBA_INDEXES B
WHERE A.INDEX_NAME=B.INDEX_NAME AND B.TABLE_NAME='T_PARTI' ORDER BY 1,2;

/*

INDEX_NAME PARTITION_NAME ORP STATUS
------------------------------ ------------------------------ --- --------
ID_G_PETICION SYS_P478 YES USABLE <<-- Con update indexes permanece valido pero con OPHANED_ENTRIES
ID_G_PETICION SYS_P479 YES USABLE
ID_G_PETICION SYS_P480 YES USABLE
ID_G_PETICION SYS_P481 YES USABLE
ID_L_FECHA P_2011 NO UNUSABLE
ID_L_FECHA P_2014 NO USABLE
ID_L_FECHA P_2015 NO USABLE
ID_L_FECHA P_2016 NO USABLE
*/
'nota: Con orphaned_entries el optimizador ignora esas entradas'

De forma automatica se pasa el job: SYS.PMO_DEFERRED_GIDX_MAINT_JOB

O lo podemos pasar de forma manual:

SQL> EXEC DBMS_PART.CLEANUP_GIDX('MOGU');

/*
INDEX_NAME PARTITION_NAME ORP STATUS
------------------------------ ------------------------------ --- --------
ID_G_PETICION SYS_P486 NO USABLE
ID_G_PETICION SYS_P487 NO USABLE
ID_G_PETICION SYS_P488 NO USABLE
ID_G_PETICION SYS_P489 NO USABLE
ID_L_FECHA P_2011 NO UNUSABLE
ID_L_FECHA P_2016 NO USABLE
*/

1 comentario »

  1. […] Aqui os dejo un post sobre los partial index. […]

    Pingback por CAP 8.- Storage and Data Loading | Oracle Live — 11/05/2016 @ 12:51 pm


RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Blog de WordPress.com.

A %d blogueros les gusta esto: