Los índices en Oracle, creación, eliminación, reconstrucción Proyecto AjpdSoft

Oracle: Los índices en Oracle, creación, eliminación, reconstrucción

Os explicamos qué son los índices y cómo usarlos en Oracle Database. Enumeramos los tipos de índices y cómo crearlos. Mostramos cómo saber cuándo es necesario reindexar un índice en Oracle y cómo hacerlo. Explicamos cómo cambiar de ubicación un índice ya creado.

Definición de índice ¿qué es un índice? ¿para qué sirve?.
Cómo crear índices en Oracle.
- Creación de un índice al crear una tabla de Oracle.
- Crear un índice en una tabla ya existente en Oracle.
Cómo saber si un índice se está utilizando, monitorización del uso de índices en Oracle.
Mostrar información de los índices de una tabla en Oracle.
Reconstrucción de índices ¿cúando es necesaria? ¿cómo hacerla?, parámetros de los índices en Oracle.
Artículos relacionados.
Créditos.

Definición de índice ¿qué es un índice? ¿para qué sirve?

El índice de una base de datos es una estructura de datos que mejora la velocidad de las operaciones, permitiendo un rápido acceso a los registros de una tabla. Al aumentar drásticamente la velocidad de acceso, se suelen usar sobre aquellos campos sobre los cuales se vayan a realizar búsquedas frecuentes.

El índice tiene un funcionamiento similar al índice de un libro, guardando parejas de elementos: el elemento que se desea indexar y su posición en la base de datos. Para buscar un elemento que esté indexado, sólo hay que buscar en el índice de dicho elemento para, una vez encontrado, devolver el registro que se encuentre en la posición marcada por el índice.

Los índices pueden ser creados usando una o más columnas, preparando la base de datos tanto para búsquedas rápidas al azar como para ordenaciones eficientes de los registros.

Los índices son construidos sobre árboles B, B+, B* o sobre una mezcla de ellos, funciones de cálculo u otros métodos.

El espacio en disco requerido para almacenar el índice es típicamente menor que el espacio de almacenamiento de la tabla (puesto que los índices generalmente contienen solamente los campos clave de acuerdo con los que la tabla será ordenada, y excluyen el resto de los detalles de la tabla), lo que da la posibilidad de almacenar en memoria los índices de tablas que no cabrían en ella. En una base de datos relacional un índice es una copia de parte de una tabla.

Cómo crear índices en Oracle

Creación de un índice al crear una tabla de Oracle

Para crear un índice en la creación de una tabla de Oracle podemos utilizar SQL*Plus (herramienta de línea de comandos incluida con Oracle), se puede utilizar también Oracle Enterprise Manager (modo gráfico) o una aplicación externa que permita acceso a Oracle, como por ejemplo:

AjpdSoft Administración Bases de Datos

Abriremos esta aplicación e introduciremos los datos para la conexión (nombre de la base de datos, nombre de usuario, contraseña, tipo de acceso):

Crearemos una tabla para el ejemplo de creación de índices ejecutando la siguiente consulta SQL:

CREATE TABLE facturacion (
codigo number(10) not null,
fecha date default sysdate,
codigocliente number(10),
nombrecliente varchar(100),
observacion varchar(2000),
constraint pk_facturacion_codigo primary key (codigo)
) tablespace USERS

Como se puede observar en la consulta SQL anterior, al añadir la línea:

constraint pk_facturacion_codigo primary key (codigo)

Estamos indicando a Oracle que cree la tabla "facturacion", con el campo "codigo" y que éste sea clave primaria, por lo que creará un índice automáticamente para este campo. Esta es una forma de crear índices, en la creación de la tabla:

Tras crear la tabla insertaremos algunos registros con la instrucción SQL:

insert into facturacion
(codigo, codigocliente, nombrecliente)
values (1, 50, 'AjpdSoft')

Puesto que hemos indicado que la columna "codigo" sea clave primaria, Oracle no permitirá valores duplicados para esta columna, esta comprobación la realizará basándose en el índice creado. Si intentamos agregar un nuevo registro con con el valor de "codigo" a "1" (que ya existe) nos mostrará este error:

Con el texto: ORA-00001: restricción única (SYSTEM.PK_FACTURACION_CODIGO) violada, indicando que la restricción única PK_FACTURACION_CODIGO no se ha cumplido por lo que no dejará insertar el registro.

Insertaremos varios registros (cambiando el valor de "codigo"). Ejecutando la consulta SQL:

select * from facturacion

Podremos ver los registros insertados en la tabla "facturacion":

Para ver el índice creado se puede utilizar esta consulta.

Crear un índice en una tabla ya existente en Oracle

La creación de un índice en Oracle se realiza mediante el comando create index. Cuando se define una clave primaria o una columna unívoca (UNIQUE) durante la creación de una tabla o su mantenimiento, Oracle creará automáticamente un índice de tipo UNIQUE que gestione dicha restricción, como hemos indicado anteriormente. La sintaxis completa de create index es la siguiente:

create [bitmap | unique] index nombre_indice on
nombre_tabla (nombre_columna [, nombre_columna2] ...) [reverse];

bitmap indica que se cree un índice de mapa de bits que permite crear índices en columnas con muy pocos valores diferentes.
unique indica que el valor de la o las columnas indexadas debe ser único, no puede haber duplicidades.
nombre_indice debe ser un nombre unívoco (no debe existir otro nombre de objeto en Oracle) que siga los convenios de denominación de Oracle para nombrar columnas.
nombre_tabla será el nombre de la tabla donde se creará el índice.
nombre_columna (o columnas) será la columna de la tabla nombre_tabla en la que se creará el índice. Se puede crear un índice para varias columnas.
reverse indica a Oracle que invierta los bytes del valor indexado, lo que puede mejorar la distribución del procesamiento y de los datos cuando se insertan muchos valores de datos secuenciales.

Para crear un índice en Oracle podemos utilizar SQL*Plus (herramienta de línea de comandos incluida con Oracle), se puede utilizar Oracle Enterprise Manager (modo gráfico) o una aplicación externa que permita acceso a Oracle, como por ejemplo:

AjpdSoft Administración Bases de Datos

Abriremos esta aplicación e introduciremos los datos para la conexión (nombre de la base de datos, nombre de usuario, contraseña, tipo de acceso):

Siguiendo con el ejemplo, añadiremos un índice normal para la columna "nombrecliente" de la tabla "facturacion". Para ello ejecutaremos la siguiente consulta SQL:

create index IN_FACTURACION_NOMBRECLIENTE
on FACTURACION (NOMBRECLIENTE)

Para ver el índice creado podemos ejecutar la siguiente consulta SQL (como indicamos aquí):

select index_name Nombre, index_type Tipo,
table_name Tabla, tablespace_name Tablespace,
secondary Secundario
from all_indexes
where table_name = 'FACTURACION'

Para añadir un índice de tipo UNIQUE, obligando a que los valores del campo indexado sean unívocos, no se puedan repetir en el campo de la tabla, ejecutaremos la siguiente sentencia SQL:

create unique index IN_FACTURACION_COD_CODCLI_FE
on FACTURACION (CODIGOCLIENTE, FECHA)

De esta forma Oracle no permitirá que haya dos registros en la tabla "facturacion" con el mismo valor en los campos "codigocliente" y "fecha", es decir, sólo podrá añadirse una factura por cliente y por día, un cliente no podrá tener dos facturas en un mismo día. Por ejemplo, si insertamos este registro:

insert into facturacion
(codigo, codigocliente, fecha)
values (6900, 500, to_date('31/12/2009', 'DD-MM-YYYY'))

Intentaremos insertar un segundo registro con el mismo valor en codigocliente y en fecha:

insert into facturacion
(codigo, codigocliente, fecha)
values (6910, 500, to_date('31/12/2009', 'DD-MM-YYYY'))

Oracle detectará que se está intentando infringir una restricción única, por lo que mostrará el siguiente mensaje de error y no dejará que se inserte el registro:

Con el texto: ORA-00001: restricción única (SYSTEM.IN_FACTURACION_COD_CODCLI_FE) violada.

Cómo saber si un índice se está utilizando, monitorización del uso de índices en Oracle

Para consultar las estadísticas sobre el uso de uno o varios índices de tablas de Oracle, en primer lugar deberemos activar la monitorización del índice que queramos, para ello utilizaremos el siguiente comando SQL:

alter index nombre_indice monitoring usage;

Por ejemplo, en nuestro caso, para monitorizar el índice IN_FACTURACION_NOMBRECLIENTE:

alter index IN_FACTURACION_NOMBRECLIENTE monitoring usage;

Tras activar la monitorización, podremos ver si el índice ha sido usado ejecutando la siguiente consulta SQL. En nuestro caso comprobaremos si han sido usado los índices monitorizados de la tabla FACTURACION:

select *
from v$object_usage
where table_name = 'FACTURACION'

Mostrará un registro indicando los siguientes datos:

INDEX_NAME: nombre del índice usado.
TABLE_NAME: nombre de la tabla a la que pertenece el índice usado.
MONITORING: estado de monitorización, si está activa mostrará "YES".
USED: mostrará "NO" si aún no ha sido usado.
START_MONITORING: fecha y hora de inicio de monitorización.
END_MONITORING: fecha y hora de fin de monitorización.

Si insertamos algunos registros en la tabla FACTURACION y hacemos alguna consulta SQL ordenando por "nombre_cliente" para que el índice sea usado podremos ver cómo cambia el valor de USED en v$object_usage:

Para desactivar la monitorización ejecutaremos el siguiente comando SQL:

alter index nombre_indice nomonitoring usage;

En nuestro ejemplo:

alter index IN_FACTURACION_NOMBRECLIENTE nomonitoring usage

Ejecutando nuevamente la consulta:

select *
from v$object_usage
where table_name = 'FACTURACION'

Veremos que ha cambiado el valor de MONITORING a "NO" y ha añadido la fecha y hora a END_MONITORING:

Mostrar información de los índices de una tabla en Oracle

Mostrar todas las restricciones de una tabla de Oracle

Para mostrar todas las restricciones de una tabla oracle (nombre de la restricción, nombre de la tabla, columnas a las que afecta) ejecutaremos la siguiente consulta SQL:

select constraint_name Nombre, table_name Tabla,
substr(column_name, 1, 255) Columnas
from all_cons_columns
where table_name = 'FACTURACION'

Mostrar todos los índices de una tabla en Oracle

La siguiente consulta SQL mostrará todos los índices de la tabla "FACTURACION" de Oracle (nombre del índice, tipo de índice, nombre de la tabla, tablespace en el que se guarda, si es secundario):

select index_name Nombre, index_type Tipo,
table_name Tabla, tablespace_name Tablespace,
secondary Secundario
from all_indexes
where table_name = 'FACTURACION'

Obtener el tamaño ocupado por un índice de una tabla de Oracle

La siguiente consulta SQL mostrará el tamaño en megabytes de un índice determinado, en nuestro caso del índice PK_FACTURACION_CODIGO perteneciente a la tabla FACTURACION y el campo CODIGO del ejemplo. La consulta SQL para mostrar el tamaño ocupado por un índice es la siguiente:

select segment_name, sum(bytes)/1024/1024 MB
from dba_extents
where segment_name = 'PK_FACTURACION_CODIGO'
group by segment_name

Reconstrucción de índices ¿cúando es necesaria? ¿cómo hacerla?, parámetros de los índices en Oracle

Reconstruir índices en Oracle

Oracle proporciona la capacidad de realizar una reconstrucción rápida de índices, que permite crear de nuevo un índice sin necesidad de eliminar el índice existente. El índice actualmente disponible se usa como origen de datos para el índice nuevo, en lugar de utilizar la tabla como origen de datos. Durante la reconstrucción del índice pueden cambiarse los parámetros de almacenamiento (storage) y la asignación del espacio de tablas (tablespace).

Para reconstruir un índice de una tabla utilizaremos la cláusula rebuild con el comando alter index. Un ejemplo de reconstrucción de un índice en Oracle podría ser:

ALTER INDEX nombre_indice REBUILD;

Siguiendo nuestro ejemplo, para reindexar el índice PK_FACTURACION_CODIGO ejecutaremos la siguiente instrucción SQL:

ALTER INDEX PK_FACTURACION_CODIGO REBUILD;

Ejecutaremos esta consulta desde Oracle Enterprise Manager, accediendo a la opción "Hoja de Trabajo de SQL", desmarcaremos la opción "Permitir Sólo Sentencias SELECT", en "Comandos SQL" introduciremos:

ALTER INDEX PK_FACTURACION_CODIGO REBUILD;

Y pulsaremos en "Ejecutar". Si todo es correcto nos devolverá en "Resultados": SQL se ha ejecutado correctamente.

AjpdSoft Cómo crear índices en Oracle con AjpdSoft Administración de Bases de Datos

Cambiar los parámetros de configuración de un índice en Oracle

Si queremos cambiar algún parámetro de configuración de algún índice, como el almacenamiento (storage) o incluso el espacio de tablas (tablespace) podremos ejecutar la siguiente instrucción SQL:

alter index nombre_indice rebuild
storage (initial 8M next 4M)
tablespace nombre_tablespace;

Con la instrucción anterior se reconstruirá el índice "nombre_indice" asignándole un tamaño inicial de extensión de 8MB y un tamaño para las siguientes extensiones de 4MB. El índice "nombre_indice" será creado en el tablespace "nombre_tablespace". Por lo que esta instrucción SQL servirá para cambiar de tablespace un índice (si así lo deseamos).

Cambiar la ubicación de un índice a otro tablespace en Oracle

Oracle recomienda que los índices sean almacenados en un tablespace que apunte a una unidad física diferente a la de los tablespaces de los datos. A continuación explicamos cómo cambiar la ubicación (tablespace) de un índice ya creado.

En primer lugar necesitaremos disponer de un tablespace para los índices, crearemos uno accediendo a Oracle Enterprise Manager, pulsando en "Servidor" y en la sección "Almacenamiento" pulsando en "Tablespaces". Nos mostrará los tablespaces actuales, pulsaremos "Crear":

AjpdSoft Cómo crear índices en Oracle con AjpdSoft Administración de Bases de Datos

O bien ejecutando una consulta SQL como la siguiente:

CREATE SMALLFILE TABLESPACE "TB_INDICES" DATAFILE 'F:Oracleindicesf_tbindices.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT NOCOMPRESS

---Continuará---

Cómo saber cuándo es necesaria la reindexación o reconstrucción de índices