Benefits And Considerations For Logical Deletion Of Historical Records In Large Tables (Doc ID 2881171.1)

Last updated on AUGUST 26, 2022

Applies to:

Goal

In database schemas where some tables have high volume of inserts every day and the older data is not accessed and hence is safe to be removed, some performance overhead of large tables can be avoided by deleting older records. This reduces the rate of growth of such tables reduces the overhead of large tables and can help performance as follows:

1. Improves response times of queries which perform index scans and/or table scans on such large tables.
2. Improves overall performance by reducing storage I/O by reducing the the size of working set of data.

The second optimization is applicable in cases where the size of the indexes of such large tables is of similar order as the size of the InnoDB buffer pool.

Archiving of data comprises of two tasks:

1. Copying records of tables belonging to a schema satisfying a given criteria, and
2. Deleting the copied records.

As the copying and deleting data is performed at record level (which is logical) and not at the file level (which is physical), it is very closely tied to the data model. Hence, such archiving task would need to be developed and implemented based on the table definitions and also on the dependencies that exist among the tables in the schema. Furthermore, the deletion of records needs to be performed with some caution.

The goal of this document is to list:

• the benefits of purging older records where applicable,
• the precautions and techniques to employ while deleting records, and
• considerations to rebuild tables after the deletion of records is completed.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.