Boas práticas para escrever instruções SQL

 

Olá pessoal,

    A pedido de um colega de trabalho, estou disponibilizando neste post um guia de boas práticas com 10 dicas para escrever intruções SQL em Banco de Dados Oracle. É importante ressaltar que apesar do foco ser Oracle, a maior parte das dicas se aplicam em outros SGBDs também. Outro ponto importante, é que de um modo geral, essas dicas se aplicam somente aos SQLs que são executados inúmeras vezes, em BDs de produção (e não para queries ad-hoc, de testes ou que serão executadas apenas 1 vez). 

Aprendendo a ler um plano de execução com foco na ordem das ligações das tabelas

Olá pessoal,

     Neste post quero apenas compartilhar com vocês um vídeo do Chris Saxon demonstrando como ler um plano de execução com foco na ordem das ligações (join order) de tabelas.

Usando a cláusula HAVING em instruções SQL

Olá pessoal,

    Compartilhei no meu canal do youtube mais um vídeo de uma questão resolvida e comentada, preparatória para o exame 1Z0-071 da Oracle, que explica detalhes sobre como usar a cláusula HAVING em instruções SQL.

Aprendendo a usar as cláusulas OFFSET e FETCH no Oracle 12c

Olá pessoal,

    Compartilhei no meu canal do youtube mais um vídeo de uma questão resolvida e comentada, preparatória para o exame 1Z0-071 da Oracle, que explica como usar as cláusulas OFFSET e FETCH, que nasceram no Oracle 12c.

Executando SQL dinâmico no Oracle Database

 

ATUALIZADO EM 30/5/25

Olá pessoal,

     No artigo de hoje vou explicar o que é SQL dinâmico e quando você realmente deve utilizá-lo, ao invés de escrever SQL estático. 

Minicursos gratuitos

Quer conhecer ou participar de
cursos Oracle online gratuitos?

--------------------------------------------------------------------------------------------------
O UDEMY, plataforma onde os cursos estão disponibilizados gratuitamente,
não está mais emitindo certificados para cursos gratuitos no Brasil. 
Se precisar de certificado ao final do treinamento, entre em contato comigo
 informando nome completo + nome do usuário no portal do UDEMY. 
Farei a emissão, caso você queira, pelo custo de R$ 49,00
--------------------------------------------------------------------------------------------------

Para participar do minicurso gratuito Aprendendo SQL,

clique na imagem abaixo e cadastre-se no formulário de acesso:

Minicurso Aprendendo SQL

Para participar do minicurso gratuito PL/SQL Essentials, 

clique na imagem abaixo e cadastre-se no formulário de acesso:

Minicurso PL/SQL Essentials

Visão geral sobre instruções SQL, Oracle e SQL Server

Olá pessoal,

     É com muita alegria que eu compartilho através deste post, o vídeo de uma palestra online que eu apresentei no dia 25/02/2015, juntamente com o DBA/Instrutor Vitor Fava, onde a gente falou sobre instruções SQL, Oracle e SQL Server.

Gerando arquivos DSV com PL/SQL

ATUALIZADO EM 17/5/19

Olá pessoal,

      No artigo de hoje vou mostrar como gerar um arquivo texto em formato DSV (Delimited Separated Values) de forma simples e rápida, utilizando apenas uma função que criaremos no Banco de Dados. O arquivo será gerado a partir do resultado de uma instrução SQL e terá o conteúdo das linhas de seu resultado com cada valor de coluna separado por algum caractere definido por você, como por exemplo, o caractere ponto e vírgula (;). Opcionalmente, é possível incluir no arquivo DSV um cabeçalho gerado de forma dinâmica (ver parâmetro "p_inclui_cabecalho" da funçãoo que mostrarei adiante), que contém a descrição das colunas inclusas no SQL. 

SQL padrão ANSI X padrão Oracle: Qual é mais rápido?

ATUALIZADO EM 12/03/2015

Olá pessoal,

     No artigo de hoje explicarei em detalhes a resposta de uma pergunta que é realizada por alunos em quase toda turma presencial de SQL ou SQL Tuning que eu leciono: Existe diferença de desempenho entre instruções SQL escritas no padrão ANSI ou no padrão Oracle? Antes de dar a resposta, vamos entender primeiro o que é SQL e o que são os padrões ANSI SQL e Oracle. 

     Resumindo, a Structured Query Language, mais conhecida pela sigla SQL, é uma linguagem que foi desenvolvida no início dos anos 70, pela IBM, para manipular bancos de dados relacionais. A partir de então, diversos fabricantes de Sistemas Gerenciadores de Bancos de Dados Relacionais (SGBDRs), como por exemplo a Oracle, começaram a desenvolver versões próprias da linguagem SQL (chamadas de dialetos ou extensões) e isso levou à necessidade da criação de uma linguagem SQL padronizada.

     Com a sua popularização e sucesso, organizações como o Instituto Americano Nacional de Padrões (ANSI) e a Organização Internacional de Padronização (ISO), resolveram padronizar a linguagem SQL. Em 1986 foi criado um padrão ANSI e em 1987 foi criado um padrão ISO. A partir de então, surgiram várias versões do padrão SQL, onde cada versão acrescenta novos comandos ou funcionalidades. Seguem abaixo alguns detalhes sobre algumas versões do padrão ANSI:

   

          - SQL-86: 

               - Primeira versão da linguagem, lançada em 1986, consiste basicamente na versão inicial da linguagem criada pela IBM.

   

          - SQL-92: 

              - Lançada em 1992, inclui novos recursos tais como tabelas temporárias, novas funções, expressões nomeadas, valores únicos, instrução CASE etc.

          - SQL:1999 (SQL3): 

               - Lançada em 1999, foi a versão que teve mais recursos novos significativos, entre eles: a implementação de expressões regulares, recursos de orientação a objetos, queries recursivas, triggers, novos tipos de dados (boolean, LOB, array e outros), novos predicados etc.

  

          - SQL:2003: 

               - Lançada em 2003, inclui suporte básico ao padrão XML, sequências padronizadas, instrução MERGE, colunas com valores auto-incrementais etc.

          - SQL:2006:
               - Lançada em 2006, não inclui mudanças significativas para as funções e comandos SQL. Contempla basicamente a interação entre SQL e XML

 

     Atualmente, os principais fabricantes de SGBDRs, implementam em seus Bancos de Dados, além das instruções SQL referentes ao seu "dialeto", as instruções SQL do padrão ANSI mais recente. No caso de instruções SQL no SGBD Oracle, o que o pessoal costuma chamar de padrão Oracle, é o dialeto SQL da Oracle. As instruções dos dialetos normalmente surgem quando o fabricante necessita implementar recursos no SGBD, que ainda não possuem instruções correspondentes no padrão ANSI . 

     Um exemplo muito utilizado de comando SQL do dialeto Oracle, é a ligação OUTER JOIN, que o pessoal costuma escrever utilizando o caractere +. Vejam a diferença nos exemplos abaixo e na imagem 01:

   
          Instrução SQL com ligação representando LEFT JOIN utilizando o dialeto Oracle:

          SELECT      e.first_name || e.last_name NAME, 
                              D.DEPARTMENT_NAME 
          FROM         HR.EMPLOYEES E,
                              HR.DEPARTMENTS D
          WHERE      E.DEPARTMENT_ID = D.DEPARTMENT_ID (+);

   
          Instrução SQL com ligação representando LEFT JOIN utilizando o padrão ANSI:

          SELECT        E.FIRST_NAME || E.LAST_NAME NAME, 
                                D.DEPARTMENT_NAME 
          FROM          HR.EMPLOYEES E
          LEFT JOIN  HR.DEPARTMENTS D
                   ON      E.DEPARTMENT_ID = D.DEPARTMENT_ID;

Imagem 01 - Instrução SQL no padrão Oracle e padrão ANSI

  

     Agora que já sabemos o que é o padrão Oracle e o que é o padrão ANSI, vou comentar sobre alguns itens que me fazem defender o uso do padrão ANSI, antes de falar especificamente sobre a performance dos 2 padrões:

             1- Permite que você migre a aplicação contendo as instruções SQL para outro BD sem ter que fazer qualquer alteração;

             2- As ligações entre colunas são mais fáceis de serem identificadas, pois ficam fora da cláusula WHERE, separadas das condições de filtro. Essa característica pode muitas vezes facilitar manutenções futuras e evitar queries ruins. Já vi muita gente escrever queries no padrão Oracle e esquecer de incluir na cláusula WHERE a(s) coluna(s) necessária(s) para efetuar a ligação correta entre 2 tabelas. Neste caso, para evitar linhas duplicadas resultantes do produto cartesiano gerado pela falta da(s) coluna(s) necessária(s) na ligação, o desenvolvedor inclui a cláusula DISTINCT, "uma grande gambiarra para corrigir um erro de programação, que gera degradação da performance da query". Ligações no padrão ANSI forçam a inclusão de uma cláusula ON que obrigam a inclusão de uma ligação entre as tabelas;

   

     Bom... agora quanto à performance, de um modo geral, não há diferença entre o plano de execução gerado nos 2 padrões, até porque, internamente, muitas instruções escritas no padrão ANSI são convertidas para o dialeto Oracle, porém o padrão ANSI tem uma vantagem bastante conhecida, como por exemplo, permitir fazer um FULL OUTER JOIN com melhor performance e com menor complexidade do que a alternativa correspondente no dialeto Oracle. Nas Imagens 02 e 03 vemos uma instrução SQL equivalente e seu respectivo plano de execução nos padrões Oracle e ANSI. Nelas podemos observar (destacado em vermelho) que o custo da instrução SQL no padrão ANSI (7) é bem menor que o do dialeto Oracle (15) e isso implica em melhor desempenho na primeira.

Imagem 02 - Full Outer Join no dialeto Oracle

Imagem 03 - Full Outer Join no padrão ANSI

      O padrão ANSI é ensinado atualmente nos treinamentos oficiais de instruções SQL da Oracle e nos meus treinamentos Aprendendo SQL! Por todos os motivos que citei neste artigo, sugiro fortemente a utilização do padrão ANSI!

  

        

Bom pessoal, por hoje é só! Espero que tenham gostado!

[]s

Referências:

  - SQL: http://pt.wikipedia.org/wiki/SQL
  - ANSI SQL: http://allthingsoracle.com/ansi-sql/   
  - Padrão SQL e sua Evolução: http://www.ic.unicamp.br/~geovane/mo410-091/Ch05-PadraoSQL-art.pdf
  - História do Padrão SQL: http://www.altabooks.com.br/capitulos_amostra/sql_cap_amostra.pdf
  - Oracle Database SQL Reference: http://docs.oracle.com/cd/B12037_01/server.101/b10759/queries006.htm
  - Oracle Database SQL Language Reference 11GR2:  
   http://docs.oracle.com/cd/E11882_01/server.112/e10592/queries006.htm#i2054062
  - Execution Plans: Part 1 Finding plans     
- http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#SQLRF01702 

Queries hierárquicas no Oracle Database

ATUALIZADO EM 11/07/2016

Olá pessoal,

  

     No artigo de hoje veremos como escrever queries hierárquicas, através da cláusula CONNECT BY, que existe no Oracle Database, se não me engano desde a versão 8, e que é um recurso bastante rápido e fácil de se utilizar (comparando-a com as outras opções existentes, que iremos ver ao longo do artigo)!

Recuperando dados da sessão de usuário

Pessoal,
 

     No artigo de hoje irei demonstrar o uso da função SYS_CONTEXT, uma função muito simples e útil do Oracle Database, que pode ser utilizada, por exemplo, para recuperar dados de uma sessão de usuário e e permitir auditoria de acesso. Como parte das minhas atividades de administração e segurança de BD, eu já utilizei essa função várias vezes dentro de triggers de logon para permitir verificar quem está se conectando no BD, horário, aplicação etc.

    A função SYS_CONTEXT pode ser utilizada tanto em instruções SQL quanto em blocos PL/SQL. No Oracle 10g/11g ela retorna um valor do tipo VARCHAR2 e aceita 2 parâmetros de entrada, também do tipo VARCHAR2: namespace e parâmetro. Esta função permite retornar dados de diversos tipos de contexto (conjunto de atributos ou variáveis predefinidas que são utilizadas por uma aplicação), dentre eles, o contexto USERENV, que é criado automaticamente pelo Oracle quando um usuário inicializa uma sessão de Banco de Dados, e que contém dados que descrevem a atual sessão do usuário.

     Segue abaixo um exemplo de instrução SQL utilizada para retornar dados do contexto USERENV relacionados à sessão de usuário atual (sessão que irá executar a instrução SQL):

         SELECT SYS_CONTEXT('USERENV', 'SERVER_HOST')   "Nome SERVIDOR",
                        SYS_CONTEXT('USERENV', 'INSTANCE_NAME') "Instância",
                        SYS_CONTEXT('USERENV', 'HOST')          "CLIENTE",
                        SYS_CONTEXT('USERENV', 'IP_ADDRESS')    "IP Cliente",
                        SYS_CONTEXT('USERENV', 'OS_USER')       "Usuário SO",
                        SYS_CONTEXT('USERENV', 'SESSION_USER')  "Usuário BD"
          FROM    DUAL;

Obs.: Se a instrução acima fosse executada em uma máquina cliente com o nome ws01 e IP 10.1.1.2, com o usuário de nome administrador logado no SO, conectado no BD bd1 do servidor server01, com o usuário de BD fabio, o resultado seria:

          Nome SERVIDOR  | Instância | CLIENTE | IP Cliente |  Usuário SO    | Usuário BD
          server01                     bd1          ws01          10.1.1.2      administrador   fabio 

   

     Para mais informações, consulte os links das referências.


Referências:
     http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/functions150.htm#g1513460
     http://psoug.org/reference/sys_context.html

Expressões regulares no Oracle Database

    Uma expressão regular (regexp) é uma sequência de caracteres compostos de literais e meta caracteres que descrevem um padrão em um texto. Expressões regulares são úteis para realizar pesquisas e manipulações de strings e podem ser usadas para reforçar CHECK CONSTRAINTs.

    Expressões Regulares estão disponíveis no Oracle Database a partir da versão 10G e são implementadas de acordo com o padrão POSIX para ASCII, através de 4 funções: REGEXP_LIKE, REGEXP_REPLACE, REGEXP_INSTR e REGEXP_SUBSTR.

    Segue abaixo a lista de alguns dos meta-caracteres mais utilizados em regexp:
       - \ : Caractere de escape -> trata o próximo caractere como um literal na expressão
       - * : Quantificador estrela -> combina zero ou mais ocorrências de uma subexpressão precedendo o meta caractere.
       - . : Ponto -> combina qualquer caractere.
       - + : Quantificador mais -> combina uma ou mais ocorrências da subexpressão precendendo o caractere meta.
       - ^ : Início de âncora de linha -> combina a próxima expressão quando ela ocorre no início de uma linha.
       - $ : Fim de âncora de linha -> combina a expressa precedente somente quando ela ocorre no final de uma   linha.
       - [...] : Lista de caracteres de combinação -> combina qualquer caractere na lista.
       - [^...] : Lista de caracteres que não combinam -> combina qualquer caractere que não está na lista.
       - ? : Quantificador de marca de questão -> combina zero ou uma ocorrência de uma subexpressão    precedendo o meta caractere.
       - {m} : Intervalo -> combina exatamente m ocorrências do caractere.
       - {m,} : Intervalo -> combina no mínimo m ocorrências do caractere.
       - {m,n} : Intervalo -> combina no mínimo m ocorrência do caractere e no máximo n ocorrências da subexpressão precedente.
       - \n : Referencia de volta -> combina a nth subexpressão precedente, where n é um inteiro de 1 a 9
       - | : Operador OR -> combina ambos os caracteres ou palavras.
       - (...) : Subexpressão ou agrupamento -> combina uma expressão como uma unidade.
      - [:class:] : Classe de caracteres POSIX -> combina qualquer caractere pertencendo a uma classe especificada de caracteres.

    Segue abaixo um exemplo, passo-a-passo, de uma consulta com regexp utilizando a função REGEXP_LIKE:
   
    PASSO 1: Criando a tabela CLIENTE:
          CREATE TABLE CLIENTE              ( ID        NUMBER,               NOME      VARCHAR2(50),               DT_NASC   CHAR(8));

    PASSO 2: Inserindo dados na tabela CLIENTE:
        INSERT INTO CLIENTE (ID, NOME, DT_NASC) VALUES (1, 'FÁBIO PRADO', '19780101');
        INSERT INTO CLIENTE (ID, NOME, DT_NASC) VALUES (2, 'CHUCK NORRIS', '19500230');
        INSERT INTO CLIENTE (ID, NOME, DT_NASC) VALUES (3, 'HOMMER SIMPSON', '9b420431');
        INSERT INTO CLIENTE (ID, NOME, DT_NASC) VALUES (4, 'FRED FLINSTONE', 'a1820331');
        INSERT INTO CLIENTE (ID, NOME, DT_NASC) VALUES (5, 'ELVIS PRESLEY', '195c0513');
        COMMIT;

    PASSO 3: Utilizando regexp para efetuar uma consulta na tabela CLIENTE para retornar somente registros com datas cadastradas com 8 dígitos e contendo somente caracteres numéricos:

        
                    SELECT     NOME,
                                        DT_NASC
                    FROM       CLIENTE
                    WHERE     REGEXP_LIKE(DT_NASC,'^[[:digit:]]{8}$');


    Obs.: :digit: é uma classe de caracteres POSIX. {8} indica o tamanho da expressão que antecede esse valor. 
                          
    RESULTADO:
               NOME                                               DT_NASC 
                 ---------------------------------------         -----------
                 FÁBIO PRADO                              19780101
                 CHUCK NORRIS                          19500230


   O mais díficil em utilizar expressões regulares é montar a expressão com os meta-caracteres. Para te ajudar neste trabalho, sugiro o site TESTE DE REGEXP (EXPRESSÕES REGULARES). Nele você poderá testar suas expressões regulares e você também encontrará alguns exemplos muito úteis, tais como, expressões para validar e-mail, telefone e URL.

  
COMENTÁRIOS FINAIS:

     Expressões regulares são muito poderosas e permitem efetuar qualquer padrão de pesquisa, porém avalie muito bem a sua utilização. Se forem mal utilizadas poderão ser muito lentas  e consequentemente, poderão degradar a performance da instrução SQL.

REFERÊNCIAS:
   - Treinamento oficial Oracle Database 10g: SQL Fundamentals Part 2
   - Função REGEXP_LIKE:   
            http://support.cs.nott.ac.uk/help/docs/databases/oracle/standard/server.101/b10759/conditions018.htm
   - Oracle Regular Expressions: http://www.psoug.org/reference/regexp.html