Indices

Introducción

 

Otros objetos de base de datos son los índices.

Los índices sirven para acceder a los registros de una tabla rápidamente, acelerando la localización de la información.

Los índices se emplean para facilitar la obtención de información de una tabla. El indice de una tabla desempeña la misma función que el índice de un libro: permite encontrar datos rápidamente; en el caso de las tablas, localiza registros.

Oracle accede a los datos de dos maneras:

1) recorriendo las tablas; comenzando el principio y extrayendo los registros que cumplen las condiciones de la consulta; lo cual implica posicionar las cabezas lectoras, leer el dato, controlar si coincide con lo que se busca (como si pasáramos una a una las páginas de un libro buscando un tema específico).

2) empleando índices; recorriendo la estructura de árbol del índice para localizar los registros y extrayendo los que cumplen las condiciones de la consulta (comparando con un libro, diremos que es como leer el índice y luego de encontrar el tema buscado, ir directamente a la página indicada).

Un índice posibilita el acceso directo y rápido haciendo más eficiente las búsquedas. Sin índice, Oracle debe recorrer secuencialmente toda la tabla para encontrar un registro.

Los índices son estructuras asociadas a tablas, una tabla que almacena los campos indexados y se crean para acelerar las consultas.

Entonces, el objetivo de un indice es acelerar la recuperación de información. La indexación es una técnica que optimiza el acceso a los datos, mejora el rendimiento acelerando las consultas y otras operaciones. Es útil cuando la tabla contiene miles de registros, cuando se realizan operaciones de ordenamiento y agrupamiento y cuando se combinan varias tablas (tema que veremos más adelante).

La desventaja es que consume espacio en el disco en disco y genera costo de mantenimiento (tiempo y recursos).

Es importante identificar el o los campos por los que sería útil crear un índice, aquellos campos por los cuales se realizan búsquedas con frecuencia: claves primarias, claves externas o campos que combinan tablas.

Crear Indices

 

Dijimos que el objetivo de un indice es acelerar la recuperación de información y que es útil cuando la tabla contiene miles de registros, cuando se realizan operaciones de ordenamiento y agrupamiento, etc.

Es importante identificar el o los campos por los que sería útil crear un índice, aquellos campos por los cuales se realizan búsquedas con frecuencia: claves primarias, claves externas o campos que combinan tablas.

No se recomienda crear índices sobre campos que no se usan con frecuencia en consultas o en tablas muy pequeñas.

Para crear índices empleamos la instrucción "create index".

La sintaxis básica es la siguiente:

create TIPOdeINDICE index NOMBREINDICE
  on NOMBRETABLA(CAMPOS);

Los índices pueden ser: no únicos (los valores pueden estar repetidos) o únicos (los valores no pueden duplicarse). De modo predeterminado, si no se especifica el tipo de índice, se crea uno no único.

En el siguiente ejemplo creamos un índice único sobre el campo "documento" de la tabla "empleados":

create unique index I_empleados_documento
  on empleados(documento);

Para identificar los índices fácilmente, podemos agregar un prefijo al nombre del índice, por ejemplo "I" y luego el nombre de la tabla y/o campo.

Si se intenta crear un índice único para un campo que tiene valores duplicados, Oracle no lo permite.

Los campos de tipo "long" y "long raw" no pueden indexarse.

Una tabla puede indexarse por un campo (o varios).

Creamos un índice compuesto para los campos "apellido" y "nombre":

create index I_empleados_apellidonombre
 on empleado(apellido,nombre);

Cuando creamos una restricción "primary key" o "unique" sobre una tabla, Oracle automáticamente crea un índice sobre el campo (o los campos) de la restricción y le da el mismo nombre que la restricción. En caso que la tabla ya tenga un índice, Oracle lo usa, no crea otro.

Para obtener información sobre los índices podemos consultar varios diccionarios.

1) "user_indexes": nos muestra las siguientes columnas (entre otras que no analizaremos):

- INDEX_NAME (nombre del índice),
 - INDEX_TYPE (tipo de índice, nosotros crearemos el stardart normal),
 - TABLE_NAME (nombre de la tabla),
 - UNIQUENESS (si es único o no).

2) "user_ind_columns": nos muestra las siguientes columnas (entre otras que no analizaremos):

- INDEX_NAME (nombre del índice),
 - TABLE_NAME (nombre de la tabla),
 - COLUMN_NAME (nombre del campo),
 - COLUMN_POSITION (posición del campo),

3) "user_objects": en la columna "OBJECT_TYPE" muestra "index" si es un índice.

4) "user_constraints": si la restricción tiene un índice asociado, aparece su nombre en la columna "INDEX_NAME".

Tablas organizadas por índices

Son tablas que mantienen los datos ordenados por la clave primaria (Indice Clustered Nomenclatura Teoría).

• Una tabla organizada por índice es una alternativa a:
• Una tabla noclustered indexada por la clave primaria usando el comando CREATE INDEX.
• Una tabla clustered almacenada en un indice cluster que ha sido creado usando el comando CREATE CLUSTER que hace corresponder la clave primaria como clave del cluster.
• Se mantienen las filas de la tabla en un B*-tree construido en la clave primaria. Cada fila del índice contiene los valores de la clave y del resto de los atributos no claves:
• <primary_key_value, non_primary_key_column_values>

Beneficios de las tablas organizadas por índices:
§ Acceso rápido para consultas con matching exacto y busqueda por rangos.
§ No se requiere espacio para almacenar los ROWID de los registros.

Areas de OverFlow:

Las entradas de una tabla organizada por índice pueden ser muy grandes y requerir
varios bloques. Para manejar el problema se puede especificar:

§ un overflow tablespace:
§ un threshold value: porcentaje del tamaño del bloque que indica el tamano a partir del cual se almacenara el registro en el area de overflow. Si un registro excede el tamano del threshold, entonces las columnas cuyos valores de las columnas para la fila que excede el threshold son almacenados en el área de overflow.

CREATE TABLE emp
(ci char(8) ,
nombre char(30),
profesion char(20)
constraint pk_emp PRIMARY KEY (ci))
ORGANIZATION INDEX TABLESPACE emp_collection
PCTTHRESHOLD 20 INCLUDING nombre
OVERFLOW TABLESPACE emp_collection_overflow;
 

Cuando el tamano de una fila excede al 20% del tamano del bloque, todas las columnas que estan despues de la columna nombre, seran movidas al segmento del tablespace emp_collection_overflow.Si no se coloca el INCLUDING, se mueven todas las columnas excepto la clave primaria.

Notar que:

 
• Índices: son estructuras usadas para proveer acceso más rápido a los datos.

• Clave Primaria: conjunto minimal de atributos que identifica univocamente a las tuplas de una tabla.

1. ¿Cuándo se utilizan los índices?.

No se recomienda crear índices sobre campos que no se usan con frecuencia en consultas o en tablas muy pequeñas.

Los cambios sobre la tabla, como inserción, actualización o eliminación de registros, son incorporados automáticamente.

Cuando creamos una restricción "primary key" o "unique" a una tabla, Oracle automáticamente crea un índice sobre el campo (o los campos) de la restricción y le da el mismo nombre que la restricción. En caso que la tabla ya tenga un índice, Oracle lo usa, no crea otro.

Oracle permite crear distintos tipos de índices. "Normal" es el standard de Oracle, son índices tipo árbol binario; contiene una entrada por cada valor de clave que almacena la dirección donde se encuentra el dato. Es el tipo predeterminado y el más común (el único que estudiaremos).