Hi,
I am trying to use MVs to enhance the performance of some queries but the CBO thinks, that querying the MV is more expensive than querying the origin table. But it takes much less time to get the results from the MV (< 1 Sec) than without the MV (> 3 seconds).
The MV:
CREATE MATERIALIZED VIEW DM_F_CURR_KOFE_MV2
compress
BUILD IMMEDIATE
USING INDEX
REFRESH complete ON DEMAND
with primary key
ENABLE QUERY REWRITE
AS
select f.TIME_DWH_PK,f.PRJ_DWH_PK, f.BOOKING_DWH_PK, f.CF_DWH_PK, sum(f.FACT_GK_CST) FACT_GK_CST,sum(f.FACT_MAT_CST) FACT_MAT_CST,sum(f.FACT_PERS_CST) FACT_PERS_CST,sum(f.FACT_PERS_HRS) FACT_PERS_HRS ,grouping_id(f.TIME_DWH_PK,f.PRJ_DWH_PK, f.BOOKING_DWH_PK, f.CF_DWH_PK) as gid
from DM_F_CPL_CURR_STICHTAGE_MONAT f
group by rollup(f.TIME_DWH_PK,f.CF_DWH_PK,(f.BOOKING_DWH_PK,f.PRJ_DWH_PK));
I've created on each column a Bitmap Index.
I run the following query:
select T9068.DAY as c1,
T18972.YEAR_NUMBER as c2,
sum(T20978.FACT_MAT_CST) as c3,
sum(T20978.FACT_PERS_CST) as c4,
sum(T20978.FACT_GK_CST) as c5,
T18972.YEAR_SOURCE_PK as c6
from
V_DM_D_TIME T9068,
V_DM_D_MONTH_BOOKING T18972,
DM_F_CPL_CURR_STICHTAGE_MONAT T20978
where ( T9068.DIMENSION_KEY = T20978.TIME_DWH_PK and T9068.DAY = TO_DATE('2010-02-16' , 'YYYY-MM-DD') and T18972.DWH_PK = T20978.BOOKING_DWH_PK and T18972.YEAR_NUMBER = '2008' )
group by T9068.DAY, T18972.YEAR_SOURCE_PK, T18972.YEAR_NUMBER
order by c1, c2
If I run the query with the REWRITE hint, the MV is being used and it costs around 50:
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 300 | 50 (4)| 00:00:01 |
| 1 | HASH GROUP BY | | 6 | 300 | 50 (4)| 00:00:01 |
|* 2 | MAT_VIEW REWRITE ACCESS BY INDEX ROWID| DM_F_CURR_KOFE_MV2 | 1 | 36 | 49 (3)| 00:00:01 |
| 3 | NESTED LOOPS | | 17 | 850 | 49 (3)| 00:00:01 |
| 4 | NESTED LOOPS | | 12 | 336 | 7 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | CO_D_TIME | 1 | 14 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | D_TIME_DAY_IDX | 1 | | 1 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | CO_D_MONTH | 12 | 168 | 5 (0)| 00:00:01 |
| 8 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 9 | BITMAP AND | | | | | |
|* 10 | BITMAP INDEX SINGLE VALUE | F_CURR_KOFE_MV2_BOOKING_DWH_PK | | | | |
|* 11 | BITMAP INDEX SINGLE VALUE | F_CURR_KOFE_MV2_TIME_DWH_PK | | | | |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DM_F_CURR_KOFE_MV2"."GID"=0)
6 - access("DAY"=TO_DATE(' 2010-02-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
7 - filter("YEAR_NUMBER"=2008)
10 - access("DWH_PK"="DM_F_CURR_KOFE_MV2"."BOOKING_DWH_PK")
11 - access("DIMENSION_KEY"="DM_F_CURR_KOFE_MV2"."TIME_DWH_PK")
If I run the query without any hints, the MV isn't being used and it costs around 38:
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 288 | 38 (6)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 6 | 288 | 38 (6)| 00:00:01 | | |
|* 2 | HASH JOIN | | 29 | 1392 | 37 (3)| 00:00:01 | | |
|* 3 | TABLE ACCESS FULL | CO_D_MONTH | 12 | 168 | 5 (0)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 426 | 14484 | 32 (4)| 00:00:01 | | |
| 5 | TABLE ACCESS BY INDEX ROWID | CO_D_TIME | 1 | 14 | 2 (0)| 00:00:01 | | |
|* 6 | INDEX UNIQUE SCAN | D_TIME_DAY_IDX | 1 | | 1 (0)| 00:00:01 | | |
| 7 | PARTITION LIST ITERATOR | | 426 | 14484 | 32 (4)| 00:00:01 | KEY | KEY |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID| DM_F_CPL_CURR_STICHTAGE_MONAT | 426 | 14484 | 32 (4)| 00:00:01 | KEY | KEY |
| 9 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 10 | BITMAP INDEX SINGLE VALUE | F_CPLCURR_M_TIME_DWH_PK_IDX | | | | | KEY | KEY |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DWH_PK"="T20978"."BOOKING_DWH_PK")
3 - filter("YEAR_NUMBER"=2008)
6 - access("DAY"=TO_DATE(' 2010-02-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
10 - access("DIMENSION_KEY"="T20978"."TIME_DWH_PK")
The statistics for both the MV and the origin table have been collected as follows:
exec dbms_stats.gather_table_stats(ownname=>'DWH_MART',tabname=>'DM_F_CURR_KOFE_MV2');
exec dbms_stats.gather_table_stats(ownname=>'DWH_MART',tabname=>'DM_F_CPL_CURR_STICHTAGE_MONAT');
Why is the CBO thinking that it's more expensive to use the MV?
The DB is 10g.
// EDIT
Ok, i got it 
My source table was partitioned, my MV not. as soon as i've partitioned the MV the CBO uses the MV... god, what a waste of time...
but thx anyway
[Updated on: Tue, 03 August 2010 08:39]
Report message to a moderator
] 
Blog
Search
Help
Members
Register
Login
Home
