Skip to Main Content
  • Questions
  • Table /Index Fragmentation because of delete statement

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Geraldo.

Asked: May 13, 2020 - 1:52 pm UTC

Last updated: May 20, 2020 - 2:27 am UTC

Version: 18.6

Viewed 1000+ times

You Asked

Hello, Ask TOM Team.

We have a database in a production environment. Every 2 weeks we make changes on it. In the QA functional testing we have to insert thousands of rows on the production schema tables. After the tests are ready we DELETE (the same day) those rows (~8k rows). Meanwhile, rows keep arriving from customers using the system.

This test is mandatory because of business changes. I think this is not a good practice but I can't do nothing.

1. Will this practice fragment the tables/indexes?
2. How can I view fragmented tables/ indexes?
3. How to correct them?

Note: all the tables in the schema production once inserted will never be updated or deleted (just in extreme situations we use DML to correct some data).

Thanks in advanced.

Regards,

and Connor said...

I agree - ideally, we don't test in Production unless we have rigorous processes to ensure that it won't impact resource consumption or present invalid data to customers.

Having said that, it is very unlikely that what you are doing it going to cause a fragmentation issue.

We will reuse deleted space, except in various boundary scenarios.

A really good reference that I recommend you read is Richard Foote's presentation on this

https://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf

It will go through these scenarios and how to decide if you need to take action ...But chances are, you won't

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Follow Up

A reader, May 19, 2020 - 6:38 pm UTC

Ok. Thanks for the response.

I thought these delete statements would fragment the table because insert/delete every two weeks.

Having said that, it is very unlikely that what you are doing it going to cause a fragmentation issue. What did you mean with this?

Regards,
Connor McDonald
May 20, 2020 - 2:27 am UTC

Fragmentation with deletes *can* occur, but in specific instances (as detailed in Richard's whitepaper).

The most typical deletes are "delete old, insert new" style operations....which is not one of those specific instances.

Review

A reader, May 20, 2020 - 3:54 am UTC

Ok. I got it.

Thanks,.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database