1.8 Speeding up Query Execution with Result Tables
Result tables are auxiliary tables that store the results for generic patterns of SPARQL queries executed against an RDF graph or RDF graph collection.
Note:
Result tables were called as Subject-Property-Matrix (SPM) tables in the previous book versions (prior to Oracle Database Release 23ai). See Changes in Terminology and Subprograms for more information.Generic pattern queries include star-pattern, chain-pattern, and single-triple-pattern queries.
Improvement of performance with result tables is derived from the use of:
- Pre-materialized joins in these tables to reduce joins at query processing time
- Compact representation of triples for individual properties in separate tables for faster access
- More accurate RDF data statistics obtained from these tables to arrive at better query execution plans
The following sections provide in-depth information on result tables.
- Types of Result Tables
There are three types of result tables that can be defined on an RDF graph or an RDF graph collection. - Creating and Managing Result Tables
The following sections explain the steps for creating and managing result tables. - SPARQL Query Options for Result Tables
SPARQL queries will automatically use result tables if they are present. - Special Considerations when Using Result Tables
This section describes a few limitations to be considered when using result tables.
Parent topic: RDF Graph Overview
1.8.1 Types of Result Tables
There are three types of result tables that can be defined on an RDF graph or an RDF graph collection.
The different result tables are as follows:
- Star-Pattern Tables: These tables hold the results for
star-pattern queries (with restriction that each property must be single-valued)
such as:
?x :fname ?fnm . ?x miname ?m . ?x :lname ?lnm . - Triple-Pattern Tables: These tables hold the results for single
triple-pattern queries such as:
?x :hasHobby ?y .This is same as an RDF triple, but for a specific property.
- Chain-Pattern Tables: These tables hold the results for
chain-pattern queries such as:
?child :hasParent ?parent . ?parent :hasBrother ?uncle .A chain is stored only if all the links exist.
Note:
Star-Pattern, Triple-Pattern, and Chain-Pattern tables were called as Single-Valued Property (SVP), Multi-Valued Property (MVP), and Property Chain (PCN) tables respectively in the previous book versions (prior to Oracle Database Release 23ai). See Changes in Terminology and Subprograms for more information.Consider an RDF graph containing the following sample data.
:john :fname "John" ; :lname "Brown" ; :height 72 ; :email "john@email-example.com", "johnnyB@email-example.com" .
:mary :fname "Mary" ; :lname "Smith" ; :height 68 ; :email "Mary.Smith@email-example.com" .
:bob :fname "Robert" ; :lname "Brown" ; :height 70 ; :fatherOf :john, :mary ; :email "bobBrown@email-example.com" .
:alice :fname "Alice" ; :lname "Brown" ; :height 68 ; :motherOf :john, :mary .
:henry :fatherOf :bob .
:kathy :motherOf :bob .Note that for simplicity, Id(rdfterm) will be used instead of the
actual numeric identifier (available in the RDF_VALUE$ table) for each
rdfterm. A complete example with additional data is included in Example 1-107.
- Star-Pattern Tables
Each row in a star-pattern table holds values for one or more single-valued RDF properties for a resource in an RDF graph. - Triple-Pattern Tables
Each row in a triple-pattern table, created for a given property, holds a value for the property. - Chain-Pattern Tables
Each row in a chain-pattern table holds a fixed-length path in the RDF graph.
Parent topic: Speeding up Query Execution with Result Tables
1.8.1.1 Star-Pattern Tables
Each row in a star-pattern table holds values for one or more single-valued RDF properties for a resource in an RDF graph.
In the best case, a star-pattern table defined for n properties may
be used during query processing to replace an n-way join of the
RDF_LINK$ table with simple table lookups.
A property p is single-valued in an RDF graph if each resource in
the graph has at most one value for p regardless of named graphs. In the
sample RDF dataset (described in Types of Result Tables), the properties :first_name, :last_name,
and :height are single-valued, but the property :email is
multi-valued.
To speed up execution of a query pattern such as { ?s :first_name
?fname ; :last_name ?lname ; :height ?height }, involving use of
single-valued properties only, a star-pattern table may be created on the RDF graph to include
the preceding three single-valued properties by using the string ‘:first_name
:last_name :height’ as the value for the key_string
parameter in a call to the SEM_APIS.BUILD_RESULT_TAB
subprogram.
Table 1-19 describes the structure and content for such a star-pattern table corresponding to the preceding sample data. Also, note that:
- The table shows only a subset of the actual set of columns. Specifically, not
shown are the columns with name like
G<Id(property)>that are used to store the named graph component of the corresponding RDF statements. - The table describes values in the columns as
Id(rdfterm)instead of the actual numeric identifiers that get stored.
Table 1-19 Example Star-Pattern Table Structure
| START_NODE_ID | … | P<Id(:first_name)> | … | P<Id(:last_name)> | … | P<Id(:height)> |
|---|---|---|---|---|---|---|
Id(:john) |
...
|
Id(“John”) |
... |
Id(“Brown”) |
... |
Id(72) |
Id(:mary) |
...
|
Id(“Mary”) |
... |
Id(“Smith”) |
...
|
Id(68) |
Id(:bob) |
... |
Id(“Robert”) |
... |
Id(“Brown”) |
... |
Id(70) |
Id(:alice) |
... |
Id(“Alice”) |
...
|
Id(“Brown”) |
...
|
Id(68) |
The availability of this star-pattern table allows the preceding query pattern to be processed simply by accessing the rows in the star-pattern table and avoids the three-way self-join of the RDF_LINK$ table that would otherwise be necessary.
It is also possible to include reversed-properties that are single-valued.
In the sample RDF data (described in Types of Result Tables), the property :fatherOf is not single-valued, but its
reversed version which is denoted as ^:fatherOf (intuitively equivalent to a
:hasFather property), is indeed single-valued. To speed up execution of a
query pattern such as { ?s :fname ?fname; :lname ?lname; :height ?height;
^:fatherOf ?father }, an extended version of the preceding star-pattern table
may be created, by using ‘:fname :lname :height ^:fatherOf’ as the
key_string value.
Table 1-20 describes the structure and content of this extended version of the
star-pattern table that includes a reversed property. The use of the letter
R, instead of P, as the first character in the column name,
R<Id(:fatherOf)>, indicates that this is a reversed property. As
mentioned earlier, availability of this star-pattern table allows avoiding a (four-way)
self-join of the RDF_LINK$ table.
Table 1-20 Extended Star-Pattern Table Including a Reversed Property
| START_NODE_ID | … | P<Id(:first_name)> | … | P<Id(:last_name)> | … | P<Id(:height)> | … | R<Id(:fatherOf)> |
|---|---|---|---|---|---|---|---|---|
Id(:john) |
... |
Id(“John”) |
... |
Id(“Brown”) |
... |
Id(72) |
... |
:bob |
Id(:mary) |
... |
Id(“Mary”) |
... |
Id(“Smith”) |
... |
Id(68) |
... |
:bob |
Id(:bob) |
... |
Id(“Robert”) |
... |
Id(“Brown”) |
... |
Id(70) |
... |
:henry |
Example 1-98 Creating a Star-Pattern Table
The following code creates an extended star-pattern table on an RDF graph
named M1:
BEGIN
SEM_APIS.BUILD_RESULT_TAB(
query_pattern_type => SEM_APIS.SPM_TYPE_SVP
, result_tab_name => 'FLHF'
, rdf_graph_name => 'M1'
, key_string => ' :fname :lname :height ^:fatherOf '
, prefixes => ' PREFIX : <http://www.example.com#> '
, network_owner => 'RDFUSER'
, network_name => 'NET1'
);
END;
/The name, structure, and default indexes for a star-pattern table may be described as follows:
- The name of a star-pattern table is created based on the following
template:
<NETWORK_NAME>#RDF_XT$SVP_<MODEL_NAME>+__<SPM_NAME> - The
NUMBERcolumn,START_NODE_ID, stores the subject id or, if reversed, the object id, of the matching triple for the first property in the list of properties in the star-pattern table. - For each property covered in a star-pattern table, the following columns
are created for storing the numeric identifiers for the lexical values in a triple: :
NUMBERcolumn (G<Id(property)>) for storing the named graph id.NUMBERcolumn (P<Id(property)>for storing the object id or if reversedR<Id(property)>), the subject id.- (Optional) additional columns for internal use.
- The
START_NODE_IDcolumn is defined as the primary key of the star-pattern table and a unique index named using the template:<NETWORK_NAME>#RDF_XX$SVP_<MODEL_NAME>_UQ__<SPM_NAME>, is created on this column when the star-pattern table is created.
Parent topic: Types of Result Tables
1.8.1.2 Triple-Pattern Tables
Each row in a triple-pattern table, created for a given property, holds a value for the property.
A triple-pattern table stores the values for a given property in a separate table and in a compact fashion, thus allowing faster access and better statistics. Unlike a star-pattern table, the (single) property included in a triple-pattern table does not have to be, but could be, single-valued.
A property p is multi-valued in an RDF graph if there exist two or
more triples (regardless of named graphs), (s p o1) and
(s p o2) with o1 not equal to
o2. That is, s has more than one
distinct object values for the property p.
In the sample RDF dataset (described in Types of Result Tables), the properties :email,
:fatherOf, and :motherOf are
multi-valued.
Table 1-21 shows the structure and content of a triple-pattern
table for the :motherOf property for the preceding sample
data. The two columns shown here store the numeric identifiers for lexical
values for variables ?mom and ?c,
respectively, in a pattern { ?mom :motherOf ?c }. The
triple-pattern table contains another column,
G<id<:motherOf>), not shown here, to store
the numeric identifier of the named graph in case the matching RDF statement
is a quad.
Table 1-21 Example Triple-Pattern Table Structure
| START_NODE_ID | ... | P<Id(:motherOf)> |
|---|---|---|
Id(:alice) |
Id(:john) |
|
Id(:alice) |
Id(:mary) |
|
Id(:kathy) |
Id(:bob) |
Example 1-99 Creating a Triple-Pattern Table
To create the preceding triple-pattern table on an RDF graph named M1, you can use the following SQL command.
BEGIN
SEM_APIS.BUILD_RESULT_TAB(
query_pattern_type => SEM_APIS.SPM_TYPE_MVP
, result_tab_name => null /* must be NULL (the name is auto-generated based on id(property) */
, rdf_graph_name => 'M1'
, key_string => ' :motherOf ' /* must have exactly one property */
, prefixes => ' PREFIX : <http://www.example.com#> '
, network_owner => 'RDFUSER'
, network_name => 'NET1'
);
END;
/The name, structure, and default indexes for a triple-pattern table may be described as follows:
- The naming convention for a triple-pattern table is
created based on the following template:
<NETWORK_NAME>#RDF_XT$MVP_<MODEL_NAME>+__P<id(property)> - The
NUMBERcolumn,START_NODE_ID, stores the subject id of the matching triples that use the target property as the predicate. - For the property covered in a triple-pattern table,
the following columns are created for storing the numeric
identifiers for the lexical values in a triple:
NUMBERcolumnG<Id(property)>for storing the named graph idNUMBERcolumnP<Id(property)>for storing the object id- Optional additional columns for internal use
- A nonunique index is created on the
START_NODE_IDcolumn using the following naming convention:<NETWORK_NAME>#RDF_XX$MVP_<MODEL_NAME>_P<id(property)>.
Parent topic: Types of Result Tables
1.8.1.3 Chain-Pattern Tables
Each row in a chain-pattern table holds a fixed-length path in the RDF graph.
A path is a sequence of two or more triples where, except for the last triple in the
sequence, object of a triple is the same as the subject of the next triple. A chain-pattern
table that stores paths of length n can be used during query processing to
replace an n-way join of type current_triple.object =
next_triple.subject, of the RDF_LINK$ table with simple table
lookups.
For example, to speed up the execution of the following query pattern -
{ ?gma :motherOf ?f . ?f :fatherOf ?c }, you can create a chain-pattern
table using the following sequence of properties, specified as the
key_string: ‘ :motherOf :fatherOf ’.
Table 1-22 shows the structure and content of the chain-pattern table for the
preceding sample data. The three columns here store the numeric identifiers for lexical
values for variables ?gma, ?f, and ?c, respectively, for the two paths
that satisfy the property chain: (:kathy) –[:motherOf]-> (:bob)
–[:fatherOf]-> (:john) and (:alice) –[:motherOf]-> (:bob)
–[:fatherOf]-> (:mary).
Table 1-22 Example Chain-Pattern Table Structure
| START_NODE_ID | … | P<Id(:motherOf)> | … | P<Id(:fatherOf)> |
|---|---|---|---|---|
Id(:kathy) |
... |
Id(:bob) |
... |
Id(:john) |
Id(:kathy) |
... |
Id(:bob) |
...
|
Id(:mary) |
A property chain can include multiple occurrences of the same property. Consider the following query pattern to connect a grandfather to the children:
{ ?gfa :fatherOf ?f . ?f :fatherOf ?c }
You can create a chain-pattern table using the following sequence of
properties, specified as the key_string- ‘ :fatherOf :fatherOf
’. The following table describes the structure and content for such a
chain-pattern table. The column name with ‘#2’ as suffix corresponds to
the second occurrence of the :fatherOf property in the specified chain. It
stores two paths that satisfy the property chain - (:henry) –[:fatherOf]->
(:bob) –[:fatherOf]-> (:john) and (:henry) –[:fatherOf]-> (:bob)
–[:fatherOf]-> (:mary).
Table 1-23 Multiple Occurrences of a Single Property in a Chain-Pattern Table
| START_NODE_ID | … | P<Id(:fatherOf)> | … | P<Id(:fatherOf)>#2 |
|---|---|---|---|---|
Id(:henry) |
... |
Id(:bob) |
... |
Id(:john) |
Id(:henry) |
...
|
Id(:bob) |
...
|
Id(:mary) |
A property chain may involve reversed properties as well. For example,
consider the following query pattern { ?mom :motherOf ?c . ?c ^:fatherOf ?dad
} to connect the siblings. You can create a Chain-Pattern table with the
following key_string- ‘:motherOf ^:fatherOf ‘.
Table 1-24 shows the structure and content of this chain-pattern table. Note that
the letter ‘R’ in the rightmost column name
R<id(:fatherOf)> indicates that the column corresponds to the
reversed property. The availability of this chain-pattern table allows the preceding query
pattern to be processed simply by accessing the rows in the chain-pattern table and avoids
the two-way join of the RDF_LINK$ table that would otherwise be
necessary.
Table 1-24 Reversed Property in a Chain-Pattern Table
| START_NODE_ID | … | P<Id(:motherOf)> | … | R<Id(:fatherOf)> |
|---|---|---|---|---|
Id(:alice) |
...
|
Id(:john) |
... |
Id(:bob) |
Id(:alice) |
...
|
Id(:mary) |
...
|
Id(:bob) |
Id(:kathy) |
...
|
Id(:bob) |
...
|
Id(:henry) |
Example 1-100 Creating a Chain-Pattern Table
The following example creates a chain-pattern table representing the
grandfather chain using two occurrences of the :fatherOf property on an
RDF graph named M1.
BEGIN
SEM_APIS.BUILD_RESULT_TAB(
result_tab_name => ‘GRANDPA’
, query_pattern_type => SEM_APIS.SPM_TYPE_PCN
, rdf_graph_name => 'M1'
, key_string => ' S :fatherOf :fatherOf '
, prefixes => ' PREFIX : <http://www.example.com#> '
, network_owner => 'RDFUSER'
, network_name => 'NET1'
);
END;
/The name, structure, and default indexes for a chain-pattern table may be described as follows:
- The name of a chain-pattern table is based on the following template:
<NETWORK_NAME>#RDF_XT$PCN_<MODEL_NAME>+__<SPM_NAME> - The
NUMBERcolumn,START_NODE_ID, stores the subject id or, if reversed, the object id, of the matching triple for the first property in the sequence of properties in the chain-pattern table. - For each property’s n-th occurrence in a chain-pattern table, the
following columns are created for storing the numeric identifiers for the lexical
values in a triple: (note that the
#nsuffix is used only ifn > 1):NUMBERcolumnG<Id(property)>(orG<Id(property)>#n) for storing the named graph idNUMBERcolumnP<Id(property)>(orP<Id(property)>#n) or, if reversed,R<Id(property)>(orR<Id(property)>#n), for storing the object id or, if reversed, the subject id- (Optional) additional columns for internal use
- A nonunique index, named using the template
<NETWORK_NAME>#RDF_XX$PCN_<MODEL_NAME>__<SPM_NAME>, is created on theSTART_NODE_IDcolumn. - Additionally, a nonunique index is created on each of the property columns.
Parent topic: Types of Result Tables
1.8.2 Creating and Managing Result Tables
The following sections explain the steps for creating and managing result tables.
- Including Lexical Values in Result Tables
You can also include lexical values for objects in result tables. - Creating and Dropping Secondary Indexes on Result Tables
You can create and drop secondary indexes on result tables. - Dropping Result Tables
You can drop a specific result table. - In-Memory Result Tables
Taking advantage of Oracle Database In-Memory, you can create in-memory result tables using theINMEMORY=Tflag in the options parameter. - Metadata for Result Tables
You can use the RDF_SPM_INFO view to retrieve metadata information for the result tables defined on an RDF graph. - Utility Subprogram for Computing Per-Subject Cardinality Aggregates for Individual Properties
You can use the SEM_APIS.GATHER_SPM_INFO procedure to create and populate a table to store the per-subject cardinality information for each property in an RDF graph, based on its use as predicate of triples. - Performing DML Operations on RDF Graphs with Result Tables
All star-pattern, triple-pattern, and chain-pattern tables are automatically maintained for DML operations. - Performing Bulk Load Operations on RDF Graphs with Result Tables
- Gathering Statistics on Result Tables
Having up-to-date statistics on result tables is critical for good query performance.
Parent topic: Speeding up Query Execution with Result Tables
1.8.2.1 Including Lexical Values in Result Tables
You can also include lexical values for objects in result tables.
Result tables include numeric identifiers for object values by default. Additionally,
by storing the lexical values (RDF terms) in the SPM tables, retrieval of lexical values
during SPARQL query processing can be made faster by avoiding the lookups involving
joins with the RDF_VALUE$ table.
If you choose to include lexical values for the subject or values of any of
the properties stored in a result table, new columns for the lexical property values are
added to the star-pattern and chain-pattern tables. Note that these columns correspond
exactly to the columns with the same name in RDF_VALUE$. Specifically,
when including lexical values for a non-reversed property into a result table, the
following columns get added to the result table:
P<Id(property)>_VALUE_TYPEP<Id(property)>_VNAME_PREFIXP<Id(property)>_VNAME_SUFFIXP<Id(property)>_LITERAL_TYPEP<Id(property)>_LANGUAGE_TYPEP<Id(property)>_ORDER_NUMP<Id(property)>_ORDER_DATEP<Id(property)>_LONG_VALUE
For reversed properties, the column names use ‘R’ as the first character
instead of the character ‘P’. Names for the additional columns added
for including the lexical values for the subject (that is, corresponding to the numeric
identifiers stored in the START_NODE_ID column), use the prefix
‘S’, instead of P<Id(property)> or
R<Id(property)>.
The following example is a variation of Example 1-98, in that the lexical values for the subject and the reversed
:fatherOf property are included. The ‘+’ symbol is
used to indicate that lexical values needed to be stored in the result table. Here, use
of ‘+S’ and ‘+^:fatherOf’ in the
key_string parameter causes the additional columns to get added for
the subject and the (reversed) :fatherOf property, respectively.
Example 1-101 Including Lexical Values for the Subject and for the Reversed Property
BEGIN
SEM_APIS.BUILD_RESULT_TAB(
query_pattern_type => SEM_APIS.SPM_TYPE_SVP
, result_tab_name => 'FLHF'
, rdf_graph_name => 'M1'
, key_string => ' +S :fname :lname :height +^:fatherOf '
, prefixes => ' PREFIX : <http://www.example.com#> '
, network_owner => 'RDFUSER'
, network_name => 'NET1'
);
END;
/If a result table is already present, you can use the SEM_APIS.ALTER_RESULT_TAB subprogram to include lexical values for either the subject or any one of the
properties by using the string ‘ADD_S_VALUE’ or
‘ADD_VALUE’, respectively, as value for the command parameter.
The following example results in inclusion of the lexical values for the
:lname property. (The command DROP_S_VALUE or
DROP_VALUE, not shown in this example, can be used to remove
the lexical value columns for the subject or a property, respectively.)
Example 1-102 Altering a Star-Pattern Table to Add Lexical Values for a Property
BEGIN
SEM_APIS.ALTER_RESULT_TAB(
query_pattern_type => SEM_APIS.SPM_TYPE_SVP
, result_tab_name => 'FLHF'
, rdf_graph_name => 'M1'
, command => 'ADD_VALUE'
, pred_name => '<http://www.example.com#lname>'
, network_owner => 'RDFUSER'
, network_name => 'NET1'
);
END;
/Parent topic: Creating and Managing Result Tables
1.8.2.2 Creating and Dropping Secondary Indexes on Result Tables
You can create and drop secondary indexes on result tables.
If for a given workload, accessing the content of a result table through access paths other than those already provided by the default indexes on the result table are needed, corresponding secondary (B+-tree) indexes may be created by using the SEM_APIS.CREATE_INDEX_ON_RESULT_TAB subprogram.
The following example shows creation of such an index, named
name_idx, on the star-pattern table created in Example 1-101. The key_string parameter, ‘2P 1P
S’, indicates that the key should be the (numeric id) value from the column
corresponding to the second property in the table, namely, :lname,
followed by that from the first property in the table, namely, :fname,
followed by the subject (that is, the START_NODE_ID column). Note that
the reference to the n-th property is always <n>P
regardless whether the corresponding column name in the result table is of the form
P<Id(property)> or R<Id(property).
If the lexical values for a property are included in the result table, then
the index key may also include one or more of the columns that store the components of
the lexical values. To refer to a component, use the form
<n><component-code>, where n is 0 (for
START_NODE_ID) or position of the target property, and the
component code is determined based on the suffix of the included value component names
as shown in Table 1-25
Table 1-25 Mapping from Suffix of Lexical Value Component Column Names to Component Code
| Suffix of Lexical Value Component Column Name | Component Code |
|---|---|
VALUE_TYPE |
VT |
VNAME_PREFIX |
VP |
VNAME_SUFFIX |
VS |
LITERAL_TYPE |
LT |
LANGUAGE_TYPE |
LA |
ORDER_NUM |
VN |
ORDER_DATE |
VD |
For example, the reference to 2VP and 0VP
in the key ‘2P 1P 2VP 0VP S’ indicates the inclusion of the
following two columns in the key at the respective positions:
- The
<column_name_for_the_2nd_property_of_the _SPM_table>_VNAME_PREFIXcolumn - The
S_VNAME_PREFIXcolumn (whereScorresponds to the zeroth column of the SPM table, that is , theSTART_NODE_IDcolumn).
Example 1-103 Creating a Secondary (B+-tree) Index on a Result Table
SEM_APIS.CREATE_INDEX_ON_RESULT_TAB(
index_name. => ‘name_idx’
, query_pattern_type => SEM_APIS.SPM_TYPE_SVP
, result_tab_name => 'FLHF'
, rdf_graph_name => 'M1'
, key_string => ' 2P 1P S '
, network_owner => 'RDFUSER'
, network_name => 'NET1'
);
END;
/To drop any index created using this subprogram, use the SQL
DROP INDEX <index_name> command. For example:
DROP INDEX name_idx;
Parent topic: Creating and Managing Result Tables
1.8.2.3 Dropping Result Tables
You can drop a specific result table.
You can use the SEM_APIS.DROP_RESULT_TAB subprogram to drop a result table as shown in the following example.
Example 1-104 Dropping a Result Table
BEGIN
SEM_APIS.DROP_RESULT_TAB(
query_pattern_type => SEM_APIS.SPM_TYPE_SVP
, result_tab_name => 'FLHF'
, rdf_graph_name => 'M1'
, network_owner => 'RDFUSER'
, network_name => 'NET1'
);
END;
/Note that the use of the special string, ‘*’, for the
result_tab_name parameter, allows dropping all result tables of
the type specified by the query_pattern_type parameter. To drop all
the result tables, regardless of the type, use
SEM_APIS.SPM_TYPE_ALL for the
query_pattern_type parameter.
Parent topic: Creating and Managing Result Tables
1.8.2.4 In-Memory Result Tables
Taking advantage of Oracle Database In-Memory, you can create in-memory
result tables using the INMEMORY=T flag in the options parameter.
Generally, on-disk result tables are designed based on the commonly occurring patterns in the individual queries in a workload. If the result tables contain extra columns that are not needed for the query, it could incur disk scan overhead. If the query workload is not known or varying, building a result table with all properties could be a good choice. The in-memory columnar format ensures that only the necessary columns are accessed. Only one in-memory result table with all properties can be built and any other result tables are not allowed.
The in-memory result table with all properties can be built using
‘INMEMORY=T’ as shown in the following example.
Example 1-105 Creating an In-memory Result Table
As a prerequisite, ensure that the table M1_PRED_INFO that is used
in this example already exists. This table can be created using the SEM_APIS.GATHER_SPM_INFO subprogram.
BEGIN
SEM_APIS.BUILD_RESULT_TAB(
rdf_graph_name =>'M1',
pred_info_tabname =>'M1_PRED_INFO',
pred_name =>NULL,
options =>' INMEMORY=T ',
degree =>2,
network_owner =>'RDFUSER',
network_name =>'NET1'
);
END;
/ALTER TABLE “MYNET#RDF_XT$SVP_M1+__SVP1” INMEMORY;Parent topic: Creating and Managing Result Tables
1.8.2.5 Metadata for Result Tables
You can use the RDF_SPM_INFO view to retrieve metadata information for the result tables defined on an RDF graph.
Table 1-26 Predicate Information Table Columns
| Column Name | Type | Description |
|---|---|---|
| TABLE_NAME | VARCHAR2(128) | Name of the SPM table. |
| COLUMN_NAME | VARCHAR2(128) | Name of a column in the SPM table: either
START_NODE_ID, or
P<id(property)> or
R<id(property)>.
|
| COLUMN_ID | NUMBER | Position of the column in the SPM table’s column list. |
| HASVALUES | NUMBER(1) | Indicates if in addition to the numeric identifiers for the values, their lexical values too are stored in the SPM table. |
| MODEL_ID | NUMBER | Numeric identifier of the RDF graph. |
| MODEL_NAME | VARCHAR2(128) | Name of the RDF graph. |
Parent topic: Creating and Managing Result Tables
1.8.2.6 Utility Subprogram for Computing Per-Subject Cardinality Aggregates for Individual Properties
You can use the SEM_APIS.GATHER_SPM_INFO procedure to create and populate a table to store the per-subject cardinality information for each property in an RDF graph, based on its use as predicate of triples.
The P_VALUE_ID column stores the numeric identifier corresponding to a
property. For a reversed property, P_VALUE_ID stores the negative value of
the id for the property.
This property cardinality table has the structure as shown in the following table. If
MAX_CNT > 1 for a given property, then that property is multi-valued,
that is, for at least one of the subject resources, this property has been used as predicate
for two or more distinct triples (that share the same subject and same predicate but has
distinct objects).
Table 1-27 Predicate Information Table Columns
| Column Name | Type | Description |
|---|---|---|
| P_VALUE_ID | NUMBER | The value id for this property. A negative value indicates reversed property. |
| PRED_NAME | VARCHAR2(4000) | The lexical value for this property. |
| MIN_CNT | NUMBER | The minimum of the per-subject cardinalities for this property. |
| MAX_CNT | NUMBER | The maximum of the per-subject cardinalities for this property. |
| MED_CNT | NUMBER | The median of the per-subject cardinalities for this property. |
| AVG_CNT | NUMBER | The average of the per-subject cardinalities for this property. |
| TOT_CNT | NUMBER | The total number of triples that have this property as predicate. |
| INCLUDE | VARCHAR2(30) | Not used. |
For the sample RDF dataset (described in Types of Result Tables), the cardinality information is described in the following table.
Table 1-28 Sample Cardinality Information in the Predicate Table
| P_VALUE_ID | PRED_NAME | MIN_CNT | MAX_CNT | MED_CNT | AVG_CNT | TOT_CNT | INCLUDE |
|---|---|---|---|---|---|---|---|
| Id(:fname) | :fname | 1 | 1 | … | … | 4 | ... |
| Id(:lname) | :lname | 1 | 1 | … | … | 4 | ... |
| Id(:height) | :height | 1 | 1 | … | … | 4 | ... |
| Id(:email) | 1 | 2 | … | … | 4 | ... | |
| Id(:fatherOf) | :fatherOf | 1 | 2 | … | … | 3 | ... |
| Id(:motherOf) | :motherOf | 1 | 2 | … | … | 3 | ... |
A second procedure, SEM_APIS.BUILD_RESULT_TAB, creates and populates star-pattern, triple-pattern, and chain-pattern tables.
The following example illustrates creation of a set of result tables for an RDF graph with SEM_APIS.GATHER_SPM_INFO and SEM_APIS.BUILD_RESULT_TAB. These result tables are automatically used for SPARQL query execution. This example uses SEM_MATCH, but SPARQL queries executed through other APIs, such as those supported for Apache Jena or RDF server will also automatically use result tables.
Example 1-106 Creating Result Tables and Using the Tables in SPARQL Queries
SQL> set echo on pages 10000 numwidth 20 lines 200 long 10000
SQL> column s format a30
SQL> column fname format a5
SQL> column lname format a5
SQL> column height format a6
SQL> column email format a25
SQL> column nick format a10
SQL> column friend format a30
SQL> column state format a5
SQL> conn rdfuser/rdfuser
Connected.
SQL> -- create an RDF network
SQL> exec sem_apis.create_rdf_network('tbs_rdf',network_owner=>'RDFUSER',network_name=>'NET1');
PL/SQL procedure successfully completed.
SQL> --move the RDF_SPM$ table and indexes defined on it to the network's tablespace
SQL> alter table NET1#RDF_SPM$ move tablespace tbs_rdf;
SQL> set serverout on;
SQL> begin
2 for idx in (select index_name from sys.user_indexes where table_name='NET1#RDF_SPM$') loop
3 execute immediate 'alter index "' || idx.index_name || '" rebuild tablespace TBS_RDF';
4 sys.dbms_output.put_line('moved (rebuild) index: ' || idx.index_name);
5 end loop;
6 end;
7 /
SQL> set serverout off;
SQL> -- create an RDF graph
SQL> exec sem_apis.create_rdf_graph('M1',null,null,network_owner=>'RDFUSER',network_name=>'NET1');
PL/SQL procedure successfully completed.
SQL> -- add some data: fname, lname, height, and nickName are single-valued; email and friendOf are multi-valued
SQL> begin
2 sem_apis.update_rdf_graph('M1',
3 'PREFIX : <http://www.example.com#>
4 PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
5 INSERT DATA {
6 :john :fname "John" ; :lname "Brown" ; :height 72
7 ; :email "john@email-example.com", "johnnyB@email-example.com"
8 ; :nickName "Johnny B"
9 ; :friendOf :ann
10 ; :address [ :addrNum 20 ; :addrStreet "Elm Street" ; :addrCityState [ :addrCity "Boston" ; :addrState "MA" ] ] .
11 :ann :fname "Ann" ; :lname "Green" ; :height 65
12 ; :email "ann@email-example.com"
13 ; :nickName "Annie"
14 ; :friendOf :john, :bill
15 ; :address [ :addrNum 10 ; :addrStreet "Main Street" ; :addrCityState [ :addrCity "New York" ; :addrState "NY" ] ] .
16 :bill :fname "Bill" ; :lname "Red" ; :height 70
17 ; :email "bill@email-example.com"
18 ; :nickName "Billy"
19 ; :friendOf :ann, :jane
20 ; :address [ :addrNum 5 ; :addrStreet "Peachtree Street" ; :addrCityState [ :addrCity "Atlanta" ; :addrState "GA" ] ] .
21 :jane :fname "Jane" ; :lname "Blue" ; :height 68
22 ; :email "jane@email-example.com", "jane2@email-example.com"
23 ; :friendOf :bill
24 ; :address [ :addrNum 101 ; :addrStreet "Maple Street" ; :addrCityState [ :addrCity "Chicago" ; :addrState "IL" ] ] .
25 }'
26 ,network_owner=>'RDFUSER'
27 ,network_name=>'NET1');
28 end;
29 /
PL/SQL procedure successfully completed.
SQL> -- create a star-pattern table for single-valued predicates :fname, :lname, :height
SQL> BEGIN
2 SEM_APIS.BUILD_RESULT_TAB(
3 query_pattern_type => SEM_APIS.SPM_TYPE_SVP
4 , result_tab_name => 'fnm_lnm_hght'
5 , rdf_graph_name => 'M1'
6 , key_string => ' :fname :lname :height '
7 , prefixes => ' PREFIX : <http://www.example.com#> '
8 , degree => 2
9 , network_owner => 'RDFUSER'
10 , network_name => 'NET1'
11 );
12 END;
13 /
PL/SQL procedure successfully completed.
SQL> -- check the star-pattern table
SQL> select * from "NET1#RDF_XT$SVP_M1+__FNM_LNM_HGHT" order by start_node_id;
START_NODE_ID G8337314745347241189 P8337314745347241189 G7644445801044650266 P7644445801044650266 G4791477124431525340 P4791477124431525340
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
1399946303865654932 2838435233532231409 5036507830384741776 7949294891880010615
7024748068782994892 9071571320455459462 8802343394415720481 7603694794035016230
8531245907959123227 50859040499294923 9011354822640550059 4318017261525689661
8972322488425499169 3239737248730612593 6648986869806945928 2028730158517518732
4 rows selected.
SQL> -- create a chain-pattern table for :address/:addrCityState/:addrState
SQL> BEGIN
2 SEM_APIS.BUILD_RESULT_TAB(
3 query_pattern_type => SEM_APIS.SPM_TYPE_PCN
4 , result_tab_name => 'addr_state'
5 , rdf_graph_name => 'M1'
6 , key_string => ' S :address :addrCityState :addrState '
7 , prefixes => ' PREFIX : <http://www.example.com#> '
8 , degree => 2
9 , network_owner => 'RDFUSER'
10 , network_name => 'NET1'
11 );
12 END;
13 /
PL/SQL procedure successfully completed.
SQL> -- check the chain-pattern table content
SQL> -- Note: Since generated blank node labels may differ from run to run, the 3rd and 5th column values may vary as well
SQL> select * from "NET1#RDF_XT$PCN_M1+__ADDR_STATE" order by start_node_id, 3, 5, 7;
START_NODE_ID G5055192271510902740 P5055192271510902740 G2282073771135796724 P2282073771135796724 G594560333771551504 P594560333771551504
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
1399946303865654932 6519232173603163724 2583525877732786353 2028557412112123936
7024748068782994892 5974521208853734660 3828178052943534859 7995579594576433205
8531245907959123227 7758805114187110754 6401534854183681859 5359878998404290171
8972322488425499169 875920943154203631 3729916732662692051 4933462079191011078
4 rows selected.
SQL> -- create triple-pattern tables for :email and :friendOf
SQL> -- :email
SQL> BEGIN
2 SEM_APIS.BUILD_RESULT_TAB(
3 query_pattern_type => SEM_APIS.SPM_TYPE_MVP
4 , result_tab_name => null
5 , rdf_graph_name => 'M1'
6 , key_string => ' :email '
7 , prefixes => ' PREFIX : <http://www.example.com#> '
8 , degree => 2
9 , network_owner => 'RDFUSER'
10 , network_name => 'NET1'
11 );
12 END;
13 /
PL/SQL procedure successfully completed.
SQL> -- check the triple-pattern table
SQL> select * from "NET1#RDF_XT$MVP_M1+_P2930492586059823454" order by start_node_id;
START_NODE_ID G2930492586059823454 P2930492586059823454
-------------------- -------------------- --------------------
1399946303865654932 6100245385739701229
7024748068782994892 2096397932624357828
7024748068782994892 6480436012276020283
8531245907959123227 1846003049324830366
8531245907959123227 7834835188342349976
8972322488425499169 7251371240613573863
6 rows selected.
SQL> -- :friendOf
SQL> BEGIN
2 SEM_APIS.BUILD_RESULT_TAB(
3 query_pattern_type => SEM_APIS.SPM_TYPE_MVP
4 , result_tab_name => null
5 , rdf_graph_name => 'M1'
6 , key_string => ' :friendOf '
7 , prefixes => ' PREFIX : <http://www.example.com#> '
8 , degree => 2
9 , network_owner => 'RDFUSER'
10 , network_name => 'NET1'
11 );
12 END;
13 /
PL/SQL procedure successfully completed.
SQL> -- check the triple-pattern table
SQL> select * from "NET1#RDF_XT$MVP_M1+_P1285894645615718351" order by start_node_id, 3;
START_NODE_ID G1285894645615718351 P1285894645615718351
-------------------- -------------------- --------------------
1399946303865654932 7024748068782994892
1399946303865654932 8972322488425499169
7024748068782994892 1399946303865654932
8531245907959123227 8972322488425499169
8972322488425499169 1399946303865654932
8972322488425499169 8531245907959123227
6 rows selected.
SQL> -- gather optimizer statistics on result auxiliary tables
SQL> begin
2 sem_perf.analyze_aux_tables(
3 model_name=>'M1',
4 network_owner=>'RDFUSER',
5 network_name=>'NET1');
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> -- Execute a SPARQL query that uses result tables
SQL> SELECT s, fname, lname, height, email, nick, friend, state
2 FROM TABLE(SEM_MATCH(
3 'PREFIX : <http://www.example.com#>
4 SELECT *
5 WHERE {
6 ?s :fname ?fname
7 ; :lname ?lname
8 ; :height ?height
9 ; :email ?email
10 ; :nickName ?nick
11 ; :friendOf ?friend
12 ; :address/:addrCityState/:addrState ?state
13 }'
14 ,sem_models('M1')
15 ,null,null,null,null
16 ,' '
17 ,null,null
18 ,'RDFUSER','NET1'))
19 ORDER BY 1,2,3,4,5,6,7,8;
S FNAME LNAME HEIGHT EMAIL NICK FRIEND STATE
------------------------------ ----- ----- ------ ------------------------- ---------- ------------------------------ -----
http://www.example.com#ann Ann Green 65 ann@email-example.com Annie http://www.example.com#bill NY
http://www.example.com#ann Ann Green 65 ann@email-example.com Annie http://www.example.com#john NY
http://www.example.com#bill Bill Red 70 bill@email-example.com Billy http://www.example.com#ann GA
http://www.example.com#bill Bill Red 70 bill@email-example.com Billy http://www.example.com#jane GA
http://www.example.com#john John Brown 72 john@email-example.com Johnny B http://www.example.com#ann MA
http://www.example.com#john John Brown 72 johnnyB@email-example.com Johnny B http://www.example.com#ann MA
6 rows selected.
SQL> -- See the relevant portion of the SQL translation showing the result table usage.
SQL> --
SQL> -- This SQL evaluates 9 triple patterns with only 4 joins
SQL> -- instead of the 8 joins that would normally be required
SQL> -- without result tables.
SQL> --
SQL> -- The star-pattern table is used for :fname, :lname, :height.
SQL> -- triple-pattern tables are used for :email and :friendOf.
SQL> -- RDFM_M1 (view of RDF_LINK$ for RDF graph M1) is used for :nickName.
SQL> -- The chain-pattern table is used for the sequence
SQL> -- :address/:addrCityState/:addrStat
SQL> SELECT sys.dbms_lob.substr(
2 SEM_APIS.SPARQL_TO_SQL(
3 'PREFIX : <http://www.example.com#>
4 SELECT *
5 WHERE {
6 ?s :fname ?fname
7 ; :lname ?lname
8 ; :height ?height
9 ; :email ?email
10 ; :nickName ?nick
11 ; :friendOf ?friend
12 ; :address/:addrCityState/:addrState ?state
13 }'
14 ,sem_models('M1')
15 ,null,null,null
16 ,' '
17 ,null,null
18 ,'RDFUSER','NET1'), 1004, 3377) AS SQL_TRANS_PORTION
19 FROM SYS.DUAL;
SQL_TRANS_PORTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT SVP0.START_NODE_ID AS S$RDFVID,
SVP0.P7644445801044650266 AS LNAME$RDFVID,
MVP1.P1285894645615718351 AS FRIEND$RDFVID,
T4.CANON_END_NODE_ID AS NICK$RDFVID,
PCN0.P594560333771551504 AS STATE$RDFVID,
SVP0.P4791477124431525340 AS HEIGHT$RDFVID,
MVP0.P2930492586059823454 AS EMAIL$RDFVID,
SVP0.P8337314745347241189 AS FNAME$RDFVID,
SVP0.START_NODE_ID AS BGP$1
FROM (
SELECT * FROM "RDFUSER".NET1#RDFM_M1) T4,
"RDFUSER"."NET1#RDF_XT$SVP_M1+__FNM_LNM_HGHT" SVP0,
"RDFUSER"."NET1#RDF_XT$PCN_M1+__ADDR_STATE" PCN0,
"RDFUSER"."NET1#RDF_XT$MVP_M1+_P2930492586059823454" MVP0,
"RDFUSER"."NET1#RDF_XT$MVP_M1+_P1285894645615718351" MVP1
WHERE SVP0.P8337314745347241189 IS NOT NULL AND
SVP0.P7644445801044650266 IS NOT NULL AND
SVP0.P4791477124431525340 IS NOT NULL AND
T4.P_VALUE_ID = 2558054308995111125 AND
1=1 AND
1=1 AND
1=1 AND
SVP0.START_NODE_ID = MVP0.START_NODE_ID AND
SVP0.START_NODE_ID = T4.START_NODE_ID AND
SVP0.START_NODE_ID = MVP1.START_NODE_ID AND
SVP0.START_NODE_ID = PCN0.START_NODE_ID AND
1=1
1 row selected.Example 1-107 Including Lexical Values in Result Tables
The following example includes lexical values in result tables. Note that this example follows Example 1-106.SQL> conn rdfuser/rdfuser
SQL> -- Drop and recreate the FNM_LNM_HGHT SVP table, with in-line lexical values for the :fname and :height properties.
SQL> -- Check metadata for the new result table to verify that HASVALUES=1 for the two properties whose lexical values are in-lined.
SQL> exec sem_apis.drop_result_tab(sem_apis.SPM_TYPE_SVP, ' fnm_lnm_hght ', 'm1', network_owner=>'rdfuser', network_name=>'net1');
PL/SQL procedure successfully completed.
SQL>
SQL> BEGIN
2 SEM_APIS.BUILD_RESULT_TAB(
3 query_pattern_type => SEM_APIS.SPM_TYPE_SVP
4 , result_tab_name => 'fnm_lnm_hght'
5 , rdf_graph_name => 'M1'
6 , key_string => ' S +:fname :lname +:height '
7 , prefixes => ' PREFIX : <http://www.example.com#> '
8 , degree => 2
9 , network_owner => 'RDFUSER'
10 , network_name => 'NET1'
11 );
12 END;
13 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from net1#rdf_spm_info where table_name like '%SVP%FNM_LNM_HGHT' order by table_name, column_id;
TABLE_NAME COLUMN_NAME COLUMN_ID HASVALUES MODEL_ID MODEL_NAME
---------------------------------------- -------------------- -------------------- -------------------- -------- ----------
NET1#RDF_XT$SVP_M1+__FNM_LNM_HGHT START_NODE_ID 1 0 1 M1
NET1#RDF_XT$SVP_M1+__FNM_LNM_HGHT P8337314745347241189 3 1 1 M1
NET1#RDF_XT$SVP_M1+__FNM_LNM_HGHT P7644445801044650266 5 0 1 M1
NET1#RDF_XT$SVP_M1+__FNM_LNM_HGHT P4791477124431525340 7 1 1 M1
4 rows selected.
SQL>
SQL> -- Drop and recreate the ADDR_STATE chain-pattern table, with in-line lexical values for the :addrState property.
SQL> -- Check metadata for the new table to verify that HASVALUES=1 for the :addrState property.
SQL> exec sem_apis.drop_result_tab(sem_apis.SPM_TYPE_PCN, ' addr_state ', 'm1', network_owner=>'rdfuser', network_name=>'net1');
PL/SQL procedure successfully completed.
SQL>
SQL> BEGIN
2 SEM_APIS.BUILD_RESULT_TAB(
3 query_pattern_type => SEM_APIS.SPM_TYPE_PCN
4 , result_tab_name => 'addr_state'
5 , rdf_graph_name => 'M1'
6 , key_string => ' S :address :addrCityState +:addrState '
7 , prefixes => ' PREFIX : <http://www.example.com#> '
8 , degree => 2
9 , network_owner => 'RDFUSER'
10 , network_name => 'NET1'
11 );
12 END;
13 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from net1#rdf_spm_info where table_name like '%PCN%ADDR_STATE' order by table_name, column_id;
TABLE_NAME COLUMN_NAME COLUMN_ID HASVALUES MODEL_ID MODEL_NAME
---------------------------------------- -------------------- -------------------- -------------------- -------- ----------
NET1#RDF_XT$PCN_M1+__ADDR_STATE START_NODE_ID 1 0 1 M1
NET1#RDF_XT$PCN_M1+__ADDR_STATE P5055192271510902740 3 0 1 M1
NET1#RDF_XT$PCN_M1+__ADDR_STATE P2282073771135796724 5 0 1 M1
NET1#RDF_XT$PCN_M1+__ADDR_STATE P594560333771551504 7 1 1 M1
4 rows selected.
SQL>
SQL> -- Drop and recreate the triple-pattern table for the :email property (id: 2930492586059823454), with in-line lexical values for the :email property.
SQL> -- Check metadata for the new table to verify that HASVALUES=1 for the :email property.
SQL> exec sem_apis.drop_result_tab(sem_apis.SPM_TYPE_MVP, '<http://www.example.com#email>', 'm1', network_owner=>'rdfuser', network_name=>'net1');
PL/SQL procedure successfully completed.
SQL> BEGIN
2 SEM_APIS.BUILD_RESULT_TAB(
3 query_pattern_type => SEM_APIS.SPM_TYPE_MVP
4 , result_tab_name => null
5 , rdf_graph_name => 'M1'
6 , key_string => ' +:email '
7 , prefixes => ' PREFIX : <http://www.example.com#> '
8 , degree => 2
9 , network_owner => 'RDFUSER'
10 , network_name => 'NET1'
11 );
12 END;
13 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from net1#rdf_spm_info where table_name like '%MVP%P2930492586059823454' order by table_name, column_id;
TABLE_NAME COLUMN_NAME COLUMN_ID HASVALUES MODEL_ID MODEL_NAME
---------------------------------------- -------------------- -------------------- -------------------- -------- ----------
NET1#RDF_XT$MVP_M1+_P2930492586059823454 START_NODE_ID 1 0 1 M1
NET1#RDF_XT$MVP_M1+_P2930492586059823454 P2930492586059823454 3 1 1 M1
2 rows selected.
SQL>
SQL> -- gather optimizer statistics on result auxiliary tables
SQL> begin
2 sem_perf.analyze_aux_tables(
3 model_name=>'M1',
4 network_owner=>'RDFUSER',
5 network_name=>'NET1');
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- Execute a SPARQL query that uses result tables
SQL> SELECT s, fname, lname, height, email, nick, friend, state
2 FROM TABLE(SEM_MATCH(
3 'PREFIX : <http://www.example.com#>
4 SELECT *
5 WHERE {
6 ?s :fname ?fname
7 ; :lname ?lname
8 ; :height ?height
9 ; :email ?email
10 ; :nickName ?nick
11 ; :friendOf ?friend
12 ; :address/:addrCityState/:addrState ?state
13 }'
14 ,sem_models('M1')
15 ,null,null,null,null
16 ,' '
17 ,null,null
18 ,'RDFUSER','NET1'))
19 ORDER BY 1,2,3,4,5,6,7,8;
S FNAME LNAME HEIGHT EMAIL NICK FRIEND STATE
------------------------------ ----- ----- ------ ------------------------- ---------- ------------------------------ -----
http://www.example.com#ann Ann Green 65 ann@email-example.com Annie http://www.example.com#bill NY
http://www.example.com#ann Ann Green 65 ann@email-example.com Annie http://www.example.com#john NY
http://www.example.com#bill Bill Red 70 bill@email-example.com Billy http://www.example.com#ann GA
http://www.example.com#bill Bill Red 70 bill@email-example.com Billy http://www.example.com#jane GA
http://www.example.com#john John Brown 72 john@email-example.com Johnny B http://www.example.com#ann MA
http://www.example.com#john John Brown 72 johnnyB@email-example.com Johnny B http://www.example.com#ann MA
6 rows selected.
SQL>
SQL> -- See the relevant portion of the SQL translation showing SPM table usage including in-line lexical values.
SQL> --
SQL> -- The number of joins with the RDF_VALUE$ table (for looking up lexical values) goes down from 8 to 4
SQL> -- because out of the 8 variables being projected, 4 -- fname, height, email, state -- appear
SQL> -- with properties whose lexical values are present in-line in the available result tables.
SQL> --
SQL> SELECT SEM_APIS.SPARQL_TO_SQL(
2 'PREFIX : <http://www.example.com#>
3 SELECT *
4 WHERE {
5 ?s :fname ?fname
6 ; :lname ?lname
7 ; :height ?height
8 ; :email ?email
9 ; :nickName ?nick
10 ; :friendOf ?friend
11 ; :address/:addrCityState/:addrState ?state
12 }'
13 ,sem_models('M1')
14 ,null,null,null
15 ,' '
16 ,null,null
17 ,'RDFUSER','NET1')
18 FROM SYS.DUAL;
SEM_APIS.SPARQL_TO_SQL('PREFIX:<HTTP://WWW.EXAMPLE.COM#>SELECT*WHERE{?S:FNAME?FN
--------------------------------------------------------------------------------
SELECT * FROM (
SELECT … <omitted> …
FROM (SELECT … <omitted> …
FROM (
SELECT * FROM "RDFUSER".NET1#RDFM_M1) T4,
"RDFUSER"."NET1#RDF_XT$SVP_M1+__FNM_LNM_HGHT" SVP0,
"RDFUSER"."NET1#RDF_XT$PCN_M1+__ADDR_STATE" PCN0,
"RDFUSER"."NET1#RDF_XT$MVP_M1+_P2930492586059823454" MVP0,
"RDFUSER"."NET1#RDF_XT$MVP_M1+_P1285894645615718351" MVP1
WHERE 1=1 AND
1=1 AND
1=1 AND
1=1 AND
SVP0.P8337314745347241189 IS NOT NULL AND
SVP0.P7644445801044650266 IS NOT NULL AND
SVP0.P4791477124431525340 IS NOT NULL AND
T4.P_VALUE_ID = 2558054308995111125 AND
1=1 AND
1=1 AND
1=1 AND
SVP0.START_NODE_ID = MVP0.START_NODE_ID AND
SVP0.START_NODE_ID = T4.START_NODE_ID AND
SVP0.START_NODE_ID = MVP1.START_NODE_ID AND
SVP0.START_NODE_ID = PCN0.START_NODE_ID AND
1=1) R, "RDFUSER".NET1#RDF_VALUE$ V0, "RDFUSER".NET1#RDF_VALUE$ V1, "RDFUSER".NET1#RDF_VALUE$ V2, "RDFUSER".NET1#RDF_VALUE$ V3
WHERE (1=1) AND (R.S$RDFVID = V0.VALUE_ID) AND (R.LNAME$RDFVID = V1.VALUE_ID) AND (R.FRIEND$RDFVID = V2.VALUE_ID) AND (R.NICK$RDFVID = V3.VALUE_ID)
) WHERE (1=1)
1 row selected.
SQL>
SQL> -- In addition to value projection. In-line lexical values
SQL> -- can be used to evaluate FILTER conditions.
SQL> -- The value for ?height can be taken directly from the
SQL> -- SVP table in this case.
SQL> SELECT s, height
2 FROM TABLE(SEM_MATCH(
3 'PREFIX : <http://www.example.com#>
4 SELECT ?s ?height
5 WHERE {
6 ?s :fname ?fname
7 ; :lname ?lname
8 ; :height ?height
9 FILTER (?height >= 72)
10 }'
11 ,sem_models('M1')
12 ,null,null,null,null
13 ,' '
14 ,null,null
15 ,'RDFUSER','NET1'))
16 ORDER BY 1,2;
S HEIGHT
------------------------------ ------
http://www.example.com#john 72
1 row selected.
SQL>
SQL> -- The SQL translation shows in-line lexical value usage for ?height >= 72.
SQL> SELECT SEM_APIS.SPARQL_TO_SQL(
2 'PREFIX : <http://www.example.com#>
3 SELECT ?s ?height
4 WHERE {
5 ?s :fname ?fname
6 ; :lname ?lname
7 ; :height ?height
8 FILTER (?height >= 72)
9 }'
10 ,sem_models('M1')
11 ,null,null,null
12 ,' '
13 ,null,null
14 ,'RDFUSER','NET1') AS SQL_TRANS
15 FROM SYS.DUAL;
SQL_TRANS
--------------------------------------------------------------------------------
SELECT * FROM (
SELECT … <omitted> …
FROM (SELECT …<omitted> …
FROM "RDFUSER"."NET1#RDF_XT$SVP_M1+__FNM_LNM_HGHT" SVP0
WHERE 1=1 AND
SVP0.P8337314745347241189 IS NOT NULL AND
SVP0.P7644445801044650266 IS NOT NULL AND
SVP0.P4791477124431525340 IS NOT NULL AND
1=1 AND
1=1 AND
(SVP0.P4791477124431525340_ORDER_NUM >= to_number(72))) R, "RDFUSER".NET1#RDF_VALUE$ V0
WHERE (1=1) AND (R.S$RDFVID = V0.VALUE_ID)
) WHERE (1=1)
1 row selected.
SQL> Example 1-108 Creating Secondary Indexes on Result Auxiliary Tables
The following example illustrates creation of secondary indexes on result auxiliary tables. Note that this example follows Example 1-106 and Example 1-107.
SQL>
SQL> conn rdfuser/rdfuser
Connected.
SQL>
SQL> -- create index on the ORDER_NUM (VN) component of the lexical value of the :height property.
SQL> -- This component is stored as a column in the FNM_LNM_HGHT SVP table.
SQL> -- It holds the numeric value for RDF literals of numeric type.
SQL> -- Since the :height property is the 3rd property in the SVP table, it is referred to using 3VN in the key_string argument below.
SQL> BEGIN
2 SEM_APIS.CREATE_INDEX_ON_RESULT_TAB(
3 index_name => 'height_idx'
4 , query_pattern_type => SEM_APIS.SPM_TYPE_SVP
5 , result_tab_name => 'fnm_lnm_hght'
6 , rdf_graph_name => 'M1'
7 , key_string => ' 3VN S '
8 , degree => 2
9 , network_owner => 'RDFUSER'
10 , network_name => 'NET1'
11 );
12 END;
13 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- EXPLAIN PLAN for the SPARQL query above involving "height >= 72" shows use of this index for access.
SQL> EXPLAIN PLAN FOR
2 SELECT s, height
3 FROM TABLE(SEM_MATCH(
4 'PREFIX : <http://www.example.com#>
5 SELECT ?s ?height
6 WHERE {
7 ?s :fname ?fname
8 ; :lname ?lname
9 ; :height ?height
10 FILTER (?height >= 72)
11 }'
12 ,sem_models('M1')
13 ,null,null,null,null
14 ,' '
15 ,null,null
16 ,'RDFUSER','NET1'))
17 ORDER BY 1,2;
Explained.
SQL>
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic +predicate'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3046664063
-------------------------------------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
| 4 | VIEW | |
|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| NET1#RDF_XT$SVP_M1+__FNM_LNM_HGHT |
|* 6 | INDEX RANGE SCAN | HEIGHT_IDX |
|* 7 | INDEX UNIQUE SCAN | NET1#C_PK_VID |
| 8 | TABLE ACCESS BY INDEX ROWID | NET1#RDF_VALUE$ |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("SVP0"."P8337314745347241189" IS NOT NULL AND
"SVP0"."P7644445801044650266" IS NOT NULL AND "SVP0"."P4791477124431525340"
IS NOT NULL)
6 - access("SVP0"."P4791477124431525340_ORDER_NUM">=72 AND
"SVP0"."START_NODE_ID">0 AND "SVP0"."P4791477124431525340_ORDER_NUM" IS NOT
NULL)
filter("SVP0"."START_NODE_ID">0)
7 - access("R"."S$RDFVID"="V0"."VALUE_ID")
27 rows selected.
SQL>
SQL> select column_name, column_position from all_ind_columns where index_name='HEIGHT_IDX' order by 2;
COLUMN_NAME COLUMN_POSITION
------------------------------ --------------------
P4791477124431525340_ORDER_NUM 1
START_NODE_ID 2Parent topic: Creating and Managing Result Tables
1.8.2.7 Performing DML Operations on RDF Graphs with Result Tables
All star-pattern, triple-pattern, and chain-pattern tables are automatically maintained for DML operations.
- Delete: For delete operations, corresponding rows from the triple-pattern table are deleted. In star-pattern tables, the corresponding column value is set to null including value columns. In chain-pattern tables, rows that use the deleted triple are deleted to reflect the removal of a link in the chain.
- Insert: For insert operations, a new subject row or the corresponding column value is inserted into the triple-pattern table if it does not exist including value columns. For star-pattern and chain-pattern tables, a new subject row or the column value is inserted if the existing value is null. If a different value is inserted than the existing value, an error is raised for constraint violation for star-pattern table.
Parent topic: Creating and Managing Result Tables
1.8.2.8 Performing Bulk Load Operations on RDF Graphs with Result Tables
When bulk-loading RDF data into an RDF graph, if any result tables are present for the graph, those will be truncated before loading the data and re-populated after the loading has been completed.
Parent topic: Creating and Managing Result Tables
1.8.2.9 Gathering Statistics on Result Tables
Having up-to-date statistics on result tables is critical for good query performance.
You can call the SEM_PERF.ANALYZE_AUX_TABLES procedure to gather statistics for your result tables.
Parent topic: Creating and Managing Result Tables
1.8.3 SPARQL Query Options for Result Tables
SPARQL queries will automatically use result tables if they are present.
An existing SPARQL workload does not need to change to take advantage of result tables. However, several new query options and optimizer hints can be used to fine-tune result table usage.
The following query options can be used in the options argument of
SEM_MATCH or in the SEM_FS_NS prefix used by
support for Apache Jena and RDF Server.
COST_BASED_SPM_OPT– usage of result tables is determined by the query execution plan costDISABLE_SPM_OPT– do not use result tables (star-pattern, triple-pattern, and chain-pattern)DISABLE_SVP_OPT– do not use star-pattern tablesDISABLE_PCN_OPT– do not use chain-pattern tablesDISABLE_MVP_OPT– do not use triple-pattern tablesDISABLE_SPM_VALUES_OPT– do not use in-line lexical values in result tables for value projection or filter evaluation (star-pattern, triple-pattern, and chain-pattern)DISABLE_SPM_VALUE_PROJ_OPT– do not use in-line lexical values in result tables for value projection (star-pattern, triple-pattern, and chain-pattern)MIN_SVP_CLUSTER_SIZE(n)– only use the star-pattern table for star pattern clusters that reference at least n properties contained in the star-pattern table (n = 1 by default)PREFER_PCN=T– when a triple pattern can be evaluated using either a star-pattern or chain-pattern table, choose the chain-pattern table (the default behavior is to use the star-pattern table)
The following query optimizer hints can be used in HINT0 hint
strings, the options argument of SEM_MATCH, and the
SEM_FS_NS prefix used by support for Apache Jena and RDF
server.
ALL_SPM_HASH / ALL_SPM_NL– use hash / nested-loop join for all joins with result tables (star-pattern, triple-pattern, and chain-pattern)ALL_SVP_HASH / ALL_SVP_NL– use hash / nested-loop join for all joins with star-pattern tablesALL_MVP_HASH / ALL_MVP_NL– use hash / nested-loop join for all joins with triple-pattern tablesALL_PCN_HASH / ALL_PCN_NL– use hash / nested-loop join for all joins with chain-pattern tables
Parent topic: Speeding up Query Execution with Result Tables
1.8.4 Special Considerations when Using Result Tables
This section describes a few limitations to be considered when using result tables.
- Result tables are only supported for a single RDF graph. RDF graph collections and inferred graphs are not supported.
- Result tables are not supported on RDF networks that are using Oracle Label Security.
- Flashback queries are not supported with result tables.
- An RDF graph with result tables cannot be used as the destination RDF graph in a SEM_APIS.MERGE_RDF_GRAPHS operation.
- SPARQL queries that use GeoSPARQL functions or Oracle Text functions do not utilize result tables.
- Evaluation of + and * property path expressions does not utilize result tables.
- Result tables are not supported for
SEM_APIS.APPEND_SEM_NETWORK_DATA,SEM_APIS.MOVE_SEM_NETWORK_DATAorSEM_APIS.RESTORE_SEM_NETWORK_DATAoperations.
Parent topic: Speeding up Query Execution with Result Tables