Slides - Sandra de Amo

Indices: Estruturas
Auxiliares para Otimizar
Acesso aos Dados
AULA 7
Profa. Sandra de Amo
GBC053 – BCC
Organização dos Dados nos Arquivos
 Arquivos Heap (Sequencial)
 Arquivos Ordenados por uma Chave (de
ordenação)

Chave = conjunto de atributos
 Arquivos Agrupados por uma Chave (de
agrupamento)


Chave = conjunto de atributos
Função de agrupamento (= função hash)
 Métodos de Acesso: utilizam estruturas auxiliares
para acelerar a busca

Estruturas auxiliares = arquivos de indice
BCC-2013-1
O que é um índice ?
Select *
From Emp E
Where E.Nome = ‘João da Cruz’
• Arquivo Heap: custo de um scan
• Arquivo Ordenado por Nome: busca binária
• Arquivo Hash (por nome): scan no subarquivo Sk
onde k = Hash(João da Cruz)
Em qualquer das organizações de arquivos,
há a possibilidade de se ler muitas páginas
de dados até encontrar o registro procurado.
João da Cruz, ....
Arquivo de Dados
O que é um índice ?
Select *
From Emp E
Where E.Nome = ‘João da Cruz’
E se conhecessemos o rid = (Npag,Nsot) do
registro procurado ?
slot 4
Carlos, (2,2)
Pagina 5
João..., (5,4)
Arquivo de Indice
Um indice é um ARQUIVO AUXILIAR ASSOCIADO AO ARQUIVO ORIGINAL
Dados, indices, índices, índices, ...
Consulta: Me dê o livro
Database Systems –
Ramakrishnan/Gehrke
Arquivo de Indice
DADOS
BCC-2013-1
Arquivos de Indice
rid A B C D E F G
A D E F rid
a d
a d
a d
e f
e f
e f
(12,1)
(18,5)
(22,4)
(12,1)
a b1 c1 d
e
f g1
(18,5)
a b2 c2 d
e
f g2
(22,4)
a b3 c3 d
e
f g4
chave
Arquivo de Indice
Endereços obtidos no diretório de arquivos e
no diretório de slots das páginas
Arquivo de Dados
BCC-2013-1
6/29/2017
6
Dinâmica geral dos Arquivos de Indice
 Busca no arquivo de indice:

Páginas deste arquivo são organizadas de modo a permitir a
localização rápida das entradas com uma dada chave.
 Uma vez encontradas rapidamente estas entradas no arquivo
de índice, já temos os endereços dos registros de dados
associados (seu rid = (N,X)).
 O local exato no disco onde se encontra a página de número N
é encontrado através do diretório de arquivos do disco: fornece
o endereço da header page do arquivo.
 O local exato na página N onde se encontra o slot X do registro
procurado é obtido através do diretório de slots da página N
BCC-2013-1
6/29/2017
7
Como encontrar um registro num Arquivo A
conhecendo o rid do registro?
 Rid = (12, 3)
 Vai no diretório de arquivos no disco e procura o
registro correspondente ao arquivo A, para obter o
ponteiro para o primeiro bloco do arquivo.
 Calcula o endereço do 12º bloco e posiciona a cabeça
de leitura neste endereço.
 Transfere o 12º bloco para o buffer pool
 Processador de consultas consulta o diretório de slot
da página 12 e procura o endereço do registro contido
no slot 3, bem como o tamanho (L) do registro contido
neste slot.
 Vai para este endereço de memória e lê L bytes.
BCC-2013-1
Arquivos de Indice
 Otimiza a procura de registros em arquivos, pois o arquivo de indice
contém o rid dos registros.
 Através do rid encontra-se facilmente os registros de dados completos
nos arquivos.
 No arquivo de índice, cada registro é da forma (valores_chave,rid)
 Chave = conjunto de atributos
6/29/2017

Se chave contém chave candidata do arquivo, então chave determina
unicamente o rid

Caso contrário, uma mesma chave pode acessar diversos rids.

Exemplo : se chave = {Salário}
9
Como as informações são obtidas
rapidamente no arquivo de indice ?
Métodos de Acesso
Técnicas de organização das páginas dos
arquivos de índices
 B+Trees
 Tabelas
Hash
Catálogo do Sistema
 Metadados
 Descrição dos dados e índices
 Informações sobre visões
 Catálogo = conjunto de relações (arquivos)
contendo todas as informações sobre os
arquivos sendo utilizados pelo sistema.
BCC-2013-1
6/29/2017
11
Catálogo do Sistema
 Arquivos « Relações », « Atributos », « Restrições », ...





Nome da relação, nome do arquivo , estrutura do arquivo
Nomes e tipos dos atributos
Nome do índice
Restrições de Integridade (chave primária, estrangeira)
Guardam inclusive registros correspondentes às relações
« Relações », « Indices », « Atributos », « Visões », ...
 Arquivo « Indices »


Nome e estrutura de cada índice
Chave de cada índice
 Arquivo « Visões »
 Nome e código
6/29/2017
12
Resumo: Arquivo de Indice
 O que é ?

estrutura auxiliar projetada para agilizar
operações de busca, inserção e deleção
 Em que consiste ?



Uma coleção de registros
Uma chave de busca k
Cada entrada contém informação suficiente para
localizar registros de dados contendo a chave de
busca k.
Vantagens
 Tamanho: normalmente é bem menor do que o
arquivo de dados
 Organização optimizada: pode ser sequencial,
ordenado ou hashed
 Método de Acesso rápido: pode ser estruturado
usando uma b-tree ou hash (estático, dinâmico)
O que armazenar em um índice ?
Registro de Dados =
Chave
+
Registro do indice =
Rid
Chave do índice !!
Indice: como são os registros ?
 Alternativa 1
 Entrada = registro inteiro de dados
 Neste caso, a única vantagem do índice é a forma como é
organizado: ordenado, hash, com método de acesso ou não
 Alternativa 2
 Entrada = (k,rid), k = chave
 Chave = conjunto de atributos
 Alternativa 3
 Entrada = (k, lista de rids)
 Vantagem: ocupa menos espaço. Uma chave acessa
diversos registros no arquivo de dados
 Desvantagem: registro de tamanho variável
Como organizar as entradas do
índice ?
 Entrada = registro
 Entradas podem ser ordenadas
 Entradas podem ser organizadas por Hash
BCC-2013-1
Exemplo de Indice ordenado
2000
Paulo, 44, 2000
2000
Pedro, 35, 2000
2000
Carlos, 44, 2000
2500
José, 40, 2500
3000
João, 35, 3000
3500
Ilmério, 40, 3500
3500
Rodrigo, 40, 3500
4000
Maria, 30, 4000
4000
Sara, 35, 4000
5000
Sabrina, 31, 5000
Entradas : organizadas
de forma ordenada
BCC-2013-1
Registros de dados
Exemplo de Indice organizado por
Hash
Paulo, 44, 2000
2000
H(sal) = 01
H
H(sal) = 11
H(sal) = 00
2000
Pedro, 35, 2000
2000
Carlos, 44, 2000
2500
José, 40, 2500
5000
João, 35, 3000
3000
Ilmério, 40, 3500
3500
Rodrigo, 40, 3500
3500
Maria, 30, 4000
4000
Sara, 35, 4000
4000
Sabrina, 31, 5000
Registros de dados
Entradas
BCC-2013-1
Tipos de indices
Relação entre a maneira como são organizados
os dados no índice e no arquivo de dados




Agrupado – não agrupado
Densos – esparsos
Primários – secundários
Simples - compostos
BCC-2013-1
Indices Agrupados (ordenados)
 Agrupados : a ordem dos registros é
compatível com a ordem das entradas no
arquivo de índice.
 Se entrada é do tipo (chave, rid) então os
registros de dados são ordenados por chave.
 Somente um índice agrupado do tipo
(chave,rid)
BCC-2013-1
Indices Agrupados (hash)
 Agrupados : a função hash utilizada na distribuição dos
registros de dados é a mesma função utilizada para distribuir as
entradas no arquivo de índice.
 Se a entrada é do tipo (chave, rid) e arquivo de índice é
organizado por hash, utilizando a função hash h(chave), eentão
os registros de dados são organizados por hash através dos
atributos de chave.
Exemplo de Indice Agrupado
(ordenado)
= rid
2000
Paulo, 44, 2000
2000
Pedro, 35, 2000
2000
Carlos, 44, 2000
2500
José, 40, 2500
3000
João, 35, 3000
3500
Ilmério, 40, 3500
3500
Rodrigo, 40, 3500
4000
Maria, 30, 4000
4000
Sara, 35, 4000
5000
Sabrina, 31, 5000
Entradas (ordenadas por Sal)
Registros de dados
(ordenador por Sal)
Exemplo de Indice Agrupado
(ordenado)
= rid
1000
Maria, 30, 4000
1000
Sabrina, 31, 5000
1000
Pedro, 35, 1000
1500
3000
João, 35, 3000
Sara, 35, 4000
3500
Ilmério, 40, 3500
3500
Rodrigo, 40, 3500
4000
José, 40, 1500
4000
Paulo, 44, 1000
5000
Carlos, 44, 1000
Entradas (ordenadas por Sal)
Registros de dados
(ordenados por Idade)
Exemplo de Indice agrupado
= rid
1000
1000
Pedro, 35, 1000
Bucket 1 1000
1500
Carlos, 44, 1000
H(sal) = 01
H
H(sal) =
00
Paulo, 44, 1000
H(sal) = 11
5000
3000
Bucket 2 3500
3500
4000
Bucket 3 4000
Entradas (organizadas
pela função hash h(x) = (x/1000) mod 4,
x: valor do atributo Salário
Bucket 1
José, 40, 1500
Sabrina, 31, 5000
Rodrigo, 40, 3500
Ilmério, 40, 3500
Bucket 2
João, 35, 3000
Sara, 35, 4000
Bucket 3
Maria, 30, 4000
Registros de dados (organizados
pela função hash h(x) = (x/1000) mod 4
x: valor do atributo Salário
Exemplo de Indice não agrupado
= rid
1000
H(sal) = 01
H
H(sal) = 11
H(sal) = 00
Paulo, 44, 1000
1000
Pedro, 35, 1000
1000
Carlos, 44, 1000
1500
José, 40, 1500
5000
João, 35, 3000
3000
Ilmério, 40, 3500
3500
Rodrigo, 40, 3500
3500
Maria, 30, 4000
4000
Sara, 35, 4000
4000
Sabrina, 31, 5000
Entradas (organizadas
pela função hash h(x) = (x/1000) mod 4,
x: valor do atributo Salário
Registros de dados (ordenados
por Salário)
Vantagens e desvantagens

Desvantagem (no caso de ordenado) : grande overhead para mover registros a
fim de preservar a ordem depois de inserções e deleções.

Desvantagem (no caso de hash) : se o hash é estático, se há muitas inserções
no banco de dados, tanto a estrutura hash do indice quanto a dos dados deverá
ser refeita periodicamente. Se o hash é dinâmico, há overhead para manter a
estrutura hash tanto no arquivo de indice quanto no de dados.

Vantagem – no caso de agrupado ordenado
Seleções do tipo ‘= a’ ou ‘< a’ ... são altamente otimizadas caso os registros
sejam ordenados pela chave do indice, e este seja ordenado pela chave.
 Rids das entradas apontam para registros contíguos, menos páginas são
manipuladas.

Vantagem – no caso de agrupado hash
Seleções do tipo ‘= a‘ são altamente otimizadas
 Rids das entradas satisfazendo a condição da busca apontam para
registros que estão no mesmo bucket, logo menos páginas são
manipuladas.
Indices Densos
 Densos : se para cada valor v da chave de
busca existe uma entrada (v,rid).
 Não-denso = esparso
Exemplo de Indices denso e não denso
André, 44, 2000
30
Carlos, 44, 2000
31
Ilmério, 40, 3500
José
35
João, 35, 3000
35
Rodrigo
José, 40, 2500
35
Maria, 30, 4000
40
Pedro, 35, 2000
40
Rodrigo, 40, 3500
Sabrina, 31, 5000
40
44
Sara, 35, 4000
44
André
= rid
Indice Esparso e Agrupado
Registros de dados
(ordenados por Nome)
Indice Denso
(em Idade)
e não-agrupado
Exemplo de Indice denso onde a chave
não é chave da relação
Sabrina, 31, 5000
31
(1,1)
40
(2,1)
Sara, 35, 4000
Pagina 1
Ilmério, 40, 3500
44 (3,1)
João, 40, 3000
José, 40, 2500
Maria, 40, 4000
Pagina 2
Pedro, 40, 2000
Carlos, 44, 2000
Select * From EMP E
Where E.Sal = 40
André, 44, 2000
Pagina 3
Rodrigo, 45, 3500
Registros de dados
Pagina 2 é carregada,
(ordenados por Idade)
Página 1 é carregada
Página 3 não é carregada, pois
Através do indice sabe-se que o 1o registro de Pag. 3 tem Idade = 44
Exemplo de Indice denso onde a chave
não é chave da relação
Sabrina, 31, 5000
31
(1,1)
40
(2,1)
Sara, 35, 4000
Pagina 1
Ilmério, 40, 3500
44 (5,1)
João, 40, 3000
José, 40, 2500
Maria, 40, 4000
Pedro, 40, 2000
Select * From EMP E
Where E.Sal = 40
Carlos, 44, 2000
André, 44, 2000
Rodrigo, 45, 3500
Pagina 2
*
*
*
Pagina 3
Pagina 4
Pagina 5
Pagina 2, 3, 4 são carregadas,
Página 1 é carregada
Páginas 5 não é carregada, pois
Através do indice sabe-se que o 1o registro de Pag. 5 tem Idade = 44
Vantagens e desvantagens
 Esparso tem que ser agrupado
 Vantagens de esparso : arquivo de indice
ocupa menor espaço.
 Desvantagem de esparso : técnicas de
otimização de busca são apropriadas para
índices densos.
Indices Primários e Secundários
 Primários : Chave do índice inclue a chave
primária da relação.

Não há entradas duplicadas (com mesmo
valor da chave)
 Secundários : Não contém chave primária.


Pode conter chave candidata
Pode conter duplicatas ou não
Indices com chaves compostas
Indice em (Idade,Sal)
Indice em Idade
31,80
31
33,75
33
42,10
bob
42
10
42
42,20
cal
31
80
42
joe
42
20
sue
33
75
Indice em Sal
10,42
10
20,42
20
75,33
80,31
75
Indice em (Sal,Idade)
80
Consultas
 Consultas com igualdade

Chave composta (Idade, Sal)
Idade = 10, Sal = 80

Arquivos Hashed só são convenientes para consultas com
igualdade
 Consultas Range

Chave composta (Idade, Sal)



Idade = 10
Idade < 10, Sal > 80
Arquivos Ordenados são convenientes para consultas com
igualdade
Indices em SQL
 CREATE INDEX IndAgeGrau ON Estudantes
With Structure = BTREE,
Key = (Idade, Média)