Utilizando cursores

Olá, pessoal!

Neste artigo vamos simular a utilização dos diversos tipos de cursores no Oracle, por final vamos comparar a velocidade destes. Utilizaremos a extração de uma tabela com uma massa de dados de 50 mil registros para que possamos fazer um teste considerável.

01. Criar tabela

Crie a estrutura da tabela

create table TB_PESSOA
(
  IDPESSOA       INTEGER not null,
  NOME           VARCHAR2(40) not null,
  DATANASCIMENTO DATE not null,
  ALTURA         NUMBER(5,2),
  PESO           NUMBER(5,2)
)

02. Inserir registros

Vamos inserir 50 mil registros na tabela para criarmos um volume de dados considerável para nossos testes.

declare

v_id_pessoa tb_pessoa.idpessoa%type;
v_nome      tb_pessoa.nome%type;
v_dt_nascimento tb_pessoa.datanascimento%type;
begin

v_nome := 'Joao da Silva';
v_dt_nascimento := to_date('01/01/1900');

  for dd in 1..50000 loop   
     select nvl(max(p.idpessoa),0)+1
       into v_id_pessoa
       from tb_pessoa p;      
    insert into tb_pessoa(idpessoa,nome,datanascimento)                    values(v_id_pessoa,v_nome,v_dt_nascimento);
  end loop;
end;
commit;

03. Criação dos objetos

Vamos criar as procedures com as seguintes formas de cursores. Vejam que em todas temos as variáveis v_dt_inicio e v_dt_fim, que farão o controle do tempo de processamento da procedure.

3.1. O cursor contido nesta procedure é o mais usual. O cursor de tipo pré-declarado

Procedure PRC_TST_CURSOR

create or replace procedure prc_tst_cursor is

v_dt_inicio timestamp;
v_dt_fim    timestamp;

cursor cr_pessoa is select * from tb_pessoa;

v_pessoa cr_pessoa%rowtype;

begin

v_dt_inicio := systimestamp;

  open cr_pessoa;
   loop
   fetch cr_pessoa into v_pessoa;
   exit when cr_pessoa%notfound;
     null;
   end loop;
  close cr_pessoa;

v_dt_fim := systimestamp;

dbms_output.put_line('Tempo processamento: '||to_char(v_dt_fim -v_dt_inicio,'dd/mm/yyyy hh24:mi:ss.ff'));

end;

3.2. Esta procedure abaixo contém uma forma de cursor bastante utilizada também. O cursor do tipo for é um dos de mais fácil de utilização.

Procedure PRC_TST_FOR
create or replace procedure prc_tst_for is

v_dt_inicio timestamp;
v_dt_fim    timestamp;

begin

v_dt_inicio := systimestamp;

  for dd in (select p.idpessoa from tb_pessoa p) loop
     null;
   end loop;

v_dt_fim := systimestamp;

dbms_output.put_line('Tempo processamento: '||to_char(v_dt_fim - v_dt_inicio,'dd/mm/yyyy hh24:mi:ss.ff'));

end;

3.3. Nesta procedure abaixo, temos a extração da informação através de um bulk collection. Forma menos usual, alguns front end não suportam este tipo de cursor, que é o caso do FORMS.

Procedure PRC_TST_BULK_COUNT

create or replace procedure prc_tst_bulk_count is

v_dt_inicio timestamp;
v_dt_fim    timestamp;

type typ_pessoa is table of tb_pessoa%rowtype;
v_tb_pessoa typ_pessoa;

begin

v_dt_inicio := systimestamp;

  select *
    bulk collect into v_tb_pessoa
    from tb_pessoa;

   for dd in 1..v_tb_pessoa.count loop
     null;
   end loop;

v_dt_fim := systimestamp;

dbms_output.put_line('Tempo processamento: '||to_char(v_dt_fim -v_dt_inicio,'dd/mm/yyyy hh24:mi:ss.ff'));

end;

3.4. Nesta procedure abaixo, temos a variação do cursor de bulk collection, onde a diferença está apenas na execução do loop.

Procedure PRC_TST_BULK_FIRST_LAST

create or replace procedure prc_tst_bulk_first_last is

v_dt_inicio timestamp;
v_dt_fim    timestamp;

type typ_pessoa is table of tb_pessoa%rowtype;
v_tb_pessoa typ_pessoa;

begin

v_dt_inicio := systimestamp;

  select *
    bulk collect into v_tb_pessoa
    from tb_pessoa;

   for dd in v_tb_pessoa.first.. v_tb_pessoa.last loop
     null;
   end loop;

v_dt_fim := systimestamp;

dbms_output.put_line('Tempo processamento: '||to_char(v_dt_fim - v_dt_inicio,'dd/mm/yyyy hh24:mi:ss.ff'));

end;

Fonte: http://imasters.com.br/artigo/12960/oracle/utilizando-cursores-no-oracle

Removendo registros duplicados de uma tabela usando o ROWID

Olá,

Antes de demonstrar como remover linhas duplicadas de uma tabela, irei comentar um pouco sobre o que é ROWID (estendido) no Oracle. Na verdade, os ROWIDS não são endereços na memória ou em disco; em vez disso, eles são identificadores que o Oracle pode utilizar para calcular o local de uma linha em uma tabela. Então, localizar uma linha de uma tabela fazendo o uso do ROWID é a maneira mais rápida de encontra-la. Um ROWID não é armazenado explicitamente como um valor de coluna e, quando uma linha é adicionada a uma tabela, o mesmo é gerado para identificar o local exclusivo dessa linha no banco de dados. Um ROWID estendido precisa de 10 bytes de armazenamento em disco, é exibido com 18 caracteres e consiste nos seguintes componentes:

• Data Object number: É designado a cada objeto de dados, como uma tabela ou um índice, quando criado e é exclusivo no banco de dados.

• Relative file number: É exclusivo para cada arquivo de um tablespace.

• Block Number: Representa a posição do bloco que contém a linha no arquivo de dados.

• Row number: Identifica a posição do slot de diretório de linha no cabeçalho do bloco.

Internamente, o número do objeto de dados precisa de 32 bits, o número do arquivo relativo precisa de 10 bits, o número do bloco precisa de 22 bits e o número da linha precisa de 16 bits que totalizam 80 bits ou 10 bytes.

Podemos perceber que um ROWID estendido é exibido por meio de um schema de codificação de base 64, que usa seis posições para o número do objeto de dados, três posições para o número do arquivo relativo, seis posições para o número do bloco e três posições para o número da linha. Este schema de codificação de base 64 usa caracteres de A-Z, a-z, 0-9, +, e / como mostrado no exemplo abaixo:

Irei criar um tabela para exemplificar.

SQL> create table t1 (id number);

Tabela criada.

SQL> insert into t1 select rownum rn from dual connect by rownum < style="" face="courier new" size="3">
SQL> commit;

Validação completa.

SQL> select rowid,id from t1;

ROWID                      ID
------------------ ----------
AAOifoAAIAAAPhPAAA          1
AAOifoAAIAAAPhPAAB          2
AAOifoAAIAAAPhPAAC          3
AAOifoAAIAAAPhPAAD          4
AAOifoAAIAAAPhPAAE          5
AAOifoAAIAAAPhPAAF          6
AAOifoAAIAAAPhPAAG          7
AAOifoAAIAAAPhPAAH          8
AAOifoAAIAAAPhPAAI          9
AAOifoAAIAAAPhPAAJ         10

10 linhas selecionadas.

Selecionarei o registro de id=1 para ver o valor da pseudo-coluna ROWID

SQL> select substr(rowid,1,6) "object",
 2          substr(rowid,7,3) "file",
 3          substr(rowid,10,6) "block",
 4          substr(rowid,16,3) "row"
 5          from t1 where id = 1;

object fil block  row
------ --- ------ ---
AAOifo AAI AAAPhP AAA

No resultado do SQL acima podemos ver que:

• AAOifo é o número do objeto de dados

• AAI é o número do arquivo relativo

• AAAPhP é o número do bloco

• AAA é o número da linha para o ID = 1

OBS: As versões anteriores ao Oracle 8 usavam o formato ROWID restrito na qual usava internamente apenas seis bytes e não continha o número do objeto de dados.

Concluindo, como um segmento pode residir apenas em um tablespace, o servidor Oracle pode determinar o tablespace que contém uma linha usando o número do objeto de dados, o número do arquivo relativo no tablespace é usado para localizar o arquivo, o número do bloco é usado para localizar o bloco que contém a linha e o número da linha é usado para localizar a entrada de diretório da linha.

O pacote DBMS_ROWID está disponível a partir do Oracle 8 e fornece os procedimentos para criar e interpretar os ROWIDS permitindo que as informações de ROWID sejam decodificadas em informações de arquivo/bloco/identificador de objeto. Abaixo, irei exemplificar algumas das funções deste pacote:

Selecionarei o registro de id=1 para ver o valor da pseudo-coluna ROWID.

SQL> select rowid,id from t1 where id = 1;

ROWID                      ID
------------------ ----------
AAOifoAAIAAAPhPAAA          1

DBMS_ROWID.ROWID_TYPE retorna o tipo de um ROWID (0 significa restrito e 1 significa estendido).

SQL> select dbms_rowid.rowid_type('AAOifoAAIAAAPhPAAA') from dual;

DBMS_ROWID.ROWID_TYPE('AAOIFOAAIAAAPHPAAA')
-------------------------------------------
                                          1

DBMS_ROWID.ROWID_OBJECT extrai o número do objeto de dados de um ROWID.

SQL> select dbms_rowid.rowid_object('AAOifoAAIAAAPhPAAA') from dual;

DBMS_ROWID.ROWID_OBJECT('AAOIFOAAIAAAPHPAAA')
---------------------------------------------
                                      3811304

Através do número do objeto retornado, podemos ver que objeto é esse.

SQL> select object_name,object_type from user_objects
2   where object_id = 3811304;

OBJECT_NAME        OBJECT_TYPE
------------------ -------------------
T1                 TABLE

DBMS_ROWID.ROWID_RELATIVE_FNO extrai o número do arquivo relativo de um ROWID.

SQL> select dbms_rowid.rowid_relative_fno('AAOifoAAIAAAPhPAAA') from dual;

DBMS_ROWID.ROWID_RELATIVE_FNO('AAOIFOAAIAAAPHPAAA')
---------------------------------------------------
                                                  8

Através do número retornado, podemos ver em que arquivo de dados o objeto está armazenado.

SQL> select tablespace_name,file_name,file_id from dba_data_files
2   where relative_fno=8;

TABLESPACE_NAME             FILE_NAME                             FILE_ID
--------------------------- ---------------------------------- ----------
USERS                       /u02/oradata/BD1/users01.dbf                8

DBMS_ROWID.ROWID_BLOCK_NUMBER é usado para extrair o número de bloco de determinado ROWID.

SQL> select dbms_rowid.rowid_block_number('AAOifoAAIAAAPhPAAA') from dual;

DBMS_ROWID.ROWID_BLOCK_NUMBER('AAOIFOAAIAAAPHPAAA')
---------------------------------------------------
                                              63567

Através do número do bloco retornado, podemos também encontrar o objeto.

SQL> select segment_type,owner||'.'||segment_name
  2  from dba_extents
  3  where file_id = 8 and 63567 between block_id
  4  and block_id+blocks -1;

SEGMENT_TYPE       OWNER||'.'||SEGMENT_NAME
------------------ ----------------------------------------
TABLE              LEGATTI.T1

DBMS_ROWID.ROWID_ROW_NUMBER extrai o número de linha de um ROWID (0 significa a primeira linha da tabela).

SQL> select dbms_rowid.rowid_row_number('AAOifoAAIAAAPhPAAA') from dual;

DBMS_ROWID.ROWID_ROW_NUMBER('AAOIFOAAIAAAPHPAAA')
-------------------------------------------------
                                                0

Podemos também extrair também da view dba_segments informações de armazenamento de um segmento.

SQL> select s.owner,t.ts#,s.header_file,s.header_block
  2  from v$tablespace t, dba_segments s
  3  where s.segment_name='T1'
  4  and t.name = s.tablespace_name;

OWNER                       TS# HEADER_FILE HEADER_BLOCK
-------------------- ---------- ----------- ------------
LEGATTI                       8           8        63563

Agora, irei inserir alguns registros na minha tabela criada anteriormente e adicionar alguns registros que já existem atualmente na tabela.

SQL> insert into t1 values (1);

1 linha criada.

SQL> insert into t1 values (10);

1 linha criada.

SQL> insert into t1 values (10);

1 linha criada.

SQL> insert into t1 values (10);

1 linha criada.

SQL> commit;

Validação completa.

Podemos ver abaixo os registros duplicados.

SQL> select rowid,id from t1;

ROWID                      ID
------------------ ----------
AAOifoAAIAAAPhPAAA          1
AAOifoAAIAAAPhPAAB          2
AAOifoAAIAAAPhPAAC          3
AAOifoAAIAAAPhPAAD          4
AAOifoAAIAAAPhPAAE          5
AAOifoAAIAAAPhPAAF          6
AAOifoAAIAAAPhPAAG          7
AAOifoAAIAAAPhPAAH          8
AAOifoAAIAAAPhPAAI          9
AAOifoAAIAAAPhPAAJ         10
AAOifoAAIAAAPhPAAK          1
AAOifoAAIAAAPhPAAL         10
AAOifoAAIAAAPhPAAM         10
AAOifoAAIAAAPhPAAN         10

14 linhas selecionadas.

Podemos perceber que os registros com id's 1 e 10 estão duplicados.

SQL> select id,count(*) from t1 group by id having count(*) > 1;

      ID   COUNT(*)
---------- ----------
         1          2
        10          4

A query abaixo irá retornar todos os registros da tabela sem nenhuma duplicação, isso porque irei fazer um subselect na qual será retornado sempre as linhas com menor ROWID encontrado para cada coluna ID da minha tabela.

SQL> select rowid,id from t1 A
2   where rowid = (select min(rowid) from t1 B where a.id = b.id);

ROWID                      ID
------------------ ----------
AAOifoAAIAAAPhPAAA          1
AAOifoAAIAAAPhPAAB          2
AAOifoAAIAAAPhPAAC          3
AAOifoAAIAAAPhPAAD          4
AAOifoAAIAAAPhPAAE          5
AAOifoAAIAAAPhPAAF          6
AAOifoAAIAAAPhPAAG          7
AAOifoAAIAAAPhPAAH          8
AAOifoAAIAAAPhPAAI          9
AAOifoAAIAAAPhPAAJ         10

10 linhas selecionadas.

Agora é só usar o mesmo SQL apenas trocando o comando SELECT pelo DELETE, e o operador de = para >.

SQL> delete from t1 A
2   where rowid > (select min(rowid) from t1 B where a.id = b.id);

4 linhas deletadas.

SQL> commit;

Validação completa.

Pronto. Agora não há mais registros duplicados na tabela.

SQL> select rowid,id from t1;

ROWID                      ID
------------------ ----------
AAOifoAAIAAAPhPAAA          1
AAOifoAAIAAAPhPAAB          2
AAOifoAAIAAAPhPAAC          3
AAOifoAAIAAAPhPAAD          4
AAOifoAAIAAAPhPAAE          5
AAOifoAAIAAAPhPAAF          6
AAOifoAAIAAAPhPAAG          7
AAOifoAAIAAAPhPAAH          8
AAOifoAAIAAAPhPAAI          9
AAOifoAAIAAAPhPAAJ         10

10 linhas selecionadas.

Fonte: http://eduardolegatti.blogspot.com/2007/12/removendo-registros-duplicados-de-uma.html