How will DELETE WHERE sales_id IN (...); , given that test_old has no index?
Of course, we don't know if there's an index on sales.bill_date, since OP's
only response has been another "tell me how to do it".
On 7/20/22 13:32, Adrian Klaver wrote:
On 7/20/22 11:16 AM, Ron wrote:
But the first thing he does is drop test_old.
Then:
Create table test_old as select * from sales where bill_date<now()
-interval '1 year';
At that point you could do either:
Delete from sales where sales_id in (select sales_id from test_old);
or
DELETE FROM sales WHERE bill_date<NOW() -INTERVAL '1 year';
On 7/20/22 09:52, Adrian Klaver wrote:
On 7/20/22 01:28, Ron wrote:
On 7/20/22 00:08, Rama Krishnan wrote:
Hi All,
I am doing purge activity my sales table contains 5M records I am
going to delete more than 1 year data (which was 3M) records so it was
running more so I want to do batch wise deletion through plsql
created or replace function data_purge() returns void as$$
Declare
Begin
Drop table test_old;
Create table test_old as select * from sales where bill_date<now()
-interval '1 year';
Delete table sales where sales_id in (select sales_id from test_old;
End;
$$ language plpgsql;
Kindly guide me
Why not just DELETE FROM sales WHERE bill_date<NOW() -INTERVAL '1 year';?
Because it looks like the OP is saving the old records in test_old.
--
Angular momentum makes the world go 'round.
