Aprenda PL/SQL

Category Archive: SQL

Estou escrevendo esse texto para colocar meu parecer sobre o novo curso de SQL Tuning oferecido pela Nerv. Como eu participei da primeira turma de SQL Tuning, tenho o privilégio de compartilhar sobre o curso de antemão. Este é o segundo curso que eu faço na Nerv. O primeiro, você pode ver o review neste link.

Instrutor

O professor é o Ricardo Portilho Proni que é uma referência técnica no Brasil. Para quem não conhece, ele possui uma grande quantidade de certificações, incluindo Oracle ACE e palestrante em diversos eventos sobre Oracle. Possui uma experiência profissional muito grande. Sou totalmente suspeito em falar sobre o Portilho, pois quem me conhece, sabe que eu sou fã de carteirinha dele desde 2010. 😀

Nerv1Nerv2Nerv Certificados

A Turma

Tive a grande alegria de re-encontrar amigos e conhecer novos. Na turma estavam os seguintes alunos:
* Fernando Franquini (DBA Capin do certificacaobd.com.br. Atualmente DBA na Softplan )
* Eduardo Ribeiro ( DBA na UOL Diveo )
* Hugo Torralbo ( DBA no Itaú / Unibanco )
* Itagyba Kuhlmann ( Arquiteto de soluçoes na Pernambucanas )
* Thomas Glufke ( Oracle EBS Senior Developer na Dell Computers )
* Abaixo, o instrutor Ricardo Portilho ( Instrutor e Owner da Nerv Informática )

Turma SQL Tuning

O curso

São três dias de treinamento, alternando entre teoria, exemplos práticos, laboratório e muita troca de experiência. Como sempre, turmas pequenas.

O assunto Tuning é quase sempre um tabu, pois muita coisa pode afetar o comportamento do Oracle, mesmo em ambientes idênticos. É bem comum o profissional simplesmente “ir testando” algumas coisas pra ver se melhora a performance. É neste momento que o curso faz a diferença!

Como o Portilho mesmo disse, se o curso fosse sobre carros, “eu não vou te ensinar a pilotar, eu vou te ensinar a ser um mecânico!“. Isso é realmente verdade. O curso não é baseado em dicas, ou seja, receitas de bolo: “Se isso, faça aquilo”. É totalmente voltado ao funcionamento do otimizador, plano de execução, funcionamento e comportamento de índices e estatísticas, etc.

É realmente impressionante fazer um TRACE do otimizador e ver o que ele faz pra decidir qual plano de execução vai optar, e todas transformações que uma query sofre. Com os tópicos que estudamos, é possível analisar o motivo da demora e ajustá-las para ganhar performance.

Se você quiser saber mais detalhes de todos assuntos que são abordados, veja o link do curso aqui.

Opinião Pessoal

Já fiz alguns cursos de Tuning, mas até hoje nenhum deles foi tão aprofundado no assunto. Eu diria que o curso é voltado tanto pra DBAs como para desenvolvedores. É ideal se você já conhece algumas coisas sobre o assunto e quer ir mais a fundo.

Se você tiver a oportunidade de ir pra São Paulo e fazer este treinamento, tenho certeza que será um grande investimento e um upgrade na sua carreira.




bug

Tem algumas coisas que estão praticamente enraizadas no mundo Oracle: me refiro a algumas práticas que quase todo mundo faz e que foi adotado como padrão. O texto abaixo não contém nenhuma nova feature do Oracle, nem é nenhuma novidade pra ninguém. Mas por incrível que pareça, eu vejo em quase todas empresas essa prática e isso normalmente gera problemas. Me refiro a utilização de um NVL pra fazer um filtro opcional na query. Abaixo está um exemplo:

SELECT *
FROM tabela
WHERE campo = NVL ( p_parametro1 , campo )

Quase todo programa tem um sistema de filtro como esse acima.
Se o usuário informar o p_parametro1, a query vai mostrar apenas as linhas cujo campo é igual ao p_parametro1.
Se o usuário não informar nada, cai no NVL, e ele faz join com a própria coluna, trazendo tudo.

Leia mais…




Neste post, vamos mostrar como utilizar funções analíticas para fazer o rateio de um valor em diversas linhas de uma tabela.

Primeiro, vamos criar a tabela de teste:

CREATE TABLE glufke_teste
(nro_nota  NUMBER, item NUMBER, valor NUMBER);
INSERT INTO glufke_teste VALUES ( 50, 1, 1.45);
INSERT INTO glufke_teste VALUES ( 50, 2, 3.91);
INSERT INTO glufke_teste VALUES ( 50, 3, 5.04);
SQL> SELECT * FROM glufke_teste;

  NRO_NOTA       ITEM      VALOR
---------- ---------- ----------
        50          1       1,45
        50          2       3,91
        50          3       5,04

SQL>

Nosso objetivo é ratear um valor proporcionalmente nessas linhas. Como exemplo prático, vamos distribuir o valor R$ 49,30!
Leia mais…




É comum termos que procurar qual package ou qual procedure de banco que faz INSERT dentro de uma determinada tabela.

Nestes casos, podemos usar a ALL_SOURCE, que contém os códigos de todos programas de banco. Normalmente um select com LIKE resolve o problema:

SELECT * FROM all_source
WHERE UPPER(text) LIKE '%INSERT%TABELA%'

O problema é que nem sempre o código está no padrão, por exemplo, o nome da tabela pode estar no lado ou na próxima linha!

INSERT INTO tabelax...

INSERT 
INTO tabelax...

O mesmo vale pra UPDATES! Ou seja, se o comando INSERT estiver na linha anterior, essa consulta simples não vai retornar o que procuramos!

UPDATE tabelax SET...

UPDATE 
tabelax
SET...


Leia mais…




Neste artigo irei apresentar um recurso muito bom que existe no Oracle Database desde a versão 9i e chama-se Pipelined Table Function.

Este recurso permite criar funções que retornam dados como se fossem uma tabela virtual, podendo transformar os dados de retorno enquanto eles são produzidos, ou seja, é possível alterar os dados pesquisados em uma tabela, linha por linha, enquanto eles são processados, sem ter que esperar pelo retorno completo do “result set” (conjunto de dados que são retornados pela função).

Este recurso é ótimo para ETL (Extract, Transform, and Load), pois é rápido e consome menos memória que outros métodos que podem ser utilizados para o mesmo objetivo, como por exemplo, preencher um cursor e percorrê-lo para transformar e retornar dados.

Seguem abaixo 3 scripts que demonstram como criar e testar uma Pipelined Table Function. Os scripts utilizam a tabela EMPLOYEES do schema de exemplo HR.

Para iniciar o passo-a-passo dos itens abaixo, é necessário conectar-se previamente no Banco de Dados desejado, através do SQL Plus, com um usuário com privilégios administrativos (usuário contendo a role DBA ou o privilégio de sistema SYSDBA) ou com o usuário HR.

1- Criando a package HR.PKG_TYPES

A package HR.PKG_TYPES contém os tipos de dados que são criados para retornarem uma tabela virtual na função que será criada no próximo passo:

create or replace package HR.PKG_TYPES as
  TYPE TABLEEMPTYPE IS TABLE OF EMPLOYEES%ROWTYPE;
  TYPE ROWEMPTYPE IS RECORD(
          EMPLOYEE_ID    EMPLOYEES.EMPLOYEE_ID%TYPE,
          FIRST_NAME     EMPLOYEES.FIRST_NAME%TYPE,
          LAST_NAME      EMPLOYEES.LAST_NAME%TYPE,
          EMAIL          EMPLOYEES.EMAIL%TYPE,
          PHONE          EMPLOYEES.PHONE_NUMBER%TYPE,
          HIRE_DATE      EMPLOYEES.HIRE_DATE%TYPE,
          JOB_ID         EMPLOYEES.JOB_ID%TYPE,
          SALARY         EMPLOYEES.SALARY%TYPE,
          COMMISSION_PCT EMPLOYEES.COMMISSION_PCT%TYPE,
          MANAGER_ID     EMPLOYEES.MANAGER_ID%TYPE,
          DEPARTMENT_ID  EMPLOYEES.DEPARTMENT_ID%TYPE
         );
END;
/


Leia mais…




A maioria das pessoas tem essas perguntas sobre Index Partition:

  • O que é um Local Index?
  • O que é um Global Index?
  • Quando você forçaria a criação de um Global Index em uma partition table?
  • Quando você recomendaria criar um Global Index em vez de um Local Index?

Para responder a essas perguntas….

1. O que é um Local Index?

Local Indexes particionados são mais fáceis de gerenciar, cada partição do Local Index está associado a uma partição. Eles também oferecem maior disponibilidade e são comuns em ambientes de DSS. Quando tomamos qualquer ação (MERGE, SPLIT,EXCHANGE etc) em um Local Index, isso impacta apenas aquela partição e as outras estarão disponíveis. Nós não podemos adicionar explicitamente um Local Index para uma nova partição. O Local Index será adicionado implicitamente a nova partição, quando for criada uma nova partição na tabela. Da mesma forma, não podemos dropar o índice local em uma partição específica. Ele pode ser dropado automaticamente quando nós dropamos a partição da tabela subjacente. Local Indexes podem ser UNIQUE quando a chave da partição é parte do índice composto. Unique Local Indexes são úteis para o ambiente OLTP. Podemos também criar bitmap indexes em tabelas, com a restrição de que os índices de bitmap deve ser local para a tabela particionada. Eles não podem ser Global Indexes.

SQL> CREATE TABLE employees
2 (employee_id NUMBER(4) NOT NULL,
3 last_name VARCHAR2(10),
4 department_id NUMBER(2))
5 PARTITION BY RANGE (department_id)
6 (PARTITION employees_part1 VALUES LESS THAN (10) TABLESPACE ODS_STAGE_DATA,
7 PARTITION employees_part2 VALUES LESS THAN (20) TABLESPACE ODS_STAGE_DATA,
8 PARTITION employees_part3 VALUES LESS THAN (30) TABLESPACE ODS_STAGE_DATA);

Table created.

SQL> declare
2 v_no number :=1;
3 begin
4 delete employees;
5 for i in 1..10 loop
6 insert into employees values(v_no,'name...',v_no);
7 v_no := v_no+1;
8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> create index idx_local on employees(last_name) local;

Index created.

SQL>


Leia mais…




Pois é isso mesmo! A querida tabela do ORACLE que nos mostra o plano de execução dos SQLs hoje completa 21 anos!
Hoje eu fui olhar um plano de execução e me deparei com a seguinte mensagem:

SET AUTOTRACE ON
SELECT bla bla bla...

Execution Plan
----------------------------------------------------------

--------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Cost (%CPU)|
--------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     1 |   102   (0)|
|   1 |  SORT AGGREGATE    |            |     1 |            |
|   2 |   TABLE ACCESS FULL| TABELA     |  4322 |   102   (0)|
--------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

PLAN_TABLE is old version ???
Leia mais…




A partir do Oracle 9i foi criado o conceito de EXTERNAL TABLES, ou seja, você cria uma tabela baseado num arquivo texto no sistema operacional e pode fazer consultas SQL nessa tabela (ou seja, diretamente no arquivo texto como se fosse uma tabela)
Agora a partir do oracle 10g é possível também criar um arquivo texto baseado numa tabela do banco usando o novo driver de Data Pump existente.
Leia mais…




A partir do Oracle 10g temos mais algumas funcionalidades no uso da cláusula CONNECT BY dentro dos comandos SELECT. Essas mudanças se aplicam a queries hiearquicas permitindo o retorno de não apenas PAIS, FILHOS mas também “ancestrais”. São 3 as novas cláusulas disponíveis com CONNECT BY.
Leia mais…




Oracle 10g agora permite o uso de funções somatórias na cláusula RETURNING. O seguinte exemplo faz um UPDATE no salário de todos empregados e retorna a média salarial resultante para as linhas afetadas.
Leia mais…