Operações do optimizador



Baixar 219,69 Kb.
Página1/2
Encontro30.07.2017
Tamanho219,69 Kb.
  1   2

Análise do desenho da base de dados:
Desnormalizar uma base de dados !!!


Imaginar uma conta bancária (Cod, nome, etc) e o saldo da conta nos quatro trimestres do ano guardados numa outra tabela (uma chave e o saldo) com ligação à anterior.

Cli Trimestre

Cod# Tri#

Nome Saldo

Casos em que se pode considerar tal situação:



  • Quando se sabe quantos registos existem do lado M

  • Este número é conhecido e estático ao longo do tempo

  • Este n.º não é muito grande.


Vantagens:


  • Elimina-se o tempo gasto com a junção das tabelas

  • Elimina-se a propagação das chaves

  • Depende do n.º de registos que se processa e do tipo de operações que se faz com eles. (Os melhores ganhos de performance verificam-se quando se evita a junção de vários milhares de linhas das duas tabelas)


Desvantagens:


  • Poder-se-á aumentar a complexidade de manipulação e acesso aos dados.

  • Menos flexibilidade

  • Pode tornar mais lentas operações de alteração dos dados.

Terá que se analisar as duas situações e verificar qual a mais vantajosa.


Cli#

Nome

Tri-1

Tri-2

Tri-3

Tri-4

0012745

Paulo

1000

1500

17500

2500

0017346

João

12400

12700

14000

15500




  • Guardar os dados mais recentes nas duas tabelas:

Considerar o caso em que temos uma tabela A associada a um conjunto de registos ordenados de uma outra tabela B.

A - conta bancária

B - taxa que varia de tempos a tempos.




COD#

Nome

Taxa corrente




COD#

Data

Taxa

0012745

Smith

15,75




0012745

10-Jan-91

21,00

0017346

Jones

10,75




0012745

30-Jun-91

19,50













0012745

16-May-92

17,25













0012745

01-Jan-93

15,75













0017346

16-Dec-92

12,50













0017346

01-Fev-93

11,75













0017346

01-Jul-93

11,00













0017346

26-Sep-93

10,75

Visto que existe uma grande probabilidade das perguntas à base de dados precisarem da taxa actual, guardando a taxa mais recente na tabela A, consegue-se responder às perguntas sem aceder à tabela B. No caso em que as perguntas precisam das taxas anteriores terá que se fazer uma junção das tabelas.



É necessário manter rotinas para que a duplicação de informação não fique incoerente.


  • Guardar totais




EMP#

Nome

Total




Emp#

Trimestre

Valor

0012745

Smith

6.750




0012745

1

1000

0017346

Jones

54.600




0012745

2

1500













0012745

3

1750













0012745

4

2500













0017346

1

12400













0017346

2

12700













0017346

3

14000













0017346

4

15500

A tabela Vendas pode conter milhares de records e por isso manter o total na tabela Emp pode melhorar a performance.

Mais memória e problemas de inconsistência.


  • Usar chaves primárias atribuídas pelo sistema em vez de chaves naturais:




Nome

Total




Chave

Nome

Total

Smith

6.750




1

Smith

6.750

Jones

54.600




2

Jones

54.600


Vantagens:

  • Poupar espaço porque um n.º ocupa menos espaço que um nome e a chave pode ser propagada para outras tabelas com chave estrangeira.

  • Torna-se mais fácil modificar a chave natural.

Desvantagens:

As chaves naturais devem ser usadas quando melhoram a performance de acesso às tabelas subordinadas e quando a chave natural não é modificada muitas vezes.




  • Combinar várias tabelas pequenas numa única: (Tabelas com um código e uma descrição podem ser unidas numa única tabela)


Empestimo Estado

Código

Descrição




Código

Estado

10

Aplicação




VIC

Victoria

20

Aprovado




NSW

New South Wales

30

Avaliação




QLD

Queensland




Tipo_Cod

Código

Descrição

Emp

10

Aplicação

Emp

20

Aprovado

Emp

30

Avaliação

Est

VIC

Victoria

Est

NSW

New South Wales

Est

QLD

Queensland




  • Reduz o n.º de índices

  • Reduz o n.º de tabelas

  • Reduz o dicionário de dados (cache) (permissões para uma tabela e sinónimos para uma tabela)




  • Criar tabelas de extracção de dados:

Alguns relatórios obrigam a vários cálculos e junção de várias tabelas. Quando os dados estão estáveis, podem-se criar tabelas com essa informação obtendo-se ganhos significativos de performance.


A criação destas tabelas pode ser feita de noite.
Nem sempre a informação que tem que estar presente no relatório é a mais recente.
Com as tabelas auxiliares os cálculos e junções são realizadas uma única vez e não sempre que alguém se lembra de gerar um relatório com essa informação.

Índices:
Vantagens:




  • Acesso mais rápido aos dados

  • Modo de garantir a unicidade dos valores de uma coluna

  • Em geral são mais vantajosos quando definidos sobre tabelas grandes.

  • Quando se faz muitas vezes junção de duas tabelas (mesmo que pequenas), a definição de índices também pode verificar-se vantajosa.


Desvantagens:


  • A definição de índices torna mais demoradas as operações de inserção, alteração e remoção de dados (há que alterar também o índice).

  • Gasto de memória adicional.


Conclusão:


  • Os índices só deverão ser criados se melhorarem a performance da aplicação.

Tipos de índices





  • Índice do tipo bitmap:



Cliente#

Est_Civil

Região

Sexo

Nível

101

solteiro

este

masculino

1

102

casado

centro

feminino

4

103

casado

oeste

feminino

2

104

divorciado

oeste

masculino

4

105

solteiro

centro

feminino

2

106

casado

centro

feminino

3

Estado civil  três possibilidades

Região  três possibilidades

Sexo  duas possibilidades

Nível  quatro possibilidades
É apropriado criar um índice bitmap para estas colunas. Um índice deste género não deve ser criado sobre a coluna cliente porque tem uma cardinalidade elevada. Nesta coluna o mais apropriado seria uma árvore-B* para o índice.
Tabela : Índice bitmap para a coluna região. Três bits. Um para cada região.


REGIÃO='este'

REGIÃO='centro'

REGIÃO='oeste'

1

0

0

0

1

0

0

0

1

0

0

1

0

1

0

0

1

0

Cada linha do índice corresponde a uma linha da tabela.


Pergunta: Quantos clientes casados vivem nas regiões central ou oeste?
SELECT COUNT(*) FROM CLIENTE

WHERE Est_Civil = 'casado' AND REGIÃO IN ('centro','oeste');


Com o índice bitmap basta contar o n.º de uns no resultado.


Estrutura em árvore-B*




  • As folhas da árvore estão à mesma profundidade por isso o acesso a um registo em qualquer sítio da base de dados demora sempre o mesmo tempo.

  • índice mantém-se balanceado.

  • Todos os blocos da árvore-B* estão 3/4 cheios em média.



Estrutura interna de um índice em árvore-B*






  • A performance de acesso, tanto em perguntas que procuram em valor exacto com em perguntas que procuram valores dentro de um intervalo é excelente.

  • Inserções, alterações e remoções são eficientes e mantém-se a ordem para o acesso.

  • A performance das árvores-B* é boa tanto para tabelas pequenas como grandes e não se degrada quando as tabelas crescem

Organização das tabelas




  • Cluster

Um cluster é uma grupo de tabelas que partilham os mesmo blocos de dados porque têm colunas clumuns e são usadas muitas vezes juntas.







Vantagens:


  • As operações de I/O a disco são reduzidas e o tempo de acesso melhora para junção das tabelas

  • A chave de cluster é guardada uma única vez no cluster e no índice de cluster independentemente do n.º de linhas das diferentes tabelas.


Desvantagens:


  • Pode reduzir a performance das operações de inserção porque o espaço e o nº de blocos que têm que ser visitados para percorrer uma tabela é superior. Visto que são guardadas várias tabelas num bloco, são necessários mais blocos para guardar uma tabela num cluster.



  • U
    so de Hash vs. uso de índice




  • Um cluster baseado numa função de hash guarda as linhas relacionadas com a mesma chave de hash num mesmo bloco.

  • Para encontrar ou guardar uma linha num cluster baseado numa função de hash, o Oracle aplica a função de hash à chave de cluster da linha. O resultado corresponde a um bloco num cluster.

  • Para encontrar ou guardar uma linha numa tabela indexada são necessárias pelo menos duas operações de I/O. Uma para encontrar ou guardar a chave no índice e outra para ler ou guardar a linha da tabela.


Problemas de colisões:
Se a função de hash gera muitas colisões, pode-se encher o bloco de dados e por isso ter-se-á que alocar mais um bloco que se vai ligar ao anterior. No limite poder‑se-á ter que ler tantos blocos que nos aproximamos de uma leitura sequencial



  • Tabela indexada vs. tabela organizada por índice



Em vez de se manter dois espaços separados, um para o índice e outro para a tabela, o sistema de base de dados mantém apenas o índice que contém não só a chave como também os valores associados com a chave (todos os valores duma linha).


Uma linha numa tabela normal tem uma localização física estável.
Quando se cria um índice sobre uma coluna duma tabela normal, o índice criado guarda o rowid assim como os dados da coluna.
Uma tabela organizada por índice guarda os dados ordenados de acordo com a ordem das folhas da árvore-B* que constitui o índice da chave primária da tabela. Estas linhas podem ser mudadas de lugar para manter uma ordenação. Por exemplo, uma inserção pode causar uma partição numa das folhas e obrigar a que uma linha já existente mude por exemplo de bloco.
As folhas de um índice com estrutura de árvore-B* de uma tabela organizada por índice guarda a chave primária e os dados associados a essa chave. Alterações como adicionar novos dados, apagar ou modificar linhas existentes resulta numa alteração ao índice.
Vantagens


  • Uma vez que a procura encontra os valores das chaves, os dados restantes estão na mesma localização. Não é necessário aceder à tabela através do rowid. Estas tabelas assim organizadas eliminam a operação de I/O adicional à tabela necessária nas outras tabelas sem a organização por índice.




  • Reduz o espaço de memória necessário




  • As colunas da chave não são duplicadas na tabela e no índice e não é necessário guardar o rowid.


Desvantagens





  • Quando a informação das linhas da tabela é muito grande poderemos, ao adicionar novos dados, estar muitas vezes a obrigar a partições nas folhas e a que uma linha já existente mude de bloco.




  • Quando os dados das linhas são grandes podemos terminar guardando apenas uma linha ou parte de uma linha nas folhas da árvore destruíndo a capacidade de clusterização dos índices em árvore-B*.


  1   2


©livred.info 2017
enviar mensagem

    Página principal