Monday, January 09, 2006
ROLLUP
As vezes esquecemos como pode ser simples uma query através das opções disponíveis. O rollup, por exemplo, é uma clausula ainda pouco explorado, embora seja extremamente útil para sumarizações e acima de tudo é performática.
Quero ver!
Na tabela emp, queremos saber o total pago em salários por job, deptno e gran total em um mesmo relatório.
Poderíamos começar com:
Seria um método. Agora, qual o custo disso? Ahh, eu fiz 3 tablescan contra a tabela emp para: primeiro conseguir o total por job, depois para conseguir o total por deptno e finalmente o "Gran Total". Vejam o custo.
Sem entrar muito em hash/sort, o resumo foi 3 full table scan em EMP com 9 consistent gets para chegar ao result set. Agora vamos usar o ROLLUP.
Além de ser mais entendível semanticamente, a performance não se compara. O custo do mesmo resultado é de 1/3 se compararmos com o custo da primeira execução, desta vez o Oracle precisou somente de 1 full e 3 consistent gets para compor o MESMO result set.
A conclusão que devemos ter em conta a partir deste pequeno exemplo é procurar entender e aprender os recursos que a ferramenta oference, não importa se Oracle, SQL Server, MySQL, Windows, Linux, VMS, etc - Todos os dias procure conhecer algo novo da ferramente velha.
Quero ver!
Na tabela emp, queremos saber o total pago em salários por job, deptno e gran total em um mesmo relatório.
Poderíamos começar com:
SQL> col deptno format a10
SQL> break on deptno skip 2
SQL>
SQL> select *
2 from (
3 select to_char(deptno) deptno, job, sum(sal)
4 from emp
5 group by deptno, job
6 union all
7 select to_char(deptno) deptno, 'Total....' job, sum(sal)
8 from emp
9 group by deptno
10 union all
11 select 'Gran' deptno, 'Total....' job, sum(sal)
12 from emp
13 )
14 order by 1, job
15 /
DEPTNO JOB SUM(SAL)
---------- --------- -------------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
Total.... 8750
20 ANALYST 6000
CLERK 1900
MANAGER 2975
Total.... 10875
30 CLERK 950
MANAGER 2850
SALESMAN 5600
Total.... 9400
Gran Total.... 29025
13 rows selected.
Seria um método. Agora, qual o custo disso? Ahh, eu fiz 3 tablescan contra a tabela emp para: primeiro conseguir o total por job, depois para conseguir o total por deptno e finalmente o "Gran Total". Vejam o custo.
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 615 | 12 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 15 | 615 | 12 (25)| 00:00:01 |
| 2 | VIEW | | 15 | 615 | 11 (19)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | HASH GROUP BY | | 11 | 165 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 210 | 3 (0)| 00:00:01 |
| 6 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
| 8 | SORT AGGREGATE | | 1 | 4 | | |
| 9 | TABLE ACCESS FULL| EMP | 14 | 56 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
769 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
13 rows processed
Sem entrar muito em hash/sort, o resumo foi 3 full table scan em EMP com 9 consistent gets para chegar ao result set. Agora vamos usar o ROLLUP.
SQL> select nvl(to_char(deptno), 'Gran') deptno,
2 nvl(job, rpad('Total',9,'.')) job,
3 sum(sal)
4 from emp
5 group by rollup(deptno, job)
6 /
DEPTNO JOB SUM(SAL)
---------- --------- -------------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
Total.... 8750
20 CLERK 1900
ANALYST 6000
MANAGER 2975
Total.... 10875
30 CLERK 950
MANAGER 2850
SALESMAN 5600
Total.... 9400
Gran Total.... 29025
13 rows selected.
Além de ser mais entendível semanticamente, a performance não se compara. O custo do mesmo resultado é de 1/3 se compararmos com o custo da primeira execução, desta vez o Oracle precisou somente de 1 full e 3 consistent gets para compor o MESMO result set.
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 165 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY ROLLUP| | 11 | 165 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
769 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
13 rows processed
A conclusão que devemos ter em conta a partir deste pequeno exemplo é procurar entender e aprender os recursos que a ferramenta oference, não importa se Oracle, SQL Server, MySQL, Windows, Linux, VMS, etc - Todos os dias procure conhecer algo novo da ferramente velha.
Labels: how to
