Попытка оптимизировать * случайный * запрос в Oracle SQL

Вы можете сделать это с помощью одного оператора delete:

delete from observations o
    where (o.nplate, o.odatetime) in (select nplace, odatetime
                                      from (select o2.nplate, o2.odatetime
                                            from observations o2
                                            order by DBMS_RANDOM.VALUE
                                           ) o2
                                      where rownum <= v_cuantos
                                     );

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

Что вам определенно нужно, так это индекс на OBSERVATIONS, чтобы позволить DELETE с доступом по индексу.

 CREATE INDEX cuantos ON OBSERVATIONS(nplate, odatetime);

Выполнение процедуры приведет к одному FULL TABLE SCAN в таблице OBSERVATIONS и одному INDEX ACCESSдля каждой удаленной записи.

Для ограниченного числа удаленных повторных стержней он будет вести себя так же, как набор DELETE, предложенный в другом ответе; для большего количества удаленных записей прошедшее время будет линейно масштабироваться с количеством удалений.

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

Другими словами для удаления 100 тыс. строк потребуется ок. 1/4 часа.

Чтобы удалить На 1 миллион строк вам понадобится 2 3/4 часа.

Вы видите, удаляя в этой шкале первую часть задачи - FULL SCAN вашей таблицы пренебрежимо, это займет всего несколько минут. Единственная возможность получить приемлемое время отклика в этом случае - это переключить логику на одно выражение DELETE, как предлагается в других ответах.

Это поведение также называется правилом: «Строка за строкой медленно за медленностью» (т.е. обработка в цикле работает нормально, но только с ограниченным количеством записей).

Попробуй это. test на MSSQL надеется, что он будет работать и на Oracle. пожалуйста, отметьте статус.

CREATE OR REPLACE PROCEDURE DEL_OBS(cuantos number) IS
begin

   DELETE OBSERVATIONS  FROM OBSERVATIONS 
   join (select * from  OBSERVATIONS ORDER BY VALUE ) as i on
            nplate=i.nplate AND 
            odatetime=i.odatetime  AND 
            i.ROWNUM<=cuantos;
End DEL_OBS;

Поскольку вы говорите, что nplate и odatetime являются первичным ключом observations, я предполагаю, что проблема здесь:

SELECT * FROM (
  SELECT * 
  FROM observations 
  ORDER BY DBMS_RANDOM.VALUE)
  WHERE ROWNUM<=cuantos;

Невозможно предотвратить полное сканирование observations, а также большую сортировку, если это большая таблица.

Вам нужно изменить выполняемый код. По далеко, самый простой способ изменить код - это изменить исходный код и перекомпилировать его.

Однако существуют способы находятся для изменения кода, который выполняется без изменения исходного кода. Вот два:

(1) Используйте DBMS_FGAC, чтобы добавить политику, которая определяет, участвуете ли вы в этой процедуре, и, если да, добавьте предикат в таблицу observations следующим образом:

AND rowid IN 
   ( SELECT obs_sample.rowid 
     FROM observations sample (0.05) obs_sample)

(2) Используйте DBMS_ADVANCED_REWRITE, чтобы переписать ваш запрос, изменяя:

FROM observations

.. к ..

FROM observations SAMPLE (0.05)

Использование текста вашего запроса в политике перезаписи должно предотвратить его влияние на другие запросы к таблице observations.

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

Используйте псевдоколонку ROWID для фильтрации столбцов:

CREATE OR REPLACE PROCEDURE DEL_OBS(
  cuantos number
)
IS
BEGIN
  DELETE FROM OBSERVATIONS
  WHERE ROWID IN (
    SELECT rid
    FROM   (
      SELECT ROWID AS rid
      FROM   observations
      ORDER BY DBMS_RANDOM.VALUE
    )
    WHERE ROWNUM < cuantos
  );
END del_obs;

Если у вас есть индекс в таблице, он может использовать быстрое полное сканирование индекса:

SQL Fiddle

Настройка схемы Oracle 11g R2:

CREATE TABLE table_name ( id ) AS
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 50000;

Запрос 1: нет индекса:

DELETE FROM table_name
WHERE ROWID IN (
  SELECT rid
  FROM   (
    SELECT ROWID AS rid
    FROM   table_name
    ORDER BY DBMS_RANDOM.VALUE
  )
  WHERE ROWNUM <= 10000
)

План выполнения:

----------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes  | Cost | Time     |
----------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT               |            |     1 |     24 |  123 | 00:00:02 |
|   1 |   DELETE                       | TABLE_NAME |       |        |      |          |
|   2 |    NESTED LOOPS                |            |     1 |     24 |  123 | 00:00:02 |
|   3 |     VIEW                       | VW_NSO_1   | 10000 | 120000 |  121 | 00:00:02 |
|   4 |      SORT UNIQUE               |            |     1 | 120000 |      |          |
| * 5 |       COUNT STOPKEY            |            |       |        |      |          |
|   6 |        VIEW                    |            | 19974 | 239688 |  121 | 00:00:02 |
| * 7 |         SORT ORDER BY STOPKEY  |            | 19974 | 239688 |  121 | 00:00:02 |
|   8 |          TABLE ACCESS FULL     | TABLE_NAME | 19974 | 239688 |   25 | 00:00:01 |
|   9 |     TABLE ACCESS BY USER ROWID | TABLE_NAME |     1 |     12 |    1 | 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 5 - filter(ROWNUM<=10000)
* 7 - filter(ROWNUM<=10000)

Запрос 2 Добавить индекс:

ALTER TABLE table_name ADD CONSTRAINT tn__id__pk PRIMARY KEY ( id )

Запрос 3 с индексом:

DELETE FROM table_name
WHERE ROWID IN (
  SELECT rid
  FROM   (
    SELECT ROWID AS rid
    FROM   table_name
    ORDER BY DBMS_RANDOM.VALUE
  )
  WHERE ROWNUM <= 10000
)

План выполнения:

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows | Bytes  | Cost | Time     |
---------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT               |            |    1 |     37 |   13 | 00:00:01 |
|   1 |   DELETE                       | TABLE_NAME |      |        |      |          |
|   2 |    NESTED LOOPS                |            |    1 |     37 |   13 | 00:00:01 |
|   3 |     VIEW                       | VW_NSO_1   | 9968 | 119616 |   11 | 00:00:01 |
|   4 |      SORT UNIQUE               |            |    1 | 119616 |      |          |
| * 5 |       COUNT STOPKEY            |            |      |        |      |          |
|   6 |        VIEW                    |            | 9968 | 119616 |   11 | 00:00:01 |
| * 7 |         SORT ORDER BY STOPKEY  |            | 9968 | 119616 |   11 | 00:00:01 |
|   8 |          INDEX FAST FULL SCAN  | TN__ID__PK | 9968 | 119616 |    9 | 00:00:01 |
|   9 |     TABLE ACCESS BY USER ROWID | TABLE_NAME |    1 |     25 |    1 | 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 5 - filter(ROWNUM<=10000)
* 7 - filter(ROWNUM<=10000)

Если вы не можете сделать это в одном операторе SQL с использованием ROWID, вы можете переписать существующую процедуру, чтобы использовать точно такие же запросы, но использовать оператор FORALL:

CREATE OR REPLACE PROCEDURE DEL_OBS(cuantos number)
IS
  TYPE obs_tab IS TABLE OF observations%ROWTYPE;
begin
  SELECT *
  BULK COLLECT INTO obs_tab
  FROM   (
    SELECT * FROM observations ORDER BY DBMS_RANDOM.VALUE
  )
  WHERE ROWNUM<=cuantos;

  FORALL i IN 1 .. obs_tab.COUNT
    DELETE FROM OBSERVATIONS
    WHERE  nplate    = obs_tab(i).nplate
    AND    odatetime = obs_tab(i).odatetime; 
END del_obs;