Удалить повторяющиеся строки в таблице Oracle

Одним из способов является использование этого запроса:

SELECT principal,
    MAX(apellido) AS apellido,
    MAX(nombre) AS nombre 
FROM table_name
GROUP BY principal
HAVING COUNT(*) = 1
ORDER BY principal;

Вы можете использовать оконные функции. Логика будет такой:

select principal, apellido, nombre
from (select t.*, count(*) over (partition by principal) as cnt
      from t
     ) t
where cnt = 1;

Это вернет строки, где principal равно NULL; такие строки исключены в NOT EXISTS.

Однако, самый быстрый метод, если у вас есть первичный ключ и индекс на (principal, pk), это, вероятно,:

select principal, apellido, nombre
from t
where not exists (select 1
                  from t t2
                  where t2.principal = t.principal and t2.pk <> t.pk
                 );

Вы можете использовать агрегированную функцию COUNT для удаления всех дублированных строк.

select PRINCIPAL, APELLIDO, NOMBRE,
count(*) over (partition by PRINCIPAL) dup_cnt
from tab

P A N    DUP_CNT
- - - ----------
a b c          2
a l m          2
b c d          1
c d e          1

COUNT подсчитывают строки для каждого уникального ключа, определенного в предложении PARTITION BY.

В последнем запросе выбираются только уникальные строки, то есть строки с DUP_CNT = 1

with dedup as (
select PRINCIPAL, APELLIDO, NOMBRE,
count(*) over (partition by PRINCIPAL) dup_cnt
from tab)
select PRINCIPAL, APELLIDO, NOMBRE
from dedup
where dup_cnt = 1

Примечание: используя ROW_NUMBER вместо COUNT, вы можете сделать дедупликация, т.е. вы позволяете одной из дублированных строк в результате и удаляете дубликаты.

Обратите внимание , что этот метод требует сортировки таблицы (WINDOW SORT), которая может быть тяжелой для больших таблиц. В этом случае метод, использующий NOT EXISTS, может дать лучшую производительность, так как он преобразуется и выполняется как антихеш-соединение - HASH JOIN RIGHT ANTI.

select principal, apellido, nombre 
from tab t
where not exists 
(select null
 from tab 
 where  principal = t.principal and rowid <> t.rowid
)

Необходимо соблюдать осторожность, если столбец дедупликации (principal) имеет значение NULL. В отличие от первого решения с COUNT not exusts оставляет все null в результате. Если это не требуется, необходимо добавить фильтр:

 and t.principal is not NULL

Если у вас есть индекс в столбце pricipal, оптимальный план выполнения выглядит следующим образом

--------------------------------------
| Id  | Operation             | Name | 
--------------------------------------
|   0 | SELECT STATEMENT      |      |    
|*  1 |  HASH JOIN RIGHT ANTI |      |     
|   2 |   INDEX FAST FULL SCAN| IDX  |  
|*  3 |   TABLE ACCESS FULL   | TAB  |  
--------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("PRINCIPAL"="T"."PRINCIPAL")
       filter(ROWID<>"T".ROWID)
   3 - filter("T"."PRINCIPAL" IS NOT NULL)

Иметь подзапрос, который возвращает значения PRINCIPAL, существующие более одного раза:

select *
from tablename
where PRINCIPAL not in (select PRINCIPAL from tablename
                        group by PRINCIPAL
                        having count(*) > 1)