Bases de Dados - Dei-Isep

Nuno Melo e Castro
Limitações da linguagem SQL
O SQL não permite resolver problemas em que os dados a
processar têm uma estrutura recursiva
Por exemplo, para representar uma estrutura hierárquica como a
do modelo em baixo, poderíamos criar a seguinte tabela:
codpai
codfilho
A
B
A
C
A
D
C
E
C
F
D
G
G
H
Se quisermos obter todos os produtos que são necessários
para o produto A, como podemos fazer?
Seleccionar todos os codfilho onde codpai = A
O resultado é (B, C, D)
Para cada codfilho obtido, obter os seus descendentes
Os filhos de B são (<null>)
Os filhos de C são (E, F)
Os filhos de D são (G)
E assim sucessivamente…
O SQL standard não permite resolver este tipo de
questões sem usar mecanismos de recursividade ou
iteração de uma linguagem de programação
Seria necessário efectuar 8 consultas (neste caso em
concreto)
1.
2.
3.
4.
5.
6.
7.
8.
select codfilho from produto where codpai = ‘A’
select codfilho from produto where codpai = ‘B’
select codfilho from produto where codpai = ‘C’
select codfilho from produto where codpai = ‘E’
select codfilho from produto where codpai = ‘F’
select codfilho from produto where codpai = ‘D’
select codfilho from produto where codpai = ‘G’
select codfilho from produto where codpai = ‘H’
!
Quase todos os SGBD permitem o desenvolvimento de programas
complexos porque dispõem de linguagens de programação bastante
elaboradas e que permitem declarar variáveis ou estruturas, têm
mecanismos de recursividade, de iteração, de controlo de fluxo, etc…
Oracle
- PL/Sql
SqlServer - T/Sql
…
Essas linguagens permitem embeber comandos SQL dentro da
linguagem.
As interfaces para o SQL baseiam-se em chamadas a funções
Mas essas linguagens não são SQL, mas sim extensões do SQL
próprias de cada SGBD!
No entanto, alguns fabricantes de SGBD (Oracle) desenvolveram
facilidades extra sobre o “seu” SQL, nomeadamente disponibilizando
formas de resolver questões hierárquicas
Podemos ver um exemplo no slide seguinte
"
Estruturas hierárquicas
A condição recursiva pode fazer uso da
palavra chave prior
connect by prior codfilho = codpai
Este construtor estabelece a recursividade
Primeiro definimos qual é o ponto de partida
Todos os registos que fazem parte do próximo
nível hierárquico são encontrados se codpai =
codfilho, sendo codfilho um valor do nível
hierárquico imediatamente abaixo
select *
from produto
start with codpai = ‘A'
connect by prior codfilho = codpai;
codpai
codfilho
A
B
A
C
C
E
C
F
A
D
D
G
G
H
Imaginemos que o produto D também tem como filho o
produto C…
Basta criar uma nova linha na tabela produto:
(codpai, codfilho ) = (D, C)
Note-se que não temos que inserir as linhas (C, E) e (C, F) pois
estas já existem na tabela…
É importante notar que
pelo simples facto de
inserir mais uma linha na
tabela, a nossa estrutura
hierárquica vai ter muitas
algumas relações…
select *
from produto;
codpai
codfilho
A
B
A
C
A
D
C
E
codpai
codfilho
C
F
A
B
D
G
A
C
G
H
C
E
C
F
A
D
D
G
G
H
D
C
C
E
C
F
select *
from produto
start with codpai = ‘A'
connect by prior codfilho = codpai;
#
%
Como é que uma instrução SELECT com uma cláusula start with ... connect by pode ser
interpretado?
O Oracle procede de acordo com o seguinte pseudo-código:
for rec in (select * from some_table) loop
if FULLFILLS_START_WITH_CONDITION(rec) then
RECURSE(rec, rec.child);
end if;
end loop;
procedure RECURSE (rec in MATCHES_SELECT_STMT, new_parent IN field_type) is
Begin
APPEND_RESULT_LIST(rec);
for rec_recurse in (select * from some_table) loop
if FULLFILLS_CONNECT_BY_CONDITION(rec_recurse.child, new_parent) then
RECURSE(rec_recurse,rec_recurse.child);
end if;
end loop;
end procedure RECURSE;
$
%& '
O Sql é uma linguagem baseada em conjuntos (set-oriented)
Numa única operação podem ser manipulados vários registos
A maior parte das outras linguagens de programação são orientadas
ao registo (record-oriented)
Quando estas linguagens necessitam de processar vários registos recorrem
a operações recursivas ou estruturas cíclicas executadas tantas vezes
quantos os registos a processar
O resultado da execução de um comando SQL pode ser um conjunto
Quando usamos SQL em linguagens de programação record-oriented temos
de ter disponíveis formas de embeber os comandos SQL em estruturas que
possam ser interpretadas por essas linguagens
As interfaces para o SQL baseiam-se em chamadas a funções
Especificamos uma query a ser executada através da chamada a uma
função e executamos a query através da chamada a outra função, etc.
O resultado é um conjunto. Como podemos tratá-lo?
Usamos cursores.
Os cursores proporcionam uma forma de interface entre linguagens
%& '
O SQL é uma linguagem declarativa
Com o SQL especificamos o que queremos obter, ignorando por completo a
forma de como o obter. Isso é responsabilidade do SGBD.
As linguagens de programação tradicionais não são declarativas, são
procedimentais
Nessas linguagens o programador define como se vai obter o resultado, não
se limita a especificar o que pretende
Um exemplo:
Em SQL escrevemos um comando para obter todos os alunos com idade
superior a 20 anos…
Não dizemos como é que o SGBD deve proceder para dar o resultado…
Numa linguagem procedimental temos de obter todos os registos dos
alunos…
E percorrê-los a todos, guardando no result-set aqueles registos que se
enquadram no critério desejado…
Além de definir o que queremos, temos também de especificar a forma de o
obter.
%& '
Como vimos anteriormente, alguns SGBS têm extensões do
SQL próprias (PL/Sql, T/Sql, …) que permitem desenvolver subprogramas complexos, tomando partido de alguns mecanismos
procedimentais (ciclos, controlo de fluxo, etc.)
O Embedded SQL permite não apenas embeber instruções
simples do SQL como por exemplo, SELECT, CREATE TABLE,
mas permite também invocar os sub-programas que se
desenvolvem directamente na linguagem do SGBD
%& '
O objectivo do Embedded SQL é o de permitir que o programa inclua instruções
SQL, para que o programa possa usar, manipular e processar dados contidos
numa determinada base de dados.
Algumas regras genéricas para a utilização de Embedded SQL em linguagens
de programação procedimentais:
Tem de existir algo que represente a base de dados a que nos queremos ligar
Pode ser um componente ou podemos ter a necessidade de implementar todo o processo de
ligação à base de dados
Se for um componente, quem o desenvolveu já se encarregou de fazer todas as validações
necessárias, caso contrário, temos de ser nós a fazê-las (validação do sucesso na ligação,
tratamento de erros, etc…)
Existe a possibilidade de enviar comandos para o SGBD
Qualquer instrução é iniciada por EXEC SQL e termina com ;
Existe a possibilidade de declarar variáveis que são usadas não só no código do
programa mas também nos comandos SQL
Estas chamam-se bind-variables e são identificadores antecedidos de :
Quando o resultado da execução da instrução SQL é um conjunto, existem estruturas
que permitem o seu processamento. Essas estruturas chamam-se cursores.
O processamento é efectuado registo a registo, e não sobre o conjunto
Existem instruções que permitem ir obtendo cada um dos registos sequencialmente
Existem instruções que permitem saber se ainda há registos a processar ou se já foi
processada a totalidade do conjunto
O resultado da execução consulta pode não ser um conjunto, e nesse caso, há
também a possibilidade de obter directamente o resultado (para bind-variables)
%( ) )
Os cursores representam conjuntos de registos
Na prática, são variáveis que apontam para áreas de memória que
contêm o conjunto de registos
A declaração de um cursor não carrega em memória o conjunto,
apenas prepara o cursor para conter o resultado da execução de um
comando SQL específico, o SELECT
A declaração de uma variável escalar também não aloca memória, só a
atribuição de valores o faz…
Um cursor pode ser manipulado através de 5 instruções:
DECLARE
OPEN
FETCH
… CURRENT OF
CLOSE
Os cursores podem ser apenas de leitura, mas também podem ser
alteráveis.
Obviamente que o ser alterável significa que é a informação contida no
cursor que é alterável, não o próprio cursor, nem sequer o SELECT a ele
associado.
!
%( ) )
DECLARE
Associa um SELECT à variável representativa do cursor
OPEN
Abre o cursor, ou seja, coloca em memória o resultado da execução do
SELECT
FETCH
Permite obter uma linha do cursor, ou seja, um registo
Coloca um apontador na próxima linha do cursor
O primeiro FETCH após o OPEN coloca o apontador na primeira linha do
cursor
Pode ter variantes, permitindo o avançar do apontador não só para a linha
seguinte, mas também para posições anteriores, avançar ou recuar n linhas
ou ir para uma linha específica
… CURRENT OF
Só é usado quando o cursor é alterável.
Usa-se em conjunto com uma instrução SQL-DML (UPDATE ou DELETE), e
indica qual a linha que vai ser sujeita à execução do comando.
CLOSE
Fecha o cursor, desalocando memória
"
%( ) )
Sintaxe:
EXEC SQL {cursor_statement}
cursor_statement ::=
DECLARE <cursor_variable> [SCROLL] CURSOR FOR
<select_statement>;
OPEN <cursor_variable>;
FETCH <cursor_variable> [NEXT | PRIOR | FIRST |
LAST | ABSOLUTE <n> | RELATIVE <n>] INTO
<:bind_variable_list>;
UPDATE <table_name> …
WHERE CURRENT OF <cursor_variable>
CLOSE <cursor_variable>
% )*
+
O processamento de questões pode implicar a manipulação de
vários milhões de registos
A manipulação de grandes volumes de informação normalmente
envolve vários acessos a disco
Existem diversas formas de especificar a mesma questão
(programador), e também existem diversas formas de resolver a
mesma questão (SGBD)
Cada forma de resolução de uma questão envolve um custo que
depende de diversos factores, e tem um tempo de resposta
associado
Cada forma de resolução representa um plano de execução
Ao processo de análise e selecção do plano de execução com
menor custo chama-se de Processamento e Optimização de
Questões
Vimos anteriormente que o SQL é uma linguagem declarativa, o
que significa que é da responsabilidade do SGBD a
determinação de qual a melhor forma para devolver o resultado
% )*
+
Fases do Processamento e Optimização de
Questões
Análise da questão
Verificação da sintaxe do comando
Verificar se a entidade que colocou a questão tem permissão
para o fazer, e caso o tenha, se tem permissão para aceder
aos objectos referenciados na questão
Optimização da questão
Obtenção de todos os planos de execução
Selecção do plano que implica um menor custo
Execução da questão
Os objectos referenciados na questão (tabelas, vistas), e
todos aqueles que o SGBD decidiu envolver no plano de
execução (índices), são nesta fase efectivamente acedidos
#
% )*
+
Optimização da questão
Em primeiro lugar, o SGBD transforma a questão que
foi descrita num formato puramente declarativo, num
formato procedimental
Transforma a questão numa sequência concreta de
operações sobre os dados
As várias possibilidades de resolução são analisadas
recorrendo ao cálculo e álgebra relacional
Se esquecermos a vertente optimização, o
processamento de uma questão pode traduzir-se no
cálculo do produto cartesiano de todos conjuntos
envolvidos na questão, seguindo-se a aplicação de
filtros sobre o conjunto resultante do produto cartesiano
$
%
Consideremos o modelo de dados:
Aluno (id_aluno, nome)
Disciplina (id_disciplina, nome)
Nota (id_aluno, id_disciplina, nota)
Consideremos a questão:
Quais são os nomes dos alunos de Bases de Dados que têm nota
superior a 10?
Pode ser escrita em SQL
SELECT A.nome
FROM
Aluno A, Disciplina D, Nota N
WHERE A.id_aluno = N.id_aluno and D.id_disciplina = N.id_disciplina and
D.nome = ‘Bases de Dados’ and N.nota > 10
E em álgebra relacional, numa possível versão nada eficiente…
π A.nome (σ A.id _ aluno = N .id _ aluno ∧ D.id _ disciplina = N .id _ disciplina ∧ N .nota >10∧ D.nome= 'BasesdeDados ' ( A * D * N ))
Se por exemplo, a tabela Aluno tiver 200 registos, a tabela
Disciplina 50, e a tabela Nota 10000, o produto cartesiano será um
conjunto com 100 milhões de registos!
E sobre esse conjunto, linha a linha, será verificada a condição…
%
O volume de informação envolvido é de tal grandeza que a
cada instante apenas parte estará em memória central,
sendo necessário recorrer a memória secundária para
armazenar a outra parte
Em suma, vai ser necessário aceder ao disco não só para
obter as tabelas Aluno, Disciplina e Nota, mas também para
armazenar a relação intermédia
Como facilmente se compreende, podia ser muito pior… as
tabelas envolvidas são tão pequenas e a questão deriva
numa relação intermédia com tantos registos. Se as tabelas
fossem muito maiores seria incomportável
O SGBD antes de resolver uma questão tenta optimizá-la
%
π A.nome (σ A.id _ aluno = N .id _ aluno ∧ D.id _ disciplina = N .id _ disciplina ∧ N .nota >10∧ D.nome= ' BasesdeDados ' ( A * D * N ))
É equivalente a:
π nome ( A
(π id _ aluno ((σ nome = ' BasesdeDados ' ( D))
(σ nota >10 ( N )))))
Note-se que esta versão é muito mais eficiente do que a primeira
São obtidas relações intermédias muito menores
Se Nota tem 10000 registos, σ nota>10 ( N ) terá garantidamente menos…
σ nome= 'BasesdeDados ' ( D) , terá apenas 1 registo…
Facilmente se compreende que o processamento da questão envolverá
muito menos informação!
É importante também notar que apesar de a junção de relações ser
associativa, em termos de custo de processamento podem ser muito
diferentes
%
O SGBD possui conhecimento de todas as estruturas e dados
envolvidos
Tem estatísticas sobre os objectos (tabelas, índices, etc.)
Em suma, tem toda a informação necessária para permitir decidir qual
o melhor plano de execução… se os determinar a todos e os
comparar…
No entanto, como o número de planos de execução pode ser grande,
o tempo dispendido no cálculo de todos poderá tornar-se um
problema porque poderá demorar mais tempo do que a simples
execução de um plano de execução aleatório…
O SGBD não calcula todos os planos possíveis
Baseia-se em critérios de optimização
Baseados em heurísticas
Baseados em custos
% () ,)
Baseados em heurísticas
Cada SGBD tem as suas próprias heurísticas que traduzem opções
interessantes de optimização
O SGBD procura no dicionário de dados quais os índices que podem ser
opção interessante para aligeirar o acesso aos dados nas tabelas
Cada tipo de operação elementar tem um custo associado
Cada plano de execução é pesado de acordo com o custo das operações
É escolhido o plano com menor custo
!
% () ,)
Baseados em custos
O SGBD faz uso das estatísticas das tabelas, colunas e dos índices
Tabelas
As estatísticas incluem o número de registos (cardinalidade) e o número de
blocos ocupados em disco
Colunas
Número de valores distintos
Valores mais alto e mais baixo
Índices
Número de níveis e de folhas da B-tree
Número de valores distintos da 1ª coluna de indexação
Número de valores distintos da chave de indexação
Normalmente, estas estatísticas não são geradas automaticamente
É o administrador da base de dados que periodicamente inicia o processo
de geração de estatísticas
Se o processo não é automático, o esquecimento de actualização das
estatísticas pode derivar na selecção de planos de execução ineficientes
"
% () ,)
Baseados em custos (continuação)
Os custos são diferentes quando o plano de execução implica a consulta
directa sobre uma tabela ou sobre um índice, nomeadamente, é verificado
se a informação necessária está toda no índice ou se está na tabela
As junções podem ser de 3 tipos, cada uma com custos diferentes
Nested Loop Join, Merge Scan Join, Hash Join
A sequência de junção é determinada pelo SGBD e é independente da
forma como a questão é colocada
A junção das relações é feita na ordem das relações com menor
cardinalidade para as de maior cardinalidade