1

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.

William Robertson
  • 15,273
  • 4
  • 38
  • 44
Karthick88it
  • 601
  • 2
  • 12
  • 28
  • The link mentioned above is for multiple select statement. But i am looking for delete operation. – Karthick88it Aug 22 '18 at 07:45
  • 1
    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 –  Aug 22 '18 at 08:22

2 Answers2

2

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.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
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

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
Moudiz
  • 7,211
  • 22
  • 78
  • 156