O'Reilly logo

Discovering SQL: A Hands-On Guide for Beginners by Alex Kriegel

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

images

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required