Any index or primary key, i.e., any table, can be declared to be stored column-wise. A single table can have multiple indices, of which some are stored column-wise and some are not. As with tables stored row-wise, the table row itself is stored following the primary key index entry on the index tree leaf corresponding to the entry. This arrangement is sometimes called a clustered index .

One can specify column-wise storage as the default for any new tables or indices by adding ColumnStore = 1 to the [Parameters] section of the virtuoso.ini file. Otherwise, tables and indices are created tow-wise unless the column option is specified, as described below.

The statement below declares the table xx to be stored column-wise:

CREATE TABLE xx ( id    INT,
                  data  VARCHAR,
                  PRIMARY KEY (id) COLUMN
                );
 

This statement adds a column-wise stored index to the table:

CREATE COLUMN INDEX xxi
  ON xx (data);
 

The COLUMN keyword can come after the column list of the primary key declaration of a table or anywhere between the CREATE and INDEX keywords of a create index statement.

Note that the BITMAP keyword cannot be used together with the COLUMN keyword. Column-wise indices will automatically use bitmap compression when appropriate without this being specified. A column-wise index is likely to be more space-efficient than a row-wise bitmap index with the same key parts.

The directives for column compression in CREATE TABLE (NO COMPRESS, COMPRESS PREFIX) have no effect on column-wise stored tables. Data is compressed in a manner chosen at run time based on the data itself.

All SQL operations work identically for column- or row-wise tables and indices. The locking behavior is also identical, with row-level locking supported on all isolation levels. The behavior of the READ COMMITTED isolation is non-locking, showing the pre-image of updated data when reading pages with uncommitted inserts or updates.

Recovery is by roll forward, and checkpoints will only store committed states of the database, even if started when there are uncommitted transactions pending.

The system table DB.DBA.sys_col_info holds information about space utilization of column-wise indices.

This table is updated only after the DB.DBA.sys_index_space_stats procedure view has been accessed. Thus, one must first make a selection from DB.DBA.sys_index_space_stats .

The columns of sys_col_info have the following meaning:

To see which columns take the most space, and how full the pages are, as well as the overall effectiveness of compression, one can do:

SELECT                                       coi_column         ,
                         coi_pages * 8192  AS  total_bytes        ,
         coi_bytes / (coi_pages * 8192.0)  AS  page_fill          ,
                                               coi_bytes          ,
             1.0 * coi_bytes / coi_values  AS  ce_bytes_per_value ,
          8192.0 * coi_pages / coi_values  AS  bytes_per_value
    FROM sys_col_info
   WHERE coi_type = -1
ORDER BY coi_pages DESC ;
 

Note that issuing a query like:

 SELECT TOP 20 *
    FROM sys_index_space_stats
ORDER BY iss_pages DESC;
 

will update the sys_col_info table which is initially empty.

The sys_index_space_stats view shows the number of pages used for the sparse row-wise index tree top for column-wise indices.

The number of rows shown there for column-wise indices is the number of entries of the sparse index, not the row-count of the index. The space utilization here will be under 1% of the total for a column-wise index.

Below we look at space utilization of the O column of the primary key of the RDF_QUAD table.

SELECT *
  FROM sys_col_info
 WHERE  coi_index = 'DB.DBA.RDF_QUAD'
   AND coi_column = 'O' ;
 coi_table             coi_index           coi_nth           coi_type          coi_column    coi_pages      coi_ces    coi_values    coi_bytes
 VARCHAR NOT NULL      VARCHAR NOT NULL    INTEGER NOT NULL  INTEGER NOT NULL  VARCHAR       INTEGER        INTEGER    INTEGER       INTEGER
 _______________________________________________________________________________

 DB.DBA.RDF_QUAD       DB.DBA.RDF_QUAD     2                 -1                O             654663         0          1252064815    4617808494
 DB.DBA.RDF_QUAD       DB.DBA.RDF_QUAD     2                 1                 O             0              229074     97104862      947215
 DB.DBA.RDF_QUAD       DB.DBA.RDF_QUAD     2                 3                 O             0              3227395    490806316     3905658370
 DB.DBA.RDF_QUAD       DB.DBA.RDF_QUAD     2                 4                 O             0              94038      17227799      8554746
 DB.DBA.RDF_QUAD       DB.DBA.RDF_QUAD     2                 6                 O             0              389126     551074747     579191659
 DB.DBA.RDF_QUAD       DB.DBA.RDF_QUAD     2                 8                 O             0              160814     48480188      12026273
 DB.DBA.RDF_QUAD       DB.DBA.RDF_QUAD     2                 10                O             0              652817     47370903      111430231
 

The top line is the overall summary across all the compression types.

The lines below give information per-compression-type. The values of coi_type mean the following: