You Asked

hi tom,

this is in reference of below.sql running time was too much so put filter on date..canT run full query it took one and half hour.

https://asktom.oracle.com/pls/apex/asktom.search?tag=materialized-view

Below are the info you wanted..here pr_fact has hug data..

Main sql of MV is

-------------------------
select /*+ gather_plan_statistics */ * from (select 
         f.client_id,
         f.account_dim_id,
         f.org_dim_dc_id,
         f.org_dim_unit_id,
         f.instill_product_dim_id,
         f.instill_product_id,
         f.client_product_dim_id,
         d.month_period_num,
         sum(f.fact_delivered_cost) as TOTAL_COST,
         sum(f.fact_delivered_cases) as TOTAL_CASES ,
         sum(f.fact_delivered_weight) as TOTAL_WEIGHT,
         sum(f.fact_delivered_volume)  as TOTAL_VOLUME,
         sum(f.fact_delivered_count) as TOTAL_COUNT,
         f.currency_dim_id as CURRENCY_DIM_ID
  from   pr_fact_x f,
         day_pr_mst_x d
  where  f.client_id            = d.client_id
  and    f.invoice_trx_date = d.calendar_date
  group by f.client_id,
         f.account_dim_id,
         f.org_dim_dc_id,
         f.org_dim_unit_id,
         f.instill_product_dim_id,
         f.instill_product_id,
         f.client_product_dim_id,
         d.month_period_num ,
         f.currency_dim_id ) ;


------------------------


SQL_ID  ahyy6cjfrq4d3, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from (select           
f.client_id,          f.account_dim_id,          f.org_dim_dc_id,       
   f.org_dim_unit_id,          f.instill_product_dim_id,          
f.instill_product_id,          f.client_product_dim_id,          
d.month_period_num,          sum(f.fact_delivered_cost) as TOTAL_COST,  
        sum(f.fact_delivered_cases) as TOTAL_CASES ,          
sum(f.fact_delivered_weight) as TOTAL_WEIGHT,          
sum(f.fact_delivered_volume)  as TOTAL_VOLUME,          
sum(f.fact_delivered_count) as TOTAL_COUNT,          f.currency_dim_id 
as CURRENCY_DIM_ID   from   pr_fact_x f,          day_pr_mst_x d   
where  f.client_id            = d.client_id   and    f.invoice_trx_date 
= d.calendar_date  and f.invoice_trx_date between '01-12-19' and 
'31-12-19'   group by f.client_id,          f.account_dim_id,          
f.org_dim_dc_id,          f.org_dim_unit_id,          
f.instill_product_dim_id,          f.instill_product_id,          
f.client_product_dim_id,
 
Plan hash value: 3596705395
 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name                  | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers | Writes |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                       |      1 |        |       |   856K(100)|     50 |00:02:15.02 |    1978K|  92512 |       |       |          |
|   1 |  HASH GROUP BY                                |                       |      1 |     31 |  6107 |   856K (96)|     50 |00:02:15.02 |    1978K|  92512 |   651K|   651K|          |
|*  2 |   FILTER                                      |                       |      1 |        |       |            |     23M|00:01:09.06 |    1978K|      0 |       |       |          |
|*  3 |    HASH JOIN                                  |                       |      1 |     25M|  4756M|   854K (96)|     23M|00:01:01.73 |    1978K|      0 |   922K|   922K|   77M (0)|
|*  4 |     VIEW                                      | index$_join$_003      |      1 |    874 | 16606 |    31   (4)|    868 |00:00:00.02 |      67 |      0 |       |       |          |
|*  5 |      HASH JOIN                                |                       |      1 |        |       |            |    868 |00:00:00.02 |      67 |      0 |  1128K|  1128K| 1585K (0)|
|*  6 |       HASH JOIN                               |                       |      1 |        |       |            |    868 |00:00:00.01 |      35 |      0 |  1242K|  1242K| 1629K (0)|
|*  7 |        INDEX RANGE SCAN                       | XIE_DAY_PR_MST_DATE   |      1 |    874 | 16606 |     2   (0)|    868 |00:00:00.01 |       3 |      0 |       |       |          |
|   8 |        INDEX FAST FULL SCAN                   | XIE_DAY_PR_MST_CLIENT |      1 |    874 | 16606 |    17   (0)|  22757 |00:00:00.01 |      32 |      0 |       |       |          |
|   9 |       INDEX FAST FULL SCAN                    | XIE_DAY_PR_MST_MON    |      1 |    874 | 16606 |    17   (0)|  22757 |00:00:00.01 |      32 |      0 |       |       |          |
|  10 |     PARTITION RANGE ITERATOR                  |                       |      1 |     25M|  4259M|   854K (96)|     25M|00:00:49.53 |    1978K|      0 |       |       |          |
|  11 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T_PR_FACT             |      1 |     25M|  4259M|   854K (96)|     25M|00:00:43.72 |    1978K|      0 |       |       |          |
|  12 |       BITMAP CONVERSION TO ROWIDS             |                       |      1 |        |       |            |     25M|00:00:05.05 |     759 |      0 |       |       |          |
|* 13 |        BITMAP INDEX RANGE SCAN                | XIE_T_PR_FACT_TRX_DT  |      1 |        |       |            |   1460 |00:00:00.03 |     759 |      0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(TO_DATE('31-12-19')>=TO_DATE('01-12-19'))
   3 - access("CLIENT_ID"="D"."CLIENT_ID" AND "INVOICE_TRX_DATE"="D"."CALENDAR_DATE")
   4 - filter(("D"."CALENDAR_DATE">='01-12-19' AND "D"."CALENDAR_DATE"<='31-12-19'))
   5 - access(ROWID=ROWID)
   6 - access(ROWID=ROWID)
   7 - access("D"."CALENDAR_DATE">='01-12-19' AND "D"."CALENDAR_DATE"<='31-12-19')
  13 - access("INVOICE_TRX_DATE">='01-12-19' AND "INVOICE_TRX_DATE"<='31-12-19')
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan



—view definition……………..

replace view pr_fact_x as lock row for access 
select pr_fact_id,
       client_id,
       account_dim_id,
       org_dim_unit_id,
       org_dim_dc_id,
       instill_product_dim_id,
       instill_product_id,
       client_product_dim_id,
       currency_dim_id ,
       invoice_trx_date,
       invoice_num,
       fact_delivered_quantity,
       fact_delivered_cost,
       equival_cases  as fact_delivered_cases,
       equival_weight as fact_delivered_weight,
       equival_volume as fact_delivered_volume,
       equival_count  as fact_delivered_count,
       po_num,
       line_num,
       unit_price,
       gross_weight,
       tax,
       handling_cost,
       discount,
       package_id,
       known_acct_sig_id,
       known_trx_sig_id,
       idm_data_type_id,
       data_tag_id,
       ts
from   idm_etl.t_pr_fact fact; 

———————MV  definition


create materialized view day_pr_mst_x nologging
tablespace idm_msta_ts
build deferred
using index tablespace idm_msta_is
refresh complete
as
select distinct t.client_id, calendar_date, day_num, day as DAY_OF_WEEK,
                week_num, week, month_period_num, month_period,
                quarter_num, quarter, year_num, year ,
                prev_calendar_date, prev_week_num, prev_month_period_num, prev_quarter_num, prev_year_num,
                last_year_calendar_date,last_year_week_num, last_year_month_period_num,last_year_quarter_num, last_year_year_num ,
                prev_three_period_num ,
                to_number(to_char (( case when to_char(t.calendar_date,'DY') = 'SUN' then calendar_date else trunc(t.calendar_date,'iw' ) -1 end),'YYYYMMDD')) as calendar_week_num , 
               ( to_char (( case when to_char(t.calendar_date,'DY') = 'SUN' then calendar_date else trunc(t.calendar_date,'iw' ) -1 end ) , 'MM/DD' )  
                  || ' - ' ||
                 to_char (( case when to_char(t.calendar_date,'DY') = 'SUN' then calendar_date else trunc(t.calendar_date,'iw' ) -1 end ) +6 ,'MM/DD' )
               ) as calendar_week ,
               to_date(to_char((case when to_char(t.calendar_date,'DY') = 'SUN' then calendar_date else trunc(t.calendar_date,'iw' ) -1 end ),'MM/DD/YYYY' ),'mm/dd/yyyy')        as calendar_week_start_date ,
               to_date(to_char((case when to_char(t.calendar_date,'DY') = 'SUN' then calendar_date else trunc(t.calendar_date,'iw' ) -1 end ) +6 , 'MM/DD/YYYY' ), 'mm/dd/yyyy' ) as calendar_week_end_date ,
               to_number(to_char((case when to_char(t.calendar_date,'DY') = 'SUN' then calendar_date else trunc(t.calendar_date,'iw' ) -1 end) -7,'YYYYMMDD' ))                   as prev_calendar_week_num   ,
               to_number(to_char(((case when to_char(t.calendar_date,'DY') = 'SUN' then t.calendar_date else trunc(t.calendar_date,'iw') -1 end )- (52*7) ) ,'YYYYMMDD' ))        as last_year_calendar_week_num  
               /* t.partition_week_num  ,
                t.partition_month_period_num */
from t_time_period_dim t,
     t_client_min_trx_date cmd,
     t_solution s
where t.client_id     = cmd.client_id
  and t.solution_id   = cmd.solution_id
  and cmd.solution_id = s.solution_id
  and s.solution_name = upper('PR')
  and t.calendar_date >= cmd.min_trx_date;

-----------------INDEXES ON T_PR_FACT...

XPKT_PR_FACT - PR_FACT_ID
XIE_T_PR_FACT_CLIENT - CLIENT_ID
XIE_T_PR_FACT_ACCOUNT - ACCOUNT_DIM_ID
XIE_T_PR_FACT_ORG_DC - ORG_DIM_DC_ID
XIE_T_PR_FACT_IPC_DIM - INSTILL_PRODUCT_DIM_ID
XIE_T_PR_FACT_TRX_DT - INVOICE_TRX_DATE
XIE_T_PR_FACT_KAS - KNOWN_ACCT_SIG_ID
XIE_T_PR_FACT_KTS - KNOWN_TRX_SIG_ID
XIE_T_PR_FACT_PACKAGE - PACKAGE_ID
XIE_T_PR_FACT_TS - TS
XIE_T_PR_FACT_INSTILL_PRODUCT - INSTILL_PRODUCT_ID
XIE_T_PR_FACT_CLIENT_PRODUCT - CLIENT_PRODUCT_DIM_ID

and Connor said...

You can see you are burning most of your time in lines 10-13.

You didn't tell us what the partitioning key is, but I'd be inclined to take a look at changing this to a full scan, especially if you can get some partition elimination in there. For example, :

  where  f.client_id            = d.client_id
  and    f.invoice_trx_date = d.calendar_date

could be augmented with

  where  f.client_id            = d.client_id
  and    f.invoice_trx_date = d.calendar_date
  and   f.invoice_trx_date >= ( select min(d.calendar_date) ... )

*if* invoice_trx_date is a partitioning key. A full scan also opens up more potential for benefits with parallel query.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer

You Asked

and Connor said...

More to Explore

Performance

Materialized view

Breadcrumb

Question and Answer

You Asked

and Connor said...

More to Explore

Performance