1

I want to keep the last 20 records in VMR table and delete all other records. The VMR table has 5000000 records and its growing. I also have create_date column which has date datatype in VMR table and it has non unique index. I tried using rownum to delete the records and keep the last 20 records using below query but its taking too much time for deletion. Is there any other way to run the query faster. 

delete from VMR
 where rowid not in 
       (select rowid
          from VMR
         where rownum <=20);
Andrew
  • 3,632
  • 24
  • 64
  • 113

2 Answers2

3

Just to show an alternative:

-- get 20 last records and remember them
create table vmr20 as
  select * from vmr order by create_date desc fetch first 20 rows only;

-- empty table via DDL which should be fastest
truncate vmr;

-- re-insert the last 20 rows
insert into vmr
  select * from vmr20;

-- delete temp table
drop table vmr20;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • A downside of using truncate is that you then cannot `ROLLBACK`. – MT0 Jun 14 '16 at 13:53
  • Yes that's true. So one has to make sure not to Forget to execute the first statement before truncating :-) And there is a short time when the table is completely empty. So this should be done at a time the tables is not being used. – Thorsten Kettner Jun 14 '16 at 14:02
1

Try using ROW_NUMBER()

WITH CTE AS (
SELECT t.*,
       ROW_NUMBER() OVER(ORDER BY create_date DESC) as rnk
FROM VMR t)
DELETE FROM CTE 
WHERE CTE.rnk > 20
sagi
  • 40,026
  • 6
  • 59
  • 84
  • can you please explain the query ? it looks complex to me and i have never used partition in queries – Andrew Jun 14 '16 at 13:22
  • It doesn't actually use partitions, this partition is like a `GROUP BY` clause to rank rows, as you can see I used `1` there and `1` in the order by, since it doesn't matter. This just ranks first row as 1, second as 2 and ETC.. and then with this data, delete everything that is bigger then 20. @Andrew – sagi Jun 14 '16 at 13:27
  • Why include the `PARTITION BY` clause if you are going to partition by a constant literal value? It does not do anything and can just be removed. – MT0 Jun 14 '16 at 13:30
  • I think there is confusion.The first 20 records means from descending you have to consider the past 20 records I ran this query but it also includes the past 20 rows which i dont want to delete. I will edit this in my question. – Andrew Jun 14 '16 at 13:34
  • @Andrew I updated the answer, you didn't include that in your query so I assumed the order doesn't matter. Now it will keep the 20 latest records by `create_date` . – sagi Jun 14 '16 at 13:37
  • @sagi You've linked to the Oracle documentation - in there it shows that the `PARTITION BY` clause is optional. The `ORDER BY` clause is mandatory. – MT0 Jun 14 '16 at 13:37
  • thanks its working now and the query is also much faster. – Andrew Jun 14 '16 at 13:38
  • Yea I checked it while updating the answer @MT0 – sagi Jun 14 '16 at 13:41
  • @sagi i just tried your same query now and i am getting error as ORA-00928: missing SELECT keyword – Andrew Jun 14 '16 at 14:09
  • What changed? @Andrew – sagi Jun 14 '16 at 14:13
  • @sagi Actually previously i remove delete statement from your query and tried with select statement instead just to check records and it works fine. But now when i tried your query with delete statement then its giving an error. Here is the query i tried and it works : WITH CTE AS ( SELECT t.*, ROW_NUMBER() OVER(ORDER BY create_date DESC) as rnk FROM VMR t) Select * FROM CTE WHERE CTE.rnk > 20 – Andrew Jun 14 '16 at 14:15
  • i just check the forums and i think its not possible to use Subquery Factoring/CTE with anything but the SELECT statement in WITH clause:http://stackoverflow.com/questions/6603171/oracle-delete-statement-with-subquery-factoring – Andrew Jun 14 '16 at 14:26