Bases de DatosÍndices

Table scan

• Un table scan es una búsqueda en donde se leen todas las filas de una tabla

• Una tabla que no tenga índices creados, solamente puede hacer búsquedas a través de un table scan

Indices

• Un índice es un objeto de base de datos que ayuda al servidor a encontrar un dato más rápidamente

Estructura de un Indice : Caso de Estudio

key

PAGE 1001PAGE 1001BennetKarsen Smith

1421, 11876, 11242, 1

100713051062

PAGE 1132PAGE 11321421, 11129, 31409, 1

BennetChanDullEdwards 1018, 5

PAGE 1007PAGE 1007BennetGreane Hunter

1421, 11242, 41242, 1

113211331127

pgptr

rowptr

PAGE 1305PAGE 1305Karsen

1876, 1

1311

keypgptr

rowptr

PAGE 1133PAGE 11331242, 41421, 21409, 2

GreaneGreenGreene

PAGE 1127PAGE 11271242, 11241, 4

HunterJenkins

PAGE 1241PAGE 1241O’LearyRingerWhiteJenkins

10111213

PAGE 1242PAGE 1242HunterSmithRingerGreane

14151617

PAGE 1421PAGE 1421BennetGreenRinger

181920

PAGE 1409PAGE 1409DullGreeneWhite

212223

(more pages)(more pages)

(more pages)

authors table(data pages)Index pages

create index idx_authors_2

on authors(au_lname)

Crear y borrar índices

• Sintaxis simplificada para create :create [unique] [ clustered | nonclustered ]index index_name on table_name (column1 [, column2] ... )

• Ejemplo:create clustered index idx_c_titles_1on titles (title_id)

• Sintaxis Simplicada para drop :drop index table_name.index_name

• Ejemplo:drop index titles.idx_c_titles_1

Atributos de los índices

• Tres atributos describen cada índice

– El número de columnas sobre las cuales se declara el índice• Una columna – índice no-compuesto• Múltiples columnas - índice compuesto

– Si el índice acepta o no valores duplicados• Se permiten valores duplicados - índice no-único• No se permiten valores duplicados - índice único

– Si están o no ordenados los datos en la tabla por el concepto del índice cuando la tabla se crea

• Datos ordenados durante la creación - índice cluster• Datos no ordenados durante la creación - índice no-cluster

Indice no-compuesto

• Un índice no-compuesto es un índice creado sobre una columna– Ejemplo:create index idx_authors_2on authors(state)

• Apropiado cuando las consultas se hacen frecuentemente sobre una sola columna– Ejemplo:select * from authorswhere state = "UT"

Indice compuesto

• Un índice compuesto es un índice creado sobre dos o más columnas– Ejemplo:create index idx_authors_3on authors(au_lname, au_fname)

• Apropiado cuando las consultas se hacen sobre múltiples columnas– Ejemplo:select * from authorswhere au_lname = "Ringer" and au_fname = "Anne"

Indice no-único

• Un Indice No-único es un índice que permite valores duplicados– Ejemplo:create index idx_authors_2on authors(state)

• Apropriado cuando las consultas se hacen sobre valores duplicados– Ejemplo:select * from authorswhere state = "UT"

Indice único

• Un índice único es un índice que no permite valores duplicados– Ejemplo:

create unique index idx_u_authors_1on authors(au_id)

• Apropiado cuando cada valor en la columna indizada debe ser único– Ejemplo:

select * from authorswhere au_id = "213-46-8915"

• Puede ser creado solamente sobre columnas que no tengan valores duplicados

Indice no-cluster

• Un índice no-cluster es un índice que utiliza un concepto de ordenamiento diferente a como se realizó el almacenamiento de la tabla– Ejemplo:create nonclustered index idx_authors_4on authors(state)

• Una tabla puede tener muchos índices no-cluster

• Apropiado para:– Tablas que ya tienen un índice cluster

Estructura de un índice no-cluster

PAGE 1132PAGE 11321421, 11129, 31409, 1

BennetChanDullEdwards 1018, 5

PAGE 1007PAGE 1007BennetGreane Hunter

1421, 11242, 41242, 1

113211331127

keypgptr

PAGE 1001PAGE 1001BennetKarsen Smith

1421, 11876, 11242, 1

100713051062

rowptr

PAGE 1305PAGE 1305Karsen

1876, 1

1311

keypgptr

rowptr

PAGE 1133PAGE 11331242, 41421, 21409, 2

GreaneGreenGreene

PAGE 1127PAGE 11271242, 11241, 4

HunterJenkins

PAGE 1241PAGE 1241O’LearyRingerWhiteJenkins

10111213

PAGE 1242PAGE 1242HunterSmithRingerGreane

14151617

PAGE 1421PAGE 1421BennetGreenRinger

181920

PAGE 1409PAGE 1409DullGreeneWhite

212223

(more pages)(more pages)

(more pages)

authors table(data pages)

Index pages

root level intermediate level leaf levelkey row ptr

create index idx_authors_2

on authors(au_lname)

Indice cluster

• Un índice cluster es un índice que, cuando se crea, indica cómo están físicamente almacenados los datos en la tabla– Ejemplo:

create clustered index idx_c_authors_1 on authors(au_id)

• Una tabla sólo puede contener un índice cluster

• Típicamente mejora el rendimientos de las consultas que se hacen a una tabla– Consultas con valor único (where state = ''CA'')– Consultas por rango de valores (where price > $10.00)

• Puede dismuir el rendimiento en operaciones de modificación de los datos de una tabla– Esta reducción se debe a que se deben almacenar físicamente los datos

ordenados

Etructura de un índice cluster

PAGE 1007PAGE 1007BennetGreane Hunter

124112421421

key pg ptr

PAGE 1001PAGE 1001BennetKarsen Smith

100713051062 PAGE 1305PAGE 1305

Karsen

1409

key pg ptr

PAGE 1241PAGE 1241BennetChanDullEdwards

10111213

PAGE 1242PAGE 1242GreaneGreenGreene

14151617

PAGE 1421PAGE 1421HunterJenkins

181920

PAGE 1409PAGE 1409KarsenO'LearyRinger

212223

(more pages)

(more pages)

authors table(leaf/data pages)

Index pages

root level intermediate level

create clustered index idx_authors_2

on authors(au_lname)

Escritura de consultas que usen índices

• Los índice se usan solamente cuando una consulta hace referencia a columna(s) indizada(s) en la cláusula where

• Si una tabla:– Tiene dos columnas, y– Cualquiera puede identificar una fila desada, y– Una columna está indizada mientras la otra no, entonces– Se debe utilizar la columna indizada en la cláusula where de la consulta

Ejemplo usando índices

• crear una tabla:create table novels (

book_id int,title varchar(40),author varchar(40)

)

• Insertar tres filas:insert into novels

values (1, "Congo", "M Crichton")insert into novels

values (2, "The Client", "J Grisham")insert into novels

values (3, "Jurassic Park", "M Crichton")

• Ejecutar las sentenciascreate clustered index idx_c_novels_1 on

novels(book_id)create unique index idx_u_novels_2 on

novels(author)

• ¿Qué sentencia falla? ¿Por qué?________________________________________

• Borrar los objetos creados:drop index novels.idx_c_novels_1drop table novels

• crear una tabla:create table novels (

book_id int,title varchar(40),author varchar(40)

)

• Insertar tres filas:insert into novels

values (1, "Congo", "M Crichton")insert into novels

values (2, "The Client", "J Grisham")

insert into novelsvalues (3, "Jurassic Park", "M

Crichton")