Materialized view is not showing up in plan table output from explain plan statement spool?

user_15185219 0
Viewed 98

I have created a materialized view on SH2 but, when I run my explain plan statement I can't see the materialized view being used in the plan table output. I'm not sure if it's a more complex materialized view with additional key columns to join to other dimensions, so I'm a bit confused why the materialized view isn't being utilized as I'm refering to the SH2 prefix in my select query.

CREATE MATERIALIZED VIEW fweek_pscat_sales_mv
PCTFREE 5
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS
SELECT   t.week_ending_day
,        p.prod_subcategory
,        sum(s.amount_sold) AS Money
,        s.channel_id
,        s.promo_id
FROM     sales s
,        times t
,        products p
WHERE    s.time_id = t.time_id
AND      s.prod_id = p.prod_id
GROUP BY t.week_ending_day
,        p.prod_subcategory
,        s.channel_id
,        s.promo_id;

CREATE BITMAP INDEX FW_PSC_S_MV_SUBCAT_BIX  
ON fweek_pscat_sales_mv(prod_subcategory);

CREATE BITMAP INDEX FW_PSC_S_MV_CHAN_BIX
ON fweek_pscat_sales_mv(channel_id);

CREATE BITMAP INDEX FW_PSC_S_MV_PROMO_BIX   
ON fweek_pscat_sales_mv(promo_id);

CREATE BITMAP INDEX FW_PSC_S_MV_WD_BIX
ON fweek_pscat_sales_mv(week_ending_day);

spool &data_dir.EXP_query_on_SH2_2.txt

alter session set query_rewrite_integrity = TRUSTED;
alter session set query_rewrite_enabled = TRUE;

set timing on

EXPLAIN PLAN FOR
SELECT   t.week_ending_day
,        p.prod_subcategory
,        sum(s.amount_sold) AS Money
,        s.channel_id
,        s.promo_id
FROM     SH2.sales s
,        SH2.times t
,        SH2.products p
WHERE    s.time_id = t.time_id
AND      s.prod_id = p.prod_id
GROUP BY t.week_ending_day
,        p.prod_subcategory
,        s.channel_id
,        s.promo_id;

REM Now Let us Display the Output of the Explain Plan
SET pagesize 9999
set linesize 250
set markup html preformat on
select * from table(dbms_xplan.display());
set linesize 80

spool off

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                         |  1016K|    60M|       | 17365   (1)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY                 |                         |  1016K|    60M|    70M| 17365   (1)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                    |                         |  1016K|    60M|       |  2178   (1)| 00:00:01 |       |       |
|   3 |    VIEW                        | index$_join$_003        | 10000 |   224K|       |    74   (0)| 00:00:01 |       |       |
|*  4 |     HASH JOIN                  |                         |       |       |       |            |          |       |       |
|   5 |      INDEX FAST FULL SCAN      | PRODUCTS_PK             | 10000 |   224K|       |    41   (0)| 00:00:01 |       |       |
|   6 |      INDEX FAST FULL SCAN      | PRODUCTS_PROD_SUBCAT_IX | 10000 |   224K|       |    51   (0)| 00:00:01 |       |       |
|*  7 |    HASH JOIN                   |                         |  1016K|    37M|       |  2101   (1)| 00:00:01 |       |       |
|   8 |     PART JOIN FILTER CREATE    | :BF0000                 |  1016K|    37M|       |  2101   (1)| 00:00:01 |       |       |
|   9 |      TABLE ACCESS FULL         | TIMES                   |  1461 | 23376 |       |    13   (0)| 00:00:01 |       |       |
|  10 |     PARTITION RANGE JOIN-FILTER|                         |  1016K|    22M|       |  2086   (1)| 00:00:01 |:BF0000|:BF0000|
|  11 |      TABLE ACCESS FULL         | SALES                   |  1016K|    22M|       |  2086   (1)| 00:00:01 |:BF0000|:BF0000|
----------------------------------------------------------------------------------------------------------------------------------
data-warehouse oracle11g plsql oracle
1 Answers

Why would you expect reference to the MV from the baseline query. For that to happen Oracle would have to compare the query to all MVs to find a match. Even further it would require every query be compared to every MV. MVs are typically created to avoid running the baseline query by accessing the MV directly (that is why MVs are the stored results of a query). If you want the MV just select from it directly.

SELECT  week_ending_day                                                                                                    
     ,  prod_subcategory                                                                                                   
     ,  Money                                                                                                              
     ,  channel_id                                                                                                         
     ,  promo_id                                                                                                           
  from fweek_pscat_sales_mv;
user_76238560
Related