Delete multiple records from huge table

Question

I would like to delete a few records (100 thousand records) from a table containing 10 million records in an Oracle database. When I tried to delete the records using the below query

DELETE from TEST where ID in (1,2,3,4,1000,,, etc)

It looks like there is limitation of 1000 values, hence the below error:

SQL Error: ORA-01795: maximum number of expressions in a list is 1000

Is there any query to delete more than 1000 records in a single statement without using PL/SQL?

I am thinking of something like the following - please suggest the correct query:

DELETE  from TEST where ID in (1,2,3,4,1000)
AND
DELETE  from TEST where ID in (1001,1002);

Also please advise whether the delete operation will be faster with or without using the primary key.

The link mentioned above is for multiple select statement. But i am looking for delete operation. — Karthick88it, Aug 22 '18 at 07:45
It's the same question though, really. You basically have to use a table or a CTE to store the ids, that's the only option. — Tim Biegeleisen, Aug 22 '18 at 07:46
Are the number sequence then u can create table then select from it as much as u want — Moudiz, Aug 22 '18 at 07:48
[This answer](https://stackoverflow.com/a/42487575) and [this answer](https://stackoverflow.com/a/9084247) from the duplicate can be applied to DELETE as well — a_horse_with_no_name, Aug 22 '18 at 08:22

score 2 · Answer 1 · answered Aug 22 '18 at 07:43

The simplest option (from my point of view) is to store that huge list into a table, and then

delete from test
  where id in (select id from a_table_that_contains_huge_list_of_entries)

Will it be faster with a primary key involved? I suppose that it is not a primary key that matters, but whether something is indexed or not (doesn't have to be a primary key). But, will Oracle really use that index? Who knows; depends on the optimizer. It may choose that full table scan is more appropriate. Check the explain plan.

score 1 · Answer 2 · edited Aug 22 '18 at 08:18

1

If the data are sequence u can do the below

Create table seqnumbrs as
select level as id from dual connect by level <= 10000;

Delete from tab1 were id in (select id from seqnumbrs);

You can create pk or index on the table for performane

edited Aug 22 '18 at 08:18

Littlefoot

107,599
14
32
52

answered Aug 22 '18 at 07:54

Moudiz

7,065
19
73
147

Delete multiple records from huge table

2 Answers2