Delete and index 1M rows

delete_uses_index_1M.sql

CREATE TABLE test_index_delete(
  num  NUMBER)
  
INSERT INTO test_index_delete
SELECT 1000238674 + LEVEL
FROM DUAL
CONNECT BY LEVEL <= 1000000

commit

select * from test_index_delete order by num asc

begin
  for i in 1..100
  loop
    delete from test_index_delete where num < 1000238711 + i*10;
  end loop;
end;

2.636

rollback

create index test_index_delete_idx on test_index_delete(
  num asc)
tablespace hydra_index

0.04

begin
  for i in 1..100
  loop
    delete from test_index_delete where num < 1000238711 + i*10;
  end loop;
end;

rollback