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

gaurav · Apr 17, 2015 6:59AM

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

AndrewSayer · Apr 19, 2015 5:50AM

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.

Dom Brooks · Apr 17, 2015 8:45AM

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 · 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.

Martin Preiss · 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.

Deepak Mahto · Apr 18, 2015 1:00AM

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..

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

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

| 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 :

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

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

| 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 · Apr 19, 2015 5:50AM

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.

Martin Preiss · 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 · 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 · 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 · 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 · 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

select rownum sr_no, c.* from (

mvview query1 

union all 

mview query2) c;

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

Forum Stats

Quick Links

Categories

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

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

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

Best Answer

Answers