20230908 - Materialized View Example

declare

    tune_mv varchar2(20) := 'mview_task';

begin

dbms_advisor.tune_mview

(

task_name=>tune_mv,

mv_create_stmt=>'create materialized view usage.c_s_mv

enable query rewrite

as

select tenant_name

     , usage_interval_start

     , team_compartment

     , prd_region

     , prd_service

     , prd_resource

     , sum(cost_my_cost_usd) cost_my_cost_usd

  from usage.oci_cost

group by tenant_name

     , usage_interval_start

     , team_compartment

     , prd_region

     , prd_service

     , prd_resource'

);

end;

select * from user_tune_mview;

drop materialized view log on oci_cost;

create materialized view log on oci_cost 

  with rowid, sequence 

  (tenant_name

 , usage_interval_start

 , prd_service

 , prd_resource

 , prd_region

 , cost_product_sku

 , cost_my_cost_usd

 , team_compartment)

including new values;

drop materialized view mv_oci_cost;

create materialized view mv_oci_cost

refresh fast with rowid enable query rewrite 

as 

elect tenant_name

     , trunc(usage_interval_start) usage_interval_start

     , team_compartment

     , team_manager

     , prd_region

     , prd_resource

     , prd_service

     , cost_product_sku

     , sum(cost_my_cost_usd) cost_my_cost_usd

     , count(cost_my_cost_usd) cost_my_cost_cnt

     , count(*) ttl_cnt

 from oci_cost

group by tenant_name

     , trunc(usage_interval_start)

     , team_compartment

     , team_manager

     , prd_region

     , prd_resource

     , prd_service

     , cost_product_sku;

BEGIN

  DBMS_MVIEW.REFRESH('mv_oci_cost');

END;