Oracle Live

18/02/2009

Administracion de indices

Filed under: Database,Laboratorios — mogukiller @ 8:54 am

En esta entrada vamos a analizar las diferentes opciones que tenemos para administrar los indices de nuestra base de datos. Las alternativas que vamos a explicar son la de regeneracion de indices o fusion de indices.

Si un indice subre muchas inserciones, actualizaciones o borrados a lo largo del tiempo perdeca su eficiencia espacial.

Ejemplo.

1) Creamos un tablespace para almacenar nuestros indices.

SQL> create tablespace appli_idx
datafile '/u01/app/oracle/oradata/ORCL/datafile/appli_idx.dbf'
size                                  10M
autoextend on maxsize                200M
extent management local uniform size  64K;

2) Creamos una tabla y la cargamos con 1000 elementos.

2.1) SQL> create table pruebas.datos_limpios
( id_dato     number,
fecha_dato  varchar2(256 byte))
tablespace application;

SQL> INSERT INTO DATOS_LIMPIOS SELECT * FROM DATOS_BRUTOS WHERE ROWNUM<1000;

NOTA: DATOS_BRUTOS contiene 65000 filas.

3) Creamos un indice sobre esa tabla.

SQL> CREATE INDEX ID_DATO_idx  ON DATOS_BRUTOS(ID_DATO) TABLESPACE appli_idx;

4) Para determinar si un indice necesita ser recreado analizaremos su estructura.

SQL> ANALYZE INDEX ID_DATO_IDX VALIDATE STRUCTURE;
SQL> SELECT PCT_USED FROM INDEX_STATS WHERE NAME='ID_DATO_IDX';

PCT_USED
------------
90

Si con el tiempo vemos que este porcentaje se degrada podremos pensar en recrear el indice.

RECREAR Vs FUSIONAR

Ambas operaciones regeneran el indice pero cada una de ellas tiene sus ventajas y sus desventajas.

1) Rebuild permite mover un indice de Tablespace coalesce no.
2) Rebuild requiere doble cantidad de disco que coalesce.

SQL> alter index id_dato_idx coalesce;
SQL> alter index id_dato_idx rebuild online;

Hemos utilizado la opcion de ONLINE para hacer accesible al indice mientras se hace la copia.

IDENTIFICAR INDICES QUE NO SE ESTAN USANDO.

Se puede dar el caso que hayamos definido un indice sobre una columna de una tabla y este indice lo este siendo usado por oracle.

Para identificar si este es el caso haremos:

1) Activamos el monitoreo del indice

SQL> ALTER INDEX ID_DATO_IDX MONITORING USAGE;

2) Comprobamos que se esta monitorizando correctamente.

SQL> SELECT A.INDEX_NAME, A.TABLE_NAME, A.MONITORING, A.USED, A.START_MONITORING FROM V$OBJECT_USAGE A

INDEX_NAME  TABLE_NAME     MONITORING     USED    START_MONITORING
--------------------------------------------------------------
ID_DATO_IDX DATOS_BRUTOS YES            NO      01/12/2009 02:44:21

3) Operamos con el indice

SQL>SELECT * FROM DATOS_BRUTOS WHERE ID_DATO<100;

4) Comprobamos si lo ha utilizado

SQL> SELECT A.INDEX_NAME, A.TABLE_NAME, A.MONITORING, A.USED, A.START_MONITORING FROM V$OBJECT_USAGE A

INDEX_NAME  TABLE_NAME     MONITORING     USED    START_MONITORING
--------------------------------------------------------------
ID_DATO_IDX DATOS_BRUTOS YES            YES      01/12/2009 02:44:21

5) Deshabilitamos la monitorizacion

SQL> ALTER INDEX ID_DATO_IDX NOMONITORING USAGE;

1 comentario »

  1. Excelente post, me sirvio mucho ya que se me presento un caso de indices en estado unusable y la parte para determinar si un indice necesita ser recreado me fue más util aún.

    Comentario por Luis Fernando Aristizabal Zapata — 23/07/2009 @ 9:49 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: