9.3. CREAR INDICES.

CREAR INDICES.

Los índices tienen dos funciones: hacer cumplir los Constraint Primary Key y Unique, y mejorar el rendimiento. Una estrategia de aplicación de indexación es crítica para el rendimiento. No hay delimitación clara de cuyo dominio de índices se encuentra dentro de administración de índices. Cuando los analistas de negocios especifican reglas de negocio que serán implementadas como restricciones, son en efecto especificaciones de índices. Los administradores de la Base de datos monitorearan la ejecución  de código que corre en la Base de datos, y harán recomendación de índices. El desarrollador, que debe tener la mejor idea de lo que está pasando en el código y la naturaleza de los datos, también participará en el desarrollo de la estrategia de indexación

POR QUE LOS INDICES SON NECESARIOS

Los índices son parte del mecanismo de los Constraint. Si una columna (o grupo de columnas) son marcados como una llave principal de una tabla, entonces cada vez que una fila es insertada en la tabla, oracle debe comprobar que una fila con el mismo valor en la llave principal no existe. Si la tabla no tiene un índice en la columna, la única manera de hacer esto sería explorar  a la derecha atraves de la tabla, comprobando cada fila. Mientras esto puede ser aceptable para una tabla de solo unas pocas filas, para una tabla con miles o millones (o billones) de filas esto no es factible. Un índice da (casi) acceso inmediato a los valores llaves, asi la comprobación de la existencia puede ser hecha virtualmente instantáneamente. Cuando un Constraint Primary Key se define, Oracle automáticamente crea un índice en la columna Llave principal, si uno no existe.

Un Constraint Unique también requiere un índice. La diferencia de un Constraint Primary Key es que la columna  del Constraint Unique puede permitir nulos, tal vez en muchas filas.  Esto no afecta a la creación y uso del índice: nulos no van en los índices B*Tree, como es descrito en la siguiente sección tipos de índices.

Constraint Foreign Key son forzados por medio de índices, pero el índice debe existir en la tabla padre, no necesariamente en la tabla para el cuela se define el Constraints. Un Constraint Foreign Key se refiere a una columna en la tabla hija con la llave principal o una Llave Unique en la tabla padre. Cuando una fila es insertado en la tabla hijo, oracle va ha hacer una búsqueda en el índice en la tabla padre para confirmar que hay una fila coincidente antes de permitir insertar. Sin embargo, usted debería siempre crear índices  en las columnas Foreign Key en la tabla hijo por razones de rendimiento: un DELETE en la tabla padre será mucho más rápido si Oracle puede utilizar un índice para determinar si hay alguna referencia a las filas en la tabla hijo de la fila que está siendo eliminada. 

Los índices son críticos para el rendimiento. Cuando ejecutamos cualquier sentencia SQL que incluye una clausula WHERE, Oracle tiene que identificar que filas de la tabla deben ser seleccionadas o modificadas. Si no hay un índice en la columna referenciada en la clausula WHERE, la única manera de hacer esto es con una exploración completa de la tabla. Una Exploración completa de tabla lee cada registro de la tabla, con el fin de encontrar las filas correspondientes. Si la tabla tiene billones de filas, esto puede tomar horas. Si hay un índice en la columna correspondiente, oracle puede buscar en el índice en su lugar. Un índice es una lista ordenada de valores claves, estructurada de una manera que hacer la búsqueda más eficiente. Con cada valor clave es un puntero a la fila en la tabla. La localización de filas relevantes por medio de un índice de búsqueda es mucho más rápido que usar exploración completa de tabla, si la tabla esta sobre cierto tamaño y la proporción de filas a ser recuperadas es por debajo de un cierto valor. 

Para tablas pequeñas, o para una clausula WHERE que recuperara una gran fracción de las filas de la tabla, una exploración completa será más rápido: usted puede (generalmente) confiar en Oracle para tomar la decisión correcta con respecto a utilizar un índice, basada en la información que recolecta de la base de datos sobre las tablas y las filas dentro de ellas.

Una segunda circunstancia donde los índices pueden ser utilizados es para clasificación y ordenamiento. Una sentencia SELECT que incluye palabras como ORDER BY, GROUP BY o UNION (y algunos otros) debe ordenar las filas en orden, a menos que haya un índice, que puede devolver las filas en el orden correcto sin necesidad de ordenar primero. 

Una tercera circunstancia cuando los índices pueden mejorar el rendimiento es  cuando las tablas se unen (join), pero de nuevo Oracle tiene una opción: dependiendo  del tamaño de las tablas y los recursos disponibles de memoria, puede ser más rápido escanear las tablas en memoria y unirlas ahí, en lugar del uso de índices. La técnica Nested Loop Join (Bluce Anidado) pasa atraves de una tabla utilizando un índice  en la otra tabla para localizar las filas que coincidan: esto es usualmente una operación intensiva de disco. Un técnica hash join (unión de comprobación)  lee toda la tabla en memoria, convierte esta en una tabla hash, y utiliza un algoritmo hasging para localizar  filas coincidentes; esto es mas memoria y CPU intensivo. Un sort merge joins ordena las tablas sobre la columna JOIN y luego y luego las combina entre si: esto es a menudo en compromiso entre disco, memoria y recursos de CPU. Si no hay índices, a continuación, Oracle es muy limitada en la combinación de técnicas disponibles.

EN EL TRABAJO.

Los índices ayudaran a declaraciones SELECT, y también UPDATE, DELETE o MERGER que utilicen clausula WHERE, pero retrasaran a declaraciones INSERT.

TIPOS DE INDICES

Oracle soporta varios tipos de índices, que tienen algunas variaciones. Los dos tipos de índices de interés aquí son el Índice B*Tree, que es el tipo de índice por defecto y el índice Bitmap. Como una regla general, los índices mejoran el rendimiento para la recuperación pero reduce el rendimiento de las operaciones DML. Esto se debe a que los índices deben ser mantenidos. Cada vez que una fila es insertada a la tabla, una nueva llave debe ser insertada  en todos los índices sobre la tabla, que coloca una tensión adicional sobre la Base de Datos. Por esta razón, en los sistemas de procesamiento de transacciones  se acostumbra a mantener el numero de índices lo más bajo posible (quizás nomas que esos necesarios para los Constraints) y en los sistemas de consultas intensivas tales como un Data Warehouse para crear  tantos como podría ser útil. 

B*TREE INDEXES

Un índice B*TREE (la B significa “Balanceado”) es una estructura de árbol. El nodo raíz de del árbol punto de muchos nodos en el segundo nivel, que puede apuntar a muchos nodos en el tercer nivel y así sucesivamente. La profundidad necesaria  del árbol será determinado en gran medida por el numero de filas en la tabla y la longitud de los valores de llaves del índice. 

EN EL TRABAJO

La estructura B*TREE es muy eficiente. Si la profundidad es mayor a tres o cuatro, a continuación, o las llaves índices son muy largos o la tabla tiene billones de filas. Si ninguno de estos es el caso, entonces el índice está en la necesidad de una reconstrucción. 

Las hojas nodo del árbol del índice  almacenan las llaves de las filas, en orden, cada uno con un puntero que identifica la localización física del registro. Así para recuperar una fila con un índice de búsqueda, si la clausula WHERE se utiliza predicado de igualdad en la columna indexada, Oracle navega abajo del árbol al nodo de hoja que contiene el valor llave deseado, y después utiliza el apuntador para encontrar la fila. Si la clausula WHERE se utiliza un predicado noneequality (como cualquiera de los operadores LIKE, BETWEEN, >, o, <) entonces Oracle puede navegar abajo del árbol para encontrar la primera llave coincidente y luego navegar atraves de los nodos hojas del índice para encontrar el resto de valores que emparejan. Como lo hace, recuperara las filas de la tabla en orden.

El puntero a la fila es el ROWID. El ROWID es una pseudo-columna propiedad de Oracle, que cada fila en cada tabla tiene. Cifrada dentro de la dirección física de la fila. Como ROWID no es parte del estándar SQL, nunca son visibles a una declaración SQL, pero usted puede ver y usar si lo desea. Esto es una muestra en la figura 9-5. 

Una fila ROWID es globalmente única. Cada fila en cada tabla en la base de datos completa tendrán ROWID diferentes. El cifrado ROWID da la dirección física de la fila. De ella, Oracle puede calcular que archivo del sistema operativo, y donde en el archivo esta la fila, e ir directamente a ella. 

Los índices B*Tree son una forma muy eficiente  de recuperar filas si el numero de filas necesarias es bajo en proporción  al número total de filas en la tabla y si la tabla es grande.  Considere esta declaración:

select count(*) from employees where last_name between 'A%' and 'Z%';

Esta clausula WHERE es lo suficientemente amplia que incluirá todas las filas en la tabla. Sería mucha más lenta para buscar en el índice para encontrar los ROWIDS y luego utilizar los ROWIDS para buscar las filas que explorar la tabla completa. Después de todo. Es la tabla entera que es necesaria. Otro ejemplo seria si la tabla fuera muy pequeña que una lectura de disco podría analizarla completamente; no habría punto  en la lectura del primer índice.

Se dice a menudo que si la consulta va a recuperar  más de 2 o 4 por ciento de las filas, entonces una exploración completa de tabla  será más rápida. Una importante excepción a esto es si el valor especificado en la clausula WHERE es nulo. NULL no van en el índice B*TREE, asi que una consulta tal como esta: 

select * from employees where last_name is null;

Siempre resultara una exploración complete de la tabla. Hay poco valor crear un índice B*TREE en una columna con pocos valores únicos, no será lo suficiente selectiva: la proporción de la tabla que será recuperada para cada llave distinta será demasiado alto. En general, índices B*TREE debe ser utilizados si:

• La cardinalidad (el numero de valores distintos) en la columna es alta.

• El numero de filas de la tabla es alta, y

• La columna es utiliza en clausulas WHERE o condiciones JOIN.

INDICES BITMAP

En muchas aplicaciones de negocio, la naturaleza de los datos y las consultas es tal que índices B*TREE no son de mucha utilidad. Considere la tabla de ventas para una cadena de supermercados. Almacena un año de datos históricos, que pueden ser analizados en varias dimensiones. La figura 9-6 muestra un simple diagrama entidad relación, que solo cuatro de las dimensiones.

La cardinalidad de cada dimensión puede ser bastante bajo. Hacer estos suuestos:

Suponiendo una distribución uniforme de los datos, solo dos de las dimensiones (producto y fecha) tiene una selectividad que es mejor que el criterio de uso general  de 2 por ciento a 4 por ciento, que hace un índice que vale la pena. Pero si consultas utilizan predicados de rango (tales como cantidad de ventas en el mes o de una clase de productos  de diez o más), entonces ni siquiera estos se clasificaran. Esto es un simple hecho: los índices B*TREE a menudo son inútiles en una entorno Data Warehouse. Una consulta típica podría ser que desee comparar las ventas entre dos tiendas para atender a clientes de una determinada clase de producto en un mes. Podrían bien haber índices B*TREE en las columnas relevantes, pero Oracle ignora como insuficiente selectivo. Esto es que los índices Bitmap están diseñados para.

Un índice Bitmap almacena los ROWIDS asociado con cada valor llave como un Bitmap. El Bitmap para el índice CHANNEL podría tener este aspecto:

WALKIN 11010111000101011101011101.....

DELIVERY 00101000111010100010100010.....

Esto indica que las dos primeras filas fueron ventas a entrega a los clientes, la tercera venta era una entrega, la cuarta venta fue entrega, y asi sucesivamente.

Los Bitmaps para índices de SHOP podrían ser.

LONDON 11001001001001101001010000.....

OXFORD 00100010011000010001001000.....

READING 00010001000100000100100010.....

GLASGOW 00000100100010000010000101.....

Esto indica que las dos primeras ventas fueron en la Tienda de London, la tercera fue en Oxford, la cuarta en Reading, y así sucesivamente. Ahora si esta consulta es recibida;

select count(*) from sales where channel='WALKIN' and shop='OXFORD';

Oracle puede recuperar los dos Bitmaps pertinentes y agregarlas junto con una operación booleana AND:

WALKIN 11010111000101011101011101.....

OXFORD 00100010011000010001001100.....

WALKIN & OXFORD 00000010000000010000000000.....

El resultado de la operación AND muestra que solo el séptima y decima séptima filas califican para la selección.  Esta combinación de Bitmaps es muy rápida y puede ser utilizada para implementar  complejas operaciones Booleanas con muchas condiciones en muchas columnas utilizando cualquier combinación de operaciones AND, OR y NOT. Una ventaja especial que los índices Bitmaps tienen por arriba que los índices B*TREE es que incluyen valores NULL. En cuanto al índice Bitmap se refiere,  NULL es solo otro valor distinto, que tendrá su propio Bitmaps. 

En general, Índices Bitmaps deben ser utilizados si:

• La Cardinalidad (el numero de valores distintos) en la columna es baja.

• El numero de filas en la tabla es alto, y

• La columna es utilizada en operaciones de algebra Booleana.

EN EL TRABAJO

Si usted supiera por adelantado cual serian las consultas, entonces usted puede construir Índices B*TREE que trabajarían, tales como un índice compuesto en SHOP y CHANNEL. Pero por lo general no sabe, que es donde la combinación dinámica de Bitmaps da gran flexibilidad.

TIPOS DE OPCIONES DEL INDICE

Existen seis opciones utilizadas comúnmente que pueden ser aplicadas cuando creamos un índice.

• Unique o Non-Unique.

• Reserve Key.

• Compressed.

• Composite.

• Function Based.

• Ascending o Descending.

Todas estas seis variaciones aplican a los índices B*TREE, pero solo las últimas tres pueden ser aplicadas a los índices Bitmaps.

• Un Índice Unique no permitirá valores duplicados. Non-unique es el default. El atributo unique del índice opera independientemente de  un Constraint Unique o Primary Key: la presencia de un Índice Unique no permitirá inserción de valores duplicados incluso si no hay tal Constraint definido. Un Constraint Unique o Primary Key puede utilizar índice Non-Unique; solo sucederá no tener valores duplicados. Esto es, de hecho un requerimiento para un Constraint que es Deferrable (impostergable), ya que puede haber un periodo (antes que las transacciones son confirmadas) cuando los valores duplicados existen.

• Un índice reverse key es construido sobre una versión de la columna llave con sus bytes invertidos: en lugar que indexé “John,” indexara “nhoj” cuando un SELECT es hecho. Oracle invertirá automáticamente el valor de la cadena de búsqueda. Esto es una técnica muy útil  para evitar la contención de sistemas multiusuario. Por ejemplo, si muchos usuarios están insertando concurrentemente filas con llaves primarias basadas en un numero de incremento secuencial, todos sus índices insertados se concentraran en lo alto (final) del índice. Mediante las llaves invertidas, la llave índice consecutiva insertada tendera a extenderse a lo largo de todo el rango del índice. Incluso aunque “John” y “Jules” están muy juntas, “nhoj” and “seluj” serán muy muy distantes. 

• Un índice compressed (comprimido) almacena  valores llaves repetidos solo una vez. El valor por defecto es no compress, lo que significa que su un valor llave no es único, será almacenado una vez por cada ocurrencia, cada uno tiene un único puntero ROWID. Un índice Compressed almacenara la llave una vez, seguido por una cadena de todos los ROWID coincidentes.

• Un índice Composite (compuesto)  está basado en la concatenación de dos o más columnas. No hay restricciones sobre los tipos de datos que se mezclan. Si un cadena de búsqueda no incluye todas las columnas, el índice puede todavía ser utilizado-pero si no incluye la extrema columna izquierda, oracle tendrá que utilizar un método  skip-scanning que es mucho menos eficiente que si la columnas mas a la izquierda es incluida.

• Un índice function-based  está basado sobre el resultado de una función aplicado a una o mas columnas, tales como UPPER (last_name) o to_char(startdate,’ccyy-m-dd’). Una consulta tendrá que aplicar la misma función a la cadena de búsqueda, o oracle no puede ser capaz de utilizar el índice.

Por defecto, un índice es ascending, significa que la llave es almacenada en orden de menor a mayor valor. Un índice descending invierte esto. De hecho, la diferencia no suele ser importante: las entradas en un índice son almacenadas como una lista doblemente ligada por lo que es posible ir de arriba abajo con igual rapidez, pero esto afectara el orden en el cual las filas son regresadas si son recuperadas con un rango de exploración de índice.

CREANDO U TILIZANDO INDICES.

Los índices son creados implícitamente cuando Constraints Primary Key y unique son definidos. Si un índice sobre la columna relevante no existe. La sintaxis básica para crear un índice explícitamente es.

CREATE [UNIQUE | BITMAP] INDEX [ schema.]indexname

ON [schema.]tablename (column [, column...] ) ;

El tipo por defecto de un índice es un non-unique, non-compressed, non-reverse key, índice B*TREE.  No es posible crear un índice Bitmap Unique (y usted no le gustaría si pudiera-pensar la cuestión de cardinalidad). Los índices son objetos de esquema, y es posible crear un índice en un esquema sobre una tabla que está en otro esquema. Pero la mayoría de las personas encontrara esto como confuso. Un índice Composite es un índice sobre varias columnas. Índices Composite puede ser sobre columnas de diferentes tipos, y las columnas no tienes que ser adyacentes en la tabla.

EN EL TRABAJO

Muchos administradores de bases de datos no consideran buena práctica confiarse sobre la creación implícita de índices. Si el índice es creado explícitamente, el creador tiene control completo sobre las características del índice, que puede ser más fácil para el DBA su gestión posterior.

Considera este ejemplo de creación de tabla e índices, y luego la definición de Constraints:

create table dept(deptno number,dname varchar2(10));

create table emp(empno number, surname varchar2(10),

forename varchar2(10), dob date, deptno number);

create unique index dept_i1 on dept(deptno);

create unique index emp_i1 on emp(empno);

create index emp_i2 on emp(surname,forename);

create bitmap index emp_i3 on emp(deptno);

alter table dept add constraint dept_pk primary key (deptno);

alter table emp add constraint emp_pk primary key (empno);

alter table emp add constraint emp_fk

foreign key (deptno) references dept(deptno);

Los primeros índices creados están marcados como UNIQUE, esto significa que no será posible insertar valores duplicados. Esto no es definido como un Constraint en este punto pero es cierto, sin embargo. El tercer índice no está definió como UNIQUE y por lo tanto, aceptar valores duplicados; este es un índice Composite sobre dos columnas. El cuarto índice es definido como un Índice Bitmap, porque la cardinalidad de la columna es probable que sea baja en proporción a el numero de filas en la tabla.

Cuando los dos primero Constraints Primary Key son definidos,  Oracle detectara los índices pre creados y los utilizaran para hacer cumplir las restricciones. Tenga en cuenta que el índice sobre DEPT.DEPTNO no tiene propósito para rendimiento porque la tabla con toda probabilidad  sea tan pequeño que el índice nunca será utilizado para recuperar filas (una exploración será más rápida), pero es todavía esencial tener un índice para hacer cumplir el Constraints Primary key. 

Una vez que se crean, el uso de índice es completamente transparente y automático. Antes de ejecutar una declaración SQL, el servidor Oracle evaluara  todas las formas posibles de ejecutarlo. Algunas de estas formas pueden implicar el uso cualquiera que sea  índices que están disponibles; otros no pueden. Oracle hará uso de la información recolectada sobre las tablas y el entorno para tomar la decisión acerca  de los índices a usar.

EN EL TRABAJO

El servidor Oracle debe hacer la mejor decisión acerca del uso de índices, pero si está obteniendo errores, es posible para un programador embeber instrucciones, conocido como sugerencias de optimizador, en el código que obligara a utilizar  de ciertos índices.

MODIFICANDO Y ELIMINADO INDICES.

Hay un comando ALTER INDEX…,  pero no puede ser utilizados para cambiar cualquier de las características descritas en este capítulo: el Tipo (B*TREE o Bitmap) de índice, las columnas, o si es unique o non-unique. El comando ALTER INDEX se encuentra (miente)  en el dominio de la administración de la Base de datos y por lo general se utiliza para ajustar las propiedades físicas del índice, no las propiedades lógicas que son de interés para los desarrolladores. Si es necesario cambiar alguna de estas propiedades, el índice debe ser eliminado y crearlo nuevamente.  Continuando con el ejemplo en el sección anterior, para cambiar el índice EMP_12 para incluir los cumpleaños de los empleados, 

drop index emp_i2;

create index emp_i2 on emp(surname,forename,dob);

Este Índice Composite (compuesto)  ahora incluye columnas con diferentes tipos de datos. Las columnas pasaran a ser enumeradas en el mismo orden que son definidas en la tabla, pero esto es de ninguna manera necesaria.

Cuando una tabla es eliminada, todos los índices y Constraints  definidos para la tabla se eliminan también. Si un índice  fue creado implícitamente mediante la creación de un Constraints, entonces Eliminar el Constraints también eliminara el índice. Si el índice se había creado explícitamente y el Constraint creado posteriormente, entonces si el Constraint fuera eliminado el índice sobrevivirá. 

EJERCICIO 9-3

CREAR INDICES.

En este ejercicio, agregara algunos índices a la tabla EX_EMPS, y modificar el índice creado cuando el Constraints Primary Key fue agregado.

1. Conectarse a su base de datos con SQL*PLUS como usuario HR.

2. Determine el nombre y algunas otras características del Índice Primary Key ejecutando esta cosulta:

select index_name,column_name,index_type,uniqueness

from user_indexes natural join user_ind_columns

where table_name='EX_EMPS';

3. Crear un indice compuesto B*TREE sobre el nombre de empleados:

create index ex_name_i on ex_emps (last_name,first_name);

4. Crear indices Bitmap en algunas columnas de baja cardinalidad:

create bitmap index ex_dept_i on ex_emps(department_id);

create bitmap index ex_mgr_i on ex_emps (manager_id);

5. Con el Database Control, mire los índices. La ruta de navegación desde la pagina principal del Database Control tome la ficha Esquema, y entonces dar clic en el link Índices sobre la sección Database Objects.  Ingresar HR como el nombre del esquema y EX_EMPS como el nombre del objeto, y dar clic en Go. Los índices serán (cuatro, incluyendo los índices Primary Key creados automáticamente) mostrados.