Gentle intro to Heaps vs Clustered Tables
Chronicles of DB Indexes - Part 9
Opening up a new bitesized week by fullfiling last week's promise. We're going to talk about Heaps & Clustered tables :)
Before we dive into intrinsic details, I want to set up a scenery first. A scenery that you can digest in a day or two. And that scenery is about the analogy. And that's what I'm presenting you today.
It's a bit old fashioned. I know. Nobody's using Phone Books any more (just like HDDs seem to be schooled out). But, and I do take a risk here, I hope that some of you remember the actual phonebooks. Like, those papers marked with letters, and you turn to a specific letter and all your contacts whose first (or last) name's start with that letter are there. Yeah, that was a phone book. It allowed for fast search of phone numbers :)
Well, in order to make a connection between Pages which we discussed in the past (if you don't recall them, make sure to refresh your memory) and Heaps vs Clustered Tables, I want you to think of how it used to work ages ago. How the process of saving somebody's phone number used to work.
Back then (thousands of years ago :P) you really had TWO options. Either you used a regular notebook and you just kept filling up page after page, OR, you used a Binary Search made possible by somebody who invented Phone book.
Both approaches have pros and cons of course. First one is greedy and efficient on space because you literally fill up page after page. The second one though is quite generous on space usage, as it spreads data over the whole phone book. The main difference between first and second one being the fact that the latter is quite efficient when it comes to SEARCHING :) As in - you just look up the first letter, go to EXACT page where it is, and your phone number is either there or on one of adjacent pages. If you're noticing similarity with B-Trees - that makes me proud of you!
Turns out that some old fashioned ages old ideas copy quite well to data storage. Specifically, to DB Tables. But that's something will discuss next time. Until then, I'd hope you take some time to think about Phonebook vs Notebook, the pros and cons of both and .. well, we'll close up that gap in a few days with another article that dives deeper into the matter.
Until then!