物化视图反而影响查询性能

老外发来邮件说

We are facing varied database response for same query but for different values, the time gap in the execution time (1st 9 min and 2nd 56 sec) for both queries is quite considerable and reason for worry. Please check and let us know the root cause of this issue.

同样的一个SQL语句，过滤值不同，第一个跑9分钟，第二个跑56秒，他们的执行计划都一样让我调查一下root cause.
SELECT ---9:11 sec
DRTT_CAL_679_FDIM.MTH_NAME,
DRTT_GEO_707_FDIM.GEO_5_NAME,
DRTT_CHANL_658_FDIM.TRADE_CHANL_1_NAME,
DRTT_PROD_LOC_V1_FDIM.PROD_1_ENGLH_NAME,
COUNT(DISTINCT DRTT_SKU_LIVE_IFCT.STORE_ID),
DRTT_PROD_LOC_V1_FDIM.PROD_3_ENGLH_NAME,
DRTT_PROD_LOC_V1_FDIM.PROD_5_ENGLH_NAME
FROM
DRTT_CAL_679_FDIM,
DRTT_GEO_707_FDIM,
DRTT_CHANL_658_FDIM,
DRTT_PROD_LOC_V1_FDIM,
DRTT_SKU_LIVE_IFCT,
DRTT_DIST_910_V1_FDIM
WHERE
( DRTT_GEO_707_FDIM.GEO_8_ID=DRTT_SKU_LIVE_IFCT.GEO_ID )
AND ( DRTT_CHANL_658_FDIM.TRADE_CHANL_3_ID=DRTT_SKU_LIVE_IFCT.TRADE_CHANL_ID )
AND ( DRTT_SKU_LIVE_IFCT.PROD_ID=DRTT_PROD_LOC_V1_FDIM.PROD_9_ID )
AND ( DRTT_CAL_679_FDIM.DAY_DATE=DRTT_SKU_LIVE_IFCT.PRTTN_BY_FUNC_DATE )
AND ( DRTT_DIST_910_V1_FDIM.DIST_4_ID=DRTT_SKU_LIVE_IFCT.DIST_ID )
AND
(
( DRTT_CAL_679_FDIM.DAY_DATE=CASE WHEN 'AUG2010'='Current Month' THEN Last_day(to_date(('01'||upper(to_char(sysdate,'monyyyy'))),'ddmonyyyy')) ELSE last_day(to_date(('01'||'AUG2010'),'ddmonyyyy')) END OR DRTT_CAL_679_FDIM.DAY_DATE=CASE WHEN 'AUG2010'='Current Month' THEN Last_day(add_months(to_date(('01'||upper(to_char(sysdate,'monyyyy'))),'ddmonyyyy'),-1)) ELSE last_day(add_months(to_date(('01'||'AUG2010'),'ddmonyyyy'),-1)) END OR DRTT_CAL_679_FDIM.DAY_DATE=CASE WHEN 'AUG2010'='Current Month' THEN Last_day(add_months(to_date(('01'||upper(to_char(sysdate,'monyyyy'))),'ddmonyyyy'),-2)) ELSE last_day(add_months(to_date(('01'||'AUG2010'),'ddmonyyyy'),-2)) END OR DRTT_CAL_679_FDIM.DAY_DATE=CASE WHEN 'AUG2010'='Current Month' THEN Last_day(add_months(to_date(('01'||upper(to_char(sysdate,'monyyyy'))),'ddmonyyyy'),-3)) ELSE last_day(add_months(to_date(('01'||'AUG2010'),'ddmonyyyy'),-3)) END )
AND
( DRTT_DIST_910_V1_FDIM.DIST_4_NAME IN
('ALIDI RYBINSK','ALIDI NIZHNIY NOVGOROD','ALIDI PENZA','ALIDI SARANSK','ALIDI IVANOVO','ALIDI YAROSLAVL / KOSTROMA','ALIDI ULYANOVSK','ALIDI VLADIMIR','ALIDI KOSTROMA','ALIDI RYAZAN','ALIDI CENTER HQ') )
AND
( DRTT_GEO_707_FDIM.GEO_5_NAME IN ('RUSSIA/BELARUS') )
AND
(
(
( 'L3M'='L3M' )
AND
DRTT_SKU_LIVE_IFCT.STORE_DIST_P3M_IND > 0
)
OR
(
( 'L3M'='CM' )
AND
DRTT_SKU_LIVE_IFCT.STORE_DIST_IND > 0
)
OR
(
( 'L3M'='L2M' )
AND
DRTT_SKU_LIVE_IFCT.STORE_DIST_P2M_IND > 0
)
)
AND
DRTT_PROD_LOC_V1_FDIM.PROD_1_ENGLH_NAME = 'Beauty Care'
AND
DRTT_PROD_LOC_V1_FDIM.PROD_3_ENGLH_NAME = 'Shampoos/ Conditioners/ Treatments'
)
GROUP BY
DRTT_CAL_679_FDIM.MTH_NAME,
DRTT_GEO_707_FDIM.GEO_5_NAME,
DRTT_CHANL_658_FDIM.TRADE_CHANL_1_NAME,
DRTT_PROD_LOC_V1_FDIM.PROD_1_ENGLH_NAME,
DRTT_PROD_LOC_V1_FDIM.PROD_3_ENGLH_NAME,
DRTT_PROD_LOC_V1_FDIM.PROD_5_ENGLH_NAME
====================================
SELECT ---56 sec
DRTT_CAL_679_FDIM.MTH_NAME,
DRTT_GEO_707_FDIM.GEO_5_NAME,
DRTT_CHANL_658_FDIM.TRADE_CHANL_1_NAME,
DRTT_PROD_LOC_V1_FDIM.PROD_1_ENGLH_NAME,
COUNT(DISTINCT DRTT_SKU_LIVE_IFCT.STORE_ID),
DRTT_PROD_LOC_V1_FDIM.PROD_3_ENGLH_NAME,
DRTT_PROD_LOC_V1_FDIM.PROD_5_ENGLH_NAME
FROM
DRTT_CAL_679_FDIM,
DRTT_GEO_707_FDIM,
DRTT_CHANL_658_FDIM,
DRTT_PROD_LOC_V1_FDIM,
DRTT_SKU_LIVE_IFCT,
DRTT_DIST_910_V1_FDIM
WHERE
( DRTT_GEO_707_FDIM.GEO_8_ID=DRTT_SKU_LIVE_IFCT.GEO_ID )
AND ( DRTT_CHANL_658_FDIM.TRADE_CHANL_3_ID=DRTT_SKU_LIVE_IFCT.TRADE_CHANL_ID )
AND ( DRTT_SKU_LIVE_IFCT.PROD_ID=DRTT_PROD_LOC_V1_FDIM.PROD_9_ID )
AND ( DRTT_CAL_679_FDIM.DAY_DATE=DRTT_SKU_LIVE_IFCT.PRTTN_BY_FUNC_DATE )
AND ( DRTT_DIST_910_V1_FDIM.DIST_4_ID=DRTT_SKU_LIVE_IFCT.DIST_ID )
AND
(
( DRTT_CAL_679_FDIM.DAY_DATE=CASE WHEN 'AUG2010'='Current Month' THEN Last_day(to_date(('01'||upper(to_char(sysdate,'monyyyy'))),'ddmonyyyy')) ELSE last_day(to_date(('01'||'AUG2010'),'ddmonyyyy')) END OR DRTT_CAL_679_FDIM.DAY_DATE=CASE WHEN 'AUG2010'='Current Month' THEN Last_day(add_months(to_date(('01'||upper(to_char(sysdate,'monyyyy'))),'ddmonyyyy'),-1)) ELSE last_day(add_months(to_date(('01'||'AUG2010'),'ddmonyyyy'),-1)) END OR DRTT_CAL_679_FDIM.DAY_DATE=CASE WHEN 'AUG2010'='Current Month' THEN Last_day(add_months(to_date(('01'||upper(to_char(sysdate,'monyyyy'))),'ddmonyyyy'),-2)) ELSE last_day(add_months(to_date(('01'||'AUG2010'),'ddmonyyyy'),-2)) END OR DRTT_CAL_679_FDIM.DAY_DATE=CASE WHEN 'AUG2010'='Current Month' THEN Last_day(add_months(to_date(('01'||upper(to_char(sysdate,'monyyyy'))),'ddmonyyyy'),-3)) ELSE last_day(add_months(to_date(('01'||'AUG2010'),'ddmonyyyy'),-3)) END )
AND
( DRTT_DIST_910_V1_FDIM.DIST_4_NAME IN
('ALIDI RYBINSK','ALIDI NIZHNIY NOVGOROD','ALIDI PENZA','ALIDI SARANSK','ALIDI IVANOVO','ALIDI YAROSLAVL / KOSTROMA','ALIDI ULYANOVSK','ALIDI VLADIMIR','ALIDI KOSTROMA','ALIDI RYAZAN','ALIDI CENTER HQ') )
AND
( DRTT_GEO_707_FDIM.GEO_5_NAME IN ('RUSSIA/BELARUS') )
AND
(
(
( 'L3M'='L3M' )
AND
DRTT_SKU_LIVE_IFCT.STORE_DIST_P3M_IND > 0
)
OR
(
( 'L3M'='CM' )
AND
DRTT_SKU_LIVE_IFCT.STORE_DIST_IND > 0
)
OR
(
( 'L3M'='L2M' )
AND
DRTT_SKU_LIVE_IFCT.STORE_DIST_P2M_IND > 0
)
)
AND
DRTT_PROD_LOC_V1_FDIM.PROD_1_ENGLH_NAME = 'Beauty Care'
AND
DRTT_PROD_LOC_V1_FDIM.PROD_3_ENGLH_NAME = 'Colorants'
)
GROUP BY
DRTT_CAL_679_FDIM.MTH_NAME,
DRTT_GEO_707_FDIM.GEO_5_NAME,
DRTT_CHANL_658_FDIM.TRADE_CHANL_1_NAME,
DRTT_PROD_LOC_V1_FDIM.PROD_1_ENGLH_NAME,
DRTT_PROD_LOC_V1_FDIM.PROD_3_ENGLH_NAME,
DRTT_PROD_LOC_V1_FDIM.PROD_5_ENGLH_NAME

我们看一下第一个SQL语句的执行计划

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 534280334

---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 816 | 48014 (3)| 00:03:07 | | |
| 1 | SORT GROUP BY | | 3 | 816 | 48014 (3)| 00:03:07 | | |
|* 2 | HASH JOIN | | 275 | 74800 | 48012 (3)| 00:03:07 | | |
|* 3 | HASH JOIN | | 275 | 70400 | 47602 (3)| 00:03:06 | | |
| 4 | TABLE ACCESS FULL | DRTT_CHANL_658_FDIM | 72 | 2016 | 3 (0)| 00:00:01 | | |
|* 5 | HASH JOIN | | 275 | 62700 | 47598 (3)| 00:03:06 | | |
|* 6 | TABLE ACCESS FULL | DRTT_DIST_910_V1_FDIM | 34 | 1020 | 5638 (14)| 00:00:22 | | |
|* 7 | HASH JOIN | | 810 | 156K| 41960 (1)| 00:02:44 | | |
| 8 | INLIST ITERATOR | | | | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID | DRTT_CAL_679_FDIM | 4 | 64 | 3 (0)| 00:00:01 | | |
|* 10 | INDEX UNIQUE SCAN | DRTT_CAL_679_FDIM_PK | 4 | | 2 (0)| 00:00:01 | | |
|* 11 | HASH JOIN | | 5061 | 899K| 41957 (1)| 00:02:44 | | |
|* 12 | MAT_VIEW REWRITE ACCESS FULL | PROD_LC_V1_FD_L7MV | 7 | 448 | 23 (5)| 00:00:01 | | |
| 13 | PARTITION RANGE MULTI-COLUMN | | 200K| 22M| 41927 (1)| 00:02:43 |KEY(MC)|KEY(MC)|
|* 14 | MAT_VIEW REWRITE ACCESS BY LOCAL INDEX ROWID| LD_LCP5_MV | 200K| 22M| 41927 (1)| 00:02:43 |KEY(MC)|KEY(MC)|
| 15 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 16 | BITMAP MERGE | | | | | | | |
| 17 | BITMAP KEY ITERATION | | | | | | | |
| 18 | BUFFER SORT | | | | | | | |
|* 19 | MAT_VIEW REWRITE ACCESS FULL | PROD_LC_V1_FD_L7MV | 7 | 448 | 23 (5)| 00:00:01 | | |
|* 20 | BITMAP INDEX RANGE SCAN | LD_LCP5_MV_BX1 | | | | |KEY(MC)|KEY(MC)|
|* 21 | TABLE ACCESS FULL | DRTT_GEO_707_FDIM | 2411 | 38576 | 410 (14)| 00:00:02 | | |
---------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("DRTT_GEO_707_FDIM"."GEO_8_ID"="LD_LCP5_MV"."GEO_ID")
3 - access("DRTT_CHANL_658_FDIM"."TRADE_CHANL_3_ID"="LD_LCP5_MV"."TRADE_CHANL_ID")
5 - access("DRTT_DIST_910_V1_FDIM"."DIST_4_ID"="LD_LCP5_MV"."DIST_ID")
6 - filter("DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI CENTER HQ' OR "DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI IVANOVO' OR
"DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI KOSTROMA' OR "DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI NIZHNIY NOVGOROD' OR
"DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI PENZA' OR "DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI RYAZAN' OR
"DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI RYBINSK' OR "DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI SARANSK' OR
"DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI ULYANOVSK' OR "DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI VLADIMIR' OR
"DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI YAROSLAVL / KOSTROMA')
7 - access("DRTT_CAL_679_FDIM"."DAY_DATE"="LD_LCP5_MV"."PRTTN_BY_FUNC_DATE")
10 - access("DRTT_CAL_679_FDIM"."DAY_DATE"=CASE WHEN ('AUG2010'='Current Month') THEN
LAST_DAY(TO_DATE('01'||UPPER(TO_CHAR(SYSDATE@!,'monyyyy')),'ddmonyyyy')) ELSE TO_DATE(' 2010-08-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') END OR "DRTT_CAL_679_FDIM"."DAY_DATE"=CASE WHEN ('AUG2010'='Current Month') THEN
LAST_DAY(ADD_MONTHS(TO_DATE('01'||UPPER(TO_CHAR(SYSDATE@!,'monyyyy')),'ddmonyyyy'),(-1))) ELSE TO_DATE(' 2010-07-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss') END OR "DRTT_CAL_679_FDIM"."DAY_DATE"=CASE WHEN ('AUG2010'='Current Month') THEN
LAST_DAY(ADD_MONTHS(TO_DATE('01'||UPPER(TO_CHAR(SYSDATE@!,'monyyyy')),'ddmonyyyy'),(-2))) ELSE TO_DATE(' 2010-06-30 00:00:00',
'syyyy-mm-dd hh24:mi:ss') END OR "DRTT_CAL_679_FDIM"."DAY_DATE"=CASE WHEN ('AUG2010'='Current Month') THEN
LAST_DAY(ADD_MONTHS(TO_DATE('01'||UPPER(TO_CHAR(SYSDATE@!,'monyyyy')),'ddmonyyyy'),(-3))) ELSE TO_DATE(' 2010-05-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss') END )
11 - access("PROD_LC_V1_FD_L7MV"."PROD_5_ID"="LD_LCP5_MV"."PROD_ID")
12 - filter("PROD_LC_V1_FD_L7MV"."PROD_3_ENGLH_NAME"='Shampoos/ Conditioners/ Treatments' AND
"PROD_LC_V1_FD_L7MV"."PROD_1_ENGLH_NAME"='Beauty Care')
14 - filter(("LD_LCP5_MV"."PRTTN_BY_FUNC_DATE"=CASE WHEN ('AUG2010'='Current Month') THEN
LAST_DAY(TO_DATE('01'||UPPER(TO_CHAR(SYSDATE@!,'monyyyy')),'ddmonyyyy')) ELSE TO_DATE(' 2010-08-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') END OR "LD_LCP5_MV"."PRTTN_BY_FUNC_DATE"=CASE WHEN ('AUG2010'='Current Month') THEN
LAST_DAY(ADD_MONTHS(TO_DATE('01'||UPPER(TO_CHAR(SYSDATE@!,'monyyyy')),'ddmonyyyy'),(-1))) ELSE TO_DATE(' 2010-07-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss') END OR "LD_LCP5_MV"."PRTTN_BY_FUNC_DATE"=CASE WHEN ('AUG2010'='Current Month') THEN
LAST_DAY(ADD_MONTHS(TO_DATE('01'||UPPER(TO_CHAR(SYSDATE@!,'monyyyy')),'ddmonyyyy'),(-2))) ELSE TO_DATE(' 2010-06-30 00:00:00',
'syyyy-mm-dd hh24:mi:ss') END OR "LD_LCP5_MV"."PRTTN_BY_FUNC_DATE"=CASE WHEN ('AUG2010'='Current Month') THEN
LAST_DAY(ADD_MONTHS(TO_DATE('01'||UPPER(TO_CHAR(SYSDATE@!,'monyyyy')),'ddmonyyyy'),(-3))) ELSE TO_DATE(' 2010-05-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss') END ) AND "LD_LCP5_MV"."STORE_DIST_P3M_IND">0)
19 - filter("PROD_LC_V1_FD_L7MV"."PROD_3_ENGLH_NAME"='Shampoos/ Conditioners/ Treatments' AND
"PROD_LC_V1_FD_L7MV"."PROD_1_ENGLH_NAME"='Beauty Care')
20 - access("LD_LCP5_MV"."PROD_ID"="PROD_LC_V1_FD_L7MV"."PROD_5_ID")
21 - filter("DRTT_GEO_707_FDIM"."GEO_5_NAME"='RUSSIA/BELARUS')

Note
-----
- star transformation used for this statement

71 rows selected.
执行计划中使用了2个物化视图 LD_LCP5_MV,PROD_LC_V1_FD_L7MV 我们来看看物化视图的定义

PROD_LC_V1_FD_L7MV 这个物化视图 其实就是下面的SQL语句

SELECT PROD_7_ID AS PROD_7_ID,
PROD_7_ENGLH_NAME AS PROD_7_ENGLH_NAME,
PROD_7_RUS_LONG_NAME AS PROD_7_RUS_LONG_NAME,
PROD_1_ID AS PROD_1_ID,
PROD_1_ENGLH_NAME AS PROD_1_ENGLH_NAME,
PROD_1_RUS_LONG_NAME AS PROD_1_RUS_LONG_NAME,
PROD_2_ID AS PROD_2_ID,
PROD_2_ENGLH_NAME AS PROD_2_ENGLH_NAME,
PROD_2_RUS_LONG_NAME AS PROD_2_RUS_LONG_NAME,
PROD_3_ID AS PROD_3_ID,
PROD_3_ENGLH_NAME AS PROD_3_ENGLH_NAME,
PROD_3_RUS_LONG_NAME AS PROD_3_RUS_LONG_NAME,
PROD_4_ID AS PROD_4_ID,
PROD_4_ENGLH_NAME AS PROD_4_ENGLH_NAME,
PROD_4_RUS_LONG_NAME AS PROD_4_RUS_LONG_NAME,
PROD_5_ID AS PROD_5_ID,
PROD_5_ENGLH_NAME AS PROD_5_ENGLH_NAME,
PROD_5_RUS_LONG_NAME AS PROD_5_RUS_LONG_NAME,
PROD_6_ID AS PROD_6_ID,
PROD_6_ENGLH_NAME AS PROD_6_ENGLH_NAME,
PROD_6_RUS_LONG_NAME AS PROD_6_RUS_LONG_NAME
FROM DRTT_PROD_LOC_V1_FDIM
GROUP BY PROD_7_ID,
PROD_7_ENGLH_NAME,
PROD_7_RUS_LONG_NAME,
PROD_1_ID,
PROD_1_ENGLH_NAME,
PROD_1_RUS_LONG_NAME,
PROD_2_ID,
PROD_2_ENGLH_NAME,
PROD_2_RUS_LONG_NAME,
PROD_3_ID,
PROD_3_ENGLH_NAME,
PROD_3_RUS_LONG_NAME,
PROD_4_ID,
PROD_4_ENGLH_NAME,
PROD_4_RUS_LONG_NAME,
PROD_5_ID,
PROD_5_ENGLH_NAME,
PROD_5_RUS_LONG_NAME,
PROD_6_ID,
PROD_6_ENGLH_NAME,
PROD_6_RUS_LONG_NAME

SQL> select /*+ full(a) */ count(*) from DRTT_PROD_LOC_V1_FDIM a;

COUNT(*)
----------
11604

Elapsed: 00:00:00.68

让我很郁闷的就是 DRTT_PROD_LOC_V1_FDIM表这么小，才1万多数据 居然也弄个物化视图

LD_LCP5_MV 这个物化视图其实就是下面的SQL语句

SELECT SKUIFCT.TIME_PERD_START_DATE,
SKUIFCT.PRTTN_BY_FUNC_DATE,
SKUIFCT.SRCE_SYS_ID,
SKUIFCT.GEO_ID,
SKUIFCT.TRADE_CHANL_ID,
SKUIFCT.DIST_ID,
SKUIFCT.STORE_ID,
PROD.PROD_5_ID AS PROD_ID,
SKUIFCT.REGN_ID,
SKUIFCT.PROD_CSU_TYPE_CODE,
SKUIFCT.STORE_DIST_IND,
SKUIFCT.STORE_DIST_P2M_IND,
SKUIFCT.STORE_DIST_P3M_IND
FROM DRTT_SKU_LIVE_IFCT SKUIFCT, DRTT_PROD_LOC_V1_FDIM PROD
WHERE SKUIFCT.PROD_ID = PROD.PROD_9_ID
GROUP BY SKUIFCT.TIME_PERD_START_DATE,
SKUIFCT.PRTTN_BY_FUNC_DATE,
SKUIFCT.SRCE_SYS_ID,
SKUIFCT.GEO_ID,
SKUIFCT.TRADE_CHANL_ID,
SKUIFCT.DIST_ID,
SKUIFCT.STORE_ID,
PROD.PROD_5_ID,
SKUIFCT.REGN_ID,
SKUIFCT.PROD_CSU_TYPE_CODE,
SKUIFCT.STORE_DIST_IND,
SKUIFCT.STORE_DIST_P2M_IND,
SKUIFCT.STORE_DIST_P3M_IND

DRTT_PROD_LOC_V1_FDIM 这个表已经查过了 1万多数据

SQL> select /*+ full(a)*/ count(*) from DRTT_SKU_LIVE_IFCT a;

COUNT(*)
----------
159416825

Elapsed: 00:03:35.18

DRTT_SKU_LIVE_IFCT 这个表有1亿多数据，不过全表扫描3分钟就可以出结果

执行计划中还用了LD_LCP5_MV_BX1 这个索引 我们来看一下这个位图索引定义
CREATE BITMAP INDEX "ADWGD_DRTT"."LD_LCP5_MV_BX1" ON "ADWGD_DRTT"."LD_LCP5_MV"
("PROD_ID", "GEO_ID", "DIST_ID", "SRCE_SYS_ID", "TRADE_CHANL_ID")

太牛了，居然是个组合位图索引，并且PROD_ID是引导列，我们看一下位图索引的filter 信息
20 - access("LD_LCP5_MV"."PROD_ID"="PROD_LC_V1_FD_L7MV"."PROD_5_ID")

其实这个位图索引是建立在物化视图LD_LCP5_MV上的，那么用它的引导列和物化视图的PROD_LC_V1_FD_L7MV做等值匹配

请注意观察这个SQL语句，熟悉数据仓库的人都知道DIM表它一般不大，大表一般是FACT表，那么这个SQL查询中就只有一个大表
DRTT_SKU_LIVE_IFCT，并且只会从大表中选出 STORE_ID 这一行数据 这个大表的过滤条件如下
DRTT_GEO_707_FDIM.GEO_8_ID=DRTT_SKU_LIVE_IFCT.GEO_ID
DRTT_CHANL_658_FDIM.TRADE_CHANL_3_ID=DRTT_SKU_LIVE_IFCT.TRADE_CHANL_ID
DRTT_SKU_LIVE_IFCT.PROD_ID=DRTT_PROD_LOC_V1_FDIM.PROD_9_ID
DRTT_CAL_679_FDIM.DAY_DATE=DRTT_SKU_LIVE_IFCT.PRTTN_BY_FUNC_DATE
DRTT_DIST_910_V1_FDIM.DIST_4_ID=DRTT_SKU_LIVE_IFCT.DIST_ID

等等过滤条件，好了我们看看这个大表上哪些列建立了索引

SQL> select index_name,column_name,column_position from dba_ind_columns
2 where table_owner=upper('ADWGD_DRTT') and table_name=upper('DRTT_SKU_LIVE_IFCT');

INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ ---------------
DRTT_SKU_LIVE_IFCT_BX1 TRADE_CHANL_ID 1
DRTT_SKU_LIVE_IFCT_BX2 DIST_ID 1
DRTT_SKU_LIVE_IFCT_BX3 GEO_ID 1
DRTT_SKU_LIVE_IFCT_BX4 PROD_ID 1

这些索引都是bitmap 索引因为我们的命名规范中出现了BX 都是bitmap的意思 有了这些信息，那么我现在关闭查询重写
在看看执行计划
SQL> alter session set QUERY_REWRITE_ENABLED = FALSE ;

Session altered.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 782157820

-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 87 | 16095 | 43888 (5)| 00:02:51 | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9FD767_43D16583 | | | | | | |
|* 3 | TABLE ACCESS FULL | DRTT_DIST_910_V1_FDIM | 34 | 1020 | 5638 (14)| 00:00:22 | | |
| 4 | LOAD AS SELECT | SYS_TEMP_0FD9FD767_43D16583 | | | | | | |
|* 5 | TABLE ACCESS FULL | DRTT_PROD_LOC_V1_FDIM | 438 | 25842 | 168 (2)| 00:00:01 | | |
| 6 | LOAD AS SELECT | SYS_TEMP_0FD9FD767_43D16583 | | | | | | |
|* 7 | TABLE ACCESS FULL | DRTT_GEO_707_FDIM | 2411 | 38576 | 410 (14)| 00:00:02 | | |
| 8 | SORT GROUP BY | | 87 | 16095 | 37672 (3)| 00:02:27 | | |
|* 9 | HASH JOIN | | 87 | 16095 | 37671 (3)| 00:02:27 | | |
|* 10 | HASH JOIN | | 87 | 14703 | 37667 (3)| 00:02:27 | | |
|* 11 | HASH JOIN | | 87 | 12267 | 37663 (3)| 00:02:27 | | |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9FD765_43D16583 | 34 | 374 | 2 (0)| 00:00:01 | | |
|* 13 | HASH JOIN | | 257 | 33410 | 37661 (3)| 00:02:27 | | |
| 14 | INLIST ITERATOR | | | | | | | |
| 15 | TABLE ACCESS BY INDEX ROWID | DRTT_CAL_679_FDIM | 4 | 64 | 3 (0)| 00:00:01 | | |
|* 16 | INDEX UNIQUE SCAN | DRTT_CAL_679_FDIM_PK | 4 | | 2 (0)| 00:00:01 | | |
|* 17 | HASH JOIN | | 1542 | 171K| 37657 (3)| 00:02:27 | | |
| 18 | TABLE ACCESS FULL | SYS_TEMP_0FD9FD766_43D16583 | 438 | 25842 | 2 (0)| 00:00:01 | | |
| 19 | PARTITION RANGE MULTI-COLUMN | | 9911 | 532K| 37654 (3)| 00:02:27 |KEY(MC)|KEY(MC)|
|* 20 | TABLE ACCESS BY LOCAL INDEX ROWID| DRTT_SKU_LIVE_IFCT | 9911 | 532K| 37654 (3)| 00:02:27 |KEY(MC)|KEY(MC)|
| 21 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 22 | BITMAP AND | | | | | | | |
| 23 | BITMAP MERGE | | | | | | | |
| 24 | BITMAP KEY ITERATION | | | | | | | |
| 25 | BUFFER SORT | | | | | | | |
| 26 | TABLE ACCESS FULL | SYS_TEMP_0FD9FD765_43D16583 | 1 | 9 | 2 (0)| 00:00:01 | | |
|* 27 | BITMAP INDEX RANGE SCAN | DRTT_SKU_LIVE_IFCT_BX2 | | | | |KEY(MC)|KEY(MC)|
| 28 | BITMAP MERGE | | | | | | | |
| 29 | BITMAP KEY ITERATION | | | | | | | |
| 30 | BUFFER SORT | | | | | | | |
| 31 | TABLE ACCESS FULL | SYS_TEMP_0FD9FD767_43D16583 | 1 | 9 | 2 (0)| 00:00:01 | | |
|* 32 | BITMAP INDEX RANGE SCAN | DRTT_SKU_LIVE_IFCT_BX3 | | | | |KEY(MC)|KEY(MC)|
| 33 | BITMAP MERGE | | | | | | | |
| 34 | BITMAP KEY ITERATION | | | | | | | |
| 35 | BUFFER SORT | | | | | | | |
| 36 | TABLE ACCESS FULL | SYS_TEMP_0FD9FD766_43D16583 | 1 | 9 | 2 (0)| 00:00:01 | | |
|* 37 | BITMAP INDEX RANGE SCAN | DRTT_SKU_LIVE_IFCT_BX4 | | | | |KEY(MC)|KEY(MC)|
| 38 | TABLE ACCESS FULL | DRTT_CHANL_658_FDIM | 72 | 2016 | 3 (0)| 00:00:01 | | |
| 39 | TABLE ACCESS FULL | SYS_TEMP_0FD9FD767_43D16583 | 2411 | 38576 | 3 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI CENTER HQ' OR "DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI IVANOVO'
OR "DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI KOSTROMA' OR "DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI NIZHNIY NOVGOROD' OR
"DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI PENZA' OR "DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI RYAZAN' OR
"DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI RYBINSK' OR "DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI SARANSK' OR
"DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI ULYANOVSK' OR "DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI VLADIMIR' OR
"DRTT_DIST_910_V1_FDIM"."DIST_4_NAME"='ALIDI YAROSLAVL / KOSTROMA')
5 - filter("DRTT_PROD_LOC_V1_FDIM"."PROD_3_ENGLH_NAME"='Shampoos/ Conditioners/ Treatments' AND
"DRTT_PROD_LOC_V1_FDIM"."PROD_1_ENGLH_NAME"='Beauty Care')
7 - filter("DRTT_GEO_707_FDIM"."GEO_5_NAME"='RUSSIA/BELARUS')
9 - access("C0"="DRTT_SKU_LIVE_IFCT"."GEO_ID")
10 - access("DRTT_CHANL_658_FDIM"."TRADE_CHANL_3_ID"="DRTT_SKU_LIVE_IFCT"."TRADE_CHANL_ID")
11 - access("C0"="DRTT_SKU_LIVE_IFCT"."DIST_ID")
13 - access("DRTT_CAL_679_FDIM"."DAY_DATE"="DRTT_SKU_LIVE_IFCT"."PRTTN_BY_FUNC_DATE")
16 - access("DRTT_CAL_679_FDIM"."DAY_DATE"=CASE WHEN ('AUG2010'='Current Month') THEN
LAST_DAY(TO_DATE('01'||UPPER(TO_CHAR(SYSDATE@!,'monyyyy')),'ddmonyyyy')) ELSE TO_DATE(' 2010-08-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') END OR "DRTT_CAL_679_FDIM"."DAY_DATE"=CASE WHEN ('AUG2010'='Current Month') THEN
LAST_DAY(ADD_MONTHS(TO_DATE('01'||UPPER(TO_CHAR(SYSDATE@!,'monyyyy')),'ddmonyyyy'),(-1))) ELSE TO_DATE(' 2010-07-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss') END OR "DRTT_CAL_679_FDIM"."DAY_DATE"=CASE WHEN ('AUG2010'='Current Month') THEN
LAST_DAY(ADD_MONTHS(TO_DATE('01'||UPPER(TO_CHAR(SYSDATE@!,'monyyyy')),'ddmonyyyy'),(-2))) ELSE TO_DATE(' 2010-06-30 00:00:00',
'syyyy-mm-dd hh24:mi:ss') END OR "DRTT_CAL_679_FDIM"."DAY_DATE"=CASE WHEN ('AUG2010'='Current Month') THEN
LAST_DAY(ADD_MONTHS(TO_DATE('01'||UPPER(TO_CHAR(SYSDATE@!,'monyyyy')),'ddmonyyyy'),(-3))) ELSE TO_DATE(' 2010-05-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss') END )
17 - access("DRTT_SKU_LIVE_IFCT"."PROD_ID"="C0")
20 - filter(("DRTT_SKU_LIVE_IFCT"."PRTTN_BY_FUNC_DATE"=CASE WHEN ('AUG2010'='Current Month') THEN
LAST_DAY(TO_DATE('01'||UPPER(TO_CHAR(SYSDATE@!,'monyyyy')),'ddmonyyyy')) ELSE TO_DATE(' 2010-08-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') END OR "DRTT_SKU_LIVE_IFCT"."PRTTN_BY_FUNC_DATE"=CASE WHEN ('AUG2010'='Current Month') THEN
LAST_DAY(ADD_MONTHS(TO_DATE('01'||UPPER(TO_CHAR(SYSDATE@!,'monyyyy')),'ddmonyyyy'),(-1))) ELSE TO_DATE(' 2010-07-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss') END OR "DRTT_SKU_LIVE_IFCT"."PRTTN_BY_FUNC_DATE"=CASE WHEN ('AUG2010'='Current Month') THEN
LAST_DAY(ADD_MONTHS(TO_DATE('01'||UPPER(TO_CHAR(SYSDATE@!,'monyyyy')),'ddmonyyyy'),(-2))) ELSE TO_DATE(' 2010-06-30 00:00:00',
'syyyy-mm-dd hh24:mi:ss') END OR "DRTT_SKU_LIVE_IFCT"."PRTTN_BY_FUNC_DATE"=CASE WHEN ('AUG2010'='Current Month') THEN
LAST_DAY(ADD_MONTHS(TO_DATE('01'||UPPER(TO_CHAR(SYSDATE@!,'monyyyy')),'ddmonyyyy'),(-3))) ELSE TO_DATE(' 2010-05-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss') END ) AND "DRTT_SKU_LIVE_IFCT"."STORE_DIST_P3M_IND">0)
27 - access("DRTT_SKU_LIVE_IFCT"."DIST_ID"="C0")
32 - access("DRTT_SKU_LIVE_IFCT"."GEO_ID"="C0")
37 - access("DRTT_SKU_LIVE_IFCT"."PROD_ID"="C0")

Note
-----
- star transformation used for this statement

89 rows selected.

这个执行计划用了DRTT_SKU_LIVE_IFCT表的3个位图索引 避免了大表的全表扫描，我们现在跑一下SQL语句

--------省略-----------------
265 rows selected.

Elapsed: 00:01:25.55

关闭查询重写之后，SQL可以在1分25秒左右完成 相比以前的9分11秒 性能有了大为提升

由于我不清楚业务逻辑，但那2个物化视图应该不是专门为这个SQL语句建立的
我想说的就是: 有时候，业务需要，为某个业务建立了物化视图，在OLAP中另外的业务，可能会利用到这个物化视图，
然而性能不一定能得到提升，具体情况具体分析。