SAND CDBMS SQL Reference Guide
REFRESH MATERIALIZED JOIN

 

The REFRESH MATERIALIZED JOIN command manually updates a materialized join, created previously with the CREATE MATERIALIZED JOIN command.


Required Privileges

In order to refresh a materialized join, the user authorization must own or possess OWNER privileges on the schema to which the materialized join belongs, or possess DBA privileges.


Syntax


join name
This is the name of an existing materialized join in the current schema. The materialized join name may be qualified by a schema name (that is, schema-name.join-name) to update a materialized join that is not in the current schema.


Description

The REFRESH MATERIALIZED JOIN command updates an existing materialized join, if the data in the source tables has changed since the last refresh (or initial creation).

Materialized joins are refreshed automatically by the system, but each refresh occurs the first time a join is used in an SQL statement after changes to the source table data. Since this refresh process can take a significant amount of time, depending on the degree of changes, it is sometimes preferable to schedule it for convenience using the REFRESH MATERIALIZED JOIN command. For example, if many new records are loaded into a table that participates in a materialized join, the next SQL statement to use that join might trigger a full update of the materialized join, which means that the SQL statement will not benefit from a join index performance boost. However, if the REFRESH MATERIALIZED JOIN command is scripted to run immediately after the data load in the batch window, the next SQL statement to use the join will be able to take full advantage of the enhanced performance from the join index.

Note that the REFRESH MATERIALIZED JOIN command is subject to the query timeout setting. If the refresh process exceeds the amount of time defined in the TIMEOUT clause of the SET TRANSACTION statement from the start of the transaction, the refresh will be halted, and the materialized join will be rolled back to the state it was in before the refresh started.

The REFRESH MATERIALIZED JOIN command must also respect the JoinLimit setting in the Connection section of the nucleus.ini file. That is, the materialized join being refreshed must not exceed the intermediate join limit for the connection (500 billion rows, by default).


Example

REFRESH MATERIALIZED JOIN s1.matjoin;

The above example updates an existing materialized join in schema s1 named matjoin.