rob@XE:11g2> -- #1: IN/NOT IN Method Session

rob@XE:11g2> set autotrace traceonly explain

rob@XE:11g2> DELETE FROM test_dedup2

  2  WHERE (alpha_code, num_code) IN

  3  (

  4    SELECT alpha_code, num_code

  5    FROM test_dedup2

  6    GROUP BY alpha_code, num_code

  7    HAVING COUNT(*) > 1

  8  )

  9  AND (timestamp, alpha_code, num_code) NOT IN

 10  (

 11    SELECT MAX(timestamp), alpha_code, num_code

 12    FROM test_dedup2

 13    GROUP BY alpha_code, num_code

 14    HAVING COUNT(*) > 1

 15  );

170261 rows deleted.

Elapsed: 00:17:28.40

Execution Plan

==========================================================

Plan hash value: 882209868

-------------------------------------------------------------------------------------------------------

| Id  | Operation                       | Name           | Rows | Bytes |TempSpc|  Cost(%CPU)| Time    |

-------------------------------------------------------------------------------------------------------

|   0 | DELETE STATEMENT                |                |    1 |    66 |       |  3878   (2)| 00:00:47|

|   1 |  DELETE                         | TEST_DEDUP2    |      |       |       |            |         |

|   2 |   MERGE JOIN ANTI NA            |                |    1 |    66 |       |  3878   (2)| 00:00:47|

|   3 |    SORT JOIN                    |                |    1 |    41 |       |  1657   (2)| 00:00:20|

|   4 |     NESTED LOOPS                |                |      |       |       |            |         |

|   5 |      NESTED LOOPS               |                |    1 |    41 |       |  1656   (2)| 00:00:20|

|   6 |       VIEW                      | VW_NSO_1       |  247K|  3862K|       |   333   (4)| 00:00:04|

|*  7 |        FILTER                   |                |      |       |       |            |         |

|   8 |         SORT GROUP BY           |                |    1 |  3862K|       |   333   (4)| 00:00:04|

|   9 |          INDEX FAST FULL SCAN   | IX2_DEDUP_CONC |  247K|  3862K|       |   323   (1)| 00:00:04|

|* 10 |       INDEX RANGE SCAN          | IX2_DEDUP_CONC |    1 |       |       |     2   (0)| 00:00:01|

|  11 |      TABLE ACCESS BY INDEX ROWID| TEST_DEDUP2    |    1 |    25 |       |     4   (0)| 00:00:01|

|* 12 |    SORT UNIQUE                  |                |  247K|  6034K|    17M|  2220   (2)| 00:00:27|

|  13 |     VIEW                        | VW_NSO_2       |  247K|  6034K|       |   435   (3)| 00:00:06|

|* 14 |      FILTER                     |                |      |       |       |            |         |

|  15 |       SORT GROUP BY             |                |  247K|  6034K|       |   435   (3)| 00:00:06|

|  16 |        TABLE ACCESS FULL        | TEST_DEDUP2    |  247K|  6034K|       |   425   (1)| 00:00:06|

-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   7 - filter(COUNT(*)>1)

  10 - access("ALPHA_CODE"="ALPHA_CODE" AND "NUM_CODE"="NUM_CODE")

  12 - access(INTERNAL_FUNCTION("ALPHA_CODE")=INTERNAL_FUNCTION("ALPHA_CODE") AND

              INTERNAL_FUNCTION("NUM_CODE")=INTERNAL_FUNCTION("NUM_CODE") AND

              INTERNAL_FUNCTION("TIMESTAMP")=INTERNAL_FUNCTION("MAX(TIMESTAMP)"))

       filter(INTERNAL_FUNCTION("NUM_CODE")=INTERNAL_FUNCTION("NUM_CODE") AND

              INTERNAL_FUNCTION("ALPHA_CODE")=INTERNAL_FUNCTION("ALPHA_CODE") AND

              INTERNAL_FUNCTION("TIMESTAMP")=INTERNAL_FUNCTION("MAX(TIMESTAMP)"))

  14 - filter(COUNT(*)>1)

Note - dynamic sampling used for this statement (level=2)

rob@XE:11g2> -- #2: ROWID Method Session

rob@XE:11g2> set autotrace traceonly explain

rob@XE:11g2> DELETE FROM test_dedup2 X

 2  WHERE X.rowid <>

 3   (SELECT MAX(Y.rowid) FROM test_dedup2 Y

 4    WHERE Y.alpha_code = X.alpha_code

 5    AND Y.num_code = X.num_code);

170261 rows deleted.

Elapsed: 00:00:10.90

Execution Plan

===============================================================

Plan hash value: 1584747792

---------------------------------------------------------------------------------------------------

| Id  | Operation                | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------

|   0 | DELETE STATEMENT         |                |   232K|    12M|       |  1641   (2)| 00:00:20 |

|   1 |  DELETE                  | TEST_DEDUP2    |       |       |       |            |          |

|*  2 |   HASH JOIN              |                |   232K|    12M|  9088K|  1641   (2)| 00:00:20 |

|   3 |    VIEW                  | VW_SQ_1        |   232K|  6356K|       |   332   (4)| 00:00:04 |

|   4 |     SORT GROUP BY        |                |   232K|  6356K|       |   332   (4)| 00:00:04 |

|   5 |      INDEX FAST FULL SCAN| IX2_DEDUP_CONC |   232K|  6356K|       |   323   (1)| 00:00:04 |

|   6 |    TABLE ACCESS FULL     | TEST_DEDUP2    |   232K|  6356K|       |   424   (1)| 00:00:06 |

---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("ITEM_1"="X"."ALPHA_CODE" AND "ITEM_2"="X"."NUM_CODE")

       filter("X".ROWID<>"MAX(Y.ROWID)")

Note - dynamic sampling used for this statement (level=2)

rob@XE:11g2> -- #3: Analytic Method Session

rob@XE:11g2> set autotrace traceonly explain

ROB@XE:11g2> DELETE FROM test_dedup2

  2  WHERE ROWID IN

  3    (SELECT bad_clone FROM

  4       (SELECT

  5          rowid AS bad_clone,

  6          ROW_NUMBER() OVER

  7            (PARTITION BY alpha_code||num_code ORDER BY alpha_code||num_code)

  8             AS sibling_id

  9        FROM test_dedup2

 10       )

 11     WHERE sibling_id > 1

 12    );

170261 rows deleted.

Elapsed: 00:00:13.56

Execution Plan

===========================================================

Plan hash value: 870249232

----------------------------------------------------------------------------------------------------

| Id  | Operation                 | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------------

|   0 | DELETE STATEMENT          |                |   356K|    13M|       |  4616   (1)| 00:00:56 |

|   1 |  DELETE                   | TEST_DEDUP2    |       |       |       |            |          |

|*  2 |   HASH JOIN RIGHT SEMI    |                |   356K|    13M|  8368K|  4616   (1)| 00:00:56 |

|   3 |    VIEW                   | VW_NSO_1       |   356K|  4182K|       |  3103   (1)| 00:00:38 |

|*  4 |     VIEW                  |                |   356K|  8714K|       |  3103   (1)| 00:00:38 |

|   5 |      WINDOW SORT          |                |   356K|  9759K|    13M|  3103   (1)| 00:00:38 |

|   6 |       INDEX FAST FULL SCAN| IX2_DEDUP_CONC |   356K|  9759K|       |   323   (1)| 00:00:04 |

|   7 |    TABLE ACCESS FULL      | TEST_DEDUP2    |   356K|  9759K|       |   425   (1)| 00:00:06 |

----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access(ROWID="BAD_CLONE")

   4 - filter("SIBLING_ID">1)

Note - dynamic sampling used for this statement (level=2)