SAND CDBMS SQL Reference Guide
Joins

Materialized Joins

A specific instance of any join can be saved to a materialized join (or "join index"), in which the tables/columns involved are pre-joined. The benefit of a materialized join is that it can automatically and transparently improve performance for subsequent SQL statements that use a compatible join definition. In terms of the results, a join executed using a materialized join is equivalent to the same join executed without the materialized join.

The basic syntax for creating a materialized join is the following:

CREATE MATERIALIZED JOIN join-name FROM join-definition

The materialized join object is created in the context of a schema, so the join name can be qualified by a schema name in the CREATE MATERIALIZED JOIN statement, or the schema can be omitted to create the materialized join in the current schema. As with other database objects, each materialized join name must be unique in a given schema.

The join definition part of the materialized join creation statement specifies any supported join between two table instances (which may be the same table). One significant deviation from the join definition allowed in a query is that the materialized join definition strictly requires that a column from one table be compared to a column from another table, whereas a query join allows a value expression involving one or more columns from one table to be compared to a value expression involving one or more columns from another table. Refer to the CREATE MATERIALIZED JOIN command description in the SAND CDBMS SQL Commands section for the full join syntax.

Once created, a materialized join is maintained by the system, so that changes to the data in the underlying tables/columns will trigger an update to the materialized join the next time it is used. However, there is also a command to update a materialized join manually, allowing for the planning and scheduling of materialized join updates, as they can take a substantial amount of time, depending on the degree of changes. The syntax for updating a materialized join is the following:

REFRESH MATERIALIZED JOIN join-name

If a materialized join is no longer needed, it can be removed from the system. The syntax for deleting a materialized join is the following:

DROP MATERIALIZED JOIN join-name

Note that dropping a materialized join beforehand is the only way to explicitly prevent the system from using the materialized join if an SQL statement contains a compatible join definition.