Blog do Marcio Portes

Mais um ponto positivo para performance no 10g. A função de agregação COLLECT agrupa valores de uma coluna. A vantagem aqui é o ganho de performance já que a função é built-in, nativa, portanto evita o switch de contexto entre as engines.

No exemplo: eu criei uma type de tabela varchar2 para receber o valor do agrupamento.
Caso não exista a type o Oracle cria durante a execução com um nome estranho, como visto em negrito abaixo.

SQL> select deptno, collect( ename ) ename from emp group by deptno;

       DEPTNO ENAME
------------- ----------------------------------------------------------------------
           10 SYSTPEEPvmSZz7KjgQAB/AQAZCw==('CLARK', 'KING', 'MILLER')
...

Na sequência, uma pequena função para limpar a type e deixar o resultado "limpo" e finalmente a execução da query com o uso da função COLLECT.

SQL> create or replace type vc2 as table of varchar2(4000);
  2  /

Type created.

SQL> create or replace
  2  function clean_vc2 (
  3                  p_vc2   in vc2,
  4                  p_dl    in varchar2 default ','
  5           ) return varchar2
  6  is
  7          i               number;
  8          l_string        long;
  9          l_delim         varchar2(20);
 10  begin
 11          i := p_vc2.first;
 12          while i is not null
 13          loop
 14             l_string := l_string || l_delim || p_vc2(i);
 15             l_delim := p_dl;
 16             i := p_vc2.next(i);
 17          end loop;
 18          return l_string;
 19  end;
 20  /

Function created.

SQL> show error
No errors.
SQL>
SQL> column ename format a70
SQL>
SQL> select deptno, clean_vc2( cast( collect(ename) as vc2 ) ) ename
  2    from emp
  3   group by deptno
  4  /

       DEPTNO ENAME
------------- ---------------------------------------------------------
           10 CLARK,KING,MILLER
           20 SMITH,JONES,SCOTT,ADAMS,FORD
           30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

3 rows selected.

Labels: how to