Skip to Main Content

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

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