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.


segunda-feira, 28 de novembro de 2011

Sequence no SQL Server 2012

Aproveitando o tema abordado pelo colega Miguel sobre “Alterar valor de uma Sequence” , vou comentar que na versão do SQL Server 2012 foi incluído este recurso já tão utilizado no Oracle.

Para quem não conhece a sequence é um contador que gera valores normalmente utilizados para inicializar chaves primárias de tabelas, seguindo critérios estabelecidos no momento da criação da sequence.

Uma das vantagens sobre colunas do tipo Identity é que uma sequence pode ser utilizada por várias tabelas.

Sintaxe:

CREATE SEQUENCE [schema_name . ] sequence_name
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]
    [ START WITH ]
    [ INCREMENT BY ]
    [ { MINVALUE [ ] } | { NO MINVALUE } ]
    [ { MAXVALUE [ ] } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [ ] } | { NO CACHE } ]
    [ ; ]

Para explicar o funcionamento deste recurso vou utilizar o exemplo abaixo:
Considere uma tabela com dois campos: MesAno e TotalSalario que não possui chave, vamos incluir uma coluna ID do tipo inteiro, e colocaremos como valor default o valor atual de uma Sequence (IdSalarios) criada abaixo. Também serão atualizados todos os registros previamente existentes com um valor da sequence:

-- Script de criação da Tabela

CREATE TABLE [dbo].[TotalSalarios](
       [MesAno] [datetime] NOT NULL,
       [TotalSalario] [decimal](11, 2) NULL);

MesAno
TotalSalario
2011-01-01 00:00:00.000
89500.00
2011-02-01 00:00:00.000
91200.00
2011-03-01 00:00:00.000
93200.00
2011-04-01 00:00:00.000
97200.00
2011-05-01 00:00:00.000
101250.00
2011-06-01 00:00:00.000
103000.00
2011-07-01 00:00:00.000
103000.00
2011-08-01 00:00:00.000
105020.00
Tabela 1 - Conteúdo atual da tabela
 
-- Criação da Sequence com o nome IdSalarios

CREATE SEQUENCE dbo.IdSalarios AS INT
   MINVALUE 1     -- Menor valor da Sequence
   MAXVALUE 10000 --  Maior valor da sequence 
   START WITH 1   -- Valor inicial da Sequence
   CYCLE;         -- Indica para a sequence reiniciar do menor
                  -- valor (1) quando atingir o
                  -- maior valor (10000)

-- Criação da coluna ID
Alter Table TotalSalarios Add ID int;
 
-- Criando uma constraint para definir o valor da sequence como valor default da coluna ID
ALTER TABLE TotalSalarios 
  ADD CONSTRAINT Seq_IDSalarios DEFAULT (NEXT VALUE FOR IdSalarios) FOR Id
 
-- Inicializando os valores existentes com o próximo valor da sequence
Update TotalSalarios
   set ID = Next Value For IdSalarios;
 
-- Inserindo um registro novo deixando sem informar valor para ID
Insert into TotalSalarios (MesAno, TotalSalario) Values('2011-09-01',11000);


MesAno
TotalSalario
2011-01-01 00:00:00.000
89500.00
2011-02-01 00:00:00.000
91200.00
2011-03-01 00:00:00.000
93200.00
2011-04-01 00:00:00.000
97200.00
2011-05-01 00:00:00.000
101250.00
2011-06-01 00:00:00.000
103000.00
2011-07-01 00:00:00.000
103000.00
2011-08-01 00:00:00.000
105020.00
2011-09-01 00:00:00.000
11000.00
Tabela 2 - Conteúdo final da tabela

Alterar valor de uma sequence

Acho que este cenário é bem comum: a chave primária de uma tabela tem seus valores definidos a partir de uma sequence. Por exemplo, os valores na tabela Customer, coluna Customer_ID são obtidos da sequence Customer_Seq. Se tudo funcionar adequadamente, o valor máximo na coluna é igual ao valor atual da sequence. E na próxima inserção de uma linha, a sequence retorna um valor maior (provavelmente um número acima). 

Pois bem, por diferentes motivos isto pode não ocorrer. Um caso comum ocorre no ambiente de desenvolvimento quando a aplicação ainda em testes iniciais contem um erro, insere muitas linhas sem utilizar a sequence e perde esta sincronia. Em produção já enfrentei um cenário mais complexo envolvendo duas aplicações inserindo dados concorrentemente em um BD. De qualquer modo, aparece uma necessidade: "adiantar a sequence até o ponto máximo da tabela".  

O modo simples seria dropar e recriar a sequence. Mas há problemas de segurança, concorrência, necessidade de recompilar pacotes, etc... que podem tornar a operação não muito simples. 

A solução óbvia é escrever um pequeno loop para adiantar a sequence n vezes. Atenção, se n for grande, isto pode demorar algum tempo.

Bem, depois de ocorrer algumas vezes, desenvolvi uma procedure para me ajudar. São três parâmetros: nome da tabela, nome da coluna e nome da sequence.  A idéia básica é obter a diferença entre o valor máximo na tabela, que suponho ser maior, e o valor atual na sequence. Então, alterar o incremento da sequence para a diferença e incrementar a sequence uma única vez cobrindo toda diferença. Apenas para não afetar a sequence de maneira definitiva, é necessário saber o incremento utilizado no início e redefini-lo no final. 

Vantagens: 
  • em poucos passos o valor da sequence é corrigido 
  • tempo de execução não depende da diferença
  • a sequence continua existindo, bem como seus grants
  • não é necessário recompilar procedures ou funções
  • e uma vez escrita a procedure, basta chamá-la.
Para chamar a procedure, basta uma linha como;

exec move_sequence('customer', 'customer_id', 'customer_seq');

Aqui a procedure, com alguns put_line apenas para ajudar a sua compreensão:


create or replace
procedure move_sequence(p_table_name varchar2, p_column_name varchar2, p_sequence_name varchar2 )
as
  sqlc varchar2(500);
  max_column_value number;
  curr_seq_value number;
  diff number;
  curr_seq_increment number;


begin


  sqlc := 'select max('||p_column_name||') from '||p_table_name;
  execute immediate sqlc into max_column_value;
  
  dbms_output.put_line('Max Current Value: '||max_column_value);


  select us.increment_by
    into curr_seq_increment
    from user_sequences us
   where sequence_name = upper(p_sequence_name); 
  
  sqlc := 'select '||p_sequence_name||'.nextval from dual';
  execute immediate sqlc into curr_seq_value;
  
  dbms_output.put_line('Seq Current Value: '||curr_seq_value);

  diff := max_column_value - curr_seq_value;
  sqlc := 'alter sequence '||p_sequence_name||' increment by '||diff;
  execute immediate sqlc;
  
  sqlc := 'select '||p_sequence_name||'.nextval from dual';
  execute immediate sqlc into curr_seq_value;
  
  dbms_output.put_line('New Seq Current Value: ' ||curr_seq_value);
   
 sqlc := 'alter sequence '||p_sequence_name||' increment by '||curr_seq_increment;
  execute immediate sqlc;
  
end;

Uma variação simples é resetar a sequence para o valor inicial. Esta eu deixo por conta do leitor. Alguns detalhes podem ser incluídos, como um parâmetro para o nome do esquema. 




domingo, 27 de novembro de 2011

Insert: valores default e nulos

Este post tem por objetivo esclarecer um pequeno erro, mas infelizmente comum, relativo ao comando INSERT, valores default e nulos. 

Comecemos pela declaração da tabela. Como é fácil notar, a terceira coluna chama-se dtNasc e tem sysdate como valor default. 

create table pessoa 
(id number primary key
 nome varchar2(50), 
 dtNasc date default trunc(sysdate)); 

Agora uma pequena sequencia de INSERTs, com uma alteração na foram como valores são inseridos na coluna dtNasc: 

insert into pessoa(id, nome, dtnasc) 
values(1, 'João', trunc(sysdate-20) );

insert into pessoa(id, nome) 
values(2, 'Maria'); 


insert into pessoa(id, nome, dtnasc) 
values(3, 'João', null ); 


commit

E para ver o resultado, um SELECT:

select * from pessoa; 


ID    NOME       DTNASC                    
----- ---------- ------------ 
1     João       06-NOV-11                 
2     Maria      26-NOV-11                 
3     João                                                                         

O primeiro INSERT define claramente um valor para dtNasc, no caso 06-NOV-2011, sobrescrevendo o valor default. O segundo nem inclui a coluna na lista, muito menos define um valor. Neste caso, o valor default é assumido. Já o terceiro também define explicitamente o valor NULL. A intenção do usuário é clara: o valor é desconhecido, não assuma o valor default. Este é justamente o erro que comentei no início, pois muitos assumem que seria definido o valor default. 

Se já pode causar alguma confusão quando utilizando diretamente SQL, mais atenção é necessária quando a aplicação é desenvolvida com Hibernate ou algum outro framework para acesso a dados por que há diferenças no comportamento se a coluna está ou não mapeada e como certos parâmetros do framework são setados. Não vou entrar nas várias alternativas de cada framework, mas é importante estar atento.

quinta-feira, 17 de novembro de 2011

Objetos dependentes

A alteração de um objeto (tabela, visão, procedure, ...) pode afetar outros objetos e obrigar, no mínimo , a recompilá-los. Assim, antes de iniciar a alteração é útil verificar quais outros objetos poderão ser afetados. Ou talvez muito antes disto, quando estiver avaliando custos e riscos de alterar o esquema para suportar um novo requisito. Para tanto, basta uma consulta a visão DBA_DEPENDENCIES (ou às equivalentes ALL_DEPENDENCIES  e USER_DEPENDENCIES).


SELECT 
FROM dba_dependencies 
WHERE referenced_owner = 'HR'
  AND referenced_name = 'JOB_HISTORY';

Esta consulta retorna uma única linha: a procedure ADD_JOB_HISTORY. Examinando o código fonte desta procedure, nota-se que há um comando de INSERT na tabela. Parece-me razoável que, se você incluir uma coluna na tabela, a procedure seja alteradap ara incluir um valor nesta nova coluna. E se por acaso remover uma coluna da tabela, a procedure obrigatoriamente terá que ser alterada.

Agora um passo a mais: como saber quais objetos a procedure pode afetar? Uma solução simples seria aplicar novamente a mesma consulta, apenas usando o nome da procedure. Para um esquema simples, até pode ser, mas num esquema complexo, com centenas de objetos, seria um esforço manual desnecessário e sujeito a erros. Muito mais conveniente e elegante é usar a consulta abaixo e deixar que o Oracle resolva este problema. 

SELECT *
FROM dba_dependencies
START WITH referenced_owner = 'HR
       AND referenced_name  = 'JOB_HISTORY'
CONNECT BY prior owner = referenced_owner
       AND prior name  = referenced_name; 


A consulta retorna duas linhas: a procedure ADD_JOB_HISTORY e a trigger UPDATE_JOB_HISTORY, que tem uma chamada para a procedure. 

Por tratar-se de uma consulta hierárquica, se houver um ciclo no grafo de dependências, o Oracle retornará um erro. Neste caso, é possível incluir um controle para limitar a recursão. Por exemplo:

SELECT *
FROM dba_dependencies
START WITH referenced_owner = 'SYS
       AND referenced_name  = 'OBJ$'
CONNECT BY prior owner = referenced_owner
       AND prior name  = referenced_name
       AND level < 4;


O valor 4 é o limite para este caso específico (tabela SYS.OBJ$). Se aumentar para 5, fecha-se um ciclo no grafo e um erro é retornado. Outros objetos podem permitir outros limites.

quarta-feira, 16 de novembro de 2011

Novo grupo de usuários SQL Server do RS

Foi criado um grupo de usuários SQL Server do Rio Grande do Sul, por enquanto as pessoas envolvidas são André Pretto @prettoandre), Cesar Blumm (@cesarblumm), Marcus Vinicius Bittencourt (@mvbitt) e Rodrigo Crespi (@crespidb).

Dia 19/11/2011 terá um encontro do grupo para apresentar o assunto Auditoria de Banco de Dados SQL Server de Acordo com a SoX com o membro André Pretto.

Twitter: @sqlserverrs
Blog: http://sqlserverrs.com.br

Dicionário de Dados do Oracle - a visão DICTIONARY

O dicionário de dados de todo SGBD armazena informações sobre o esquema de dados criado pelos usuários. No caso do Oracle é um conjunto de tabelas e visões criadas no esquema SYS. 

A visão DICTIONARY é especialmente interessante por que contém a lista de visões que formam o próprio dicionário de dados. Ou seja, seria o meta-meta-esquema de dados. 

A consulta abaixo retorna o número de visões para certos grupos de informações. 

SELECT 
CASE WHEN SUBSTR(TABLE_NAME, 1, 5) = 'USER_' THEN 'USER_'
     WHEN SUBSTR(TABLE_NAME, 1, 4) = 'ALL_' THEN 'ALL_'
     WHEN SUBSTR(TABLE_NAME, 1, 4) = 'DBA_' THEN 'DBA_'
     WHEN SUBSTR(TABLE_NAME, 1, 3) = 'V_$' THEN 'V_$'
     WHEN SUBSTR(TABLE_NAME, 1, 2) = 'V$' THEN 'V$'
     WHEN SUBSTR(TABLE_NAME, 1, 4) = 'GV_$' THEN 'GV_$'
     WHEN SUBSTR(TABLE_NAME, 1, 3) = 'GV$' THEN 'GV$'            
     ELSE 'Others'
END  PREFIX    
, COUNT(*)
FROM DICTIONARY
GROUP BY 
CASE WHEN SUBSTR(TABLE_NAME, 1, 5) = 'USER_' THEN 'USER_'
     WHEN SUBSTR(TABLE_NAME, 1, 4) = 'ALL_' THEN 'ALL_'
     WHEN SUBSTR(TABLE_NAME, 1, 4) = 'DBA_' THEN 'DBA_'
     WHEN SUBSTR(TABLE_NAME, 1, 3) = 'V_$' THEN 'V_$'
     WHEN SUBSTR(TABLE_NAME, 1, 2) = 'V$' THEN 'V$'
     WHEN SUBSTR(TABLE_NAME, 1, 4) = 'GV_$' THEN 'GV_$'
     WHEN SUBSTR(TABLE_NAME, 1, 3) = 'GV$' THEN 'GV$'            
     ELSE 'Others'
END;


O número de visões em cada grupo pode variar dependendo da versão de Oracle instalada. Os números abaixo correspondem ao Oracle 11g XE que estou utilizando.

Prefix Count(*) Descrição
USER_ 361 Objetos pertencentes ao usuário corrente.
DBA_ 703 Todos objetos na base de dados.
ALL_ 339 Objetos que o usuário corrente tem privilégios de acesso, seja no seu esquema ou em outro.
V$ 618 Informações dinâmicas de desempenho.
GV$ 491 Informações de desempenho global do sistema, especialmente úteis em RACs.
Others 39 Variados usos

Caso você não lembre do nome de determinadas visões do dicionário de dados, você pode utilizar a visão DICTIONARY e escrever consultas como SELECT * FROM DICTIONARY WHERE TABLE_NAME LIKE '%INDEX%'. Afinal, ninguem é obrigado a lembrar o nome de 2000 visões, mas certametne é muito útil ter certo conhecimento do dicionário de dados.

segunda-feira, 14 de novembro de 2011

Subtotais em Oracle SQL - parte III

A combinação de GROUP BY com ROLLUP, CUBE ou GROUPING_SETS resulta em dois tipos de linhas: agregadas, aquelas com valores em todas as colunas do GROUP BY; e super-agregadas, as linhas de subtotais ou totais.

A função GROUPING permite facilmente dferenciar as duas, pois retorna o valor 0 se a linha representar um valor agregado e o valor 1 caso seja um super-agregado. Veja na consulta abaixo os valroes da primeira e segunda coluna. A última linha, por ser o grande total, tem valor 1 para ambas. 

SELECT grouping(department_name) grp_dept,
      grouping(TO_CHAR(hire_date, 'yyyy')) grp_hd,
       department_name,
       TO_CHAR(hire_date, 'yyyy') hire_year,
       SUM(salary)
FROM hr.employees emp
JOIN HR.departments dept USING (department_id)
JOIN HR.jobs j USING (job_id)
WHERE department_name IN ( 'IT', 'Sales')
GROUP BY CUBE(department_name, TO_CHAR(hire_date, 'yyyy'))
ORDER BY 1,2;

Grp_Dept Grp_HD Department_Name Hire_Year SUM(Salary)
0 0 IT 2005 4800
0 0 Sales 2007 42200
0 0 Sales 2006 59100
0 0 Sales 2005 100300
0 0 Sales 2004 53500
0 0 IT 2007 10200
0 0 IT 2006 13800
0 0 Sales 2008 49400
0 1 Sales
304500
0 1 IT
28800
1 0
2008 49400
1 0
2007 52400
1 0
2006 72900
1 0
2005 105100
1 0
2004 53500
1 1

333300


Obviamente 0 ou 1 podem ser úteis para algum tipo de processamento automático, mas caso queira mostrar um resultado final para usuários, é mais conveniente substituir por mensagens adequadas. Por exemplo, a primeira coluna é resultado da função GROUPING numa combinação com CASE para definir um valor que o usuário final receberá:

SELECT case when grouping(department_name) = 1 AND grouping(TO_CHAR(hire_date, 'yyyy')) = 1 then 'Grande total'
            when grouping(department_name) = 1 then 'Subtotal por ano'
            when grouping(TO_CHAR(hire_date, 'yyyy')) = 1 then  'Subtotal por departamento'
            else null
       end column_type,
       department_name,
       TO_CHAR(hire_date, 'yyyy') hire_year,
       SUM(salary)
FROM hr.employees emp
JOIN HR.departments dept USING (department_id)
JOIN HR.jobs j USING (job_id)
WHERE department_name IN ( 'IT', 'Sales')
GROUP BY CUBE(department_name, TO_CHAR(hire_date, 'yyyy'))
ORDER BY 1,2;

Column_Type Department_Name Hire_Year SUM(Salary)
IT 2005 4800
IT 2006 13800
IT 2007 10200
Subtotal by department IT 28800
Sales 2004 53500
Sales 2005 100300
Sales 2006 59100
Sales 2007 42200
Sales 2008 49400
Subtotal by department Sales
304500
Subtotal by year
2004 53500
Subtotal by year
2005 105100
Subtotal by year
2006 72900
Subtotal by year
2007 52400
Subtotal by year
2008 49400
Grand total

333300

Por fim, uma nota referente a clausula HAVING: ela filtra linhas agregadas que atendam (ou não) o predicado, mas todas linhas são consideradas para calcular os subtotais e totais. Isto pode resultar em uma certa inconsistência nos dados.  Por exemplo, a consulta abaixo retorna duas linhas para os anos 2006 e 2007 em que mais de um funcionário foi contratado. Mas o valor na linha e subtotal (e total) é resultado da soma de três anos!

SELECT department_name,
       TO_CHAR(hire_date, 'yyyy') hire_year,
       SUM(salary)
FROM hr.employees emp
JOIN HR.departments dept USING (department_id)
JOIN HR.jobs j USING (job_id)
WHERE department_name IN ( 'IT')
GROUP BY department_name, ROLLUP(TO_CHAR(hire_date, 'yyyy'))
HAVING COUNT(*) > 1
ORDER BY 1,2;

Department_Name Hire_Year SUM(Salary)
IT 2006 13800
IT 2007 10200
IT
28800


28800

Uma possível solução é utilizar subconsultas:

SELECT department_name,
       hire_year,
       SUM(sum_salary)
FROM(
     SELECT department_name,
            TO_CHAR(hire_date, 'yyyy') hire_year,
            SUM(salary) sum_salary
     FROM hr.employees emp
     JOIN HR.departments dept USING (department_id)
     JOIN HR.jobs j USING (job_id)
     WHERE department_name IN ( 'IT')
     GROUP BY department_name, TO_CHAR(hire_date, 'yyyy')
     HAVING COUNT(*) > 1)
GROUP BY ROLLUP(department_name, hire_year)
ORDER BY 1,2;

Department_Name Hire_Year SUM(Salary)
IT 2006 13800
IT 2007 10200
IT
24000


24000

Espero que tenha sido claro nas explicações. Por favor, qualquer dúvida, sugestão, etc...  utilizem os comentários.

Parte 1 Parte 2 Parte 3