how to delete rows from large table

Solution depends on the following:

  1. Space availability?
  2. Is table partitioned?
  3. What percentage of the table will be deleted?
  4. Number of indexes?
  • Solution based on CTAS (create temporally table):

CREATE TABLE <temp table> AS


FROM <source table>

WHERE <filter>


DROP <source table>;

RENAME < temp table> to <source table>;

  • Solution based on rowid and blocks:

< in progress>