Создать процедуру, которая будет удалять из таблицы все дублирующиеся строки. Имя таблицы и схема — параметры. Задачу решить двумя способами:

1. Средствами языка SQL с использованием только базового варианта команды SELECT (без разделов WITH, UNION, START WITH, CONNECT BY, подзапросов и пр.).
2. При помощи пакета DBMS_SQL.

Сравнить эффективность программ.

CREATE OR REPLACE PROCEDURE distinctDSQL (
    tableName VARCHAR2, userName VARCHAR2 := USER
  ) IS
  fullName VARCHAR2(255) := userName ||'.'|| tableName;
BEGIN
  EXECUTE IMMEDIATE
       'DECLARE'
    || '  TYPE tableRowsT IS TABLE OF '|| fullName ||'%ROWTYPE;'
    || '  tableRows tableRowsT;'
    || 'BEGIN'
    || '  SELECT DISTINCT * BULK COLLECT INTO tableRows FROM '|| fullName ||';'
    || '  DELETE FROM '|| fullName ||';'
    || '  FORALL i IN tableRows.FIRST..tableRows.LAST'
    || '    INSERT INTO '|| fullName ||' VALUES tableRows(i);'
    || 'END;';
END;
/

CREATE OR REPLACE PROCEDURE distinctDBMS_SQL (
    tableName VARCHAR2, userName VARCHAR2 := USER
  ) IS
  fullName VARCHAR2(255) := userName ||'.'|| tableName;
  cursorID PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
  rowsAdded PLS_INTEGER;
BEGIN
  DBMS_SQL.PARSE(
     cursorID
   ,    'DECLARE'
     || '  TYPE tableRowsT IS TABLE OF '|| fullName ||'%ROWTYPE;'
     || '  tableRows tableRowsT;'
     || 'BEGIN'
     || '  SELECT DISTINCT * BULK COLLECT INTO tableRows FROM '|| fullName ||';'
     || '  DELETE FROM '|| fullName ||';'
     || '  FORALL i IN tableRows.FIRST..tableRows.LAST'
     || '    INSERT INTO '|| fullName ||' VALUES tableRows(i);'
     || 'END;'
   , DBMS_SQL.NATIVE
  );
  rowsAdded := DBMS_SQL.EXECUTE(cursorID);
  DBMS_SQL.CLOSE_CURSOR(cursorID);
END;
/

Сравним время выполнения первого варианта и второго для таблиц с различным количеством столбцов и строк. Сравнивать будем по следующему шаблону:

DECLARE
  startTimestamp TIMESTAMP;
BEGIN
  startTimestamp := SYSTIMESTAMP;
  -- distinctDSQL('Название Таблицы');     (1) -- Выполним сначала для Dynamic SQL,
  -- distinctDBMS_SQL('Название Таблицы'); (2) -- а потом — для DBMS_SQL.
  DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP - startTimestamp); -- Длительность выполнения.
END;
/

Тест #1

Используем таблицу Duplicates, состояющую из одного столбца (Value) и из строк вида:

VALUE  UNION ALL  VALUE   UNION ALL  VALUE   ...
-----             -----              -----
    1                 1                  1
                      2                  2
                                         3

Генерируются эти строки с помощью следующего пакета:

CREATE OR REPLACE PACKAGE generate IS
  PROCEDURE rows (n SIMPLE_INTEGER := 0);
END;
/

CREATE OR REPLACE PACKAGE BODY generate IS
  TYPE rowsT IS TABLE OF SIMPLE_INTEGER;

  PROCEDURE rows (n SIMPLE_INTEGER := 0) IS
    rowsTable rowsT := rowsT();
    maxValue  SIMPLE_INTEGER := 1;
    rowsCount SIMPLE_INTEGER := 0;
  BEGIN
    rowsTable.EXTEND(n);
    <<mainLoop>> WHILE TRUE LOOP
      FOR i IN 1..maxValue LOOP
        EXIT mainLoop WHEN rowsCount >= n;
        rowsCount := rowsCount + 1;
        rowsTable(rowsCount) := i;
        DBMS_OUTPUT.PUT_LINE(i);
      END LOOP;
      maxValue := maxValue + 1;
    END LOOP mainLoop;

    FORALL i IN 1..n
      SAVE EXCEPTIONS
      INSERT INTO Duplicates VALUES (rowsTable(i));
  END;
END;
/

Как можно заметить, Dynamic SQL работает быстрее, чем DBMS_SQL.

Тест #2

Используем таблицы TestEmps, TestEmps2, TestEmps3, содержащие, соответственно 109, 11990 и 275793 строки. Эти таблицы являются результатом копирования строк в таблице Employees посредством CONNECT BY.

CREATE TABLE TestEmps AS  --     109 строк
  SELECT * FROM Employees;

CREATE TABLE TestEmps2 AS --  11 990 строк
  SELECT * FROM TestEmps
  CONNECT BY LEVEL <= 2;

CREATE TABLE TestEmps3 AS -- 275 793 строки
  SELECT * FROM TestEmps
  START WITH Employee_ID > 185
  CONNECT BY LEVEL <= 3;

Тут, опять же, DBMS_SQL показал себя несколько хуже.