• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4100
  • Last Modified:

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)
0
dplinnane
Asked:
dplinnane
3 Solutions
 
mganeshCommented:
Before going ahead, can u check for fragmentation on the table and index? They are usually the culprits in large tables + indexes

Regards,
Ganesh
0
 
dplinnaneAuthor Commented:
The table spaces are only a few weeks old there is no fragmentation on MVIEWS_DATA, MVIEWS_INDEX or MVIEWS_LOG tablespaces.
0
 
dplinnaneAuthor Commented:

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


0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
schwertnerCommented:
You have to run the compute statistics procedure over the database
in order to help the Optimizer to know and to use the indexes.
0
 
AkenathonCommented:
The answer you are seeking is *VERY* difficult to get too. MANY parameters, hidden parameters and of course statistics take part on the CBO calculations, which are the ONLY thing that can tell you "Why on earth did the optimizer choose this plan??" If you really want to mess with it, you need to turn on event 10053 for your session, execute the query and then look at the generated tracefile. Not for the faint of heart!!
0
 
dplinnaneAuthor Commented:
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.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now