Delete trillion records from a large table

Try to clean a large table (non partition, over 5 trillion records) on a old Oracle 10g Database. [TYPE_ID] in the PK. First try:

DELETE FROM LARGE_TABLE_T
WHERE TYPE_ID = 199
AND ROWNUM < 1000;

COMMIT;

999 rows deleted. Elapsed: 00:00:11.42		90/second

DELETE FROM LARGE_TABLE_T
WHERE TYPE_ID = 199
AND ROWNUM < 10000;

COMMIT;

9999 rows deleted. Elapsed: 00:00:19.54		526/second

DELETE FROM LARGE_TABLE_T
WHERE TYPE_ID = 199
AND ROWNUM < 100000;

COMMIT;

99999 rows deleted. Elapsed: 00:04:47.26	348/second


DELETE FROM LARGE_TABLE_T
WHERE TYPE_ID = 199
AND ROWNUM < 20000;

COMMIT;

19999 rows deleted. Elapsed: 00:00:40.08	500/second

DELETE FROM LARGE_TABLE_T
WHERE TYPE_ID = 199
AND ROWNUM < 5000;

COMMIT;

4999 rows deleted. Elapsed: 00:00:10.04		499/second


DELETE FROM LARGE_TABLE_T
WHERE TYPE_ID = 199
AND ROWNUM < 9000;

COMMIT;

8999 rows deleted. Elapsed: 00:00:21.52		409/second

DELETE FROM LARGE_TABLE_T
WHERE TYPE_ID = 199
AND ROWNUM < 50000;

COMMIT;

49999 rows deleted. Elapsed: 00:01:51.70	447/second

I wrote a quick loop with commit every 10000 rows:

BEGIN
LOOP 
  DELETE FROM LARGE_TABLE_T WHERE TYPE_ID = 199 AND ROWNUM < 10000;
  COMMIT;
END LOOP;
COMMIT;
END;

Not bad, Eh?
I let it run on the sqlplus from server and keep try different way.

Found another way faster solution after few tweaks:

DECLARE

  CURSOR c_delete_rowids
  IS
	SELECT ROWID
    FROM LARGE_TABLE_T
	WHERE TYPE_ID = 199
	AND ROWNUM <= 1000000;

  v_count number(4) default 0;

BEGIN

  FOR rec IN c_delete_rowids
  LOOP
  
    delete from LARGE_TABLE_T where rowid = rec.rowid;
	v_count := v_count + 1;

	IF v_count >= 1000 then
		v_count := 0;
		COMMIT;
	END IF;

  END LOOP;

  COMMIT;

END;
/

Which delete 1,000,000 records in 1 minute.

SQL> 13:26:21 SQL> 13:26:21 SQL> 13:26:21 SQL> 13:26:21   2  13:26:21   3  13:26:21   4  13:26:21   5  13:26:21   6  13:26:21   7  13:26:21   8  13:26:21   9  13:26:21  10  13:26:21  11  13:26:21  12  13:26:21  13  13:26:21  14  13:26:21  15  13:26:21  16  13:26:21  17  13:26:21  18  13:26:21  19  13:26:21  20  13:26:21  21  13:26:21  22  13:26:21  23  13:26:21  24  13:26:21  25  13:26:21  26  13:26:21  27  13:26:21  28  13:26:21  29  13:26:21  30  13:26:21  31
PL/SQL procedure successfully completed.

Elapsed: 00:01:02.75

To protect the archive log not run crazy, I call it from a shell script and sleep 100 every loop. 1.5 trillion records has been delete around 3 days. Super!!!