A db index is analogous to a table of contents. This helps me understand db index in an easy way. My question is are tehre any real world analogies for a clustered index?
Add a comment
|
3 Answers
If the Table of Contents in the front of the book is non-clustered, the page numbering itself (on the actual pages) is the clustered index. The "clustered" nature of an index indicates that the records are stored with the index nodes (or at least in the same order). So, the page-number analogy is actually very accurate in this respect, because like a real clustered index, the page numbers are located on the pages, which are bound together in page number order.
Put another way:
When you use the book's TOC (non-clustered index), you search for a term which points to a page number. You then have to perform another search to locate the actual page.
The search performed against the page number (clustered index) leads you directly to the page. No secondary searching is required.
Not incidentally, this is why you can only have one clustered index. It defines the physical order of the records.
I would describe a clustered index as "the actual order." Thus, for example, a physical dictionary has a clustered index on word, ordered in ascending order from A-Z (slight simplification). This also explains why a clustered index is so much faster to read than a regular index. If I feel the urge to read a book by index entry it will require me to jump all over the book, since consecutive index entries tend to point to different pages. On the other hand, reading every word starting with "The" in a dictionary requires less jumping around; merely find "The", then read in order.
-
So it is like a table of contents with the difference that the actual table is sorted based on the indexed column?– KaushikCommented Jul 31, 2013 at 2:09
-
I am a bit hesitatent to call it a table of contents, because one usually would use a table of contents as a sort of index. If I wanted to read about topic X in a book, I would read through the table of contents to find out which chapter discusses X, then flip to that page. On the other hand, if I wanted to look up "candy" in the dictionary, I would open the dictionary and flip through pages to find the word candy. This has major implications for join performance. Compare someone referencing a dictionary to lookup words vs giant index...the latter has an extra layer of indirection.– BrianCommented Jul 31, 2013 at 3:01
-
I should mention that one can achieve the same performance benefits of a clustered index by just creating a non-clustered index that includes every column (or at least, every column you are using). Of course, this is similar to creating two copies of your table (each sorted in a different order). One could in theory create two identical animal reference books, one ordered alphabetically by an animal's common name and the other ordered alphabetically by the animal's scientific name (vs having an index by common name).– BrianCommented Jul 31, 2013 at 3:09
The library (or bookstore if you prefer) puts books into groups (all history books here, scifi books there) because they assume that if you want one history book, you're more likely to want other history books rather than different kinds of books. Likewise, it becomes easier to sort the scifi books by author rather than all books by author.
-
Ummm...but that sounds more like partitioning based on category(history, sci fi) and not cluster indexing.– KaushikCommented Jul 31, 2013 at 2:06
-
@kaushik - sure, the implementation differs but it's close enough for the layman (or beginner). If it helps, magazines will often be clustered by year and then by month/week. Same concept but closer to the implementation.– TelastynCommented Jul 31, 2013 at 2:31