Как удалить быстрее?

Удаление одного дубликата из многих - непростое дело, и с таким количеством записей у вас возникает проблема.

Один из вариантов - перевернуть проблему с ног на голову и скопировать записи, которые вы хочу сохранить в новой таблице. Вы можете использовать синтаксис CREATE TABLE AS SELECT DISTINCT ... NOLOGGING , который будет копировать ваши дедуплицированные записи без использования журнала транзакций, что намного быстрее. После заполнения новой таблицы удалите / переименуйте старую и переименуйте новую.

См. http://www.databasejournal.com/features/oracle/article.php/3631361/Managing -Tables-Logging-versus-Nologging.htm

О, и не забудьте поставить УНИКАЛЬНЫЙ индекс на новую таблицу, чтобы этого больше не повторилось.

Мораль этой истории такова ... никогда ​​используйте DELETE для удаления большого количества записей, он ужасающе медленный, потому что он должен хранить все удаленные записи в журнале повторов. Либо копирование и переключение, либо TRUNCATE.

DELETE
FROM    mytable
WHERE   rowid IN
        (
        SELECT  rowid
        FROM    (
                SELECT  rowid, ROW_NUMBER() OVER (ORDER BY dupfield) rn
                FROM    mytable r
                )
        WHERE   rn > 1
        )

или, может быть, даже это:

DELETE
FROM    mytable mo
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    mytable mi
        WHERE   mi.dup_field = mo.dup_field
                AND mi.rowid <> mo.rowid
        )

Оба этих запроса будут использовать довольно эффективное HASH SEMI JOIN , последний будет быстрее, если нет индекса на dup_field .

У вас может возникнуть соблазн скопировать строки, но обратите внимание, что при копировании 2G строк будет сгенерировано гораздо больше информации REDO и UNDO . чем при удалении 11M .

Сначала поместите индекс в столбец или столбцы которые определяют и содержат повторяющиеся значения,

Затем, предположим, что таблица имеет первичный ключ (PK),

  Delete Table T Where PK <> 
        (Select Min(PK) From Table
         Where ColA = T.ColA
           ...  for each column in set defined above
           And ColB = T.ColB)

ПРИМЕЧАНИЕ: можно также использовать Max (PK), все, что вы делаете, это идентифицируете одну запись, чтобы не удалять из каждого набора дубликатов

РЕДАКТИРОВАТЬ: Чтобы исключить широкое использование журнала транзакций и раздела UNDO, вы можете сохранить дублируемые значения во временной таблице, а затем удалить дублированные значения для каждой пары в рамках одной транзакции. ..

Допустим, только один столбец (назовите его ColA, число) определяет дубли ...

   Create Table Dupes (ColA Number)
   Insert Dupes(ColA)
   Select Distinct ColA
   From Table
   Group By ColA
   Having Count(*) > 1

   recordExists Number := 0 ;
   ColAValue Number;
   Select Case When Exists (Select Count(*) From Dupes)
   Then 1 Else 0 End Into recordExists From Dual;


   While recordExists = 1 
      Loop 
         Select (Select Max(ColA) From Dupes) 
         Into ColAValue From Dual;
         Begin Transaction
            Delete Table T
            Where ColA = ColAValue
               And pk <> (Select Min(Pk) From Table 
                          Where ColA = ColAValue);
            Delete Dupes Where ColA = ColAValue;
         Commit Transaction;
         Select Case When Exists (Select Count(*) From Dupes)
         Then 1 Else 0 End Into recordExists From Dual;
      End Loop;

Не проверено, поэтому синтаксис может потребовать обработки ...

Если вы уверены, что не изменяете целостность данных (ссылочную целостность), отключите ограничения (индексы, другие ограничения), выполните удаление, а затем включите ограничения. Сначала вы должны попробовать это, чтобы увидеть, занимает ли обновление индексов при включении меньше времени, чем удаление с ними.

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

Удалять ли существующие строки или создавать правильную новую таблицу и удалять старую быстрее, зависит от множества факторов. 11 миллионов строк - это много, но это всего 0,5% от общего количества строк в таблице. Вполне возможно, что воссоздание и удаление может быть намного медленнее, чем удаление, в зависимости от того, сколько индексов существует в исходной таблице, а также от того, где на страницах данных существуют строки, которые необходимо удалить.

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

Наконец, почему это необходимо эта операция будет "быстрой" ? Это потому, что система должна быть отключена во время выполнения процесса? Вы можете написать процедуру, которая удаляет дубликаты, пока система работает, но не влияет на остальную систему с точки зрения отмены отмены. Мы решили эту проблему в прошлом, сначала написав запрос, который собирает первичные ключи строк, которые должны быть удалены во второй таблице, например:

  INSERT
    INTO RowsToDeleteTable
  SELECT PKColumn
    FROM SourceTable
   WHERE <conditions used to find rows to remove>

CREATE UNIQUE INDEX PK_RowsToDelete ON RowsToDeleteTable (PKColumn);

Затем у нас есть блок PL / SQL, который либо перебирает строки в курсор вроде этого:

BEGIN
  FOR theRow IN (SELECT PKColumn FROM RowsToDeleteTable ORDER BY 1) LOOP
    <delete source table for theRow.PKColumn)
    <optionally wait a bit>
    commit;
  END LOOP;
END;

или делает что-то вроде этого:

BEGIN
  FOR theRow IN (SELECT MIN(PKColumn) FROM RowsToDeleteTable ) LOOP
    <delete source table for theRow.PKColumn)
    <optionally wait a bit>
    DELETE RowsToDeleteTable
     WHERE PKColumn = theRow.PKColumn;
    commit;
  END LOOP;
END;

Цикл и «SELECT MAX», очевидно, менее эффективны, но у него есть то преимущество, что вы можете следить за ходом операции удаления. Мы помещаем в цикл немного кода ожидания, чтобы позволить нам контролировать, насколько интенсивно происходит операция сбора урожая.

Первоначальное создание RowsToDeleteTable происходит очень быстро, и у вас есть преимущество в том, что процесс может длиться столько, сколько вы хотите. В таком случае «дыры», оставленные в ваших экстентах в результате удалений, не будут такими уж плохими, поскольку вы удаляете такой небольшой процент от общих данных.