Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Uma.

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

Last updated: September 16, 2020 - 1:47 am UTC

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*.


Rating

  (1 rating)

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

Comments

Super!

A reader, September 15, 2020 - 11:00 pm UTC

Loved the example! Thanks so much.
Connor McDonald
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