| Queries are not hiting the MV [message #220032] |
Sat, 17 February 2007 22:27 |
fhsql
Messages: 3
Registered: February 2007
Location: DUBAI
|
Junior Member |
|
|
I have created a below MV. During testing the queries are not hiting the MV. can anybody please explain me the reason.
create materialized view MV_fh_test1
parallel
build immediate
enable query rewrite
as
SELECT
a.MONTHID,
b.COUNTRY_NAME,
c.COMPARTMENT,
c.BKG_CLASS,
d.AIRLINE_NAME,
e.airport,
f.ROUTING,
g.ALL_SECTORS_ON_MAIN_CARRIER,
g.EK_PRESENT_IN_OP_CARRIERS,
f.ITINERY,
sum(h.PAX)
FROM
t_month_dim a,
t_cpos_dim b,
t_Fareclass_Dim c,
t_airline_dim d,
t_airport_dim e,
t_Itinery_Dim f,
t_itinery_flag_dim g,
t_CITINERY_FACT h
WHERE
( h.AIRLINE_KEY=d.AIRLINE_KEY )
AND ( b.CPOS_KEY=h.CPOS_KEY )
AND (h.FARECLASS_KEY=c.FARECLASS_KEY )
AND ( h.TRUE_ORIGIN_AIRPORT_KEY=e.AIRPORT_KEY )
--AND ( V_TRUE_DESTINATION.AIRPORT_KEY=h.TRUE_DEST_AIRPORT_KEY )
AND ( h.ITINERY_KEY=f.ITINERY_KEY )
AND ( a.MONTHID=h.TRAVEL_MNTH_KEY )
AND ( g.FLAG_KEY=h.FLAG_KEY )
AND ( g.FR_FLAG = 'R')
GROUP BY
a.MONTHID,
b.COUNTRY_NAME,
c.COMPARTMENT,
c.BKG_CLASS,
d.AIRLINE_NAME,
e.airport,
f.ROUTING,
g.ALL_SECTORS_ON_MAIN_CARRIER,
g.EK_PRESENT_IN_OP_CARRIERS,
f.ITINERY;
#########################################
This is the query i was testing against the above MV.
EXPLAIN PLAN FOR
SELECT
a.MONTHID,
b.country_name,
sum(c.PAX)
FROM
t_month_dim a,
t_cpos_dim b,
t_citinery_fact c,
t_ITINERY_FLAG_dim d
WHERE
( b.CPOS_KEY = c.CPOS_KEY )
AND ( a.MONTHID = c.TRAVEL_MNTH_KEY )
AND ( d.FLAG_KEY = c.FLAG_KEY )
AND ( d.FR_FLAG = 'R')
GROUP BY
a.MONTHID,
b.country_name;
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 503 | 23641 | 254 (
| 1 | HASH GROUP BY | | 503 | 23641 | 254 (
| 2 | HASH JOIN | | 128K| 5894K| 239
| 3 | INDEX FAST FULL SCAN | PK_CPOS_DIM_TEST | 237 | 3555 | 2
| 4 | HASH JOIN | | 128K| 4013K| 235
| 5 | INDEX FAST FULL SCAN| PK_MONTH_DIM | 181 | 1448 | 3
| 6 | HASH JOIN | | 128K| 3010K| 229
| 7 | INDEX FULL SCAN | PK_ITINERY_FLAG_DIM | 16 | 80 | 1
| 8 | PARTITION RANGE ALL| | 128K| 2383K| 226
| 9 | TABLE ACCESS FULL | T_CITINERY_FACT | 128K| 2383K| 226
--------------------------------------------------------------------------------
Note
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
