This HTML5 document contains 27 embedded RDF statements represented using HTML+Microdata notation.

The embedded RDF content will be recognized by any processor of HTML5 Microdata.

Subject Item

n12:this

foaf:made

n2:VirtTipsAndTricksRowToColStoreConversion

Subject Item

n7:this

sioc:creator_of

n2:VirtTipsAndTricksRowToColStoreConversion

Subject Item

n8:this

sioc:creator_of

n2:VirtTipsAndTricksRowToColStoreConversion

Subject Item

n10:VOS

sioc:container_of

n2:VirtTipsAndTricksRowToColStoreConversion

atom:entry

n2:VirtTipsAndTricksRowToColStoreConversion

atom:contains

n2:VirtTipsAndTricksRowToColStoreConversion

Subject Item

n2:UpgradingToVOS610

sioc:links_to

n2:VirtTipsAndTricksRowToColStoreConversion

Subject Item

n2:VirtTipsAndTricksRowToColStoreConversion

rdf:type

atom:Entry sioct:Comment

dcterms:created

2017-06-13T05:46:21.476275

dcterms:modified

2020-01-06T11:01:08.780140

rdfs:label

VirtTipsAndTricksRowToColStoreConversion

foaf:maker

n12:this n13:this

dc:title

VirtTipsAndTricksRowToColStoreConversion

opl:isDescribedUsing

n15:rdf

sioc:has_creator

n7:this n8:this

sioc:content

%META:TOPICPARENT{name="VirtTipsAndTricksGuide"}% ---+How to convert from Row Store to Column Store Table? ---+++ What As of version 7, Virtuoso offers a column-wise compressed storage format alongside its traditional row-wise storage format. In the column-wise storage model, each column of a table or index is stored contiguously, so that values of a single column on consecutive rows are physically adjacent. In this way, adjacent values are of the same type, and if the index is sorted on said value, the consecutive values often form an ascending sequence. This organization allows the use of more powerful compression techniques than could be used for rows where consecutive values belong to different columns, and thus are of disparate data types with values in different ranges. ---+++ Why Virtuoso 7 supports both traditional row-wise and column-wise storage formats, with the new column-wise storage format providing 3 times better data compression than row-wise storage and thus more space efficient in terms of disk space for storing the database on disk and memory for hosting the working set for best performance. Thus at times there will be a need to convert a table from row-size to column-wise storage. When migrating a Virtuoso 6 (row-wise storage) database to Virtuoso 7 as the migrated database remains in row-wise storage format when started with a Virtuoso 7 binary, they may be a need to convert it to column-wise storage. In the case of a RDF database this is especially recommended as RDF triples tend to be columnar in nature and thus lends themselves very well to column-wise storage, requiring the <code>RDF_QUAD</code> table to be converted. ---+++ How The process is the same whether your Row Store database was originally created with Virtuoso 6 or Virtuoso 7, and whether your Virtuoso instance is running as a Single-Server or in Cluster mode. The following steps must be taken to convert each <code>TABLE</code> from Row Store format to Column Store format -- 1 <code>DROP</code> any <code>INDEX</code> on the existing <code>TABLE</code> 1 <code>RENAME</code> the existing <code>TABLE</code> 1 <code>CREATE</code> a new <code>TABLE</code> with the original name, including the <code>COLUMN</code> keyword in the <code>CREATE TABLE</code> statement 1 <code>CREATE</code> a new <code>INDEX</code> (if relevant) on the new <code>TABLE</code> including the <code>COLUMN</code> keyword in the <code>CREATE INDEX</code> statement 1 <code>INSERT</code> the data from the renamed table into the newly created table Using the Virtuoso <code>RDF_QUAD</code> table as an example the following script can be used to convert it to Column Store format: <verbatim> set echo on; cl_exec ('checkpoint_interval (0)'); drop index rdf_quad_pogs; drop index rdf_quad_sp; drop index rdf_quad_op; drop index rdf_quad_gs; alter table rdf_quad rename rq_rows; -- remove non-inlined strings, can't fit into O delete from rq_rows where isstring (o); create table RDF_QUAD ( G IRI_ID_8, S IRI_ID_8, P IRI_ID_8, O any, primary key (P, S, O, G) column ) alter index RDF_QUAD on RDF_QUAD partition (S int (0hexffff00)) create column index RDF_QUAD_POGS on RDF_QUAD (P, O, S, G) partition (O varchar (-1, 0hexffff)) ; create distinct no primary key ref column index RDF_QUAD_SP on RDF_QUAD (S, P) partition (S int (0hexffff00)) create distinct no primary key ref column index RDF_QUAD_GS on RDF_QUAD (G, S) partition (S int (0hexffff00)) create distinct no primary key ref column index RDF_QUAD_OP on RDF_QUAD (O, P) partition (O varchar (-1, 0hexffff)) ; -- disable transaction log and use autocommit -- as result of query might be too large to fit into transaction log_enable(2, 1); insert into rdf_quad (g,s,p,o) select g,s,p,o from rq_rows; --checkpoint; -- reenable transaction log log_enable(1, 1); </verbatim> Note for large tables this process will take some time to complete. The <code>sys_keys</code> table can then be queried to verify if the index type of the table is <code>column</code>, <code>index</code>, <code>bitmap</code> or <code>NULL</code> (indicating row wise table): <verbatim> SQL> SELECT TOP 10 KEY_TABLE, sys_sql_val_print (KEY_OPTIONS) FROM sys_keys WHERE KEY_TABLE = 'DB.DBA.RDF_QUAD' ; KEY_TABLE sys_sql_val_print VARCHAR NOT NULL VARCHAR _______________________________________________________________________________ DB.DBA.RDF_QUAD vector ('column') DB.DBA.RDF_QUAD vector ('distinct', 'no_pk', 'column', vector (525, 0, 0, '__ALL', vector (vector (0, 'S', 1, 16776960, 0)))) DB.DBA.RDF_QUAD vector ('distinct', 'no_pk', 'column', vector (525, 0, 0, '__ALL', vector (vector (0, 'O', 3, -1, 65535)))) DB.DBA.RDF_QUAD vector ('column', vector (525, 0, 0, '__ALL', vector (vector (0, 'O', 3, -1, 65535)))) DB.DBA.RDF_QUAD vector ('distinct', 'no_pk', 'column', vector (525, 0, 0, '__ALL', vector (vector (0, 'S', 1, 16776960, 0)))) 5 Rows. -- 1 msec. SQL> </verbatim> The online Virtuoso [[http://docs.openlinksw.com/virtuoso/coredbengine.html#colstore][Column Store documentation]] provides much more detail. *Note*: The Virtuoso server should be restarted to clear the database cache in case any of the row-wise indexes might still be cached. ---++Related * [[http://docs.openlinksw.com/virtuoso/coredbengine.html#colstore][Column Store documentation]] * [[VirtTipsAndTricksGuide][Virtuoso Tips and Tricks Collection]]

sioc:id

c480b07392b86bdd95f2570384a3b007

sioc:link

n2:VirtTipsAndTricksRowToColStoreConversion

sioc:has_container

n10:VOS

atom:title

VirtTipsAndTricksRowToColStoreConversion

atom:source

n10:VOS

atom:author

n12:this

atom:published

2017-06-13T05:46:21Z

atom:updated

2020-01-06T11:01:08Z