You mean you haven't heard of the fast_delete command?!



...




That's because it doesn't exist. Deleting lots of rows is going to take a while. The only faster way is truncate - but that removes everything!

You've got a parallel hint, which may help it run faster. But only if you've enabled parallel DML!

Ensure you run this first:

ALTER SESSION ENABLE PARALLEL DML;

If that's not helping, you could look into:

Disabling indexes on the table

But, of course, any queries needing and using those indexes will be slower while your delete runs. So you're just trading one slow statement for (lots of) others. And you'll have to rebuild them afterwards which will take (possibly a looooooong) time.

Indexing date_key

Assuming it isn't already indexed. And you're deleting a small enough fraction of the table that execution plan will use it instead of a full table scan.

Obviously, this contradicts the previous suggestion.

If neither of these help enough, you may need to look into more radical solutions.

Such as saving the data you want to keep in a temporary table. Then dropping the current table and renaming the temporary one. e.g.:

create table tmp as select ...data you want to keep... from old_tab;
drop old_tab;
rename tmp to old_tab;
-- run grants, indexes etc. that were on the original table
...

But you need an outage to do this. Unless you're good with your users getting a stack of errors when you drop the current table ;)

And there's the risk of forgetting to migrate indexes or whatever from the old table to the new...