Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Uma.

Asked: September 11, 2020 - 3:56 pm UTC

Answered by: Connor McDonald - Last updated: September 16, 2020 - 1:47 am UTC

Category: Database Administration - Version: 12.1.

Viewed 100+ times

You Asked

Hello,

This is more of a fundamental question, sorry i dont have any test cases.

Does table fragmentation also imply index fragmentation for the same table. ?

and we said...

No. Let's consider a very simple hypothetical.

My table has data inserted in pairs of A and B, so the data looks like (each line being a row in a table)

COLUMN
=======
A
B
A
B
A
B
A
B
A
B
A
B

Now I have an index on "COLUMN". That index stores data in order, to it will look like: (each letter being a key in the index)

AAAAAAAABBBBBBBB

Now I delete all of the "A" values for that column.

The table look like this: ("-" is empty space)

-
B
-
B
-
B
-
B
-
B
-
B
-
B


but my index looks like this:

-----------BBBBBBBB

and all that colocated free space will be reused by subsequent inserts.

So a fragment table *might* yield a fragmented index, it might *not*.


and you rated our response

  (1 rating)

Reviews

Super!

September 15, 2020 - 11:00 pm UTC

Reviewer: A reader

Loved the example! Thanks so much.
Connor McDonald

Followup  

September 16, 2020 - 1:47 am UTC

glad we could help

More to Explore

Administration

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