Optimize Cloud Links Performance with Materialized Views

Materialized views improve cloud links performance by caching remote data locally in the consumer database and refreshing it incrementally instead of re-reading everything over the network each time.

Materialized views are often used to improve performance, especially when storing data in a local database that is sourced from remote tables or views through a database link. Similarly, materialized views are often used instead of views when there are extensive calculations or summarizations, especially on large data sets. Materialized views are refreshed at specified intervals, which means the data is not always up-to-date. However, queries against these materialized views are significantly faster in these use cases.

Refreshing Materialized Views
The database maintains data in materialized views by refreshing them after changes to the base tables. Refreshing a materialized view automatically updates all its indexes. In the case of full refresh, this requires temporary sort space to rebuild all indexes during refresh.
Incremental Refresh of Materialized Views over Cloud Links
The incremental refresh is commonly called FAST refresh because it usually performs faster than the complete refresh.
Register a Cloud Link with Materialized View Fast Refresh
You must register a cloud link to indicate that materialized view fast refresh is supported. Use the REGISTER procedure in the DBMS_CLOUD_LINK package with MV_FAST_REFRESH argument to register the cloud link.
Update a Cloud Link with Materialized View Fast Refresh
You can update a cloud link to indicate that materialized view fast refresh is supported on the cloud link.
Example: Use Case of Incremental Refresh of Materialized Views
A Data Analyst working with dashboards across Autonomous AI Database instances can now define materialized views on remote Autonomous AI Database tables accessed via cloud links and refresh them incrementally. This enables fast dashboard updates without full data reloads, improves the user experience and reduces costs.

Parent topic: Use Cloud Links for Read Only Data Access on Autonomous AI Database

Refreshing Materialized Views

The database maintains data in materialized views by refreshing them after changes to the base tables. Refreshing a materialized view automatically updates all its indexes. In the case of full refresh, this requires temporary sort space to rebuild all indexes during refresh.

Types of Refresh for Materialized Views:

The following is an incremental refresh methods for materialized views that use cloud links:

log-based refresh

For different refreshing methods, you can refer to the Refreshing Materialized Views chapter.

Note:

Partition change tracking (PCT) refresh and logical partition change tracking (LPCT) refresh are not supported for materialized views that use cloud links.

Parent topic: Optimize Cloud Links Performance with Materialized Views

Incremental Refresh of Materialized Views over Cloud Links

The incremental refresh is commonly called FAST refresh because it usually performs faster than the complete refresh.

An incremental refresh eliminates the need to rebuild materialized views from scratch. Thus, processing only the changes can result in a very fast refresh time. For materialized views that use the log-based fast refresh method, a materialized view log keep a record of changes to the base tables. A materialized view log is a schema object that records changes to a base table so that a materialized view defined on the base table can be refreshed incrementally. Each materialized view log is associated with a single base table. The materialized view log resides in the same database and schema as its base table. You can enable Oracle Autonomous AI Database to define and refresh materialized views on remote tables accessed via cloud links.

Autonomous AI Database Serverless performs incremental refresh of materialized views using FAST refresh. This method applies only the changes made since the last refresh, rather than performing a complete refresh of the entire materialized view.

With incremental refresh support:

Dashboards and reports can be refreshed quickly without full data reloads, enabling near real-time analytics.
Improved Service Level Agreements (SLAs) for analytics workloads through enhanced data freshness.

Refer to Types of Materialized Views for various types of materialized views that support incremental refresh with cloud links.

Parent topic: Optimize Cloud Links Performance with Materialized Views

Register a Cloud Link with Materialized View Fast Refresh

You must register a cloud link to indicate that materialized view fast refresh is supported. Use the REGISTER procedure in the DBMS_CLOUD_LINK package with MV_FAST_REFRESH argument to register the cloud link.

Assume that a materialized view log table has been created on the SALES_VIEW_AGG table in the CLOUDLINK schema. Then it can be registered as a cloud link with fast refresh capability enabled by passing TRUE value for MV_FAST_REFRESH argument in the REGISTER procedure.

BEGIN
   DBMS_CLOUD_LINK.REGISTER(
    schema_name     => 'CLOUDLINK',
    schema_object   => 'SALES_VIEW_AGG',
    namespace       => 'REGIONAL_SALES', 
    name            => 'SALES_AGG',
    description     => 'Aggregated regional sales information.',
    scope           => 'MY$TENANCY',
    auth_required   =>  FALSE,
    data_set_owner  =>  'tomholl@example.com',
    mv_fast_refresh =>  TRUE );
END;
/

Note:

As a provider, you must create the materialized view log table on the base table in the provider database that corresponds to the materialized view in the consumer database.

Refer to REGISTER Procedure for more information.

Parent topic: Optimize Cloud Links Performance with Materialized Views

Update a Cloud Link with Materialized View Fast Refresh

You can update a cloud link to indicate that materialized view fast refresh is supported on the cloud link.

Assume that a cloud link is registered without fast refresh capability:

BEGIN
   DBMS_CLOUD_LINK.REGISTER(
    schema_name    => 'CLOUDLINK',
    schema_object  => 'SALES_ALL',
    namespace      => 'TRUSTED_COMPARTMENT', 
    name           => 'SALES',
    description    => 'Trusted Compartment, only accessible within my compartment. Early sales data.',
    scope          => 'MY$COMPARTMENT',
    auth_required  =>  FALSE,
    data_set_owner =>  'tomholl@example.com' );
END;
/

Assume that a materialized view log table is later created on the SALES_ALL table in CLOUDLINK schema. The cloud link can be updated to enable fast refresh capability by passing TRUE value for MV_FAST_REFRESH argument in the UPDATE_REGISTRATION procedure.

BEGIN
   DBMS_CLOUD_LINK.UPDATE_REGISTRATION(
    namespace       => 'TRUSTED_COMPARTMENT', 
    name            => 'SALES',
    mv_fast_refresh => TRUE );
END;
/

The UPDATE_REGISTRATION procedure enables materialized view fast refresh over cloud links by ensuring that the materialized view log table is accessible over the cloud link. To make it accessible, you must pass a value of TRUE for the MV_FAST_REFRESH argument.

Refer to UPDATE_REGISTRATION Procedure for more information.

Since the provider has created the required materialized view logs. You must refresh the link’s metadata to ensure it reflects the new log tables.

Parent topic: Optimize Cloud Links Performance with Materialized Views

Example: Use Case of Incremental Refresh of Materialized Views

A Data Analyst working with dashboards across Autonomous AI Database instances can now define materialized views on remote Autonomous AI Database tables accessed via cloud links and refresh them incrementally. This enables fast dashboard updates without full data reloads, improves the user experience and reduces costs.

Prerequisites

Before implementing incremental refresh for materialized views over cloud links, ensure the following prerequisites are met.

Cloud links are properly configured and tested.
Network connectivity between consumer and provider databases is stable.
Appropriate privileges are granted to users who will create and refresh materialized views.
The provider database has materialized view logging configured for the base tables.

The Data Analyst will build dashboards in Consumer Autonomous AI Database that query data from Provider Autonomous AI Database using cloud links. Instead of full table scans across the Autonomous AI Database (slow and expensive), they create materialized views in Consumer Autonomous AI Database with fast refresh to incrementally update only changed data from Provider Autonomous AI Database tables.

Provider creates materialized view log on base table.
In the Provider Autonomous AI Database a database admin runs one of these DBMS_CLOUD_LINK procedures:
- REGISTER: Creates a brand new cloud link registration with MV_FAST_REFRESH=TRUE, enabling fast refresh capability from the start.
- UPDATE_REGISTRATION: Modifies an existing cloud link registration to add fast refresh capability.
The provider now knows logs exist and can read them.
The Data Analyst, working in the Consumer Autonomous AI Database (their dashboard database), runs a CREATE MATERIALIZED VIEW statement with REFRESH FAST to build their dashboard data cache.

Parent topic: Optimize Cloud Links Performance with Materialized Views