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

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Geraldo.

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

Answered by: Connor McDonald - Last updated: May 20, 2020 - 2:27 am UTC

Category: Database Development - Version: 18.6

Viewed 100+ 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 we 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

and you rated our response

  (2 ratings)

Reviews

Follow Up

May 19, 2020 - 6:38 pm UTC

Reviewer: A reader

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

Followup  

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

May 20, 2020 - 3:54 am UTC

Reviewer: A reader

Ok. I got it.

Thanks,.

More to Explore

Administration

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