Para melhor visualização, recomendo resolução de no mínimo 1280 x 800 e navegador Mozilla Firefox


quarta-feira, 12 de dezembro de 2007

Removendo registros duplicados de uma tabela usando o ROWID

Por Eduardo Legatti

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 <=10;

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




Abaixo, 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

Abaixo, 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 where object_id = 3811304;

OBJECT_NAME        OBJECT_TYPE
------------------ -------------------
T1                 TABLE
 
Abaixo, 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 where relative_fno=8;

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


Abaixo, 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


Abaixo, 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 extrair também da view DBA_SEGMENTS informações de armazenamento (storage) 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 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 where rowid > (select min(rowid) from t1 B where a.id = b.id);

4 linhas deletadas.

SQL> commit;

Validação completa.

Pronto. 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.




4 comentários:

Anônimo disse...

Fala Eduardo!
Cara me deu um branco agora pouco,numa situação que cai para selecionar registros com 2 campos duplicados numa tabela.Como o cenário não me pareceu familiar,fiquei comfuso,mas ai dei uma "googada" e cai no seu blog.Dei uma lida,belo artigo mano!

Anônimo disse...

Olá,

Apenas para registrar que tive um problema de duplicação de registros em meu banco oracle, e quando achei este blog, fiquei muito feliz em saber que existem pessoas que além de ter um ótimo conhecimento técnico, possuem um bom sentimento de apoio aos menos experientes.
Muito obrigado pela ajuda.
maycon.cesar.reis@gmail.com

Gilton Guma disse...

Lembrando que dessa forma ele mantêm apena a primeira linha repetida e eliminas as demais. O que seria um problemas caso queira manter as mais atualizadas.

Acredito que utilizando desta forma seria mais interessante:

delete from t1 A where rowid < (select max(rowid) from t1 B where a.id = b.id);

Por favor corrijam-me se eu estiver errado! ;)

Eduardo Legatti disse...

Olá Gilton,

Depende. A idéia seria manter realmente o primeiro registro inserido. A duplicações ocorreram depois, certo? ;-)

No mais, cada um decide qual registro irá ficar.

Abraços e até mais ...

Postagens populares