What’s clustered index

content

despite having index in the concept, it’s more about heap file
dbms-clusterd-index.excalidraw
⚠ Switch to EXCALIDRAW VIEW in the MORE OPTIONS menu of this document. ⚠ You can decompress Drawing data with the command palette: ‘Decompress current Excalidraw file’. For more info check in plugin settings under ‘Saving’

Excalidraw Data

Text Elements

haeder

page 1

UserId

Name

1

2

3

John

David

…

haeder

page 2

haeder

page 3

heap file
Link to original
see above, in a table, different records can be on different pages (or blocks)
when the search key is UserId, if we’re searching user from UserId 10 to 15, we might need to go to different pages
- but every time we have to load one whole page to access a record
- a lots of pages need to be loaded into the memory to perform this range search on search key UserId
- cuz that’s just how data accessing is!
despite the fact that UserId 10 to 15 are so closed on the table, the data record might be far away from each other physically
hence comes the idea of clustered index
- heap files are unsorted, unorganized
- clustered index means that we want the data to be somewhat sorted on page, to boost performance
- it doesn’t have to be perfectly sorted!

Note

the cost of index search varies greatly, depending on whether the index is clustered or not