Oracle 物化视图

嗨，汤姆，

这是在下面的参考。sql运行时间太多了，所以把过滤器放在日期 .. 不能运行完整查询，它花了一个半小时。

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

以下是你想要的信息 .. 这里有拥抱数据 ..

MV的主要sql是

-------------------------
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
 

您可以在第10-13行中看到您的大部分时间都在燃烧。

你没有告诉我们分区键是什么，但我倾向于看看把它改成全扫描，特别是如果你能在那里得到一些分区消除。例如:

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

可以用

  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) ... )

* 如果 * invoice_trx_date是一个分区键。全面扫描还为并行查询带来了更大的收益潜力。