Снова про удаление дублей в таблице

← →
Vlad (2004-02-10 13:44) [0]

Доброго всем времени суток.
Вот тут не так давно мне посоветовали для удаления дублирующих записей в таблице использовать конструкцию вида:
delete from table t1 where 1<(select count(*) from table t2 where t2.поле1=t1.поле1 and t2.поле2=t1.поле2)
Такая конструкция обеспечивала уникальность связки полей Поле1+Поле2.
На IB запрос работал прекрасно, но вот на Oracle он удаляет не только дублирующие, но и основные записи.
Подскажите, в чем может быть дело ? Возможно ли как-то решить эту проблему с помощью настроек оптимизатора, или доп. хинтов в запросе ?

← →
Johnmen (2004-02-10 13:54) [1]

Я думаю, что дело в логике работы сервера.
Для ИБ - идет делетом по записям и каждую, удовл-ую условию, удаляет, поэтому для следующей записи селект вернет уже другое значение.
А для Оракла несколько иначе. Поскольку селект делается из модифицируемой таблицы, то, скорее всего, надо отключить "мутацию".

← →
Vlad (2004-02-10 13:58) [2]

> Johnmen © (10.02.04 13:54) [1]

> скорее всего, надо отключить "мутацию".

Спасибо. А каким образом это делается ?

← →
Johnmen (2004-02-10 14:25) [3]

>Vlad © (10.02.04 13:58)

Уже основательно это хозяйство подзабыл...:)
Думаю, что надо смотреть в эту сторону:
1. ХП
2. PRAGMA AUTONOMOUS_TRANSACTION

← →
sniknik (2004-02-10 14:46) [4]

а вроде в Oracle есть чтото вроде номера записи(?) может его както задействовать?

а вообще можно проще (по моему) и в большем количестве SQL серверов работать будет. хотя и за большее количество итераций.
select * into NewTable from (select distinct * from OldTable)
дальше надо удалить старую и переименовать новую.

← →
DenK_vrtz (2004-02-10 16:01) [5]

>Vlad ©

Если копать в сторону "мутации" (Johnmen © (10.02.04 13:54) [1]), то еще вариант к Johnmen © (10.02.04 14:25) [3] это
view и instead of триггер на удаление для этого вью

Попробуй :)

← →
Vlad (2004-02-10 16:22) [6]

> [3] Johnmen © (10.02.04 14:25)

Попробовал указать прагму, но при таком запросе в ХП Оракл вобще откатывает транзакцию с ошибкой

← →
Fay (2004-02-10 17:51) [7]

Уникальность достигается другим способом. Для этого есть UNIQUE.

← →
Johnmen (2004-02-10 17:56) [8]

>Vlad ©

К сожалению ничем конкретно не помогу...
Но общие соображения : в ХП просто делаем обход по селекту в цикле и для каждой записи отдельный делете. Т.е. "развязываем" один запрос на два... Ну и прагму "подпихнуть" :)

← →
panov (2004-02-10 18:17) [9]

Пусть T1 имеет поля Id, f1,f2, где сравнение на дубль идет по полям f1, f2 .

SELECT *
FROM
T1 a
WHERE
a.id NOT IN
(
SELECT MIN(id) mn --,f1,f2
FROM
(
SELECT a.id,a.f1,a.f2
FROM
T1 a,
(
SELECT f1,f2,cnt
FROM
(
SELECT f1,f2,COUNT(*) cnt FROM T1 GROUP BY f1,f2
) b
WHERE cnt>1
) b
WHERE a.f1=b.f1 AND a.f2=b.f2
) b
GROUP BY f1,f2
)
AND
a.id in
(
SELECT a.id
FROM
T1 a,
(
SELECT f1,f2,cnt
FROM
(
SELECT f1,f2,COUNT(*) cnt FROM T1 GROUP BY f1,f2
) b
WHERE cnt>1
) b
WHERE a.f1=b.f1 AND a.f2=b.f2
)

здесь несколько лишних алиасов, так как отлаживалось в MSSQL, но будет работать и в Oracle.
Если нет уникального Id, то вместо него можно использовать RowId.

В самом верхнем селекте зменить SELECT НА DELETE, естественно

← →
panov (2004-02-10 18:18) [10]

В строчке SELECT MIN(id) mn --,f1,f2 --,f1,f2 - комментарий, не нужен.

← →
Sandman25 (2004-02-10 18:21) [11]

delete from table1 where rowid <> (select min(rowid) from table1 t2 where t2.field1=table1.field1 and t2.field2=table2.field2 )

← →
panov (2004-02-10 18:28) [12]

-))))

← →
Sandman25 (2004-02-10 18:36) [13]

[12] panov © (10.02.04 18:28)

Вы вроде бы участвовали в sql.ipps.ru, после того конкурса такие select воспринимаются достаточно легко :)

← →
Vlad (2004-02-10 18:52) [14]

Спасибо всем кто откликнулся.

Я понимаю, что это можно сделать другими способами, но меня в данном случае интересуют не другие способы, а то, почему этот способ не проходит в Oracle и как его заставить работать. Вопрос принципа так сказать :-)
Но видимо не получится :-(

← →
Sandman25 (2004-02-10 18:53) [15]

[14] Vlad © (10.02.04 18:52)

Насколько я знаю, такое проходит только для семейства IB. Во всех остальных SQL СУБД такое не проходит. Так что дело не в Oracle...

Снова про удаление дублей в таблице Найти похожие ветки