Prova Banco de Dados II (copy)

=== SEGURANÇA ===

• Requisitos BD corporativo:

  • Gerenciar e fornecer acesso

  • Controle de disponibilidade para usuários

  • Controlar leitura e escrita

  • Restringir acesso a colunas específicas

• Todo banco tem um administrador (DBA)

• Schema é uma coleção de objetos (tabelas, views, sequences)

• Privilégios:

  • Determinam o que um usuário pode fazer no banco

  • Fornecem permissões para executar comandos SQL

  • Privilégios de sistema indicam permissões que o usuários terá no banco (criar sessão, tabelas, views)

  • Privilégios de objeto para manipular o conteúdo dos objetos (editar tabelas, views, procedure)

    
    

=== VIEWS ===

• Tabela virtual definida por um select

• View não existe no banco como um conjunto de dados armazeados

• Vantagens:

  • Restringe acesso ao banco

  • Consultas complexas ficam simples

  • Recupera dados de diferentes tabelas, schemas e databases

  • Permite acesso a grupos de usuários baseado em seus critérios particulares

• Quando uma visão é utilizada, o Servidor Oracle executa as seguintes operações:

  • Recupera a definição da visão no dicionário de dados (USER_VIEWS)

  • Confere os privilégios de acesso para a tabela básica da visão

  • Os dados são recuperados a partir das tabelas básicas

• É possível usar DML (insert, update, delete)

• Se a view for criada usando “WITH READ ONLY“ não dá pra usar DML

• Visão simples:

  • Uma única tabela

  • Não utiliza funções ou agrupamento de dados

  • Pode executar operações DML

• Visão complexa:

  • Várias tabelas

  • Utiliza funções ou grupos de dados

  • Normalmente não permite operações DML

• Erros comuns:

  • DUPLICATE COLUMN NAME: duas colunas com mesmo nome na query

  • MUST NAME THIS EXPRESSION WITH A COLUMN ALIAS: a query tem um count, sum, etc, e a expressão não recebeu um apelido

  
  

=== STORED PROCEDURES ===

• Server pra resolver problemas complexos, direto no banco

• Cada procedure é um módulo de código que pode ser executado a parti de:

  • Procedures

  • Trigger

  • Aplicação cliente

  • Como parte de um SELECT

• Vantagens:

  • Melhora o desempenho das aplicações

  • Reduz o volume de dados trafegados na rede

  • Melhora a manutenção do software

  • Melhora a segurança no acesso aos dados

• Quando usar:

  • Se a operação pode ser feita no servidor, sem pedir dados do usuários

  • Se a operação processar muitas linhas

  • Se a operação é usando com frequência

  • Se a operação é usada por vários módulos ou processos diferentes

• Cursores:

  • Usados para nomear áreas de memória e acessar os dados

• Triggers:

  • São blocos de código

  • Armazenados dentro do banco

  • Disparados sempre que uma instrução SQL (evento DML) for aplicada para uma tabela específica

  • Aplicações:

    • Auditoria

    • Replicação de dados

    • Controle de dados

    • Segurança e integridade

  • Precauções:

    • Evite criar triggers que dupliquem regras já definidas em CONSTRAINTS do banco

    • Cuidado ao criar triggers que disparem por uma instrução em uma tabela, para que ela não efetue a mesma operação na própria tabela porque isso iria disparar a trigger N vezes, travando a execução.

=== OTIMIZAÇÃO ===

• Índices:

  • Utilizado para acelerar a recuperação de registros

  • Após criação, o BD atualiza e controla de forma automática

  • Reduzir I/O de disco

  • Um tabela pode ter mais de um índice

  • Pode ser simples (1 coluna) ou composto (N colunas)

  • A ideia apontar para uma pequena parte de uma tabela grande, de 2-4%

  • Criação:

    • Primário: índice único criado automaticamente quando define-se uma PK ou Unique Key

    • Secundário: criado manualmente pelo usuário

    • Criar quando:

      • Coluna usada com frequência em WHERE e JOIN

      • Coluna for Foreign Key

      • Coluna tem variedade de valores

      • Coluna com muitos valores nulos

    • Não criar quando:

      • Tabela pequena

      • Colunas não muito utilizadas

      • Queries vão retornar mais do que 4% dos registros

      • Tabela alterada com frequência

  • Para dropar tem que ser o owner ou ter o privilégio “DROP ANY INDEX”

  • Organizados em uma árvore B+

• Otimização de desempenho:

  • Conjunto de atividades para reduzir o tempo de respostas de um banco de dados

  • Fatores de hardware que reduzem o desempenho:

    • CPU

    • RAM

    • Disco rígido

    • Rede

  • Bloco de dados é a unidade mínima de armazenamento no SGBD

  • Processamento de consultas:

    • Fase de análise:

      • Parser faz a análise sintática e semântica do comando

      • Otimizador criar o plano de acesso

    • Fase de execução:

      • Dados em disco vão para cache

      • Se já estiver em cache, pega de lá. Se não, traz do disco para a cache

      • Se já está em cache, o retorno da requisição é mais rápido

    • Fase de recuperação:

      • Retorno dos dados ao usuário

  • Parser:

    • No Waze, ele verificaria se os pontos existem e se é válido traçar rota entre eles

    • Usa o dicionário do banco para fazer as análises

    • Análise sintática: verifica se o comando está escrito na forma correta, se a “sintaxe” do comando está de acordo com a linguagem SQL

    • Análise semântica : verifica se os objetos referenciados no comando SQL (tabelas, views, colunas, etc) existem

  • Otimizador:

    • Criar o plano de acesso

    • Plano de acesso é a forma como o SGBD irá realizar a requisição

    • O plano visa processar a requisição com a maior eficiência, menor tempo e custo de processamento

    • Similar ao Waze. Traça várias rotas entre dois pontos e escolher a melhor (menor custo)

    • Otimizador baseado em custo:

      • Todos os bancos usam

      • Calcula o custo usando estatísticas (quantidade de colunas, valores distinto, etc)

      • Estatísticas:

        • Armazenadas no dicionário do banco

        • Coletadas de forma periódica, geralmente em um intervalo diário

        • Estatísticas atualizadas = boas decisões do otimizador

        • Estatísticas das tabelas:

          • Quantidade de registros

          • Número de blocos de dados utilizados

          • Tamanho médio dos registros

        • Estatísticas das colunas:

          • Número de valores distintos

          • Números de valores nulos

          • Valor máximo

          • Valor mínimo

          • Distribuição dos dados

        • Estatísticas dos índices:

          • Número de níveis

          • Número de blocos folha

        • Estatísticas do ambiente:

          • CPU

          • Memória virtual

          • I/O de disco

          • Rede

    • Otimizador baseado em regras:

      • Oracle possui e ainda funciona. Porém, não é recomendado e já foi descontinuado

    • Técnicas de otimização:

      • Correta definição de PK

      • Utilizar índices

      • Escrever comandos eficientes

      • Usar stored procedures:

        • Armazenados no próprio SGBD

        • Evitam o trafego de rede

      • Hints de otimização:

        • Sugestão do usuário ao otimizador para a montagem do plano de execução

        • Usadas raramente

        • Deve ser utilizadas com cautela

        • Dicas ruins prejudicam desempenho