ORA-01452 : Супермаг Плюс (Супермаг 2000)

Автор: 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;

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
и так каждый раз... кол-во строк меняется

alter table smpayordersvat disable primary key cascade;
drop index SUPERMAG.SMCPAYORDERSVAT_PK; (если даст)