RollOver one table - having 10millin records - Still 100,000 insertion/sec at bottom - Need to delete 30,00,000 records in 1second after every 30 min

[Ijaz Rashid]

Need little SQL help please!

How to implement Roll-over in one table of database.
I'm having a table, XYZ containing 10million records. I want if record count exceed 10millions, then 30 lack

records from top of table should be deleted very efficiently. XYZ contains 10milloin records and still 1lack

records are inserted in it every second. If i use delete statment like

delete from group_1sec_1 ORDER BY time limit 3000000;

it takes many 10+ minutes to delete records
If i use multiple tables, then TRUNCATE table works.
But i want to avoid multiple tables


Is there any way to physically / logically divide one table into two tables/clusters/diskSpaces...
In this way we can truncate one tables/clusters/diskSpaces... which takes 0.5 sec for 10million records.

Please send me some reference article as well. Thank you for your help.

Ijaz R