2 / 6 OLTP, OLAP & Analytical Stores

Down The Column

The same four sales rows, laid out two ways on disk. Shaded cells hold the amount column.

Row-major storage interleaves the amount column with every other column; column-major keeps it contiguous Row-major heap pageColumn-major file, five column chunks idregtsqtyamt idregtsqtyamt idregtsqtyamt idregtsqtyamt idididid regregregreg tstststs qtyqtyqtyqty amtamtamtamt sales: 200 million rows, 60 columns, 900 bytes per row, amount is 8 bytesSELECT sum(amount) FROM sales;

Roughly how many bytes must each layout read to answer SELECT sum(amount) FROM sales, ignoring compression?

A

Row store about 180 GB, column store about 45 GB, because a column chunk still carries the other columns in its row group

B

Row store about 180 GB, column store about 1.6 GB

C

Row store about 180 GB, column store about 180 GB, because both have to visit all 200 million rows

D

About 1.6 GB either way, since a B-tree index on amount lets the row store read only that column too