Katie-us/SIVA — comment on commit cefc17f42fee089898604389b09fd7303d6704e8
File: index.html line 1
By Katie-us:

Starting with version 6.00.3126 the default RDF index scheme consists of 2 full indices over RDF quads plus 3 partial indices. This index scheme is generally adapted to all kinds of workloads, regardless of whether queries generally specify a graph.

Alternate indexing schemes are possible but will not be generally needed. For upgrading old databases with a different index scheme see the corresponding documentation.

The index scheme consists of the following indices:

PSOG - primary key
POGS - bitmap index for lookups on object value.
SP - partial index for cases where only S is specified.
OP - partial index for cases where only O is specified.
GS - partial index for cases where only G is specified.
This index scheme is created by the following statements:

create table DB.DBA.RDF_QUAD (
G IRI_ID_8,
S IRI_ID_8,
P IRI_ID_8,
O any,
primary key (P, S, O, G)
)
alter index RDF_QUAD on DB.DBA.RDF_QUAD partition (S int (0hexffff00));

create distinct no primary key ref bitmap index RDF_QUAD_SP on RDF_QUAD (S, P) partition (S int (0hexffff00));
create bitmap index RDF_QUAD_POGS on RDF_QUAD (P, O, G, S) partition (O varchar (-1, 0hexffff));
create distinct no primary key ref bitmap index RDF_QUAD_GS on RDF_QUAD (G, S) partition (S int (0hexffff00));
create distinct no primary key ref index RDF_QUAD_OP on RDF_QUAD (O, P) partition (O varchar (-1, 0hexffff));
;
The idea is to favor queries where the predicate is specified in triple patterns. The entire quad can be efficiently accessed when P and either or both S and O are known. This has the advantage of clustering data by the predicate which improves working set: A page read from disk will only have entries pertaining to the same predicate, chances of accessing other entries of the page are thus higher than if the page held values for arbitrary predicates. For less frequent cases where only S is known, as in describe, the distinct P's of the S are found in the SP index. These SP pairs are then used for accessing the PSOG index to get the O and G. For cases where only the G is known, as in dropping a graph, the distinct S's of the G are found in the GS index. The P's of the S are then found in the SP index. After this the whole quad is found in the PSOG index.

The SP, OP and GS indices do not store duplicates. If a S has many values of the P, there is only one entry. Extries are not deleted from SP, OP or GS. This does not lead to erroneous results since a full index, that is either POSG or PSOG is always consulted in order to know if a quad actually exists. For cases of updating data, most often a graph is entirely dropped and a substantially similar graph is inserted in its place. The SP, OP and GS indices get to stay relatively unaffected.

Still over time specially if there are frequent updates and values do not repeat between consecutive states the SP, OP and GS indices will get polluted which may affect performance. Dropping and recreating the index will then remedy the situation.

For cases where this is not practical the index scheme should only have full indices, i.e. each key holds all columns of the primary key of the quad. This will be the case if the distinct no primary key ref options are not specified in the create index statement. In such cases all indices remain in strict sync across deletes.

Many RDF workloads have bulk load and read intensive access patterns with few deletes. The default index scheme is optimized for these. With these situations this scheme offers significant space savings resulting in better working set. Typically this layout takes 60-70% of the space of a layout with 4 full indices.

16.15.3. Index Scheme Selection
The indexes in place on the RDF_QUAD table can greatly affect the performance of SPARQL queries, as can be determined by running the STATISTICS command on the table as follows:

SQL> STATISTICS DB.DBA.RDF_QUAD;
Showing SQLStatistics of table(s) 'DB.DBA.RDF_QUAD'
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME NON_UNIQUE INDEX_QUALIFIER INDEX_NAME TYPE SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY PAGES FILTER_CONDITION
VARCHAR VARCHAR VARCHAR SMALLINT VARCHAR VARCHAR SMALLINT SMALLINT VARCHAR VARCHAR INTEGER INTEGER VARCHAR
_______________________________________________________________________________

DB DBA RDF_QUAD NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL
DB DBA RDF_QUAD 0 DB RDF_QUAD 3 1 P NULL NULL NULL NULL
DB DBA RDF_QUAD 0 DB RDF_QUAD 3 2 S NULL NULL NULL NULL
DB DBA RDF_QUAD 0 DB RDF_QUAD 3 3 O NULL NULL NULL NULL
DB DBA RDF_QUAD 0 DB RDF_QUAD 3 4 G NULL NULL NULL NULL
DB DBA RDF_QUAD 1 DB RDF_QUAD_GS 3 1 G NULL NULL NULL NULL
DB DBA RDF_QUAD 1 DB RDF_QUAD_GS 3 2 S NULL NULL NULL NULL
DB DBA RDF_QUAD 1 DB RDF_QUAD_OP 3 1 O NULL NULL NULL NULL
DB DBA RDF_QUAD 1 DB RDF_QUAD_OP 3 2 P NULL NULL NULL NULL
DB DBA RDF_QUAD 1 DB RDF_QUAD_POGS 3 1 P NULL NULL NULL NULL
DB DBA RDF_QUAD 1 DB RDF_QUAD_POGS 3 2 O NULL NULL NULL NULL
DB DBA RDF_QUAD 1 DB RDF_QUAD_POGS 3 3 G NULL NULL NULL NULL
DB DBA RDF_QUAD 1 DB RDF_QUAD_POGS 3 4 S NULL NULL NULL NULL
DB DBA RDF_QUAD 1 DB RDF_QUAD_SP 3 1 S NULL NULL NULL NULL
DB DBA RDF_QUAD 1 DB RDF_QUAD_SP 3 2 P NULL NULL NULL NULL

15 Rows. -- 24 msec.
SQL>
With only one index (OGPS) created by default, if the graph is always given, as with one or more FROM or FROM NAMED clauses, and there are no patterns where only graph and predicate are given, then the default indices should be sufficient. If predicate and graph are given but subject is not, then it is sometimes useful to add:

CREATE BITMAP INDEX RDF_QUAD_PGOS
ON DB.DBA.RDF_QUAD (G, P, O, S)
PARTITION (O VARCHAR (-1, 0hexffff));
Note:
Note: If the server version is pre-5.0.7, leave out the partitioning clause.

Making the PGOS index can help in some cases even if it is not readily apparent from the queries that one is needed. This is so, for example, if the predicate by itself is selective, i.e. there is a predicate that