sexta-feira, 23 de dezembro de 2011

Insert em várias tabelas

O comando INSERT é amplamente utilizado para inserir dados em tabelas. Com pequenas variações, há duas formas bastante conhecidas:

INSERT INTO Contabil.Vendas(Id, Produto_ID, Qty, Data_Venda)
VALUES (Contabil.Vendas_Seq.Nextval, 1020, 5, Sysdate);

e

INSERT INTO Data_Whse.Vendas
(Produto_ID, Produto_Grupo_ID, Qty, Data_Venda)
SELECT Produto_ID, Produto_Grupo_ID, Qty, trunc(Data_Venda, 'HH') 
  FROM Contabil.Vendas V,
       Contabil.Produtos P
 WHERE V.Produto_ID = P.Produto_ID
   AND Trunc(V.DataVenda) = Trunc(Sysdate)-1;

A primeira forma insere uma única linha na tabela Vendas (esquema Contabil); a segunda forma insere muitas linhas na tabela Vendas (esquema Data_Whse). Tipicamente a primeira seria parte da aplicação, a segunda de um processo diário que copia dados de um esquema para outro (Contabil para Data_Whse).

Mas o Oracle permite uma variação do comando INSERT que poderia ser muito útil numa sitaução semelhante porque permite, em um único comando, inserir dados em várias tabelas. 

INSERT ALL 
  INTO Contabil.Vendas(Id, Produto_ID, Qty, Data_Venda)
       (Vendas_ID, Produto_ID, Qty, Data_Venda)
  INTO Data_Whse.Vendas(Produto_ID, Produto_Grupo_ID, Qty, Data_Venda)
       (Produto_ID, Produto_Grupo_ID, Qty, Trunc(Data_Venda) )
SELECT 20 as Vendas_ID, P.Produto_ID, P.Produto_Grupo_ID, 
       5 as QTY, Sysdate as Data_Venda
  FROM Contabil.Produto P
 WHERE P.Produto_ID = 1;
       
Com este INSERT, duas linhas são inseridas, uma em cada tabela, num único passo. A lista de colunas em cada tabela pode ser diferente e os valores podem ser manipulados, como n caso da coluna Data_Venda: uma tabela receberá o valor Sysdate, outra o valor truncado.

A sintaxe permite inserir em muitas tabelas ao mesmo tempo. O SELECT pode retornar várias linhas. Mas a combinação correta é sempre INSERT ALL/SELECT. 

Há algumas poucas restrições, a mais importante é que o SELECT não pode utilizar uma sequence diretamente. Note que coloquei o valor 20 (fixo) para Vendas_ID. É óbvio que isto funciona porque o SELECT retorna uma única linha neste exemplo.

Mas digamos que 5 itens de cada produto da empresa tenha sido vendido. Neste caso o SELECT teria que retornar muitas linhas e é necessário utilizar a sequence. Eis a solução:


INSERT ALL 
  INTO Contabil.Vendas(Id, Produto_ID, Qty, Data_Venda)
       ( Contabil.Vendas_Seq.Nextval , Produto_ID, Qty, Data_Venda)
  INTO Data_Whse.Vendas(Produto_ID, Produto_Grupo_ID, Qty, Data_Venda)
       (Produto_ID, Produto_Grupo_ID, Qty, Trunc(Data_Venda, 'HH') )
SELECT P.Produto_ID, P.Produto_Grupo_ID, 
       5 as QTY, Sysdate as Data_Venda
  FROM Contabil.Produto P;


Potencialmente, este SELECT reduz a necessidade de escrever código complementar, seja em triggers ou em outras procedures. O dado é inserido a partir de um único ponto em todas as tabelas onde é necessário, facilitando a compreensão e manutenção do sistema. E, claro, trata-se de uma única transação, ou seja, ou insere em todas as tabelas ou não insere em nenhuma. 


terça-feira, 20 de dezembro de 2011

Particionando arquivos de exportação no Oracle

O utilitário exp do oracle é muitas vezes utilizado como uma ferramenta auxiliar para estratégias de backup.
Algumas vezes o espaço disponível em um único disco não é suficiente para armazenar todo o conteúdo exportado pelo utilitário, o texto abaixo explica como resolver esta situação e complementa uma dúvida que recebi por e-mail que perguntava como particionar o arquivo de exportação em vários arquivos.
Como exemplo considere que o exp deva gerar um arquivo de 9G e precisaria quebrar em três arquivos de 3G.

Para resolver esta situação utilize os parâmetros file e filesize:

File - relacione os nomes dos três arquivos que devem ser gerados, separados por vírgulas.
Filesize - indique o tamanho máximo dos arquivos que serão gerados.

Exemplo:
   exp usuario/senha file=/tmp/arq01.dmp,/tmp1/arq02.dmp,/tmp2/arq03.dmp log=/tmp/exporta.log filesize=3G

Quando a exportação precisar gerar um quarto arquivo (exportação maior do que 9G), se não estiver previsto no parâmetro file, o utilitário irá solicitar o nome dele na tela.

terça-feira, 13 de dezembro de 2011

Há 100 anos no Polo Sul

Amundsen e equipe fazendo medições para
confirmar que chegaram ao local correto.
Totalmente fora do assunto deste blog, mas podemos dizer que os protagonistas desta aventura são alguns dos nossos heróis preferidos. Pois amanhã, dia 14 de Dezembro de 2011, completa-se 100 anos da chegada de Amundsen ao Polo Sul!

Amundsen pesquisou e experimentou alternativas para chegar ao Polo Sul. Escolheu trenós puxados por cachorros. Toda equipe e alguns cachorros sobreviveram o percurso completo, ida e volta.

Seu concorrente, Scott, acreditava que com esforço e coragem superaria qualquer obstáculo. Chegou ao Polo Sul na metade de Janeiro, apenas para encontrar a bandeira da Noruega marcando o ponto exato. Não retornou a tempo para o acampamento e morreu congelado.  

Portanto, pode-se colocar muitas horas de esforço na solução de um problema, mas é fundamental ter conhecimento, explorar alternativas e escolher aquelas que garantem maior chance de sucesso. Inclusive em problemas de BD.

sexta-feira, 2 de dezembro de 2011

SELECT FROM SAMPLE

Um recurso muito interessante e pouco conhecido da clausula FROM é a opção SAMPLE. Ela deve ser acompanhada de um número entre (0,100),  mas nunca nos limites 0 e 100. Este número indica a probabilidade individual de cada linha da tabela retornar na resposta da query. Por exemplo:

select obj_id, object_name
  from my_objs sample(10);

Detalhando um pouco, priemiro vou criar uma tabela e verificar o número de linhas.

create sequence my_obj_seq;

create table my_objs as
select my_obj_seq.nextval obj_id, owner, object_name, object_id
  from all_objects;
  
select count(*)
  from my_objs;

No meu Oracle retornou 17.900.  Agora tente algo simples como:

select obj_id, object_name
  from my_objs;

Obviamente todas as linhas da tabela retornarão na resposta. Incluindo o SAMPLE, temos:

select obj_id, object_name
  from my_objs sample(10);

E a chance de cada linha estar na resposta é de apenas 10%. Se executar repetidas vezes, o conjunto resposta será diferente a cada execução por que, para cada linha, o Oracle decide inclui-la ou não na resposta. Ou seja, a resposta passa a ser aleatória.

Neste ponto, você executa:

select count(*) from(  
select obj_id, object_name
  from my_objs sample(10);

E a resposta é 1.755! Opa, mas isto não é 10% de 17.900. Repetindo o mesmo SELECT COUNT(*) a resposta é 1.829! E na terceira vez é 1.793! 

Estaria tudo errado? Não deveria ser exatamente 10% do total de linhas na tabela, nesta caso exatos 1.790? 

O Oracle está correto. A chance de cada linha estar na resposta é 10%, mas eventualmente haverá mais linhas na resposta - digamos que deram sorte - ou menos linhas - estavam com azar. Se você precisar de um número exato, basta utilizar uma consulta aninhada:

select * from(
select obj_id, object_name
  from my_objs sample(100))
where rownum <= 10; 

Eu utilizei este recurso para selecionar um subconjunto aleatório de linhas da tabela. Isto fazia parte de um teste que seria repetido diversas vezes, mas era importante que o conjunto de dados fosse diferente a cada execução, afinal seria desnecessário calcular várias vezes o mesmo valor.