[ТЕМА ЗАКРЫТА]
Опции темы
04.08.2010 14:05  
John Doe
Цитата:
Автор: Denis Popov
Прислал:

Под строками- дубликатами понимаются строки таблицы, значения одного или нескольких полей которых одинаковы, неуникальны. Требуется найти такие строки и удалить лишние дубликаты, оставив только уникальные значения.

Создание и заполнение таблицы с дублирующимися значениями.

create table TEST_DUPLICATE (
duplicate_id number(9)
, value number(9)
, constraint PK_TEST_DUPLICATE primary key (duplicate_id)
);
insert into TEST_DUPLICATE (duplicate_id, value)
select rownum, dbms_random.value * 100
from ALL_OBJECTS
where rownum <= 100;

Поиск строк - дубликатов:

select *
from TEST_DUPLICATE
where value in (
select value
from TEST_DUPLICATE
having count(*) > 1
group by value
)
order by value;

Удаление строк дубликатов:

-- Вариант с NOT IN.
-- Остаются строки с минимальным значением поля DUPLICATE_ID среди дубликатов.
delete TEST_DUPLICATE
where duplicate_id not in (
select min(duplicate_id)
from TEST_DUPLICATE
group by value
);
-- То же самое через NOT EXISTS.
delete TEST_DUPLICATE d
where not exists (
select 1
from (select min(duplicate_id) duplicate_id
from TEST_DUPLICATE
group by value
) d2
where d.duplicate_id = d2.duplicate_id
);
-- Вариант с использованием аналитической функции row_number()
delete TEST_DUPLICATE
where duplicate_id in (
select duplicate_id
from (select duplicate_id
, row_number() over (partition by value order by null) rw
from TEST_DUPLICATE
)
where rw > 1
);
-- В верхнем запросе нельзя сказать, какая именно строка из дубликатов останется,
-- из-за условия "ORDER BY NULL". Для управления этим процессом
-- можно отсортировать выборку в пределах каждого неуникального значения
-- так, чтобы строка, которую хотелось бы оставить, была первой.
-- Например, оставить строки с минимальным значением первичного ключа:
delete TEST_DUPLICATE
where duplicate_id in (
select duplicate_id
from (select duplicate_id
, row_number() over (partition by value order by duplicate_id) rw
from TEST_DUPLICATE
)
where rw > 1
);


Если в таблице нет уникального ключа или индекса, по которому можно отсеивать дубликаты (в примере используется уникальность поля TEST_DUPLICATE.duplicate_id), то можно использовать псевдо столбец ROWID, значения которого гарантированно уникальны, т.е., например, первый запрос на удаление дубликатов через NOT IN может выглядеть так:

delete TEST_DUPLICATE
where rowid not in (
select min(rowid)
from TEST_DUPLICATE
group by value
);



Добавление от Деев И. Удаление дубликатов через EXCEPTIONS INTO :

-- тестовая таблица
create table drop_test
( id number,
txt varchar2(30)
);
-- таблица исключений
create table our_exceptions(row_id rowid,
owner varchar2(30),
table_name varchar2(30),
constraint varchar2(30));
-- заполнение 3-мя дублями по каждой строке
insert into drop_test select object_id, object_name from user_objects where rownum <3001;
insert into drop_test select object_id, object_name from user_objects where rownum <3001;
insert into drop_test select object_id, object_name from user_objects where rownum <3001;

-- попытка создать constraint pk
alter table drop_test
add constraint drop_test_pk primary key (id)
exceptions into our_exceptions;

-- удаляем из таблицы исключений то, что будем оставлять в основной таблице
delete from our_exceptions where row_id in
(select min(b.row_id)
from drop_test a, our_exceptions b
where a.rowid=b.row_id
group by a.id);

select count(*) from our_exceptions; --6000

-- удалим дубли из нашей таблицы
delete from drop_test where rowid in
(select row_id from our_exceptions);

-- теперь constraint будет создан успешно
alter table drop_test
add constraint drop_test_pk primary key (id);

-- чистка
drop table drop_test;
drop table our_exceptions;
FAQ / Oracle / Поиск и удаление строк - дубликатов [SQL.Ru]
 
04.08.2010 14:38  
NewBot
Прогонял скрипты, дубликаты находятся и удаляются... но индекс так и не пересоздается, говорит о дубликте...
 
04.08.2010 14:57  
John Doe
Странно, удаляются и удаляются и удаляются? Ты там коммиты нигде не забыл?
 
04.08.2010 14:59  
NewBot
Сам ничего не пойму, захжу под SUPERMAG,
commit; не забываю
 
04.08.2010 15:05  
NewBot
и каждый раз количество отобранных строк разнае +- 1-2 строки :unknw_mini:
 
04.08.2010 15:26  
Mtirt
Может быть имеет смысл выгнать всех из базы?
И убедиться, что из базы все вышли?
Остановить почтовик, кассовый сервер...
 
04.08.2010 15:50  
NewBot
Может я чего не так делаю?
В базе никого, почтовик остановлен
Захожу под SUPERMAG в базу, прогоняю

create table TEST_DUPLICATE (
duplicate_id number(9)
, value number(9)
, constraint PK_TEST_DUPLICATE primary key (duplicate_id)
);
insert into TEST_DUPLICATE (duplicate_id, value)
select rownum, dbms_random.value * 100
from ALL_OBJECTS
where rownum <= 100;

Затем

select *
from TEST_DUPLICATE
where value in (
select value
from TEST_DUPLICATE
having count(*) > 1
group by value
)
order by value;

Затем

delete TEST_DUPLICATE
where duplicate_id not in (
select min(duplicate_id)
from TEST_DUPLICATE
group by value
);


выбрано скажем 66 строк, удалено 30
пишу commit;
фиксация прошла

после этого проделываю тоже самое, но с таблицей TEST_DUPLICATE123
уже выбрано 68 строк , удалено 33
и так каждый раз... кол-во строк меняется
 
04.08.2010 17:35  
YuraZ
Цитата:
Сообщение от NewBot
Может я чего не так делаю?
В базе никого, почтовик остановлен
Захожу под SUPERMAG в базу, прогоняю

create table TEST_DUPLICATE (
duplicate_id number(9)
, value number(9)
, constraint PK_TEST_DUPLICATE primary key (duplicate_id)
);
insert into TEST_DUPLICATE (duplicate_id, value)
select rownum, dbms_random.value * 100
from ALL_OBJECTS
where rownum <= 100;

Затем

select *
from TEST_DUPLICATE
where value in (
select value
from TEST_DUPLICATE
having count(*) > 1
group by value
)
order by value;

Затем

delete TEST_DUPLICATE
where duplicate_id not in (
select min(duplicate_id)
from TEST_DUPLICATE
group by value
);


выбрано скажем 66 строк, удалено 30
пишу commit;
фиксация прошла

после этого проделываю тоже самое, но с таблицей TEST_DUPLICATE123
уже выбрано 68 строк , удалено 33
и так каждый раз... кол-во строк меняется
Это же пример. Он сделан для обкатки идеи. И в нем используются тестовые данные генерируемые функцией dbms_random.value * 100. Поэтому каждый раз разное кол-во удаленных записей.

Попробуй это:
Код:
delete smpayordersvat
where rowid not in (select min(t.rowid)
                    FROM smpayordersvat t
                    GROUP by t.doctype, t.id, t.vatrate);
Сделай только бэкап на всякий случай. А вообще проверь налоги в административном модуле.
 
05.08.2010 07:26  
NewBot
на
delete smpayordersvat
where rowid not in (select min(t.rowid)
FROM smpayordersvat t
GROUP by t.doctype, t.id, t.vatrate);

отвечает:
ORA-01502: индекс "SUPERMAG.SMCPAYORDERSVAT_PK" или часть такого индекса находится в неиспользуемом состоянии...
 
05.08.2010 07:37  
OlegON
Цитата:
alter table smpayordersvat disable primary key cascade;
drop index SUPERMAG.SMCPAYORDERSVAT_PK; (если даст)
и оптимизатора пока не запускай, а то он все вернуть назад постарается.
А вот после операций - обязательно.
Думаю, что констрейнт надо будет дропать и тогда уже убивать индекс и искать дупы. Вот еще вариант.
 
 


Опции темы


Часовой пояс GMT +3, время: 19:00.

Все в прочитанное - Календарь - RSS - - Карта - Вверх 👫 Яндекс.Метрика
Форум сделан на основе vBulletin®
Copyright ©2000 - 2016, Jelsoft Enterprises Ltd. Перевод: zCarot и OlegON
В случае заимствования информации гипертекстовая индексируемая ссылка на Форум обязательна.