Delete rows using table partition

Question

I have a huge log table which is very busy in saving the user logs. My task is to schedule a job which keeps the last 3 days log or last 50k rows (whichever is greater) and deletes the rest. Shall this can be done through TABLE PARTITION.? I can't do this through DELETE statement which is very time expensive and stopping rows to be inserted. The table contains log_time as VARCHAR.

Thanks.

May be it is ugly, but you can rename your table as Table_Histrory_20150129, then create new table and copy those 50k rows into it? — Giorgi Nakeuri, Jan 29 '15 at 07:23
Why store log_time as varchar? That requires double the space as datetime and complicates this task. — Dan Guzman, Jan 29 '15 at 12:45

score 1 · Answer 1 · answered Jan 29 '15 at 07:41

I can suggest you this simple solution:

create every 3 days table named like log-2015-01-28 and write all logs between 2015-01-28 - 2015-01-30 to this table
after 2015-01-30 create new table log-2015-01-28-31 and write all new rows to it
DROP table log-2015-01-28 after 2 days

I think it must work very fast

score 0 · Answer 2 · answered Jan 29 '15 at 07:55

0

create trigger on insert

while trigger check the spaceused of the table .

if > 50k then delete the 'oldest row'

answered Jan 29 '15 at 07:55

Dudi Konfino

1,126
2
13
24

I think trigger is not good idea, because we get slowing down the `INSERT` operation – stepozer Jan 29 '15 at 10:01

Delete rows using table partition

2 Answers2