martes, 16 de abril de 2013

Indices


Indices
El índice es un fichero que permite localizar con rapidez registros en la base de datos mediante la claves de índice (define el orden de los índices.) las cuales le proporcionan una posición o dirección a un archivo. Cuando se selecciona un índice, los registros de la base de datos aparecen ordenados según lo establecido mediante los valores de índice. El objetivo principal de la utilización de índices es agilizar la obtención de datos y reducir las operaciones de entradas y salidas en el disco duro.
El método más simple es el de organización secuencial el cual es la única manera de guardar los archivos en una cinta magnética. Con dispositivos de acceso directo se pueden almacenar en forma secuencial o bajo organizaciones indexadas las cuales utilizan las direcciones de almacenamiento para identificar la localización específica de los datos. Estas direcciones están compuestas por el número de cilindro, superficie y sector.
Las siguientes tareas forman parte de la estrategia recomendada para crear índices:
  1. Diseñar el índice.
El diseño de índices es una tarea crítica. El diseño de índices incluye la determinación de las columnas que se utilizarán, la selección del tipo de índice (por ejemplo, agrupado o no agrupado), la selección de opciones de índice adecuadas y la determinación de grupos de archivos o de la ubicación de esquemas de partición. Para obtener más información, vea Diseñar índices.
  1. Determinar el mejor método de creación. Los índices se crean de las siguientes maneras:
    • Definiendo una restricción PRIMARY KEY o UNIQUE en una columna mediante CREATE TABLE o ALTER TABLE
SQL Server DatabaseEngine (Motor de base de datos de SQL Server) crea automáticamente un índice único para hacer cumplir los requisitos de unicidad de una restricción PRIMARY KEY o UNIQUE. De forma predeterminada se crea un índice clúster único para hacer cumplir una restricción PRIMARY KEY, a menos que ya exista un índice clúster en la tabla o que usted especifique un índice no clúster único. De forma predeterminada se crea un índice único no clúster para hacer cumplir una restricción UNIQUE a menos que se especifique de explícitamente un índice clúster único y no exista un índice clúster en la tabla.
También se pueden especificar las opciones de índice, la ubicación del índice, el grupo de archivos o el esquema de la partición.
Un índice creado como parte de una restricción PRIMARY KEY o UNIQUE recibe automáticamente el mismo nombre que la restricción. Para obtener más información, vea Restricciones PRIMARY KEY y Restricciones UNIQUE.
    • Creando un índice independiente de una restricción utilizando la instrucción CREATE INDEX , o el cuadro de diálogo Nuevo índice en el Explorador de objetos de SQL Server Management Studio
Debe especificar el nombre del índice, de la tabla y de las columnas a las que se aplica el índice. También se pueden especificar las opciones de índice, la ubicación del índice, el grupo de archivos o el esquema de la partición. De forma predeterminada, se crea un índice que no es único y no está agrupado si no se especifican las opciones únicas o agrupadas. Para crear un índice filtrado, use la cláusula opcional WHERE. Para obtener más información, vea Directrices generales para diseñar índices filtrados.
  1. Crear el índice.
Un factor importante que debe tenerse en cuenta es si el índice se creará en una tabla vacía o en una tabla con datos. La creación de un índice en una tabla vacía no tiene implicaciones de rendimiento en el momento de creación del índice; sin embargo, el rendimiento se verá afectado cuando se agreguen los datos a la tabla.
La creación de índices en tablas grandes debe planearse con cuidado para que el rendimiento de la base de datos no se vea afectado. La mejor manera de crear índices en tablas de gran tamaño es empezar con el índice clúster y, a continuación, generar los índices no clúster. Considere la posibilidad de establecer la opción ONLINE en ON cuando cree índices en tablas existentes. Cuando se establece en ON, los bloqueos a largo plazo no se retienen, lo que permite que continúen consultas o actualizaciones a la tabla subyacente. Para obtener más información, vea

En la siguiente tabla se enumeran los valores máximos que se aplican a los índices clúster, no clúster, espaciales, filtrados y XML. A menos que se especifique lo contrario, las limitaciones se aplican a todos los tipos de índices.
Límites de índice máximos
Valor
Índices clúster por tabla
1
Índices no clúster por tabla
999
Índices XML por tabla
249
Índices espaciales por tabla
249
Número de columnas de clave por índice
16*
Tamaño del registro de clave de índice
900 bytes*
*Puede evitar limitaciones de tamaño de registro y de columna de clave de índice de índices no clúster incluyendo columnas sin clave en el índice. Para obtener más información, vea Índice con columnas incluidas.


Generalmente, se puede indizar cualquier columna de una tabla o de una vista. En la siguiente tabla se muestran todos los tipos de datos que tienen una participación de índice restringida.
Tipo de datos
Participación de índice
Tipo definido por el usuario CLR
Se puede indizar si el tipo admite el orden binario.
Tipos de datos de objetos grandes (LOB): image, ntext, text, varchar(max), nvarchar(max), varbinary(max) y xml
No pueden ser una columna de clave de índice. No obstante, una columna XML puede ser una columna de clave en una tabla o en un índice XML secundario o principal.
Pueden participar como columnas sin clave (incluidas) en un índice no clúster, excepto image, ntext y text.
Pueden participar si son parte de una expresión de columna calculada.
Columnas calculadas
No se pueden indizar. Esto incluye columnas calculadas definidas como invocaciones de métodos de una columna del tipo definido por un usuario CLR, mientras los métodos se marquen como deterministas.
Las columnas calculadas que se derivan de tipos de datos LOB se pueden indizar como columna con clave o sin clave mientras el tipo de datos de columna calculada se permita como columna de clave de índice o columna sin clave.
Columnas de Varchar de inserción no consecutiva
La clave de índice de un índice clúster no puede contener columnas varchar con datos existentes en la unidad de asignación ROW_OVERFLOW_DATA. Si un índice clúster se crea en una columna varchar y los datos existentes están en la unidad de asignación IN_ROW_DATA, las acciones de inserción o actualización posteriores de la columna que constituirían inserciones no consecutivas producirán un error.
geometry
Se puede indizar con varios índices espaciales.


El espacio en disco necesario para almacenar el índice depende de los siguientes factores:

Tipo de índice
Descripción
Agrupado
Un índice clúster ordena y almacena las filas de datos de la tabla o vista por orden en función de la clave del índice clúster.El índice clúster se implementa como una estructura de árbol b que admite la recuperación rápida de las filas a partir de los valores de las claves del índice clúster.
No agrupado
Los índices no clúster se pueden definir en una tabla o vista con un índice clúster o en un montón.Cada fila del índice no clúster contiene un valor de clave no agrupada y un localizador de fila.Este localizador apunta a la fila de datos del índice clúster o el montón que contiene el valor de clave.Las filas del índice se almacenan en el mismo orden que los valores de la clave del índice, pero no se garantiza que las filas de datos estén en un determinado orden a menos que se cree un índice clúster en la tabla.
Único
Un índice único se asegura de que la clave de índice no contenga valores duplicados y, por tanto, cada fila de la tabla o vista sea en cierta forma única.
La unicidad puede ser una propiedad tanto de índices clúster como de índices no clúster.
Almacén de columnas
Un índice de almacén de columnas optimizado en memoria xVelocity basado en la partición vertical de los datos por columnas, almacenadas como objetos grandes (LOB).
Índice con columnas incluidas
Índice no clúster que se extiende para incluir columnas sin clave además de las columnas de clave.
Índice en columnas calculadas
Índice de una columna que se deriva del valor de una o varias columnas, o algunas entradas deterministas.
Filtrado
Índice no clúster optimizado, especialmente indicado para cubrir consultas que seleccionan de un subconjunto bien definido de datos.Utiliza un predicado de filtro para indizar una parte de las filas de la tabla.Un índice filtrado bien diseñado puede mejorar el rendimiento de las consultas y reducir los costos de almacenamiento del índice en relación con los índices de tabla completa, así como los costos de mantenimiento.
Espacial
Un índice espacial proporciona la capacidad de realizar de forma más eficaz determinadas operaciones en objetos espaciales (datos espaciales) en una columna del tipo de datos geometry.El índice espacial reduce el número de objetos a los que es necesario aplicar las operaciones espaciales, que son relativamente costosas.
XML
Representación dividida y persistente de los objetos binarios grandes (BLOB) XML de la columna de tipo de datos xml.
Texto completo
Tipo especial de índice funcional basado en símbolos (token) que compila y mantiene el motor de texto completo de Microsoft para SQL Server.Proporciona la compatibilidad adecuada para búsquedas de texto complejas en datos de cadenas de caracteres.


ORACLE
Un índice en una base datos Oracle 11g es un objeto opcional normalmente asociado a una tabla pero que su uso es casi imprescindible. Una de las misiones de los índices es permitir que las consultas de datos sean más rápidas devolviendo su resultado, sobre todo en tablas con miles o millones de líneas. Una tabla pude tener más de un índice y estos pueden estar compuestos por una o varias columnas

El estamento básico para crear índices en una base de datosOracle 11g es:

1.CREATEINDEX<em>nombre_indice</em> ON<em>nombre_tabla</em> (<em>columna, columna1,….</em>);

En el artículo - Introducción a la creación de tablas en Oracle 11g - creamos la tabla clientes, vamos a utilizarla como base para practicar la creación de índices.

En primer lugar vamos a crear un índice sobre la columna CIF para que nuestras búsquedas por esta columna sean llo más rapidas posible:

1.CREATEINDEX<span class="IL_AD"id="IL_AD6">factura</span>.clientes_idx1 ONfactura.clientes (CIF)
2.TABLESPACE FACTURA_IDX01;

Vamos a crear otro índice compuesto por las columnas NombreCli y DireccionCli:

1.CREATEINDEXfactura.clientes_idx2 ONfactura.clientes (NombreCli, DireccionCli)
2.TABLESPACE FACTURA_IDX01;

Como podéis ver he añadido la cláusula TABLESPACE para indicar que el índice se cree en el tablespace FACTURA_IDX01, si omitimos esta cláusula el índice se creará en el DEFAULT TABLESPACE que tenga definido el usuario FACTURA.

El usuario que vaya a crear el índice tiene que tener privilegio de CREATE INDEX y UNLIMITED TABLESPACE o CUOTA sobre el tablespace FACTURA_IDX01.

En el caso que tengamos más de un índice Oracle se encargará de utilizar el que se ajuste mejor a cada query, nosotros en principio no tenemos que preocuparnos, pero si tenemos que pensar en una buena estrategia de creación de índices

viernes, 12 de abril de 2013

Modos de operacion de un SGBD


Rollback 
Operación que devuelve a la BD a un estado previo. La BD puede ser restaurada a una copia limpia incluso después de que se han realizado operaciones erróneas.
En SQL es un comando que hace que todos los cambios desde la ultima sentencia BEGIN WORK, o STAR TRANSACTION, sean descartados por la gestión de la BD relacional para que el estado de los datos sea ROLLBACK (Devuelto) a la forma que tenían antes de haber realizado los cambios. Son específicos de la conexión, quiere decir que si se hacen dos conexiones a la BD, un ROLLBACK echo sobre una conexión no afectara a otras conexiones. Esto es vital para un buen funcionamiento de concurrencia.
Mysql : mysql_query('begin');
mysql_query($query1) or die(mysql_query('rollback'));
mysql_query($query2) or die(mysql_query('rollback'));
mysql_query($query3) or die(mysql_query('rollback'));
mysql_query('commit'); 
Oracle : delete from emp;
rollback;          /* undo the changes */

Commit
En SQL finaliza una transacción de la BD dentro de un sistema gestor de la base de datos relacional y pone visibles todos los cambios a otros usuarios. El formato general es emitir una sentencia BEGIN WORK, una o mas sentencias SQL, y entonces la sentencia Commit.
Una sentencia Commit publicara cualquiera de los savepoints (puntos de recuperación) existentes que pueden estar en uso.
MySQL trae activado el modo autocommit, Para desactivar el autocommit, se puede desactivar el autocomit ejecutando el comando:
SET AUTOCOMMIT=0;
En Oracle:
insert into emp (empno,ename,sal) values (101,’Abid’,2300);
commit;

Recovery 
Un sistema de recuperación consiste en restaurar la BD a un estado correcto, tras cualquier fallo que la haya dejado en un estado incorrecto.
En Oracle hay recuperaciones automáticas ante los fallos el proceso varía dependiendo del tipo de fallo y las estructuras afectadas.
-Ficheros Redo Log: Ficheros de almacenamiento de cambios en la BD,
(Recovery).
- Ficheros de Control: Almacenan el estado de la estructura física de la BD.
Guían la recuperación.
- Rollback Segments: Almacenan las últimas sentencias realizadas sobre la
BD. Saben cuándo se ha confirmado o no una transacción.
- Backups de la BD: Copias de Seguridad, (Restoring)

BACKUP TABLE tbl_name [, tbl_name] ... TO '/path/to/backup/directory'
RESTORE TABLE tbl_name [, tbl_name] ... FROM '/path/to/backup/directory'