| ▲ | 4ndrewl 2 hours ago | |
"a database that keeps each record's details together can't take advantage of that: to read two columns, it has to read all five." My understanding is that modern databases will take advantage of that if you create an index of those fields - ie it'll read from the index, not the records. | ||
| ▲ | ConnorTsui20 an hour ago | parent | next [-] | |
We generally refer to an index that holds the data it indexes at its leaves a "clustered index". But this only means that the tuples are organized according to the ordering of the index: you still generally store the entire record (all columns/attributes) together. For example, if I cluster on my sorted primary key, that just means that the index can jump to the tuple with the correct primary key quickly (log time), but you will still end up reading* the entire tuple in the same way I explain in the blog. And if you only store a record ID that points to the other fields instead of the entire tuple at the leaves of the index, then that wouldn't be considered a clustered index. I will note, however, that DBMSs should be smart enough to know that an aggregation on only an indexed column does not need to fetch the rest of the columns, only the data at the leaves of the index. | ||
| ▲ | minkeymaniac an hour ago | parent | prev | next [-] | |
Not really modern either.. Oracle/Sybase/SQL Server have been doing that already in the 90s.. including indexed views for aggregation What they recently (and some of these were added in late 2000s) added is columnstore indexes... with compression and also in memory (Sybase Hana, SQL Server In Memory aka Hekaton, Oracle IM column store) | ||
| ▲ | kubanczyk 2 hours ago | parent | prev [-] | |
Well, yes. One of the things an index can do is column-oriented storage. | ||