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
authors table
(data pages)
Index pages
key
key
row
ptr
pg
ptr
Bennet
PAGE 1001
1421, 1
1007
Karsen
Smith
1876, 1
1242, 1
row
ptr
pg
ptr
Bennet
Greane
Hunter
PAGE 1007
1421, 1
1132
1242, 4
1133
1242, 1
1127
Karsen
PAGE 1305
1876, 1
1311
1305
1062
PAGE 1132
Bennet
1421, 1
Chan
1129, 3
Dull
1409, 1
Edwards
1018, 5
PAGE 1133
Greane
1242, 4
Green
1421, 2
Greene
1409, 2
PAGE 1127
Hunter
1242, 1
(more pages)
create index idx_authors_2
on authors(au_lname)
Jenkins
1241, 4
(more pages)
10
11
12
13
PAGE 1241
O’Leary
Ringer
White
Jenkins
PAGE 1242
14
Hunter
15
Smith
Ringer
16
17
Greane
PAGE 1421
18
Bennet
19
Green
Ringer
20
PAGE 1409
21
Dull
22
Greene
White
23
(more pages)
Crear y borrar índices
•
Sintaxis simplificada para create :
create [unique] [ clustered | nonclustered ]
index index_name
on table_name (column1 [, column2] ... )
•
Ejemplo:
•
Sintaxis Simplicada para drop :
create clustered index idx_c_titles_1
on titles (title_id)
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_2
on authors(state)
•
Apropiado cuando las consultas se hacen frecuentemente sobre una sola columna
– Ejemplo:
select * from authors
where state = "UT"
Indice compuesto
•
Un índice compuesto es un índice creado sobre dos o más columnas
– Ejemplo:
create index idx_authors_3
on authors(au_lname, au_fname)
•
Apropiado cuando las consultas se hacen sobre múltiples columnas
– Ejemplo:
select * from authors
where 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_2
on authors(state)
•
Apropriado cuando las consultas se hacen sobre valores duplicados
– Ejemplo:
select * from authors
where state = "UT"
Indice único
•
Un índice único es un índice que no permite valores duplicados
– Ejemplo:
create unique index idx_u_authors_1
on authors(au_id)
•
Apropiado cuando cada valor en la columna indizada debe ser único
– Ejemplo:
select * from authors
where 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_4
on 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
Index pages
root level
intermediate level
row
ptr
key
key
row
ptr
pg
ptr
Bennet
PAGE 1001
1421, 1
1007
Karsen
Smith
1876, 1
1242, 1
pg
ptr
Bennet
Greane
Hunter
PAGE 1007
1421, 1
1132
1242, 4
1133
1242, 1
1127
Karsen
PAGE 1305
1876, 1
1311
1305
1062
leaf level
key
row ptr
PAGE 1132
Bennet
1421, 1
Chan
1129, 3
Dull
1409, 1
Edwards
1018, 5
PAGE 1133
Greane
1242, 4
Green
Greene
1421, 2
1409, 2
PAGE 1127
Hunter
1242, 1
(more pages)
create index
idx_authors_2
on authors(au_lname)
Jenkins
1241, 4
(more pages)
authors table
(data pages)
PAGE 1241
10
O’Leary
11
Ringer
White
12
13
Jenkins
PAGE 1242
14
Hunter
15
Smith
Ringer
16
17
Greane
PAGE 1421
18
Bennet
19
Green
Ringer
20
PAGE 1409
21
Dull
22
Greene
White
23
(more pages)
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
Index pages
root level
intermediate level
key
pg ptr
PAGE 1007
key
pg ptr
PAGE 1001
Bennet
1007
Karsen
Smith
1305
1062
Bennet
Greane
Hunter
1241
1242
1421
PAGE 1305
Karsen
1409
(more pages)
create clustered index
idx_authors_2
on authors(au_lname)
authors table
(leaf/data pages)
PAGE 1241
10
Bennet
11
Chan
Dull
12
13
Edwards
PAGE 1242
14
Greane
15
Green
Greene
16
17
PAGE 1421
18
Hunter
19
Jenkins
20
PAGE 1409
21
Karsen
22
O'Leary
Ringer
23
(more pages)
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
values
insert into
values
insert into
values
novels
(1, "Congo", "M Crichton")
novels
(2, "The Client", "J Grisham")
novels
(3, "Jurassic Park", "M Crichton")
• 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 sentencias
create 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_1
drop table novels
Descargar

BD_Indices