Removendo registros duplicados de uma tabela no Oracle usando o ROWID
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:
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;
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 AAANo 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 = 1OBS: 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 1DBMS_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')
-------------------------------------------
1DBMS_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')
---------------------------------------------
3811304Atravé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 TABLEDBMS_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')
---------------------------------------------------
8Atravé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 8DBMS_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')
---------------------------------------------------
63567Atravé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.T1DBMS_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')
-------------------------------------------------
0Podemos 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 63563Agora, 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 4A 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.