asked on

Index not used on materialized View

Oracle v 9.2 on Linux
I created the following MV (300 million rows, 33 partitions)
It is partitioned on wk_idnt a partition consists of 4 weeks. I took the indexes from the detailed tables and palced them on the MV see below. They are all local indexes.
When I run a query with query rewrite= true it is slower then when it is set to false. Ie it takes 13 mins to get get data from MV and 2 mins from joining the tables. The explain plan for rewrite off shows the indexes on the tables are been used but with query rewrite on it does a table scan. Both the MV the tables and indexes have recent stats. Any idea why the indexes would not e used on the MV?

SELECT dbms_mview.pmarker (a.ROWID) AS pmarker,
a.ROWID a_rowid,
b.ROWID b_rowid,
c.ROWID c_rowid,
d.ROWID d_rowid,
a.item_key,
a.loc_key,
a.supp_key,
a.wk_idnt,
a.rtl_type_key,
a.dm_recd_load_dt,
a.f_sls_amt,
a.f_sls_qty,
a.f_rtrn_amt,
a.f_rtrn_qty,
a.f_sls_prft_amt,
a.f_rtrn_prft_amt,
a.f_sls_is_mkdn_amt,
a.f_rtrn_is_mkdn_amt,
a.f_i_rcpts_qty,
a.f_i_rcpts_cost_amt,
a.f_i_rcpts_rtl_amt,
a.f_i_tsf_in_qty,
a.f_i_tsf_in_cost_amt,
a.f_i_tsf_in_rtl_amt,
a.f_i_tsf_out_qty,
a.f_i_tsf_out_cost_amt,
a.f_i_tsf_out_rtl_amt,
a.f_i_rtv_qty,
a.f_i_rtv_cost_amt,
a.f_i_rtv_rtl_amt,
a.f_mkdn_amt,
a.f_mkdn_qty,
a.f_mkup_amt,
a.f_mkup_qty,
a.f_mkup_cancel_amt,
a.f_mkup_cancel_qty,
b.supp_prt_nbr,
c.dept_idnt,
c.dept_key,
c.dept_desc,
c.grp_idnt,
c.grp_key,
c.grp_desc,
c.sbclass_key,
c.class_key,
c.item_idnt,
c.item_desc,
c.level1_key,
d.mth_idnt,
d.QTR_IDNT,
d.HALF_IDNT,
d.YR_IDNT
FROM rdw10dm.nex_item_supp_lw_dm a,
rdw10dm.nex_prod_item_supp_dm b,
rdw10dm.prod_item_dm c,
TIME_WK_DM d
WHERE a.item_key = b.item_key(+)
AND a.item_key = c.item_key(+)
AND a.supp_key = b.supp_key(+)
AND a.wk_idnt = d.wk_idnt

CREATE UNIQUE INDEX PK_NEX_ISPLW_DM_4_MV ON NEX_ITEM_SUPP_PROD_LW_DM_4_MV
(ITEM_KEY, LOC_KEY, SUPP_KEY, WK_IDNT, RTL_TYPE_KEY)
NOLOGGING

PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT );

CREATE INDEX X_DEPT_KEY_ISPLW_DM_4_MV ON NEX_ITEM_SUPP_PROD_LW_DM_4_MV
(DEPT_KEY)
NOLOGGING

PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT );

CREATE INDEX X_LEVEL1_KEY_NEX_ISPLW_DM_4_MV ON NEX_ITEM_SUPP_PROD_LW_DM_4_MV
(LEVEL1_KEY)
NOLOGGING

PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT );

CREATE INDEX X_LOC_KEY_NEX_ISPLW_DM_4_MV ON NEX_ITEM_SUPP_PROD_LW_DM_4_MV
(LOC_KEY)
NOLOGGING

PARALLEL ( DEGREE 20 INSTANCES 2 );

CREATE INDEX X_NEX_ISPLW_DM_4_MV ON NEX_ITEM_SUPP_PROD_LW_DM_4_MV
(SUPP_KEY, WK_IDNT)
NOLOGGING

PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT );

CREATE INDEX X_WK_IDNT_NEX_ISPLW_DM_4_MV ON NEX_ITEM_SUPP_PROD_LW_DM_4_MV
(WK_IDNT)
NOLOGGING

PARALLEL ( DEGREE 20 INSTANCES 2 );

Query run.
select a13.YR_IDNT YR_IDNT,
a11.SUPP_KEY SUPP_KEY,
a11.LOC_KEY LOC_KEY,
a12.DEPT_KEY DEPT_KEY,
(sum(a11.F_SLS_AMT) - sum(a11.F_RTRN_AMT)) WJXBFS1,
(sum(a11.F_SLS_QTY) - sum(a11.F_RTRN_QTY)) WJXBFS2,
(((sum(a11.F_I_RCPTS_COST_AMT) + sum(a11.F_I_TSF_IN_COST_AMT)) - sum(a11.F_I_TSF_OUT_COST_AMT)) - sum(a11.F_I_RTV_COST_AMT)) WJXBFS3,
(((sum(a11.F_I_RCPTS_RTL_AMT) + sum(a11.F_I_TSF_IN_RTL_AMT)) - sum(a11.F_I_TSF_OUT_RTL_AMT)) - sum(a11.F_I_RTV_RTL_AMT)) WJXBFS4,
(((sum(a11.F_I_RCPTS_QTY) + sum(a11.F_I_TSF_IN_QTY)) - sum(a11.F_I_TSF_OUT_QTY)) - sum(a11.F_I_RTV_QTY)) WJXBFS5,
(sum(a11.F_SLS_IS_MKDN_AMT) - sum(a11.F_RTRN_IS_MKDN_AMT)) WJXBFS6,
sum(a11.F_MKDN_AMT) WJXBFS7,
(sum(a11.F_SLS_PRFT_AMT) - sum(a11.F_RTRN_PRFT_AMT)) WJXBFS8
from NEX_ITEM_SUPP_LW_DM a11,
PROD_ITEM_DM a12,
TIME_WK_DM a13
where a11.ITEM_KEY = a12.ITEM_KEY and
a11.WK_IDNT = a13.WK_IDNT
and (a11.WK_IDNT in (200731, 200732, 200733, 200734, 200735)
and a12.DEPT_KEY in (40)
and a11.LOC_KEY in (672))
group by a13.YR_IDNT,
a11.SUPP_KEY,
a11.LOC_KEY,
a12.DEPT_KEY

Explain Plan. Rewrite On

Operation      Object Name      Rows      Bytes      Cost      Object Node      In/Out      PStart      PStop

SELECT STATEMENT Optimizer Mode=CHOOSE            1             15
SORT GROUP BY            1      79      15      :Q5349001       P->S        QC (RANDOM)
SORT GROUP BY            1      79      15      :Q5349000       P->P        HASH
PARTITION RANGE INLIST                                :Q5349000       PCWP        KEY(I)      KEY(I)
TABLE ACCESS FULL      RDW10DM.NEX_ITEM_SUPP_PROD_LW_DM_4_MV      1      79      1      :Q5349000       PCWP        KEY(I)      KEY(I)

Explain Plan. Rewrite Off

Operation      Object Name      Rows      Bytes      Cost      Object Node      In/Out      PStart      PStop

SELECT STATEMENT Optimizer Mode=CHOOSE            2 K             3714
SORT GROUP BY            2 K      251 K      3714
HASH JOIN            551 K      55 M      2578
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID      RDW10DM.TIME_WK_DM      5      45      2
INDEX RANGE SCAN      RDW10DM.PK_TIME_WK_DM      1             1
HASH JOIN            556 K      50 M      2575
TABLE ACCESS BY INDEX ROWID      RDW10DM.PROD_ITEM_DM      33 K      322 K      2187
INDEX RANGE SCAN      RDW10DM.PI_DM_DEPT_KEY_IDX      33 K             25
PARTITION RANGE INLIST                                                KEY(I)      KEY(I)
TABLE ACCESS BY LOCAL INDEX ROWID      RDW10DM.NEX_ITEM_SUPP_LW_DM      556 K      45 M      384                      KEY(I)      KEY(I)
INDEX RANGE SCAN      RDW10DM.X_NEX_ITEM_SUPP_LW_LOC      31             3                      KEY(I)      KEY(I)

SOLUTION

mganesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dplinnane

ASKER

The table spaces are only a few weeks old there is no fragmentation on MVIEWS_DATA, MVIEWS_INDEX or MVIEWS_LOG tablespaces.

dplinnane

ASKER

Just running the query on week
and a11.WK_IDNT in (200714)

uses the index on two weeks the MV is not used.
and a11.WK_IDNT in (200714, 200715)

JUst running the query with no weeks PLAN 3

Plan 1 WEEK
SELECT STATEMENT CHOOSECost: 96 Bytes: 4,515 Cardinality: 43
      11 SORT GROUP BY Cost: 96 Bytes: 4,515 Cardinality: 43
            10 NESTED LOOPS Cost: 93 Bytes: 4,515 Cardinality: 43
                  7 HASH JOIN Cost: 7 Bytes: 4,085 Cardinality: 43
                        3 INLIST ITERATOR
                              2 TABLE ACCESS BY INDEX ROWID RDW10DM.TIME_WK_DM Cost: 2 Bytes: 18 Cardinality: 2
                                    1 INDEX RANGE SCAN UNIQUE RDW10DM.PK_TIME_WK_DM Cost: 1 Cardinality: 1
                        6 PARTITION RANGE INLIST Partition #: 7 Partitions accessed #KEY(INLIST)
                              5 TABLE ACCESS BY LOCAL INDEX ROWID RDW10DM.NEX_ITEM_SUPP_LW_DM Cost: 4 Bytes: 267,374 Cardinality: 3,109 Partition #: 7 Partitions accessed #KEY(INLIST)
                                    4 INDEX RANGE SCAN NON-UNIQUE RDW10DM.X_NEX_ITEM_SUPP_LW_LOC Cost: 3 Cardinality: 226,955 Partition #: 7 Partitions accessed #KEY(INLIST)
                  9 TABLE ACCESS BY INDEX ROWID RDW10DM.PROD_ITEM_DM Cost: 2 Bytes: 10 Cardinality: 1
                        8 INDEX UNIQUE SCAN UNIQUE RDW10DM.PK_PROD_I_DM Cost: 1 Cardinality: 1

##############################################
Plan 2 WEEKS
SELECT STATEMENT CHOOSECost: 153 Bytes: 970 Cardinality: 10
      5 SORT GROUP BY Cost: 153 Bytes: 970 Cardinality: 10
            4 TABLE ACCESS BY GLOBAL INDEX ROWID RDW10DM.NEX_ITEM_SUPP_PROD_LW_DM_4_MV Cost: 149 Bytes: 2,716 Cardinality: 28 Partition #: 2 Partitions accessed #28
                  3 AND-EQUAL
                        1 INDEX RANGE SCAN NON-UNIQUE RDW10DM.X_LOC_KEY_NEX_ISPLW_DM_4_MV Partition #: 4 Partitions accessed #28
                        2 INDEX RANGE SCAN NON-UNIQUE RDW10DM.X_DEPT_KEY_ISPLW_DM_4_MV Partition #: 5 Partitions accessed #28

#################################################
PLAN 3
Plan
SELECT STATEMENT CHOOSECost: 2,071 Bytes: 147,238 Cardinality: 1,618
      9 SORT GROUP BY Cost: 2,071 Bytes: 147,238 Cardinality: 1,618
            8 PARTITION RANGE ALL Partition #: 2 Partitions accessed #1 - #33
                  7 TABLE ACCESS BY LOCAL INDEX ROWID RDW10DM.NEX_ITEM_SUPP_PROD_LW_DM_4_MV Cost: 2,067 Bytes: 249,886 Cardinality: 2,746 Partition #: 2 Partitions accessed #1 - #33
                        6 BITMAP CONVERSION TO ROWIDS
                              5 BITMAP AND
                                    2 BITMAP CONVERSION FROM ROWIDS
                                          1 INDEX RANGE SCAN NON-UNIQUE RDW10DM.X_LOC_KEY_NEX_ISPLW_DM_4_MV Cost: 337 Partition #: 2 Partitions accessed #1 - #33
                                    4 BITMAP CONVERSION FROM ROWIDS
                                          3 INDEX RANGE SCAN NON-UNIQUE RDW10DM.X_DEPT_KEY_ISPLW_DM_4_MV Cost: 703 Partition #: 2 Partitions accessed #1 - #33

ASKER CERTIFIED SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Akenathon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dplinnane

ASKER

Very bad clustering was the problem. I also used a bitmap index instead as the column had low cardinality. The explain plan shows the bitmap index being used. Stats were carried out on all indexes and tables.