Laboratorio 0007: Aplicaciones de Índices

publicado a la‎(s)‎ 14 ago 2012, 12:07 por Hernan Nina Hanco   [ actualizado el 4 jun 2013, 10:54 ]
I) OBJETIVOS
  • Utilizar un Sistema de Gestión de Bases de Datos Comercial u OpenSource y realizar aplicaciones sobre Índices

II) MARCO CONCEPTUAL

Guía de la Asignatura.

III) PRÁCTICAS DE LABORATORIO

/*Indices en SQL Server */
-- Los indices al igual que los indices de los libros ofrecen una forma rapida de 
-- acceder a los datos que se esta buscando aun en tablas grandes

CREATE DATABASE BD_PRUEBA2
GO

use bd_prueba2
go

CREATE TABLE AlumnoInfo (
id_alumno int identity(1,1),
nombres varchar(30),
ap varchar(20),
am varchar(20),
ciudad varchar(30),
fecha_nac datetime,
obs varchar(1000)
)
GO
--DROP TABLE AlumnoInfo
/* Opciones de Indices */
/* Opcion online*/
-- Si esta en ON indica que las tablas e indices asociados estan 
-- disponibles para consultas y modificacion de datos durante la 
-- operacion de indexado. La opcion predeterminada es OFF

CREATE INDEX idx_nombres
ON AlumnoInfo (nombres)
WITH (ONLINE = ON)

-- Modificar la opcion de un indice
ALTER INDEX idx_nombres
ON AlumnoInfo
REBUILD
WITH (ONLINE=OFF)

-- Eliminar un indice de la tabla
DROP INDEX idx_nombres ON dbo.AlumnoInfo

/* Crear un indice con la opcion ALLOW_ROW_LOCKS */
-- Si la opcion esta activada es ON(Opcion Predeterminada) 
-- se permite los Bloqueos de fila
CREATE INDEX idx_nombres2
ON AlumnoInfo (nombres)
WITH (ALLOW_ROW_LOCKS = OFF)
/*Crear un indice con la opcion ALLOW_PAGE_LOCKS*/
-- Si la opcion esta activada es ON(Opcion Predeterminada) se permite los 
-- Bloqueos de pagina
CREATE INDEX idx_nombre3
ON AlumnoInfo (nombres)
WITH (ALLOW_PAGE_LOCKS = OFF)

/*Crear un indice con la opcion ONLINE, ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS*/
CREATE INDEX idx_nombres4
ON AlumnoInfo (nombres)
WITH (ONLINE = ON, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF)

/*Crear un Indice Agrupado*/
-- Los Indices agrupados ordenan las filas segun la 
-- clave de agrupamiento. En general
--, toda tabla deberia tener un solo indice agrupago;
-- generalmento sobre la clave primaria

CREATE CLUSTERED INDEX ci_fecha_nac
ON dbo.AlumnoInfo(fecha_nac);
-- Consultamos para verificar si pueden ser extraidos de la tabla
SELECT * FROM dbo.ALumnoInfo;
/*Deshabilitar el indice ejecutando la siguiente Instruccion*/
ALTER INDEX ci_fecha_nac ON dbo.AlumnoInfo DISABLE;
-- Consultamos para verificar que no se pueden extraer datos de la tabla
SELECT * FROM dbo.ALumnoInfo;
/*Habilitar un indice agrupado*/
ALTER INDEX ci_fecha_nac ON dbo.AlumnoInfo REBUILD;
GO
-- Consultamos para verificar que se pueden extraer datos de la tabla
SELECT * FROM dbo.ALumnoInfo;

/* Crear un indice no agrupado */
-- Los Indices no agrupados no ordenan las filas de la tablas. 
-- Puede crear Hasta 249 indices no agrupados 
-- por tabla para satisfacer la mayor parte de sus consultas

CREATE NONCLUSTERED INDEX idx_ap ON
dbo.AlumnoInfo(ap);
/*Indices cubiertos COVERED*/
-- Mediante los indices cuviertos, 
-- podemos ejecutar consultas sin necesidad de acceder a la tabla
CREATE NONCLUSTERED INDEX idx_ap_am_nombres
ON AlumnoInfo(ap,am,nombres)
/*Indices Incluidos */
-- los indices de columnas incluidas permiten añadir 
-- valores al nivel de hoja del indice de forma que pueda 
-- crear implementaciones de indices complejas
-- Solución para el límite de los 900 bytes en la creación de un Indice
CREATE NONCLUSTERED INDEX idx_id_obs ON
dbo.AlumnoInfo(id_alumno)
INCLUDE(obs); -- Este campo excede el limite de 900 bytes puesto que lo definimos con varchar(1000)

/* Buscar indices fragmentados*/
-- La siguiente consulta identifica el porcentaje de fragmentacion de los Indices de la BD
USE AdventureWorks
GO

SELECT
  OBJECT_NAME(i.object_id) AS TableName, 
  i.name AS IndexName,
  ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
JOIN sys.indexes i ON 
  i.object_id = ips.object_id 
  AND i.index_id = ips.index_id
WHERE ips.avg_fragmentation_in_percent > 90
-- Resultado:
-- TableName IndexName Promedio del porcentaje de Fragmentación
---------------------------------------------------------------------------------
--vProductAndDescription IX_vProductAndDescription 98.5714285714286
--SalesOrderDetail AK_SalesOrderDetail_rowguid 100
--Individual XMLVALUE_Individual_Demographics 100
--Store PXML_Store_Demographics 98.4375


/* Defragmentación de un index*/

ALTER INDEX AK_SalesOrderDetail_rowguid
ON Sales.SalesOrderDetail
REORGANIZE

IV) Tareas
  1. Verificar y comparar los costos de procesamiento para una búsqueda con y sin Índices primario.
  2. Crear una tabla con una clave primaria, la tabla debe tener mas de dos campos. crea un Índice primario para la tabla donde su clave de búsqueda no sea la clave primaria.

Comments