Business a few days ago the alarm database table space, is close to critical value, for some reason the table space can't expand, so can only remove useless historical data to release the table space, in the process, produced the following question, welcome leaders help solve,
Table space occupancy rate has reached 98%

One form with exclusive 74 g table space, there are about 160 million rows of data by querying the table, including about 100 million data can be deleted, so decided to first from the table, as described above, table space is insufficient, can't use the first put the useful data in the temporary table, then delete the original table, after the temporary table name method, cannot use TRUNCATE,

Final thought way is to delete by time bit by bit, and then use the alter table table_name shring space to high water level,

But after delete part of the data, to query the table again (select directly, without any conditions) found that the new data and keep the old data shows (below, though this can't say it, but often a new list according to time order inserting data directly select the default is in order to show), and observe for a few hours, found no increase this table takes up space, I think, that a new insert data automatically fill to delete data on the space occupied?

CodePudding user response:

If new data is indeed fill on the original space, is that I don't have to perform high water level of the statement, you just do a table analysis?

CodePudding user response:

reference 1st floor MrLeiQ response:

if new data is indeed fill on the original space, is that I don't have to perform high water level of the statement, you just do a table analysis?

Data analysis is to collect statistics table, the database without finishing,
If you want to defragment or shrink space (large table will slow) or move the table (can lead to failure such as index, it is recommended that business processing, and pay attention to the object and the index of failure to maintain)