Optimizing queries using Materialized View (MV) in Query optimizer

Materialized Views (MV) in IBM® watsonx.data are pre-calculated results of complex queries. By storing these results, watsonx.data can significantly speed up query execution, especially for those involving joins and aggregations. Instead of re-running the original query each time, watsonx.data can directly use the already computed Materialized View (MV) for faster query responses and improved overall performance.

Following are some of the benefits of Materialized Views (MV):

Improved query performance: Materialized View (MV) can significantly reduce query execution time by providing already computed results for frequently used complex queries.
Reduced resource consumption and expense: By avoiding repeated calculations, Materialized View (MV) can lower the workload on watsonx.data resources, leading to improved overall system efficiency and reduced cost.
Transparent Integration: Materialized View (MV) are seamlessly integrated into the query execution process, requiring minimal user intervention.

Before you begin

Install and activate Query Optimizer. For more information, see Activating Query Optimizer.
Sync Query Optimizer with watsonx.data meta store. For more information, see Manually syncing Query Optimizer.

Procedure

Follow the steps to enable Materialized View (MV) feature for Query Optimizer in watsonx.data.

Identify and select queries that are complex, frequently used, and involve joins or aggregations.

For example, consider the following sample query as a potential Materialized View (MV) candidate:

select sum(sales), region
from (
  select sales, region from customer_sales
  union all
  select sales, region from store_sales
  union all
  select sales, region from union_sales
  union all
  select sales, region from online_sales
) group by region;

Run the following command to create the Materialized View (MV) named mv for the selected query.

create table mv (sumsales, region) as (
  select sum(sales),  region
  from (
    select sales, region from customer_sales
    union all
    select sales, region from store_sales
    union all
    select sales, region from union_sales
    union all
    select sales, region from online_sales
  ) group by region
);

Auto refresh of Materialized View (MV) table is not available yet. To update an existing table, you must delete the table and create the Materialized View (MV) again.

Run the following command to analyze the Materialized View (MV) table mv.
```
analyze mv;
```

Run the following commands to synchronize the newly created Materialized View (MV) table mv with the Query Optimizer.

Run the following command to synchronize the Materialized View table mv from watsonx.data to Db2:

ExecuteWxdQueryOptimizer 'call syshadoop.external_catalog_sync('<catalog>', '<SCHEMA>', '<mvtablename>', 'REPLACE', 'CONTINUE', 'OAAS')';

Run the following command to synchronize statistics of Materialized View table mv in Db2:

ExecuteWxdQueryOptimizer 'call ext_metastore_stats_sync('<catalog>', '<SCHEMA>', '<MVTABLENAME>', '<engine-internal-URL:port>', '<admin>', '<admin-password>', 'true')';

Run the following command to add the Materialized View (MV) table mv in Db2:

ExecuteWxdQueryOptimizer 'values (prestosqlddl('alter table <mvtablename> add materialized query (<query>) data initially deferred refresh deferred maintained by user enable query optimization', '<catalog>', '<SCHEMA>'))';

Run the following command to set the constraints for the Materialized View (MV) table mv:

ExecuteWxdQueryOptimizer 'values (prestosqlddl('set constraints for <mvtablename> all immediate unchecked', '<catalog>', '<SCHEMA>'))';

Set the use_materialized_views session variable to true to enable Materialized View (MV) for query optimization in a particular session:
```
SET SESSION use_materialized_views=true;
```
Run SQL queries to leverage Materialized view tables for query optimization after enabling and configuring them.