Базы данных Oracle - статьи
Том Кайт: об удалении дубликатов
Перевод:
Источник: журнал Oracle Magazine, March-April 2006
(http://www.oracle.com/technology/oramag/oracle/06-mar/o26asktom.html).
Том Кайт удаляет дубликаты, задает порядок срабатывания триггеров и объясняет, почему объектная привилегия SELECT разрешает блокирование.
Вопрос. Пожалуйста, помогите мне написать SQL-запрос для избавления от дубликатов, которые ниже помечены как XXX. Дублирующие записи для меня – записи, которые имеют одинаковые значения в столбцах SA и SB, независимо от их позиции. (Первичный ключ – объединенные столбцы SA и SB).
SA SB --- --- A B A C A D B A XXX (DUPLICATE ) B C C A XXX (DUPLICATE) C B XXX (DUPLICATE) C D
Ответ. Есть несколько способов сделать это, я продемонстрирую свой подход, но на будущее я предлагаю вам добавить ограничение целостности, которое в дальнейшем будет запрещать ввод дубликатов.
А пока для удаления из таблицы дубликатов я могу использовать некоторые встроенные функции и аналитику.
Используя аналитику, я буду разделять, разбивать, данные с помощью функций LEAST(SA,SB) и GREATEST(SA,SB). То есть, сначала я найду меньшее значение столбцов SA и SB, а затем большее значение, чтобы строки со значениями A, B находились в одном и том же разделе, что и строки со значениями B, A. После разбиения данных с помощью этих функций я сортирую строки по значениям столбцов SA и SB и применяю к каждой строке функцию row_number (). Все, что я должен сделать затем, – найти любые строки, у которых номер строки row_number () <> 1 (не первая строка в разделе – в каждом разделе мне нужна только одна строка), и удалить их.
Этот SQL-код похож на показанный в листинге 1.
SQL> delete from t 2 where rowid in 3 ( select rid 4 from ( 5 select rowid rid, sa, sb, 6 row_number() over 7 (partition by least(sa,sb), 8 greatest(sa,sb) order by sa, sb) rn 9 from t 10 ) 11 where rn <> 1 12 ) 13 / 3 rows deleted.
Листинг 1. Удаление дубликатов из таблицы t.
На будущее я рекомендую вам изначально предотвращать в системе ввод дубликатов. Один из способов добиться этого – определить на уровне таблицы проверочное ограничение целостности: SQL> alter table t 2 add constraint sa_less_than_equal_sb 3 check (sa <= sb); Table altered.
Затем создайте составной первичный ключ по столбцам SA и SB. Таким образом вы никогда не будете иметь A, B и B, A; поскольку B, A изначально не будет вставлено. Если вы все же хотите иметь в первичном ключе значение B, A; вы можете использовать индекс по ключу-функции:
SQL> create unique index t_idx 2 on t(least(sa,sb), 3 greatest(sa,sb)); Index created.
Это – уникальный индекс с отсортированными значениями столбцов таблицы – в первом столбце индекса сначала помещается наименьшее значение столбцов SA и SB, а во втором столбце индекса – наибольшее значение столбцов SA и SB.
Эта тема вылилась в довольно интересное интерактивное обсуждение, когда кто-то спросил: "А что насчет трех столбцов или N столбцов"? Вы можете прочитать исходное обсуждение на сайте asktom.oracle.com, там же рассматривались альтернативные операторы DELETE, которые также позволяют удалять дубликаты.
