Some things about the delete operation of ORACLE

In practice, you often encounter the need to periodically delete data that has expired and release server resources. For example, we define a timer task, which starts at 2 o'clock in the morning, and this task is to delete data from 1 months ago to free up space.

At first, we could write:

Delete from table_name where T_time < To_date (' 2015-04-01 ', ' yyyy-mm-dd ')

This is one of the simplest operations to delete data based on conditions. At first glance there is no problem, but when the amount of data is very large, one months on the thousands data, it will occupy a large number of database resources. 2 o'clock in the morning execution of the statement, it may be until 12 o'clock noon to complete, so that during this time, the query operation will become extremely slow, usually half a minute to return the results of the query, this time will become more than 10 minutes. This is obviously undesirable.

　　

In a delete operation, the SQL statement first scans the table or index to find the records that match the criteria, and then deletes them. This process consumes a lot of CPU resources and generates a lot of Io, and also generates a lot of undo data. Delete about 10,000 pieces of data at once, almost to generate more than 10,000 blocks of data read, will be nearly 3MB of redo log.

Drop and truncate operations consume much less resources than delete. Also delete 10,000 data, delete will generate more than 10,000 blocks of data read, DRO will generate more than 1100 blocks of data read, truncate is only more than 600 blocks of data read.

In general, we delete the data in the table in order to free up space. The delete operation does not free up space, removes data from the table, and frees up space to be used only for the table, and cannot be used by other objects. Delete Does not work if you want to make room for other objects by deleting the data.

The drop and truncate operation frees up space for other objects to use.

One might ask that the drop and truncate operations consume more system resources and speed than the delete, but truncate clears all the data in the table, and the drop is deleted together with the table, so it's not possible to implement the function that the blog started by periodically deleting data from the table last month. In this case we are going to use the table partition.

Depending on the time field T_time creates a range partition in the form of one partition per month:

Partition by range (T_time)

(

Partition Table_name_2015_1 values less than (to_date (' 01/01/2015 ', ' dd/mm/yyyy ')),

Partition Table_name_2015_2 values less than (to_date (' 01/01/2015 ', ' dd/mm/yyyy ')),

Partition Table_name_2015_3 values less than (to_date (' 01/01/2015 ', ' dd/mm/yyyy ')),

Partition Table_name_2015_4 values less than (to_date (' 01/01/2015 ', ' dd/mm/yyyy ')),

Partition Table_name_2015_5 values less than (to_date (' 01/01/2015 ', ' dd/mm/yyyy ')),

Partition Table_name_2015_6 values less than (to_date (' 01/01/2015 ', ' dd/mm/yyyy ')),

Partition Table_name_2015_7 values less than (to_date (' 01/01/2015 ', ' dd/mm/yyyy ')),

Partition Table_name_2015_8 values less than (to_date (' 01/01/2015 ', ' dd/mm/yyyy ')),

Partition Table_name_2015_9 values less than (to_date (' 01/01/2015 ', ' dd/mm/yyyy ')),

Partition table_name_2015_10 values less than (to_date (' 01/01/2015 ', ' dd/mm/yyyy ')),

Partition Table_name_2015_11 values less than (to_date (' 01/01/2015 ', ' dd/mm/yyyy ')),

Partition Table_name_2015_12 values less than (to_date (' 01/01/2015 ', ' dd/mm/yyyy ')

);

So when we delete data by month, we can:

ALTER TABLE TABLE_NAME TRUNCATE PARTITION Table_name_2015_1;

Or:

ALTER TABLE table_name DROP partition Table_name_2015_1;

Others will ask, truncate deleted data will not leave the undo log, data rollback and other operations. In fact, in the actual development, historical data will generally be migrated to another database to save, which is called as an offline database or historical database.

As long as we delete the data in the tablespace, we first back up the data to the history database by means of the program or database, and delete the data in the table space.

In general, only the case of big data will be used in the table partition, only tens of thousands of data or thousands of units of data, the use of traditional delete operations can be, performance is not much difference.

Some things about the delete operation of ORACLE