Forum Stats

  • 3,815,636 Users
  • 2,259,064 Discussions
  • 7,893,194 Comments

Discussions

View doesn't use index on underlying Mview but mview uses index

gaurav
gaurav Member Posts: 98
edited Apr 21, 2015 6:37AM in General Database Discussions

Hi,

I faced a situation where while querying data from view based on mview does not use index but when we query from mview it uses index and give fast results.

Following are the details:

querying from view: (completes in more than a minute even after passing hint)
================================
explain plan for select /*+ index(  ITGI_CREDITOR_LEDGER_CMV ITGI_CREDITOR_LEDGER_INDX) */ * from CR_LEDGER_REP_V where vendor_id='1474911';

Plan hash value: 463951623

---------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                          |  9056K|  5648M| 97644   (1)| 00:00:04 |
|*  1 |  VIEW                  | CR_LEDGER_REP_V          |  9056K|  5648M| 97644   (1)| 00:00:04 |
|   2 |   COUNT                |                          |       |       |            |          |
|   3 |    MAT_VIEW ACCESS FULL| ITGI_CREDITOR_LEDGER_CMV |  9056K|  2159M| 97644   (1)| 00:00:04 |
---------------------------------------------------------------------------------------------------

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

   1 - filter("VENDOR_ID"=1474911)



Querying from mview: (completes within a second)
==============================

explain plan for select /*+ index(  ITGI_CREDITOR_LEDGER_CMV ITGI_CREDITOR_LEDGER_INDX) */ * from ITGI_CREDITOR_LEDGER_CMV where vendor_id='1474911';


Plan hash value: 1726512073

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                           |    21 |  5880 |    21   (0)| 00:00:01 |
|   1 |  MAT_VIEW ACCESS BY INDEX ROWID BATCHED| ITGI_CREDITOR_LEDGER_CMV  |    21 |  5880 |    21   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                     | ITGI_CREDITOR_LEDGER_INDX |    22 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

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

   2 - access("VENDOR_ID"=1474911)



View DDL
========================

CREATE OR REPLACE FORCE VIEW SANJAY.CR_LEDGER_REP_V
(
   QRY,
   SR_NO,
   VENDOR_ID,
   VENDOR_TYPE,
   VENDOR_NUMBER,
   VENDOR_NAME,
   VENDOR_SITE_ID,
   VENDOR_SITE_CODE,
   GL_DATE,
   ACCOUNTING_DATE,
   PO,
   INVOICE_TYPE,
   AP_INVOICE_NO,
   INVOICE_DOC_CAT,
   AP_INVOICE_DATE,
   INVOICE_VOUCHER_NO,
   PAYMENT_VOUCHER_NO,
   CHQ_NO,
   DESCRIPTION,
   PAYMENT_DATE,
   ACC_CODE,
   PAYMENT_METHOD,
   CURR_CODE,
   DEBIT,
   CREDIT
)
AS
   SELECT qry,
          ROWNUM srn,
          vendor_id,
          vendor_type_lookup_code,
          vendor_no,
          vendor_name,
          vendor_site_id,
          vendor_site_code,
          gl_date,
          accounting_date,
          po_distribution_id,
          invoice_type_lookup_code,
          invoice_num,
          doc_category_code,
          invoice_date,
          inv_voucher_num,
          payment_num,
          check_number,
          description,
          pay_accounting_date,
          account_code,
          payment_method_code,
          invoice_currency_code,
          debit,
          credit
     FROM sanjay.itgi_creditor_ledger_cmv;



Even if I don't use the hints the plan doesn't change.

Though I have told developer to use mview directly, but I could not understand this behaviour.

DB version : 12.1

OS : AIX 6.1

Regards,

Gaurav

Tagged:
gaurav

Best Answer

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Apr 19, 2015 6:18AM Answer ✓

    Note that the query plan against the view is only applying your filter predicates at line 1 rather than against the mview at line 3. Line 2 is telling you that the execution must evaluate some sort of count before it can filter which is due to the rownum pseudocolumn in your view.

    Do you need that column? In my opinion it is meaningless. Removing the column from the view would mean that the view does not need to be evaluated in its entirety (how else will it calculate rownums?) before allowing you to filter. The work around you asked the developer to implement is likely coming up with a different sr_no value (if the developer is even using this?) than what he would have obtained using the view.

    Is the column trying to make up a unique key for the view? Are you aware that rownum values can and will change between queries to the same table as the order the rows are selected is not guaranteed. If the column is actually useful (and it is correct to calculate it at this level) to you then could it make sense to have it calculated inside the mview? This would mean that you can only refresh the mview completely and rownum values can (and will) only change upon refresh.

    gaurav

Answers

  • Dom Brooks
    Dom Brooks Member Posts: 5,558 Silver Crown
    edited Apr 17, 2015 8:55AM

    You could use either the view name/alias in the index hint or view.table or use the query block qualifier n the hint.

    https://docs.oracle.com/cd/E11882_01/server.112/e41573/hintsref.htm#PFGRF50104

    https://docs.oracle.com/cd/E11882_01/server.112/e41573/hintsref.htm#PFGRF50107

    For example:

    drop table t1;
    drop view v1;
    create table t1
    as
    select rownum rn, o.* from dba_objects o;
    alter table t1 modify rn not null;
    create unique index i1 on t1 (rn);
    create or replace view v1 as select * from t1;
    
    explain plan for
    select /*+ index(t1 i1) */ *
    from  v1;
    
    select * from table(dbms_xplan.display);
    
    Plan hash value: 3617692013
    
    --------------------------------------------------------------------------
    | Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time    |
    --------------------------------------------------------------------------
    |  0 | SELECT STATEMENT  |      |  126K|    26M|  468  (1)| 00:00:06 |
    |  1 |  TABLE ACCESS FULL| T1  |  126K|    26M|  468  (1)| 00:00:06 |
    --------------------------------------------------------------------------
    
    explain plan for
    select /*+ index(v1 i1) */ *
    from  v1;
    
    select * from table(dbms_xplan.display);
    
    Plan hash value: 998099937
    
    ------------------------------------------------------------------------------------
    | Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time    |
    ------------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT            |      |  126K|    26M|  2352  (1)| 00:00:29 |
    |  1 |  TABLE ACCESS BY INDEX ROWID| T1  |  126K|    26M|  2352  (1)| 00:00:29 |
    |  2 |  INDEX FULL SCAN          | I1  |  126K|      |  272  (1)| 00:00:04 |
    ------------------------------------------------------------------------------------
    
    explain plan for
    select *
    from  v1;
    
    select * from table(dbms_xplan.display(format => 'ALL'));
    
    --------------------------------------------------------------------------
    | Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time    |
    --------------------------------------------------------------------------
    |  0 | SELECT STATEMENT  |      |  126K|    26M|  468  (1)| 00:00:06 |
    |  1 |  TABLE ACCESS FULL| T1  |  126K|    26M|  468  (1)| 00:00:06 |
    --------------------------------------------------------------------------
    
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
    
      1 - SEL$F5BB74E1 / [email protected]$2
      ...
    
    explain plan for
    select /*+ index([email protected]$2 i1) */ *
    from  v1;
    
    
    select * from table(dbms_xplan.display);
    
    Plan hash value: 998099937
    
    ------------------------------------------------------------------------------------
    | Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time    |
    ------------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT            |      |  126K|    26M|  2352  (1)| 00:00:29 |
    |  1 |  TABLE ACCESS BY INDEX ROWID| T1  |  126K|    26M|  2352  (1)| 00:00:29 |
    |  2 |  INDEX FULL SCAN          | I1  |  126K|      |  272  (1)| 00:00:04 |
    ------------------------------------------------------------------------------------
    
    
    
    
    
    
    
    
    
    explain plan for
    select /*+ index(v1.t1 i1) */ *
    from   v1;
    
    select * from table(dbms_xplan.display);
    
    Plan hash value: 998099937
    
    ------------------------------------------------------------------------------------
    | Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |      |   126K|    26M|  2352   (1)| 00:00:29 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T1   |   126K|    26M|  2352   (1)| 00:00:29 |
    |   2 |   INDEX FULL SCAN           | I1   |   126K|       |   272   (1)| 00:00:04 |
    ------------------------------------------------------------------------------------
    
    
  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy
    edited Apr 17, 2015 9:32AM

    I guess that the rownum predicate (srn) in the view definition prevents the predicate pushing: so the plan shows a full table scan (step 3) then a count operation (step 2) and then the filtering of results. Without the rownum column in the view I would expect the index access to take place.

    gauravgaurav
  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy
    edited Apr 17, 2015 9:41AM

    and to add an example:

    drop table t;
    drop view v1;
    drop view v1;
    
    create table t
    as
    select rownum id
        , lpad('*', 50, '*') col1
      from dual
    connect by level <= 1000; 
    
    create index t_idx on t(id);
    
    create view v1
    as
    select t.*
      from t;
    
    create view v2
    as
    select rownum rn
        , t.*
      from t;
    
    explain plan for
    select * from v1 where id = 1;
    
    select * from table(dbms_xplan.display);
    ---------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                    |       |     1 |    55 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T     |     1 |    55 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN                  | T_IDX |     1 |       |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("T"."ID"=1)
    
    explain plan for
    select * from v2 where id = 1;
    
    select * from table(dbms_xplan.display);
    ----------------------------------------------------------------------------
    | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |      |  1000 | 53000 |     3   (0)| 00:00:01 |
    |*  1 |  VIEW               | V2   |  1000 | 53000 |     3   (0)| 00:00:01 |
    |   2 |   COUNT             |      |       |       |            |          |
    |   3 |    TABLE ACCESS FULL| T    |  1000 | 55000 |     3   (0)| 00:00:01 |
    ----------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
      1 - filter("ID"=1)
    

    I think it's quite plausible that the "correct" rownum has to be gathered before the filtering has been done. Of course "correct" is quite pointless in a result without a defined order.

    gaurav
  • Deepak Mahto
    Deepak Mahto Member Posts: 117
    edited Apr 18, 2015 4:58AM

    Just an Observations..!!

    Frankly speaking, need of hint should not have be arise.

    Very important difference for both plan were "CARDINALITY" estimates for same filter!!

    With out hint :: FULL Table Scan..

    ---------------------------------------------------------------------------------------------------

    | Id  | Operation              | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |

    ---------------------------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT       |                          |  9056K|  5648M| 97644   (1)| 00:00:04 |

    |*  1 |  VIEW                  | CR_LEDGER_REP_V          |  9056K|  5648M| 97644   (1)| 00:00:04 |

    |   2 |   COUNT                |                          |       |       |            |          |

    |   3 |    MAT_VIEW ACCESS FULL| ITGI_CREDITOR_LEDGER_CMV |  9056K|  2159M| 97644   (1)| 00:00:04 |

    ---------------------------------------------------------------------------------------------------

    Cardinality estimate is 9056K rows, so obviously Full table options would have been  better option for optimizer.


    on the other hand for Index Access path :


    --------------------------------------------------------------------------------------------------------------------

    | Id  | Operation                              | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |

    --------------------------------------------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT                       |                           |    21 |  5880 |    21   (0)| 00:00:01 |

    |   1 |  MAT_VIEW ACCESS BY INDEX ROWID BATCHED| ITGI_CREDITOR_LEDGER_CMV  |    21 |  5880 |    21   (0)| 00:00:01 |

    |*  2 |   INDEX RANGE SCAN                     | ITGI_CREDITOR_LEDGER_INDX |    22 |       |     3   (0)| 00:00:01 |

    --------------------------------------------------------------------------------------------------------------------

    Cardinality estimate for same filter change to  22., that an huge change difference as compare to what were for FTS (Full table Scan)

    Please check below, before enforcing hints!

    1. Whether underlying stats were different for both executions.

    2. Stats on component involve (Index, MVIEW table)

    3. Underlying table in View on MVIEW is from other Schema and what your accessing directly is in different Schema..!!

    i.e. we are accessing different objects for both different cases?? (Most imp!)

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Apr 19, 2015 6:18AM Answer ✓

    Note that the query plan against the view is only applying your filter predicates at line 1 rather than against the mview at line 3. Line 2 is telling you that the execution must evaluate some sort of count before it can filter which is due to the rownum pseudocolumn in your view.

    Do you need that column? In my opinion it is meaningless. Removing the column from the view would mean that the view does not need to be evaluated in its entirety (how else will it calculate rownums?) before allowing you to filter. The work around you asked the developer to implement is likely coming up with a different sr_no value (if the developer is even using this?) than what he would have obtained using the view.

    Is the column trying to make up a unique key for the view? Are you aware that rownum values can and will change between queries to the same table as the order the rows are selected is not guaranteed. If the column is actually useful (and it is correct to calculate it at this level) to you then could it make sense to have it calculated inside the mview? This would mean that you can only refresh the mview completely and rownum values can (and will) only change upon refresh.

    gaurav
  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy
    edited Apr 19, 2015 6:54AM

    no, this is not a statistics problem (though most access problems are): it's just a problem with predicate pushing and non-mergeable views using a rownum attribute - as I demonstrated in my example. And it is indeed not unlikely that the rownum attribute is quite meaningless.

  • Deepak Mahto
    Deepak Mahto Member Posts: 117
    edited Apr 19, 2015 10:03AM

    Thanks Sir...!! For correcting me.!!

    Also found a link from ask tom... explaining same..

    https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:3469884600671

  • gaurav
    gaurav Member Posts: 98
    edited Apr 21, 2015 2:33AM

    Thanks for your reply.

    Do you need that column?

    -- Yes, the developer needs the rownum column even if it changes due to requirement in java persistence( I don't know what that means) to have any unique column as we dont have any primary key or unique key.

    Is the column trying to make up a unique key for the view?

    -- Yes


    If the column is actually useful (and it is correct to calculate it at this level) to you then could it make sense to have it calculated inside the mview?

    -- Do you mean to say that I calculate it inside the mview in a column and then access it using view ?


    Regards,

    Gaurav


  • gaurav
    gaurav Member Posts: 98
    edited Apr 21, 2015 2:53AM

    UPDATE:

    If the column is actually useful (and it is correct to calculate it at this level) to you then could it make sense to have it calculated inside the mview?

    -- if you mean to have it calculated inside the mview, but the mview contains unionall. if I use the whole query as subquery, it will reduce the performance I believe. ( I haven't tested yet)



  • gaurav
    gaurav Member Posts: 98
    edited Apr 21, 2015 6:37AM

    Thanks to Martin Preiss and Andrew Sayer, the issue is resolved.

    I have created mview as follows to deal with union all rownum duplicacy, and used it in a view query

    create ITGI_CREDITOR_LEDGER_CMV as

    <span class="kwd">select</span><span class="pln"> rownum sr_no</span><span class="pun">,</span><span class="pln"> c</span><span class="pun">.*</span><span class="pln"><br/></span><span class="kwd">from</span><span class="pln"> </span><span class="pun">(</span>

    <span class="pun">mvview query1 </span>

    <span class="pun">union all </span>

    <span class="pun">mview query2</span><span class="pun">)</span><span class="pln"> c;</span>


    Now they query does index scan instead of full table scan.

    I cannot mark is as answered as actions button not working for me.


    Regards,

    Gaurav

This discussion has been closed.