Discovering SQL: A Hands-On Guide for Beginners

COLUMN-ORIENTED DBMS

As the name implies, the column-oriented database stores data in columns as opposed to standard row/fields orientation. This turns the relational model upside down, as it flies in the face of the normalization rules. The concept can be illustrated with AUTHORS table in our Library database (see Table 12-4).

TABLE 12-4: Columnar Representation of AUTHORS Table

One advantage comes from the fact that the data are not stored as a text in these columns, but is compressed into binary uniform data type (yes, a columnar database operates with but a single data type per column, as opposed to rows, which usually contain different data types); this allows for using advanced indexing techniques (bitmap indices, for example). Another advantage comes from the fact that the database optimizes physical storage, so all data can be read in one pass, without multiple calls to the storage (such as the hard drive).

Unlike NoSQL options, such as SinpleDB or BigTable, the columnar databases actually do use SQL and sometimes even integrate with relational databases (for example, Infobright columnar database runs with MySQL, and Sybase IQ runs on top of Sybase ASE).

Most benefits are realized in data warehousing applications, which deal with aggregate data computed over a number of data items. At the same time, it slows down insert/update/delete operations, making it a really poor choice ...