Part 2 of 6 · 2 min read
Row versus column storage
Think of a row-oriented table like a stack of completed order forms, each form holding every field for one order. Think of a column-oriented table like a set of separate logs, one log per field. The order-date log holds nothing but dates, in order.
In plain words: Row-oriented storage keeps every column of one row physically next to each other on disk, which is efficient for reading or writing one whole row at a time, exactly what an OLTP transaction does. Column-oriented storage instead keeps every row's value for one column physically together in its own file, separate from every other column. A fact table is often a hundred columns wide, but a typical analytical query only reads four or five of them. Reading a column-oriented table for such a query means opening only the handful of column files actually needed and ignoring every other column entirely, while a row-oriented table would have to read every column of every matching row just to extract the few it needs.
A worked example. The bookstore's sales_fact table has columns for date_key, book_id, store_id, promotion_id, customer_id, quantity, and price. A query asking for total quantity sold by date only needs date_key and quantity. Stored by column, the engine reads just those two files; stored by row, it would have to read all seven columns of every matching row.
The key lines. No new SQL; the column files themselves are not queried directly with SQL.
date_key file: 140102, 140102, 140102, 140103, 140103
quantity file: 1, 3, 1, 5, 1
Row-oriented storage, column-oriented storage
Row-oriented storage physically groups together every column belonging to one row. Column-oriented storage physically groups together every row's value for one column, with each column kept in its own file or structure, reassembling a row by taking the same position from each column file.
Example: the two column files above store date_key and quantity separately; the first row of the table is reassembled by taking position 1 from each.